Query 1 table using query results of another table

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
transfield
Forum Commoner
Posts: 34
Joined: Wed Feb 22, 2006 6:00 am

Query 1 table using query results of another table

Post 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.
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post by GeXus »

Assign your array variables to another variable, such as $id = $row['id'], then use that variable in your second query where appropriate
Last edited by GeXus on Fri May 26, 2006 6:50 am, edited 1 time in total.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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.
transfield
Forum Commoner
Posts: 34
Joined: Wed Feb 22, 2006 6:00 am

Post 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>"
;
}
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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.
Post Reply