PHP/MySQL Join Question

[centenial]

Hello,

I have three MySQL tables:

1. table "listings" (fields below)
a. listing_number
b. agent_id
c. category_id

2. table "agents" (fields below)
a. id
b. name

3. table "categories" (fields below)
a. id
b. name

I want to display the information in one table, with these columns:
1. Listing Number
2. Agent
3. Category

I want to display the "Agent" and "category" names instead of the agent and category ids. Can this be done with a MySQL Join Query?

Thanks for your help,

[karthikeyan_coder]

do you need query alone or full coding for output?

[centenial]

Just query, thanks :)

[ssmK]

SELECT
l.listing_number
a.agent,
c.name
FROM
listings AS l,
agents AS a,
categories as c
WHERE
l.agent_id = a.id
AND
l.category_id = c.id;

[centenial]

Ok, I think I'm going to need the full PHP code on that one... Could you please show a full example?

[ssmK]

[code]<?php

// UNTESTED CODE

DEFINE ('DB_USER', 'username');
DEFINE ('DB_PASSWORD', 'password');
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_NAME', 'sitename');

$dbc = @mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) OR die ('Could not connect to MySQL: ' . mysql_error() );

@mysql_select_db (DB_NAME) OR die ('Could not select the database: ' . mysql_error() );

$query = "SELECT l.listing_number, a.agent, c.name FROM listings AS l, agents AS a, categories as c WHERE l.agent_id = a.id AND l.category_id = c.id;";
$result = @mysql_query($result);

while ($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
  //DO SOMETHING WITH UR ROWS HERE
  //$row['agent'] for example
}

if($result) {
  //THE QUERY WAS SUCCESSFUL
} else {
  //IT WAS NOT SO SUCCESSFUL
}

?>
[/code]

[centenial]

[code]include 'includes/config.php';
$sql = "select l.id, l.price, l.mls, a.name As agent, c.name As category, s.name As status from listings l
INNER JOIN agents a ON a.id = l.a_id
INNER JOIN categories c ON c.id = l.c_id
INNER JOIN status s ON s.id = l.status";
$res = mysql_query($sql,$conn) or die(mysql_error());

while ($row = mysql_fetch_assoc($res)) {
echo " <tr><td>";
print $row['mls'].'</td><td>'.
$row['category'].'</td><td>'.
$row['price'].'</td><td>'.
$row['agent'].'</td><td>'.
$row['status'].'</td><td><a href="listing.php?method=edit&id='.
$row['id'].'">Edit</a> <a href="listing.php?method=delete&id='.
$row['id'].'">Delete</a></td></tr>';
} 
echo '</table>';[/code]

Why is this not working? Can someone please help?

[.josh]

would you like to explain what is not working? do you get an error message? empty results? have you put that query directly into the database? did you get the desired results, therefore making it a php code related issue? Be more specific.

[centenial]

Sorry, I should have been more specific - It's working now. I do have one other question - I want to be able to sort by users clicking on a column.

Could someone tell me why this isn't working? I have the correct $GET variables - Is it a problem with the SQL query?

[code]select l.id, l.mls, a.name As agent, c.name As category, t.name As town, s.name As status from listings l
INNER JOIN agents a ON a.id = l.a_id
INNER JOIN categories c ON c.id = l.c_id
INNER JOIN towns t ON t.id = l.t_id
INNER JOIN status s ON s.id = l.status
ORDER BY $orderby // Which I get via the URL[/code]