What i need to do is :
A) query the ZCTA MySQL database (a bunch of zipcodes with Lon and Lat points)
B) For every zipcode returned, it will then search another table looking for that zipcode. So, if there is a value in (not null) the table it will show the label as red - if not, it will be black.
I have got to the point where i can query the ZCTA database, find the right zipcodes i need and then pass them on to the second query where it looks in the other table to see if it's null. But all i get is the first zipcode. I know how to loop stuff - sort of, but this eludes me. Like i said, i can get it to work sort of, but it will only show the one zipcode. I need it to go through the entire loop again to show them all. Here is my code so far :
Code: Select all
<?php
//---Post or Static Value for Testing---//
//$zip = "770";
//$zip = substr($_POST['CHANGE-THIS'],0,5);
$city = "houston";
// connection information
$hostName = "xxx.xxx.xxx";
$userName = "phorem";
$password = "hmmmmmm";
$dbName = "Phorem";
// make connection to database
mysql_connect($hostName, $userName, $password) or die("Unable to connect to host $hostName");
mysql_select_db($dbName) or die("Unable to select database $dbName");
// Select all the fields in all the records from City
$usquery = "SELECT DISTINCT Postal_Code FROM US WHERE City LIKE '%$city%'";
$usresult = mysql_query($usquery);
$usnumber = mysql_num_rows($usresult);
$zip = mysql_result($usresult, "Postal_Code");
// Select all the fields in all the records from ZCTA
$query = "SELECT * FROM zcta WHERE zip LIKE '%$zip%'";
$result = mysql_query($query);
$number = mysql_num_rows($result);
//----Do the old classic IF statement
if ($number == 0) {
print " - Sorry, there were no records matching that criteria. Please click the back button if you wish to continue.";
} else {
//----------------------------It's mapping time------------------------------\\
//Prepare some of the necessary values
$im = imagecreatefrompng("houston.png");
$white = imagecolorallocate ($im, 255,255,255);
$scale_x = imagesx($im);
$scale_y = imagesy($im);
$textcolor = imagecolorallocate($im, 0, 0, 0);
//----This is the most important part for getting the php script to "loop" the "query-->then grab-->then display" routine.
for($i=0; $i<$number; $i++){
//$zip = mysql_result($usresult,$i, "zip");
//$query = "SELECT * FROM zcta WHERE zip LIKE '%$zip%'";
$zipcode = mysql_result($result,$i, "zip");
$lat = mysql_result($result,$i, "lat");
$lon = mysql_result($result,$i, "lon");
$pt = getlocationcoords($lat, $lon, $scale_x, $scale_y);
imagefilledrectangle($im,$pt["x"]-2,$pt["y"]-2,$pt["x"]+2,$pt["y"]+2,$white);
imagestring($im, 4, $pt["x"], $pt["y"], "$zipcode", $textcolor);
}
// **------------------------------------------------------------------------------------------------------**
//-----Copyright Label-----// off by Default
//imagestring($im,2,1,$scale_y-20,"Aldy & Acoziates",$black);
} //------Close up our IF statement
// Show the png image -_*-*_- Jpeg could be used as well
header("Content-Type: image/png");
imagepng($im);
imagedestroy($im);
// Convert the lon/lat values into screen coordinates by taking all of that
// info above stored in cache and use to plot values and Labels in the right spot
// according to the Lat and Lon values
function getlocationcoords($lat, $lon) {
$x_min = 0; $x_max = 1486;
$y_min = 0; $y_max = 1029;
$lon_min = 95.07; $lon_max = 95.62;
$lat_min = 29.55; $lat_max = 29.93;
$x = $x_min + ($x_max - $x_min) *
( 0.99 - ($lon - $lon_min) / ($lon_max - $lon_min) );
$y = $y_max - ($y_max - $y_min) *
( ($lat - $lat_min) / ($lat_max - $lat_min) );
return array("x"=>round($x),"y"=>round($y));
}
// Close the database connection
mysql_close();
// - Closed
//end
?>Code: Select all
$zip = mysql_result($usresult, "Postal_Code");
// Select all the fields in all the records from ZCTA
$query = "SELECT * FROM zcta WHERE zip LIKE '%$zip%'";
$result = mysql_query($query);
$number = mysql_num_rows($result);As a side note, i also need the finished result to be clickable and then query the database for that particular zipcode.
Wow! Lots of writing. I hope that's enough info. Let me know if any more info. would help somebody figure it out.
Thanks, Adam.