Jump to content


Photo

PHP/MySQL Join Question


  • Please log in to reply
8 replies to this topic

#1 centenial

centenial
  • Members
  • PipPipPip
  • Advanced Member
  • 59 posts

Posted 30 June 2006 - 11:34 PM

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,

#2 karthikeyan_coder

karthikeyan_coder
  • Members
  • PipPipPip
  • Advanced Member
  • 201 posts

Posted 30 June 2006 - 11:58 PM

do you need query alone or full coding for output?
www.karthi.us

#3 centenial

centenial
  • Members
  • PipPipPip
  • Advanced Member
  • 59 posts

Posted 01 July 2006 - 12:02 AM

Just query, thanks :)

#4 ssmK

ssmK
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 01 July 2006 - 12:27 AM

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;

#5 centenial

centenial
  • Members
  • PipPipPip
  • Advanced Member
  • 59 posts

Posted 01 July 2006 - 12:52 AM

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

#6 ssmK

ssmK
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 01 July 2006 - 02:40 AM

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

?>


#7 centenial

centenial
  • Members
  • PipPipPip
  • Advanced Member
  • 59 posts

Posted 01 July 2006 - 05:52 PM

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>';

Why is this not working? Can someone please help?

#8 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 01 July 2006 - 06:28 PM

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.
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#9 centenial

centenial
  • Members
  • PipPipPip
  • Advanced Member
  • 59 posts

Posted 01 July 2006 - 07:57 PM

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?

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





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users