CODEWITHSUNDEEP
phpmysqlcomparejsonnested-loops
Diolor
Diolor

Reputation: 13450

Compare values of two tables and for results export JSON

I have two different tables. For those I select some results. On the first based on a min/max and for the second based on Lat/Lng. That's easy (don't pay much attention on the values) and is created by:

$sql1="SELECT * FROM events WHERE value BETWEEN '".$min2d."' AND '".$max2d."'";
$sql2="SELECT * FROM locations WHERE (lng BETWEEN ".$wl." AND ".$el.") AND (lat BETWEEN '".$sl."'AND'".$nl."')";

Now that we have downsized the results, we want to match the 'id' row of the first table if exists on the second table. On success we create results.

So let's get some numbers first:

$result1 = mysql_query($sql1);
$result2 = mysql_query($sql2);

$numRows1 = mysql_num_rows($result1);
$numRows2 = mysql_num_rows($result2);

$loopCount1 = 1;
$loopCount2 = 1;

For more efficient parsing of the JSON (from a user) we want to sort the events by creating them into a JSON array with the location as a 'holder'. So that means, each location might have several events.

Some locations might not have events, but also some events might not correspond to a location. Our only comparing method is by the same 'id'.

With my following try, which is of course buggy creates for all (that's wrong) locations results even if for those without events. And that's where I need your precious help.

$json = '{"markers":[';
while ($row2 = mysql_fetch_array($result2)){
    $json .= '{"coordinates": { "lat":'. $row2['lat'] .', "lng" : ' . $row2['lng'] .'},'
            .'{"events" : [';
    while ($row1 = mysql_fetch_array($result1)){
        if ($row1['id']=$row2['id'])
            $json .= '{ '   
                    .'"title": "'.$row1['title'].'",'
                    .'"info": "'.$row1['info'].'",'
                    .'"}';
        // add comma for Json if not final row
        if ($loopCount1 < $numRows1) {
            $json .= ', ';
            $loopCount1++;}
        }
    $json .= ']}}';
    // add comma for Json if not final row
    if ($loopCount2 < $numRows2) {
            $json .= ', ';
            $loopCount2++;}
    }
$json .= ']}';

And finally an echo:

echo $json;

Upvotes: 0

Views: 984

Answers (1)

Crazy4Php
Crazy4Php

Reputation: 246

To compare values of two tables and print results as json please use below code,

<?php
      /* connect to the db */
      $link = mysql_connect('localhost','username','password') or die('Cannot connect to the DB');
      mysql_select_db('db_name',$link) or die('Cannot select the DB');

      /* grab the posts from the db */
      $query1 = "SELECT * FROM test1";
      $result1 = mysql_query($query1,$link) or die('Error query:  '.$query1);

      $query2 = "SELECT * FROM test2";
      $result2 = mysql_query($query2,$link) or die('Error query:  '.$query2);

      /* create one master array of the records */
      $posts = array();
      if(mysql_num_rows($result1) && mysql_num_rows($result2)) {
        while($post1 = mysql_fetch_assoc($result1)) {
            $post2 = mysql_fetch_assoc($result2);
            if($post1['name'] == $post2['name'])
              $posts[] = array('test1'=>$post1,'test2'=>$post2);
        }
      }

     header('Content-type: application/json');
     echo json_encode(array('posts'=>$posts));

      /* disconnect from the db */
      @mysql_close($link);
?>

Upvotes: 5

Related Questions