Jump to content

Archived

This topic is now archived and is closed to further replies.

mikepuerto

*SOLVED* mysql loops and arrays...

Recommended Posts

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
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 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>";[/code]

I think thats all of it :)

Ray




Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.