Should I use table joins?

[lndlyb4]

Hi there,

I'm fairly new at php/mysql, so I'm a bit confused about how to pull information from more than one table.
I think(guessing) I may have to use joins, but I have not used them before and my attempts have not been successful. I have included the code I have tried below.

My questions are:
1. Do I need to use joins in this situation?
2. If so, have I used them correctly?
3. If so, have I gotten something else wrong in this script?

Background

First, I have several tables

Table 1: contacts

columns: contact_id, contact_name, main_category_id, sub_category_id, status_id, contact_info_id

Table 2: main_category

columns: main_category_id, main_category_name

Table 3: sub_category

columns: sub_category_id, sub_category_name

Table: 4: status

columns: status_id, status_name

Table 5: contact_info

columns: contact_id, city_id, state_id, country_id, zip, web_address

This is the code I have tried so far:

$query = ("SELECT *

FROM contacts

LEFT JOIN main_category ON contacts.main_category_id = main_category.main_category_id

LEFT JOIN sub_category ON contacts.sub_category_id = sub_category.sub_category_id

LEFT JOIN contact_info ON contacts.contact_info_id = contact_info.contact_info_id

LEFT JOIN status ON contacts.status_id = status.status_id

");

$result = mysql_query($query)
or print "<p id=\"errorMessage\">Couldn't execute query because: <b>". mysql_error() . "</b>. The query was $query.</p>";

?>

<table id ="testTable_delete">

<tr>

<th>Contact Name</th>
<th>Main Category</th>
<th>Sub Category</th>
<th>Status</th>
<th>State</th>
<th>Web</th>

<tr>

<?php

while ($row = mysql_fetch_array($result))
{
extract($row);

if ($company_organization_name != "") {
print "
<tr>

<td>$contact_name</td>
<td>$main_category_name</td>
<td>$sub_category_name</td>
<td>$status_type</td>
<td>$state</td>
<td><a href=\"http://$web_address\">$web_address</a></td>

</tr>";

}

}

?>

</table>

The Result

Only the table header prints out. I don't get an error message.

Any help would be appreciated.

[idfer]

Looks ok to me except that i don't see $company_organization_name as a field in any of your tables, so unless it's being set somewhere else it's always going to be blank and your if statement is always false.

If that doesn't work, try to put echo or print_r statements in your code to see what parts execute and the values of your variables. E.g. add print_r($row); right before your call to extract().

Tip 2: if you got phpMyAdmin installed, use it to run and debug your SQL statements. It'll help you in figuring out whether the problem is in the SQL or the PHP code.

[lndlyb4]

idfer,

Thanks for the extra set of eyes. I had changed the company_organization_name to contact_name and missed it in the if statement.

Seems like I'm moving forward again.

The phpMyAdmin suggestion will come in handy in the future.

God bless you for taking the time to help me.

Bill