multiple table query error
| From: | Steve Fitzgerald | Date: | Sun, 29 Jul 2001 21:50:05 +0000 |
| Subject: | multiple table query error | ||
| Groups: | php.db | ||
| Request: | Send a blank email to php-db+get-10881@lists.php.net to get a copy of this message | ||
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 = @mysql_connect("xxxx", "xxxx", "xxxx") or
die("Couldn't
connect.");
$db = @mysql_select_db($db_name, $connection) or die("Couldn't select
database.");
$chk_id = "SELECT ContactID FROM $table_name1 WHERE ContactID =
$ContactID";
$chk_id_res = @mysql_query($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 = @mysql_query($sql1,$connection) or die("Couldn't execute
query.");
$result_2 = @mysql_query($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'];
}
?>