Zip Codes

[CFROG]

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:

$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.

[cwarn23]

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:

$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.

[diafol]

Something like this?

$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:

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.

[CFROG]

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:

// 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.

[diafol]

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:

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.

[CFROG]

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

$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.

$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?

$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'];}  //<--??

[diafol]

OK, let's keep it simple:

$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:

$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:

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/en/nested-join-optimization.html

$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:

$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 end.

[CFROG]

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.

$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.

[diafol]

That's ok C. However, being picky, you may want to avoid <font> tags. Use <span> 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.

if($rnum>0){ $online = "<font color=\"green\">Online now!</font>"; } else { $online = '<font color="red">Offline</font>';

to this

if($rnum>0){ $online = "<span class='online'>Online now!</span>"; } else { $online = '<span class="offline">Offline</span>';

Then have two rules in CSS:

.online{
   color: green;
}
.offline{
   color: red;
}

[CFROG]

I don't use CSS too often, although I probably should. What would be the difference between a font tag and a css rule? Just curious.

[diafol]

font tag was deprecated in HTML 4.01 and is not supported by XHTML strict. Using span/div/class/id will allow a greater control of format/layout via CSS. Say you had a number of pages or a number of different instances within the same page with the <font color="green"> and wanted to change the colour to say blue, you'd have to search for each one individually and make changes. Using CSS, you'd just make the one change, e.g.

.online{
   color: blue;
}

Simple as that. <font>, <em>, <strong> tags should really be "CSS-ified".

[CFROG]

Interesting, that makes perfect sense. I don't change up font colors all that often but I use <em> & <strong> a whole lot. Looks like I need to work on changing some of my bad habits.

[diafol]

Hmm, perhaps I'm over-zealous, you could keep your <em> and <strong> if you know that you will never want to change the look. Because you could actually place rules for em and strong in your CSS file:

em{
   font-style:italic;
   color: grey;
}

strong{
   font-weight: bold;
   background-color: yellow; 
}