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['fieldname'] end.
