Query 2 tables...

[wmguk]

Hey guys..

 

I have 2 tables - One Called Users, and one called Industry.

 

what I am trying to do is show a list of industries. if the $used is 0 then the co name is vacancy, however if $used is "1" then the co name needs getting from users where industry = industry..

 

            <?
//Connection
include "admin/scripts/connection.php";  
if (!$con)  
{  
  die( 'Could not connect: ' . mysql_error() );  
} 
mysql_select_db($db, $con); 

//Get Industry Listing
$sql = "SELECT * FROM industry"; 
$result = mysql_query( $sql ) or die( "<strong>Query Error</strong>: " . mysql_error() . "<br><strong>Query</strong>: $sql<br><br>" ); 
  
while($row = mysql_fetch_array($result)) 
{ 
//Check used in industry to see if it is 0 or 1 and if it is 0 display vacancy.
if ($row['used'] == "0"){
$coname = "Vacancy" ;
$industry = $row['industry'];

} else {

//Check the users table where industry is the same industry as SQL above.
	$sql1 = "SELECT * FROM users WHERE industry = $industry"; 
$result1 = mysql_query( $sql1 ) or die( "<strong>Query Error</strong>: " . mysql_error() . "<br><strong>Query</strong>: $sql1<br><br>" ); 
  
while($row = mysql_fetch_array($result1)) 
{ 
$coname = $row['coname'] ;
}
}
echo $coname ;
?>

 

However for some reason when displaying coname it isnt checking them all, its just checking the last industry where used=0.... Is there a better way to do this dual query?

[rarebit]

check mysql manual for 'JOIN'

[btherl]

You can try this query:

 

SELECT i.industry, i.used, u.coname
FROM industry i
LEFT JOIN users u ON (i.industry = u.industry)

 

Using a left join guarantees that you will get every industry in your result, instead of just those matched in users table.

 

The only issue you might have here is if there are multiple matching rows (or if I made a mistake in the sql )

 

I think the problem in your original code is that you do your echo after the main loop has finished.  It should be inside the main loop.  The reason it's not easy to notice is that your indenting is not consistent.