mikepuerto Posted September 7, 2006 Share Posted September 7, 2006 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:[code]$result = mysql_query("SELECT * FROM $listTable WHERE redeemed = 'Y'");[/code] 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:[code]$resultZipcode = mysql_query("SELECT * FROM zipcode WHERE zip = " . $row['zip'] ."LIMIT 1");[/code]and display something like: [code]echo "the lattiitude and longitude for the zip code $zip is $lat $lon";[/code]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 Quote Link to comment Share on other sites More sharing options...
wildteen88 Posted September 7, 2006 Share Posted September 7, 2006 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. Quote Link to comment Share on other sites More sharing options...
mikepuerto Posted September 7, 2006 Author Share Posted September 7, 2006 This code is terrible... i know. i just can't figure out how to do it with one loop.[code]$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"; }}[/code] Quote Link to comment Share on other sites More sharing options...
wildteen88 Posted September 7, 2006 Share Posted September 7, 2006 This is most probably not going to work but try this:[code=php:0]$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";}[/code] Quote Link to comment Share on other sites More sharing options...
mikepuerto Posted September 7, 2006 Author Share Posted September 7, 2006 and you were right:mysql_fetch_array(): supplied argument is not a valid MySQL result resource ini 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 Quote Link to comment Share on other sites More sharing options...
wildteen88 Posted September 7, 2006 Share Posted September 7, 2006 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:[code]SELECT ... FROM $listTable lt, zipcode z WHERE ...[/code]Notice lt and z after your table names. These are aliases.You can learn aliases [url=http://www.w3schools.com/sql/sql_alias.asp]here[/url]. They save you from typing out your table name Quote Link to comment Share on other sites More sharing options...
mikepuerto Posted September 7, 2006 Author Share Posted September 7, 2006 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:[code]echo "var marker = createMarker(point, '<div id=\"infowindow\" style=\"white-space: nowrap;\">" . addslashes($row['zip']) . "</div>');\n";[/code]with something like:[code]echo "var marker = createMarker(point, '<div id=\"infowindow\" style=\"white-space: nowrap;\">" . addslashes($row['zip']) . "appeard $numOfRowsZipAppeared</div>');\n";[/code]thanks for all of your help! :)-mike Quote Link to comment Share on other sites More sharing options...
wildteen88 Posted September 7, 2006 Share Posted September 7, 2006 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 Quote Link to comment Share on other sites More sharing options...
mikepuerto Posted September 7, 2006 Author Share Posted September 7, 2006 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:[code]$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)']';[/code]Again, THANKS FOR THE HELP! Quote Link to comment Share on other sites More sharing options...
wildteen88 Posted September 7, 2006 Share Posted September 7, 2006 What are you trying to do now?However try this, as the query:[code]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[/code]Then to access how many zip codes are in each group use $row['zipcodes'] in the while loop Quote Link to comment Share on other sites More sharing options...
mikepuerto Posted September 7, 2006 Author Share Posted September 7, 2006 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! Quote Link to comment Share on other sites More sharing options...
craygo Posted September 7, 2006 Share Posted September 7, 2006 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[code]$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 totalif($row['zip'] != $lastzip { if($lastzip != ''){ echo "$subtotal<br>";// reset the sub total$subtotal = 0;}// Print the group headerecho $row['zip']."<br>";}// Print out the detailsecho $row['lon']." ".$row['lat']."<br>";//reset group values$subtotal++;$lastzip = $row['zip'];}//print out final subtotalecho "$subtotal<br>";[/code]I think thats all of it :)Ray Quote Link to comment Share on other sites More sharing options...
wildteen88 Posted September 7, 2006 Share Posted September 7, 2006 This is solved now, added COUNT(lt.zip) as zipcodes to the query. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.