Pulling Related Data

allgood2

Hi:

This pretty much a newbie question, but I haven't come across a succinct explanation. I'm two days into PHP and MySQL. I've set up a database with four tables--members, staff, profiles, & projects.

Members are nonprofit agencies. Each member can create a profile (basic description of agency and services), and spotlight projects that they worked on. But for the life of me, I can't come up with the best manner to call the related profile or project of a member agency.

I develop the following code....

Statement 1: Array results from the Members table

$results=mysql_query ("SELECT * FROM members");
while ($row=mysql_fetch_array($results)){
$agency = $row["membername"];
$address = $row["address"];
$web = $row["web"];
$id = $row["memberid"];

Statement 2: Array results from the Profile table

$results2=mysql_query ("SELECT * FROM profile WHERE memberid='$id'");
while ($row=mysql_fetch_array($results2)){
$mission = $row["mission"];
$ein = $row["ein"];

Format the output

$display_membercontact .= "
$agency
 $address
 $city, $state $postal
 $web
 $mission
";

Next close the loop statement (started with the while..)

}}

<? echo "$display_membercontact"; ?>

The problem is that this statement then only prints records where a member has a profile, rather than joining a profile to a listing of members.

Everything I've read to date seems to focus on calling and displaying data from one table, does anyone know a quick and easy rule or method for displaying related data??

Alnisa

Sorry for such a basic question, but so far it's been overlooked in every book that I've read.

Anon

The problem is that you're reading PHP books intead of SQL books. A single JOIN query will replace both of your queries, something like this:

SELECT * FROM members LEFT INNER JOIN profile WHERE members.memberid = profile.memberid

I haven't had my coffee yet, so I can't promise the above query is perfect, but it's a start. The "LEFT INNER" keywords tell mysql to include all of the records in the first table (members), whether there are matching records in profile or not.

allgood2

Thanks much. Your right. I've been reading PHP books.

I'm going to play with your statement and see how it goes. Thanks!

Alnisa