Page 1 of 1

Query 1 table using query results of another table

Posted: Fri May 26, 2006 5:23 am
by transfield
Hello,
I'm a newbie to php / mysql.

My Situation:
I have successfully queried 1 table. My code is listed below. Now I want to use the query results of this table to query another table. Then I want to display the results of the second query. How do I do this?

Code: Select all

<?php  
$username="abc23";  
$password="abc23";  
$database="abc23";  
$host="localhost";  

mysql_connect ("$host","$username","$password");  
mysql_select_db($database) or die( "Where's the database man?");  

$mktime = date('Y-m-d');  

$query1=("SELECT * FROM developer_log WHERE expiry > '$mktime'");  
      
$result1=mysql_query($query1);  
$num=mysql_num_rows($result1);  

while ($row1 = mysql_fetch_array($result1))  
{  
echo"<b>Main Location: </b> ".$row1['id'].  
"<p><b>Sub Location: </b> ".$row1['company'].  
"<p><b>Percentage of Malays: </b> ".$row1['project'].  
"<p><b>Percentage of Chinese: </b> ".$row1['property'].  
"<p><b>Percentage of Indians: </b> ".$row1['email'].  
"<p><b>Percentage of Others: </b> ".$row1['cc_email'].  
"<p>"  
;  
}  
?>
Additional Information:
The other table is called developer & there are 3 fields in it. The field names are name, development & type. So the results of field company should query name. The results of field project should query development. The results of field property should query type.

Posted: Fri May 26, 2006 6:49 am
by GeXus
Assign your array variables to another variable, such as $id = $row['id'], then use that variable in your second query where appropriate

Posted: Fri May 26, 2006 6:50 am
by Chris Corbyn
You haven't really posted enough code or info about your database to answer this but it sounds like you should using joins in your sql queries rather than two queries.

Posted: Fri May 26, 2006 7:16 am
by transfield
Thank you all for your replies. I am more familiar with JOINs so I experimented. For some strange reason, my query results are being displayed in triplicates. My table developer_log has got 3 records whereas my table developer has got 1000's of records. My code is below. Could you tell me what I'm doing wrong here?

Code: Select all

$query1=("SELECT * FROM developer_log,developer WHERE developer.date_updated < developer_log.expiry");

$result1=mysql_query($query1);
$num=mysql_num_rows($result1);

while ($row1 = mysql_fetch_array($result1))
{
echo"<b>Year Approved: </b> ".$row1['year'].
"<p><b>Name of Developer: </b> ".$row1['name'].
"<p><b>Address: </b> ".$row1['development'].
"<p><b>Type of Property: </b> ".$row1['type'].
"<p><b>Levels: </b> ".$row1['levels'].
"<p><b>Number of Units: </b> ".$row1['quantity'].
"<p>"
;
}

Posted: Fri May 26, 2006 7:25 am
by GM
You haven't joined the tables correctly - you are probably missing a WHERE clause that ties the two tables correctly... maybe you need to say "WHERE developer.name = developer_log.name" or something similar.

If you post some information about what data is in the tables, I can help you to join them correctly.