PHP/MySql JOIN, to get foreign key but need more data!

[cypher_block]

Will try to get straight to the question...

One of my tables 'case' has a 'status_id'(as a field), the status_id is linked to another table called 'status' using a primary/ foreign key relationship (basic stuff).  Now the 'status' table has the 'status_id' and a 'status_name'. 

 

When I currently echo out the case info into html, I of course get the 'status_id' (a tinyint value). What i really want us to get the name associated to the status_id from the 'status' table and display that in the html output as opposed to the numeric value of the field.

 

Using JOIN i just get the status_id from the 'status' table, same shit as i get from the 'case' table. 

 

How do i get the name?  I know i can write a function to take the status_id from the 'case' table and then query the 'status' table for that ID, and return the value, and query the 'status' table for the name....but i'm pretty sure SQL can do this.  just not sure how.

 

If i didnt make any sense, let me know please.  I find it difficult to explain stuff like this at times.

 

TNKX

[soycharliente]

It is returned in the row. You just reference the variable. Run the query in phpMyAdmin or something and look at exactly what is returned. It will show you all the field names and rows associated with the query.

 

<?php
$sql = "SELECT * FROM case c JOIN status s ON c.status_id=s.status_id WHERE ...";
$result = mysql_query($sql) OR DIE ("You suck at writing SQL!");
if (mysql_num_rows($result) > 0)
{
while ($row = mysql_fetch_array($result))
{
	echo "<p>The status is {$row['status_name']}!</p>";
}
}
else
{
echo "<p>There is no status to report!</p>";
}
?>

[cypher_block]

Thanks!  I ran my original query through phpmyadmin and saw what I was doing wrong right away.

Pretty new to php, and like an idiot i have been trying to run my queries against the db directly from php, from now on i will test through phpmyadmin first.

 

Thanks for the advice, it like one of those thing were you go "Damn! why did i never think of doing that".

 

Cyph