StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCompare values of two tables and for results export JSON
text
Body
copied!<p>I have <strong>two different tables</strong>. For those I <strong>select some results</strong>. 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:</p> <pre><code>$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."')"; </code></pre> <p>Now that we have downsized the results, we want to <strong>match the <code>'id'</code></strong> row of the first table if <strong>exists</strong> on the second table. On success we create results.</p> <p>So let's get some numbers first:</p> <pre><code>$result1 = mysql_query($sql1); $result2 = mysql_query($sql2); $numRows1 = mysql_num_rows($result1); $numRows2 = mysql_num_rows($result2); $loopCount1 = 1; $loopCount2 = 1; </code></pre> <p>For more efficient parsing of the <strong>JSON</strong> (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. </p> <p><strong>Some locations might not have events</strong>, but also some <strong>events might not correspond to a location</strong>. Our only comparing method is by the same <code>'id'</code>.</p> <p>With my following try, which is of course buggy creates for <em>all</em> (that's wrong) locations results even if for those without events. And that's where I need your precious help.</p> <pre><code>$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 .= ']}'; </code></pre> <p>And finally an echo:</p> <pre><code>echo $json; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload