Jump to content

*SOLVED* mysql loops and arrays...


mikepuerto

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
Link to comment
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]
Link to comment
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]
Link to comment
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
Link to comment
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
Link to comment
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!
Link to comment
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
Link to comment
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




Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.