display data from two tables using populated drop down box

rskuse · Jan 22, 2004

Hi,

My aim: to display data from a database dependant on the option selected in the drop down.

I have successfully managed this without a hitch, however I also want to be able to display data from a second table dependant on the id of the data displayed from the first table - all at the same time!!

I'm using the following code to populate the drop down box:

PHP:--------------------------------------------------------------------------------

<html>
<body>
<form name="clientlist" method="post" action="clients.php">
<table>
<tr>
<td> <select name="client" onChange="this.form.submit()">
<option select>select client...</option>
<?php
//connect to the DB
include('dbs.php');
//set up the query
$query = "select clients.clientid, clients.company from clients";
//run the query
$result = mysql_query($query, $connection) or die(mysql_error());
//The following lines return the results from the query and assign them to variables
while($row = mysql_fetch_array($result)){
$company = $row['company'];
$clientid = $row['clientid'];
//This line prints out the HTML Code with the returned data
echo "<option value='$clientid'>$company</option>";
};
mysql_free_result($result);
?>
</select> </td>
</tr>
</form><br><br>

--------------------------------------------------------------------------------

and this code to display my data:

PHP:--------------------------------------------------------------------------------

<?php
//connect to the DB
include('dbs.php');

if ($_POST['client'] <> "&quot

{
$id = $_POST['client'];
}
//set up query
$query = "SELECT * FROM clients, contacts where clients.clientid = '$id' AND clients.clientid = contacts.clientid";
//run query
$result = mysql_query($query, $connection) or die(mysql_error());

echo"<table>";

if($row = mysql_fetch_array($result)) {
$clientid = $row['clientid'];
$contactid = $row['contactid'];
$company = $row['company'];
$address1 = $row['address1'];
$address2 = $row['address2'];
$town = $row['town'];
$county = $row['county'];
$postcode = $row['postcode'];
$mainphone = $row['mainphone'];
$fax = $row['fax'];
$mainemail = $row['mainemail'];
$web = $row['web'];
$notes = $row['notes'];
$name = $row['name'];
$phone = $row['phone'];
$ext = $row['ext'];
$mobile = $row['mobile'];
$email = $row['email'];

echo "
<tr><td>$company</td><td>$name</td></tr>
<tr><td>$address1</td><td>$phone</td></tr>
<tr><td>$address2</td><td>$ext</td></tr>
<tr><td>$town </td><td>$mobile</td></tr>
<tr><td>$county</td><td>$email</td></tr>
<tr><td colspan=2>$postcode</td><tr>
<tr><td colspan=2>$mainphone</td><tr>
<tr><td colspan=2>$fax</td><tr>
<tr><td colspan=2>$mainemail</td><tr>
<tr><td colspan=2>$web</td><tr>
<tr><td colspan=2>$notes</td><tr>";

}//end while loop

mysql_free_result($result);

echo "</table>";

?>
</body>
</html>

--------------------------------------------------------------------------------

(I'm not concerned at the moment how the data will display on the page!)

but, it returns the first value from the first table and the second value from the second table (even though id's don't match) and will only display data for the first option in the drop down box and nothing for the second option....

So, I think my problem lies in my query because when I use:

PHP:--------------------------------------------------------------------------------
//set up query
$query = "SELECT * FROM clients where clients.clientid = '$id'";

--------------------------------------------------------------------------------

to display data from the first table it returns data as I expect it to!

Can anyone help me?

(sorry it's such a long post but thought I needed to explain myself clearly!)

Thankyou in advance.....

sleipnir214 · Jan 22, 2004

You have two choices here.

You can relate the two tables in a single SELECT query.

You can query one table, then use one column of the return of that query to generate a dependent second query string. Then pass query2 to MySQL and parse the return. Use nested loops.

You can send multiple queries to MySQL through one connection. You can even scroll through multiple query returns simultaneously -- just save the returns from mysql_connect() in different variables.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

rskuse · Jan 23, 2004

HI, thanks for the info. I have tried using muliple queries and the first table now returns the correct results but the second table always returns the same value.

I'm now using the following code:

//set up query
$query = "SELECT * FROM clients where clients.clientid = '$id'";
$query1 = "SELECT * FROM clients, contacts where clients.clientid = contacts.clientid";
//run query
$result = mysql_query($query, $connection) or die(mysql_error());
$result1 = mysql_query($query1, $connection) or die(mysql_error());

echo"<table>";

if($row = mysql_fetch_array($result)){
$clientid = $row['clientid'];
$contactid = $row['contactid'];
$company = $row['company'];
$address1 = $row['address1'];
$address2 = $row['address2'];
$town = $row['town'];
$county = $row['county'];
$postcode = $row['postcode'];
$mainphone = $row['mainphone'];
$fax = $row['fax'];
$mainemail = $row['mainemail'];
$web = $row['web'];
$notes = $row['notes'];

if($row1 = mysql_fetch_array($result1)) {
$name = $row1['name'];
$phone = $row1['phone'];
$ext = $row1['ext'];
$mobile = $row1['mobile'];
$email = $row1['email'];

echo "
<tr><td>$company</td><td>$name</td></tr>
<tr><td>$address1</td><td>$phone</td></tr>
<tr><td>$address2</td><td>$ext</td></tr>
<tr><td>$town </td><td>$mobile</td></tr>
<tr><td>$county</td><td>$email</td></tr>
<tr><td>$postcode</td><td></td></tr>
<tr><td>$mainphone</td><td></td></tr>
<tr><td>$fax</td><td></td></tr>
<tr><td>$mainemail</td><td></td></tr>
<tr><td>$web</td><td></td></tr>
<tr><td>$notes</td><td></td></tr>";
}
}//end while loop

mysql_free_result($result);

echo "</table>";

?>

Any further advice would be much appreciated - thankyou...

Westbury · Jan 23, 2004

Nest the second query into the result of the first query.

Code:

    //set up query 
    $query = &quot;SELECT * FROM clients where clients.clientid = '$id'&quot;;
    //run query 
    $result = mysql_query($query, $connection) or die(mysql_error());
        
    echo&quot;<table>&quot;;
    
    if($row = mysql_fetch_array($result)){
    	$clientid = $row['clientid'];
    	$contactid = $row['contactid'];
    	$company = $row['company'];
    	$address1 = $row['address1'];
    	$address2 = $row['address2'];
    	$town = $row['town'];
    	$county = $row['county'];
    	$postcode = $row['postcode'];
    	$mainphone = $row['mainphone'];
    	$fax = $row['fax'];
    	$mainemail = $row['mainemail'];
    	$web = $row['web'];
    	$notes = $row['notes'];
    
    	// start second query
    	$query1 = &quot;SELECT * FROM contacts where clientid = '$clientid'&quot;;
    	$result1 = mysql_query($query1, $connection) or die(mysql_error());
   
    	if($row1 = mysql_fetch_array($result1)) {
    		$name = $row1['name'];
    		$phone = $row1['phone'];
    		$ext = $row1['ext'];
    		$mobile = $row1['mobile'];
    		$email = $row1['email'];
    
    		echo &quot;
    		<tr><td>$company</td><td>$name</td></tr>
    		<tr><td>$address1</td><td>$phone</td></tr>
    		<tr><td>$address2</td><td>$ext</td></tr>
    		<tr><td>$town </td><td>$mobile</td></tr>
    		<tr><td>$county</td><td>$email</td></tr>
    		<tr><td>$postcode</td><td></td></tr>
    		<tr><td>$mainphone</td><td></td></tr>
    		<tr><td>$fax</td><td></td></tr>
    		<tr><td>$mainemail</td><td></td></tr>
    		<tr><td>$web</td><td></td></tr>
    		<tr><td>$notes</td><td></td></tr>&quot;;
    	}// end second result	
    }//end first result
         
     mysql_free_result($result);
     mysql_free_result($result1);
     
     echo &quot;</table>&quot;;

?>

Try that. I assume that as you are using if statements to fetch the array from the result, that your only expecting one result from each id.

Just as a note. Check over this code for typos... Im slightly hungover and my typing skills aren't what they normally are!!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

display data from two tables using populated drop down box

rskuse

Technical User

sleipnir214

Programmer

rskuse

Technical User

Westbury

Programmer

Similar threads

Part and Inventory Search

Sponsor