The dreaded Loop

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Phorem
Forum Newbie
Posts: 20
Joined: Sun Mar 06, 2005 3:58 am

The dreaded Loop

Post by Phorem »

Ok, first off, i have come here once before for a little help and things worked out well. I know nothing (well, not much) about php other than what i can see and slowly work my way through trying to figure out what it does. My problem still lies within the same project i was working on before.

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
?>
So what that gives me is one postal code on the map. I think my problem lies here :

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);
I think i need this part to keep looping so the values keep getting fed into the loop below.

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.
Last edited by Phorem on Tue May 17, 2005 3:35 pm, edited 1 time in total.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

try performing your second query within a while loop that is looping over the results of the first:

ex:

Code: Select all

while($zip = mysql_fetch_assoc($usresult)){
  $result = mysql_query("select * from scta where zip like '%".$zip['zip']."%'")
    or die(mysql_error());
}
that will reset $result for every iteration of the loop, so you're going to need to yank out what you want before the next iteration.
Phorem
Forum Newbie
Posts: 20
Joined: Sun Mar 06, 2005 3:58 am

Post by Phorem »

Thanks Burrito but like most noobs, i have no clue where that code would go. I tried dropping it in a couple different places but anything i would consider a "logical" spot seems to spit out errors - basically takes all the cpu and loops indefinitly. Thanks for the reply and little nudge in the right direction would be grateful. Thanks.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

what I would do is create an array before you start your loop and then add the values from the results of your second query to the array. Then you can output the values of the array which should encompass everything from both queries. See below code:

Code: Select all

&lt;?php //---Post or Static Value for Testing---//
//$zip = &quote;770&quote;;
//$zip = substr($_POST&#1111;'CHANGE-THIS'],0,5); 

$city = &quote;houston&quote;; 
// connection information
$hostName = &quote;xxx.xxx.xxx&quote;;
$userName = &quote;phorem&quote;;
$password = &quote;hmmmmmm&quote;;
$dbName = &quote;Phorem&quote;; 
// make connection to database
mysql_connect($hostName, $userName, $password) 
	or die(&quote;Unable to connect to host $hostName&quote;);
mysql_select_db($dbName) 
	or die(&quote;Unable to select database $dbName&quote;); 
	// Select all the fields in all the records from City
	$usresult = mysql_query(&quote;SELECT DISTINCT Postal_Code FROM US WHERE City LIKE '%$city%'&quote;);
	$item1 = array();
	$item2 = array();
	while($zp = mysql_fetch_assoc($usresult)){
		$zip = mysql_query(&quote;select item1, item2 from zcta where zip like '%&quote;.$zp&#1111;'zip'].&quote;%'&quote;)
			or die(mysql_error());
		while($zipz = mysql_fetch_assoc($zip)){
			$item1&#1111;] = $zipz&#1111;'item1'];
			$item2&#1111;] = $zipz&#1111;'item2'];
		}
	}
		
	//----Do the old classic IF statement
	if (sizeof($item1) == 0) {   
		echo &quote; - Sorry, there were no records matching that criteria. Please click the back button if you wish to continue.&quote;;
} else { 

// loop over your arrays to get the values and do stuff use a for loop or better yet, a foreach loop (if not multiple items)
}?&gt;
Post Reply