Jump to content


Photo

*SOLVED* mysql loops and arrays...


  • Please log in to reply
12 replies to this topic

#1 mikepuerto

mikepuerto
  • Members
  • PipPip
  • Member
  • 13 posts

Posted 07 September 2006 - 02:40 PM

Hello!

Hoping for a little help!

I have a table that has user information(name, addr, zip, etc) stored in it and if a user has completed somthing a value of "Y" gets added to their row in the column "redeemed". I have another table that houses only zip codes and lattitude/longitude for every us zip code. what i am trying to do is query the database for any user that has a "Y" using:
$result = mysql_query("SELECT * FROM $listTable WHERE redeemed = 'Y'");


and then load the zip code for each user into an array. I then want to query the database to get the lattitude/longitude for every zip code in the array from the zipcode table using:
$resultZipcode = mysql_query("SELECT * FROM zipcode WHERE zip = " . $row['zip'] ."LIMIT 1");

and display something like:
echo "the lattiitude and longitude for the zip code $zip is $lat $lon";

I have tried using using a foreach and a while loop, i can get the zip code to output with the while loop but can not get the lat/long - it's driving me crazy!

thanks in advance!
-mike

#2 wildteen88

wildteen88
  • Staff Alumni
  • Advanced Member
  • 10,482 posts
  • LocationUK, Bournemouth

Posted 07 September 2006 - 02:44 PM

Whats the code you are using. Post it here and we'll see what you're doing wrong.

However it'll be better if you use a JOIN.

#3 mikepuerto

mikepuerto
  • Members
  • PipPip
  • Member
  • 13 posts

Posted 07 September 2006 - 03:04 PM

This code is terrible... i know. i just can't figure out how to do it with one loop.

$result = mysql_query("SELECT * FROM $listTable WHERE redeemed = 'Y'");

while ($row = mysql_fetch_array($result)) {
		$resultZipcode = mysql_query("SELECT * FROM zipcode WHERE zip = " . $row['zip']);
		$row2 = mysql_fetch_array($resultZipcode);
		
	
}	

foreach ($row2 as $key => $value) {
	echo "var point = new GPoint(-" . $row['lon'] . "," . $row['lat'] . ");\n";
	echo "var marker = createMarker(point, '<div id=\"infowindow\" style=\"white-space: nowrap;\">" . addslashes($row['zip']) . "</div>');\n";
	echo "map.addOverlay(marker);\n";
	echo "\n";
		}

}


#4 wildteen88

wildteen88
  • Staff Alumni
  • Advanced Member
  • 10,482 posts
  • LocationUK, Bournemouth

Posted 07 September 2006 - 03:13 PM

This is most probably not going to work but try this:
$result = mysql_query("SELECT lt.zip, z.lon, z.lat FROM $listTable lt, zipcode z WHERE lt.redeemed = 'Y' AND z.zip = lt.zip");

while ($row = mysql_fetch_array($result))
{
    /*$resultZipcode = mysql_query("SELECT * FROM zipcode WHERE zip = " . $row['zip']);

    $row = mysql_fetch_array($resultZipcode);*/

    echo "var point = new GPoint(-" . $row['lon'] . "," . $row['lat'] . ");\n";
	echo "var marker = createMarker(point, '<div id=\"infowindow\" style=\"white-space: nowrap;\">" . addslashes($row['zip']) . "</div>');\n";
	echo "map.addOverlay(marker);\n";
	echo "\n";

}


#5 mikepuerto

mikepuerto
  • Members
  • PipPip
  • Member
  • 13 posts

Posted 07 September 2006 - 03:24 PM

and you were right:
mysql_fetch_array(): supplied argument is not a valid MySQL result resource in

i dont understand this... what it "lt."? "z." etc? is this a join?
SELECT lt.*, lt.zip as lzip, z.* FROM $listTable lt, zipcode z WHERE lt.redeemed = 'Y' AND z.zip=lt.lzip

#6 wildteen88

wildteen88
  • Staff Alumni
  • Advanced Member
  • 10,482 posts
  • LocationUK, Bournemouth

Posted 07 September 2006 - 03:25 PM

I changed my code while you where testing, recopy the above code. I chnaged the query. I tested it in phpMyAdmin this time and it worked fine.

lt. and z. are aliases to the listTable and zipped table. These aliases are defined in the FROM clause of the query:
SELECT ... FROM $listTable lt, zipcode z WHERE ...
Notice lt and z after your table names. These are aliases.

You can learn aliases here. They save you from typing out your table name

#7 mikepuerto

mikepuerto
  • Members
  • PipPip
  • Member
  • 13 posts

Posted 07 September 2006 - 04:04 PM

That worked beautifully!  ;D

one more question thing(sorry)...

As you could probably see from before i am using the google maps API to plott these things on a map...
Some of the users have the same zip code, so i dont want to have to plot the same zip more than once. is there an easy way to prevent this but at the same time use something like "mysql_num_rows" to determine how man rows that zip code was present? So that i could then replace:
echo "var marker = createMarker(point, '<div id=\"infowindow\" style=\"white-space: nowrap;\">" . addslashes($row['zip']) . "</div>');\n";
with something like:
echo "var marker = createMarker(point, '<div id=\"infowindow\" style=\"white-space: nowrap;\">" . addslashes($row['zip']) . "appeard $numOfRowsZipAppeared</div>');\n";

thanks for all of your help! :)

-mike

#8 wildteen88

wildteen88
  • Staff Alumni
  • Advanced Member
  • 10,482 posts
  • LocationUK, Bournemouth

Posted 07 September 2006 - 04:10 PM

You can do this with the query,  You can use GROUP BY lt.zip at the end of your query.

So use this as the Query:
SELECT lt.zip, z.lon, z.lat FROM $listTable lt, zipcode z WHERE lt.redeemed = 'Y' AND z.zip = lt.zip GROUP BY lt.zip

#9 mikepuerto

mikepuerto
  • Members
  • PipPip
  • Member
  • 13 posts

Posted 07 September 2006 - 04:37 PM

GROUP BY z.zip worked perfectly. I just cant figure out how to tell how many times a zip code was present(in each "group")... I tried using COUNT but nothing(probably incorrect syntax) this is what i tried:

$result = mysql_query("SELECT COUNT(lt.zip), z.lon, z.lat FROM $listTable lt, zipcode z WHERE lt.redeemed = 'Y' AND z.zip = lt.zip GROUP BY z.zip");
echo "$row['COUNT(zip)']';

Again, THANKS FOR THE HELP!

#10 wildteen88

wildteen88
  • Staff Alumni
  • Advanced Member
  • 10,482 posts
  • LocationUK, Bournemouth

Posted 07 September 2006 - 04:48 PM

What are you trying to do now?

However try this, as the query:
SELECT COUNT(lt.zip) as zipcodes, lt.zip, z.lon, z.lat FROM $listTable lt, zipcode z WHERE lt.redeemed = 'Y' AND z.zip = lt.zip GROUP BY z.zip

Then to access how many zip codes are in each group use $row['zipcodes'] in the while loop

#11 mikepuerto

mikepuerto
  • Members
  • PipPip
  • Member
  • 13 posts

Posted 07 September 2006 - 05:22 PM

trying to show how many users are in each zip code that was plotted on the map.... everything work PERFECTLY! thank you VERY, VERY MUCH!

#12 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 07 September 2006 - 05:24 PM

if you want the amount in each group you would have to reset the counter in the loop in order to give you a number.

I got this from obsidian at some point

$subtotal = 0;
$lastzip = '';
$result = mysql_query("SELECT lt.zip, z.lon, z.lat FROM $listTable lt, zipcode z WHERE lt.redeemed = 'Y' AND z.zip = lt.zip GROUP BY z.zip");
while ($row = mysql_fetch_array($result))
{
// Print the group total
if($row['zip'] != $lastzip {
  if($lastzip != ''){
    echo "$subtotal<br>";
// reset the sub total
$subtotal = 0;
}
// Print the group header
echo $row['zip']."<br>";
}
// Print out the details
echo $row['lon']." ".$row['lat']."<br>";

//reset group values
$subtotal++;
$lastzip = $row['zip'];
}
//print out final subtotal
echo "$subtotal<br>";

I think thats all of it :)

Ray






#13 wildteen88

wildteen88
  • Staff Alumni
  • Advanced Member
  • 10,482 posts
  • LocationUK, Bournemouth

Posted 07 September 2006 - 05:29 PM

This is solved now, added COUNT(lt.zip) as zipcodes to the query.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users