[PHP-DB] multiple table query error From: Steve Fitzgerald (sf <email protected>)
Date: 07/29/01

I've figured out (with some help) how to query two tables at the same time.
In order to get a corresponding CompanyName with a specified ContactID I
created a SQL query that selects both tables ($table_name1,$table_name2) and
then selects contacts.CompanyID = '$CompanyID'. The result is a match for
data in the contacts table, but the data from the company table is the same
no matter which ContactID I select. I have two CompanyNames (with unique
CompanyID) and two separate contacts each one having a different CompanyID
associated. What am I doing wrong?

Steve

<?
error_reporting(5);
$db_name = "crm";
$table_name1= "contacts";
$table_name2= "company";

$connection =  <email protected>("xxxx", "xxxx", "xxxx") or die("Couldn't
connect.");

$db =  <email protected>($db_name, $connection) or die("Couldn't select
database.");

$chk_id = "SELECT ContactID FROM $table_name1 WHERE ContactID =
$ContactID";
$chk_id_res =  <email protected>($chk_id,$connection) or die("Couldn't execute
query.");
$chk_id_num = mysql_num_rows($chk_id_res);

 $sql1 = "SELECT FirstName, LastName,WorkPhone, HomePhone, EmailName,
Birthday
  FROM $table_name1
  WHERE ContactID = '$ContactID'
  ";

$sql2 = "SELECT CompanyName,WebSite
  FROM $table_name1,$table_name2
  WHERE contacts.CompanyID='$CompanyID'
  ";
 $result_1 =  <email protected>($sql1,$connection) or die("Couldn't execute
query.");
 $result_2 =  <email protected>($sql2,$connection) or die("Couldn't execute
query.");

 while ($row = mysql_fetch_array($result_1)) {
  $FirstName = $row['FirstName'];
  $LastName = $row['LastName'];
     $WorkPhone = $row['WorkPhone'];
  $HomePhone = $row['HomePhone'];
  $EmailName = $row['EmailName'];
  $Birthday = $row['Birthday'];

 }

 while ($row = mysql_fetch_array($result_2)) {
  $CompanyName = $row['CompanyName'];
  $WebSite = $row['WebSite'];

 }

?> 

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: php-db-unsubscribe <email protected>
For additional commands, e-mail: php-db-help <email protected>
To contact the list administrators, e-mail: php-list-admin <email protected>