StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POPHP / Mysql - ORDER BY When Using Temp Tables
text
Body
copied!<p>I've been trying to get this to work using ORDER BY and LIMIT, but it will output everything that was put in. The ORDER BY and LIMIT does not seem to work:</p> <pre><code>$lat1 = 37.349418; $lon1 = -121.896286; $distance = 25; $q = "SELECT * FROM cityname WHERE feature_class = 'A' OR feature_class = 'P'"; $r = mysql_query($q) or die(mysql_error()); while ($row = mysql_fetch_array($r)) { $lat = trim($row["latitude"]); $lon = trim($row["longitude"]); $name = $row["name"]; $pop = $row["population"]; $miles = distance($lat, $lon, $lat1, $lon1, "m"); $milesrounded = round($miles, 2); if ($miles < $distance) { if ($pop > 0) { $q2 = "CREATE TEMPORARY TABLE IF NOT EXISTS `templocation4` ( `cityname` varchar(75) NOT NULL, `distance` double NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1"; $r2 = mysql_query($q2) or die(mysql_error()); $q1 = "INSERT INTO templocation4 (cityname, distance) VALUES ('$name', '$milesrounded')"; $r1 = mysql_query($q1) or die(mysql_error()); $q3 = "SELECT MIN(distance) FROM templocation4 GROUP BY distance DESC LIMIT 10"; $r3 = mysql_query($q3) or die(mysql_error()); while ($row1 = mysql_fetch_array($r3)) { echo $row1["cityname"]; echo " "; echo $row1["distance"]; echo "<br>"; } $q5 = "DROP TABLE templocation4"; $r5 = mysql_query($q5) or die(mysql_error()); } } } </code></pre> <p>The table cityname has > 250K entries and I'm trying to sort it down to the closest city name based on the latitude / longitude that a user has input.</p> <p>Thanks.</p>

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