DaniWeb Forum Index > Web Development > PHP

Zip Codes

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Jul 2009

Posts: 226

Reputation: CFROG is an unknown quantity at this point

Solved Threads: 14

CFROG

Offline

Posting Whiz in Training

Zip Codes

Sep 27th, 2009

I've been working with a script that allows users to look up other users in their area based on their zip code. I attempted the script myself but the whole lat/long thing was a bit much for me to understand, so I settled for one that I found on the net. The problem that I'm having is that the query results are loaded into an array such as $zips = $z->get_zips_in_range("".$my_zip."", "".$distance."", _ZIPS_SORT_BY_DISTANCE_ASC, true); and use a foreach loop to iterate through the resutls, ex. foreach ($zips as $key => $value) {} with $key being the zip code and $value being the distance. This works just fine and displays the expected results when echoed, but the problem is that I need to use a while loop to run through the result set and can't figure out how to alter the code to get what I want.

I would like to take the resulting zip codes ($key) in this case and run it through another query. I'm fully aware that this won't work (as expected) but just to give you an idea of what I'd like to do, here is an example:

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
$zips = $z->get_zips_in_range("".$my_zip."", "".$distance."", _ZIPS_SORT_BY_DISTANCE_ASC, true); 
 
foreach ($zips as $key => $value) {}
 
$sql="SELECT * FROM users WHERE zip='$key'"; //<-- Just an example
$res=mysql_query($sql)or die(mysql_error()); 
$i = 0; 
$num_of_cols = 5; 
echo "<table cellspacing=\"10\" cellpadding=\"10\"><tr>"; 
 
while($row = mysql_fetch_array($res)) { 
	$fname=$row['first_name'];
	$age=$row['age'];
	$id=$row['id']; 
 
	$sql1="SELECT prof_pic FROM user_profile WHERE user_id='$id'";
	$res1=mysql_query($sql1);
	$numr=mysql_num_rows($res1);
	while($r1=mysql_fetch_array($res1)){
		$pic=$r1['prof_pic']; } etc.
$zips = $z->get_zips_in_range("".$my_zip."", "".$distance."", _ZIPS_SORT_BY_DISTANCE_ASC, true); 

foreach ($zips as $key => $value) {}
            		
$sql="SELECT * FROM users WHERE zip='$key'"; //<-- Just an example
$res=mysql_query($sql)or die(mysql_error()); 
$i = 0; 
$num_of_cols = 5; 
echo "<table cellspacing=\"10\" cellpadding=\"10\"><tr>"; 

while($row = mysql_fetch_array($res)) { 
	$fname=$row['first_name'];
	$age=$row['age'];
	$id=$row['id']; 
	
	$sql1="SELECT prof_pic FROM user_profile WHERE user_id='$id'";
	$res1=mysql_query($sql1);
	$numr=mysql_num_rows($res1);
	while($r1=mysql_fetch_array($res1)){
		$pic=$r1['prof_pic']; } etc.

Any ideas how I can do this ? I can provide code from the .class that defines the function 'get_zips_in_range' if needed.

•

Join Date: Sep 2007

Posts: 1,528

Reputation: cwarn23 has a spectacular aura about

Solved Threads: 137

cwarn23

Offline

Posting Virtuoso

Re: Zip Codes

Sep 28th, 2009

Why won't that work. Is there something in the included library that prevents it working. The only thing I would suggest is escaping the variable $key like the following:

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
$zips = $z->get_zips_in_range("".$my_zip."", "".$distance."", _ZIPS_SORT_BY_DISTANCE_ASC, true); 
 
foreach ($zips as $key => $value) {}
 
$key=mysql_real_escape_string($key);
$sql="SELECT * FROM users WHERE zip='$key'"; //<-- should work
$res=mysql_query($sql)or die(mysql_error()); 
$i = 0; 
$num_of_cols = 5; 
echo "<table cellspacing=\"10\" cellpadding=\"10\"><tr>"; 
 
while($row = mysql_fetch_array($res)) { 
	$fname=$row['first_name'];
	$age=$row['age'];
	$id=$row['id']; 
 
	$sql1="SELECT prof_pic FROM user_profile WHERE user_id='$id'";
	$res1=mysql_query($sql1);
	$numr=mysql_num_rows($res1);
	while($r1=mysql_fetch_array($res1)){
		$pic=$r1['prof_pic']; } etc.
$zips = $z->get_zips_in_range("".$my_zip."", "".$distance."", _ZIPS_SORT_BY_DISTANCE_ASC, true); 

foreach ($zips as $key => $value) {}

$key=mysql_real_escape_string($key);
$sql="SELECT * FROM users WHERE zip='$key'"; //<-- should work
$res=mysql_query($sql)or die(mysql_error()); 
$i = 0; 
$num_of_cols = 5; 
echo "<table cellspacing=\"10\" cellpadding=\"10\"><tr>"; 

while($row = mysql_fetch_array($res)) { 
	$fname=$row['first_name'];
	$age=$row['age'];
	$id=$row['id']; 
	
	$sql1="SELECT prof_pic FROM user_profile WHERE user_id='$id'";
	$res1=mysql_query($sql1);
	$numr=mysql_num_rows($res1);
	while($r1=mysql_fetch_array($res1)){
		$pic=$r1['prof_pic']; } etc.

Try not to bump 10 year old threads as it can be really annoying.
http://syntax.cwarn23.net/
Smilies: ^_* +_+ v_v -_- *~*`
My favourite PC. - Oopy Doopy Do 2U2!

•

Join Date: Oct 2006

Posts: 1,076

Reputation: ardav will become famous soon enough

Solved Threads: 137

ardav

Online

Veteran Poster

Re: Zip Codes

Sep 28th, 2009

Something like this?

Help with Code Tags

PHP Syntax (Toggle Plain Text)

$sql = "SELECT u.*, up.prof_pic FROM users AS u INNER JOIN user_profile AS up ON u.id = up.user_id WHERE zip IN ({$zips}) ORDER BY surname, firstname";

The "INNER JOIN" and "IN" allow you to avoid the loops:

You need to check if the $zips array is able to parse to a simple list array format, without named keys. For example, you should get something like:

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
SELECT u.*, up.prof_pic FROM users AS u INNER JOIN user_profile AS up ON u.id = up.user_id WHERE zip IN ("CF72 9BF","CF71 3BX","CF2 2PP","CF1 0LU") ORDER BY surname, firstname
SELECT u.*, up.prof_pic FROM users AS u INNER JOIN user_profile AS up ON u.id = up.user_id WHERE zip IN ("CF72 9BF","CF71 3BX","CF2 2PP","CF1 0LU") ORDER BY surname, firstname

if you echo the $sql variable. If you get a problem, copy the output (like above) and paste into the SQL window of phpMyAdmin and fiddle around a bit until you get it to work. You may need to tweak your free script to give you the right $zips output that you need.

!!Beware, if an user does not have a picture, s/he will not be in the recordset. To rectify this, use LEFT JOIN instead of INNER JOIN.

Last edited by ardav; Sep 28th, 2009 at 11:01 am.

Happy Humbugging Christmas

•

Join Date: Jul 2009

Posts: 226

Reputation: CFROG is an unknown quantity at this point

Solved Threads: 14

CFROG

Offline

Posting Whiz in Training

Re: Zip Codes

Sep 28th, 2009

Thanks Ardav, I appreciate the help. I'm more than just a little loopy on this one! The code actually runs through one or two more loops (I only posted part of it). I originally started with a join, but I was having a lot of problems with it so I just stuck to familiar ground and did it the way I was most familiar with. You'd probably fall out of your chair if you'd see the whole code, but it does work, that is until I throw the zip codes into the mix. I'm sure the $zip array can parse to a list because there was a comment in the code that says this:

Help with Code Tags

(Toggle Plain Text)

 // One thing you may want to do with this is create SQL from it. For example, 
   // iterate through the array to create SQL that is something like:
   // WHERE zip_code IN ('93001 93002 93004')
   // and then use that condition in your query to find all pizza joints or 
   // whatever you're using it for. Make sense? Hope so.

The problem was that me being me, it didn't make sense ... LOL. Go figure. I guess I need to hit the books and work past my fear of joins. Thanks for the help, I'm sure I'll be getting back to you on this one.

•

Join Date: Oct 2006

Posts: 1,076

Reputation: ardav will become famous soon enough

Solved Threads: 137

ardav

Online

Veteran Poster

Re: Zip Codes

-1

Sep 28th, 2009

No problem. I hated joins too, but a few hours of trial and error and a good tutorial and I'm pretty sorted now. I usually just use INNER JOINS and the occasional LEFT JOIN. RIGHT JOINS also have the same syntax:

Help with Code Tags

PHP Syntax (Toggle Plain Text)

SELECT table1.field2, table1.field7, table2.field4, table2.field6 FROM table1 *** JOIN table2 ON table1.field1 = table2.field2

*** = INNER or LEFT or RIGHT.

I'd encourage anyone to bone up on JOINS if facing loads of loops.

Happy Humbugging Christmas

•

Join Date: Jul 2009

Posts: 226

Reputation: CFROG is an unknown quantity at this point

Solved Threads: 14

CFROG

Offline

Posting Whiz in Training

Re: Zip Codes

Sep 28th, 2009

Man, joins can be confusing. I'm using your code as a reference but trying to write my own because I feel it's the best way to learn. So, given the code you provided

Help with Code Tags

PHP Syntax (Toggle Plain Text)

 $sql = "SELECT u.*, up.prof_pic FROM users AS u INNER JOIN user_profile AS up ON u.id = up.user_id WHERE zip IN ({$zips}) ORDER BY surname, firstname";

SELECT u.* would be select everything from users?
AS u and AS up define an abbreviation for the table name
What is 'ON' specifying?
I have a third table to add into the mix and I think I'm over-complicating things. I'm trying to understand the JOIN you provided, so I'll work with that one for now. Here is what I have using a third table.

Help with Code Tags

PHP Syntax (Toggle Plain Text)

$sql = "SELECT u.*, up.prof_pic, uo.users_online FROM users AS u INNER JOIN user_profile AS up, users_online AS uo ON u.id = up.user_id AND u.id = uo.user_id WHERE zip IN ({$zips}) ORDER BY  first_name";

Then I would just use one loop for the result set?

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
$res=mysql_query($sql);
while(mysql_fetch_array($res)){
	$fname=$row['u.first_name'];
	$age=$row['u.age'];
	$id=$row['u.id']; 
	$pic=$r1['up.prof_pic'];}  //<--??
$res=mysql_query($sql);
while(mysql_fetch_array($res)){
	$fname=$row['u.first_name'];
	$age=$row['u.age'];
	$id=$row['u.id']; 
	$pic=$r1['up.prof_pic'];}  //<--??

Last edited by CFROG; Sep 28th, 2009 at 2:54 pm.

•

Join Date: Oct 2006

Posts: 1,076

Reputation: ardav will become famous soon enough

Solved Threads: 137

ardav

Online

Veteran Poster

Re: Zip Codes

-1

Sep 28th, 2009

OK, let's keep it simple:

Help with Code Tags

PHP Syntax (Toggle Plain Text)

$sql = "SELECT u.*, up.prof_pic FROM users AS u INNER JOIN user_profile AS up ON u.id = up.user_id WHERE zip IN ({$zips}) ORDER BY surname, firstname";

•

SELECT u.* would be select everything from users?

Yes everything. I used ALIASES to shorten the sql code 'u' for 'users' and 'up' for 'user_profile'. There's really no need to do this, I could have left it like this:

Help with Code Tags

PHP Syntax (Toggle Plain Text)

$sql = "SELECT users.*, user_profile.prof_pic FROM users INNER JOIN user_profile ON users.id = user_profile.user_id WHERE zip IN ({$zips}) ORDER BY users.surname, users.firstname";

•

AS u and AS up define an abbreviation for the table name

So yes.

BTW: sorry the first example should have 'u.surname' and 'u.firstname' at the end - oops!

•

What is 'ON' specifying?

This is setting up the link on the 'common fields', usually "Primary Key = Foreign Key".

so:

Help with Code Tags

PHP Syntax (Toggle Plain Text)

users.id = user_profile.user_id

Setting up a JOIN clauses on three or more tables looks complicated, but they can be nested easily enough. There should be plenty of online examples (mysql online manual perhaps) of multiple JOINS.

•

Then I would just use one loop for the result set?

If you can get all the info into a single query, then yes, you have one recordset, so it should be possible to get everything from a single loop.

I have no idea about your table structures so I can't advise you w.r.t. to the query you should build, however, this page may help:

http://dev.mysql.com/doc/refman/5.0/...imization.html

•

Help with Code Tags

PHP Syntax (Toggle Plain Text)
$res=mysql_query($sql);
while(mysql_fetch_array($res)){
	$fname=$row['u.first_name'];
	$age=$row['u.age'];
	$id=$row['u.id']; 
	$pic=$r1['up.prof_pic'];}
$res=mysql_query($sql); while(mysql_fetch_array($res)){ $fname=$row['u.first_name']; $age=$row['u.age']; $id=$row['u.id']; $pic=$r1['up.prof_pic'];}

This doesn't look right to me:

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
$res=mysql_query($sql);
while($row = mysql_fetch_array($res)){
	$fname=$row['first_name'];
	$age=$row['age'];
	$id=$row['id']; 
	$pic=$row['prof_pic'];
}
$res=mysql_query($sql);
while($row = mysql_fetch_array($res)){
	$fname=$row['first_name'];
	$age=$row['age'];
	$id=$row['id']; 
	$pic=$row['prof_pic'];
}

As long as your fields have unique names, you don't need to specify the table in the array key. For ease, you can give non-unique fields aliases with 'AS' as well - it just tidies things up at the $row['fieldname'] end.

Last edited by ardav; Sep 28th, 2009 at 5:58 pm. Reason: which one? this edit? or one of the 200 before it?

Happy Humbugging Christmas

•

Join Date: Jul 2009

Posts: 226

Reputation: CFROG is an unknown quantity at this point

Solved Threads: 14

CFROG

Offline

Posting Whiz in Training

Re: Zip Codes

Sep 28th, 2009

Thanks Ardav, that helped a lot. I've been working through a few tutorials but not using the code I'm working on now. I figured I would start simple and work my up as I build a better understanding. They can be really confusing when you first start working with them, and a lot of these tutorials aren't very specific.

I did alter my original code to get it to do what I want, and unfortunately the while loops are still in there. JOIN seems like it could save me a heck of a lot of work once I get a good handle on them so I may come back later and change it over once I have things figured out.

The new code, in it's entirety looks like this now.

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
$zips = $z->get_zips_in_range("".$my_zip."", "".$distance."", _ZIPS_SORT_BY_DISTANCE_ASC, true); 
 
$zipcodes = array_keys($zips); 
$zcodes = implode(",",$zipcodes);
 
$sql="SELECT * FROM users WHERE zip='$zcodes' $pages->limit"; 
$res=mysql_query($sql)or die(mysql_error()); 
$i = 0; 
$num_of_cols = 5; 
echo "<table cellspacing=\"10\" cellpadding=\"10\"><tr>"; 
 
while($row = mysql_fetch_array($res)) { 
	$fname=$row['first_name'];
	$age=$row['age'];
	$id=$row['id']; 
 
	$sql1="SELECT prof_pic FROM user_profile WHERE user_id='$id'";
	$res1=mysql_query($sql1);
	$numr=mysql_num_rows($res1);
	while($r1=mysql_fetch_array($res1)){
		$pic=$r1['prof_pic'];
		if($pic == ''){ $pic = 'default.jpg';} 
 
		$on="SELECT user_id FROM users_online WHERE user_id='$id'";
		$ron=mysql_query($on);
		$rnum=mysql_num_rows($ron);
		if($rnum>0){ $online = "<font color=\"green\">Online now!</font>"; } else { $online = '<font color="red">Offline</font>';}
 
echo ($i!= 0 && $i%$num_of_cols == 0)?'</tr><tr>':''; 
echo "<td valign=\"bottom\" align=\"center\">";
echo "<a href=\"my_profile.php?id=".$id."\"><img src=\"../xpics/profiles/small/".$pic."\" border=\"none\"></a><br />";
echo "".$fname." <font color=\"blue\">".$age."</font><br />";
echo "".$online."";
echo"</td>"; }}
$i++; 
echo '</tr></table>';
$zips = $z->get_zips_in_range("".$my_zip."", "".$distance."", _ZIPS_SORT_BY_DISTANCE_ASC, true); 

$zipcodes = array_keys($zips); 
$zcodes = implode(",",$zipcodes);
         		
$sql="SELECT * FROM users WHERE zip='$zcodes' $pages->limit"; 
$res=mysql_query($sql)or die(mysql_error()); 
$i = 0; 
$num_of_cols = 5; 
echo "<table cellspacing=\"10\" cellpadding=\"10\"><tr>"; 

while($row = mysql_fetch_array($res)) { 
	$fname=$row['first_name'];
	$age=$row['age'];
	$id=$row['id']; 
	
	$sql1="SELECT prof_pic FROM user_profile WHERE user_id='$id'";
	$res1=mysql_query($sql1);
	$numr=mysql_num_rows($res1);
	while($r1=mysql_fetch_array($res1)){
		$pic=$r1['prof_pic'];
		if($pic == ''){ $pic = 'default.jpg';} 
		
		$on="SELECT user_id FROM users_online WHERE user_id='$id'";
		$ron=mysql_query($on);
		$rnum=mysql_num_rows($ron);
		if($rnum>0){ $online = "<font color=\"green\">Online now!</font>"; } else { $online = '<font color="red">Offline</font>';}
		
echo ($i!= 0 && $i%$num_of_cols == 0)?'</tr><tr>':''; 
echo "<td valign=\"bottom\" align=\"center\">";
echo "<a href=\"my_profile.php?id=".$id."\"><img src=\"../xpics/profiles/small/".$pic."\" border=\"none\"></a><br />";
echo "".$fname." <font color=\"blue\">".$age."</font><br />";
echo "".$online."";
echo"</td>"; }}
$i++; 
echo '</tr></table>';

It's only displaying one result even though I know there should be more. I'm working on that now. You've been a great help and I appreciate you taking the time to get me pointed in the right direction.

•

Join Date: Oct 2006

Posts: 1,076

Reputation: ardav will become famous soon enough

Solved Threads: 137

ardav

Online

Veteran Poster

Re: Zip Codes

-1

Sep 28th, 2009

That's ok C. However, being picky, you may want to avoid tags. Use or a tag with a classname or a span with a classname. Then apply a rule in CSS to apply a format/colour.

e.g.

•

Help with Code Tags

php Syntax (Toggle Plain Text)
if($rnum>0){ $online = "Online now!"; } else { $online = 'Offline';
if($rnum>0){ $online = "Online now!"; } else { $online = 'Offline';

to this

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
if($rnum>0){ $online = "<span class='online'>Online now!</span>"; } else { $online = '<span class="offline">Offline</span>';
if($rnum>0){ $online = "<span class='online'>Online now!</span>"; } else { $online = '<span class="offline">Offline</span>';

Then have two rules in CSS:

 Help with Code Tags 
 css Syntax (Toggle Plain Text) 
.online{
   color: green;
}
.offline{
   color: red;
}
.online{
   color: green;
}
.offline{
   color: red;
}