Page 1 of 1

table join 'the where statment is ambigous'

Posted: Mon May 02, 2005 3:56 pm
by 2bran
Hows it going guys,

I've got this problem with a table join. I'm trying to select all the information from three different tables using the same variable inserted by the user. The inputted value is the customer ID and its present in all three tables so it thought i will use it to pull all the information out.

all the customer IDs are the same values as well in the tables


Its a very simple select query but i think i've done soemthing wrong

Code: Select all

$result = mysql_query("SELECT * FROM customer,jobs,wanker WHERE CustomerID='$CustomerID'")or die(mysql_error());
the customer ID is being inserted by the user in the HTML form.

it will then be outputted like this

Code: Select all

while ($row = mysql_fetch_array($result))
{
	echo "<tr><td>CustomerInfo.</td></tr><tr><td>CustomerID</td><td>Surname</td><td>Firstname</td><td>Address1</td><td>Address2</td><td>Address3</td><td>Postcode</td><td>HomeNo</td><td>MobileNo</td><td>E-mail</td></tr>";
	echo "<tr>";
	echo "<td>" . $row['CustomerID'] . "</td>";
	echo "<td>" . $row['Surname'] . "</td>";
    echo "<td>" . $row['Firstname'] . "</td>";
    echo "<td>" . $row['Address1'] . "</td>";
    echo "<td>" . $row['Address2'] . "</td>";
    echo "<td>" . $row['Address3'] . "</td>";
    echo "<td>" . $row['Postcode'] . "</td>";
	echo "<td>" . $row['HomeNo'] . "</td>";
	echo "<td>" . $row['MobileNo'] . "</td>";
	echo "<td>" . $row['Email'] . "</td>";
	echo "</tr>";
	echo "<tr>";
	echo "</tr>";
	echo "<tr>";
	echo "</tr>";
	echo "<tr>";
	echo "</tr>";
	echo "<tr><td>Appliance Info</td><tr><td>JobNo</td><td>ModelNo</td><td>SerialNo</td><td>ProductNo</td><td>ExpiryDate</td><td>PurchaseDate</td></tr>";
	echo "<tr>";
	echo "<td>" . $row['JobNo'] . "</td>"; 
	echo "<td>" . $row['ModelNo'] . "</td>";
    echo "<td>" . $row['SerialNo'] . "</td>";
    echo "<td>" . $row['ProductNo'] . "</td>";
    echo "<td>" . $row['ExpiryDate'] . "</td>";
    echo "<td>" . $row['PurchaseDate'] . "</td>";
	echo "</tr>";
	echo "<tr>";
	echo "</tr>";
	echo "<tr>";
	echo "</tr>";
	echo "<tr>";
	echo "</tr>";
    echo "<tr>";
	echo "<tr><td>Job Info</td></tr><tr><td>Fault</td><td>Instructions</td><td>Calldate</td><td>Calltime</td><td>Postcode</td><td>Engineer</td><td>EngineerNo.</td></tr>";
	echo "<td>" . $row['Fault'] . "</td>";
    echo "<td>" . $row['Instructions'] . "</td>";
    echo "<td>" . $row['CallDate'] . "</td>";
    echo "<td>" . $row['CallTime'] . "</td>";
    echo "<td>" . $row['Postcode'] . "</td>";
    echo "<td>" . $row['EngineerNo'] . "</td>";
	echo "</tr>";


}

	echo "</table>";
if anyone has any ideas why query is wrong please let me know

yours william

Posted: Mon May 02, 2005 3:58 pm
by The Monkey
Yup, just say ' WHERE customer.CustomerID = '$CustomerID'

;)

Awesome article on MySQL Joins

Posted: Mon May 02, 2005 4:14 pm
by 2bran
cheers for the help mate, it worked but its displaying all the rows in every table. I only wish to display 1 row from each table through the customer ID field

P.S theres only one customer ID field per row because its set to auto increment.

Posted: Mon May 02, 2005 4:18 pm
by shiznatix
the * means anything so if you want customer,jobs,wanker you shouldnt use it

Code: Select all

$result = mysql_query("SELECT customer,jobs,wanker FROM WHERE CustomerID='$CustomerID'")or die(mysql_error());

$row = mysql_fetch_row($query);

echo $row[0]; //customer
echo $row[1]; //jobs
echo $row[2]; //wanker
that will return only the one row and whatnot. is that what you needed?

Posted: Mon May 02, 2005 4:22 pm
by 2bran
well i can see what u mean, i want everything to displayed but its not joing the tables correctly. I need ot to pull ou the information specificallty to do with the customer ID number and only that specifc customer ID number.

Posted: Mon May 02, 2005 4:24 pm
by 2bran
cheers for the help, you are right. I'm not being rude, sorry.

Posted: Mon May 02, 2005 4:27 pm
by shiznatix
no no your not being rude, but did you figure it out? you do know between the FROM and WHERE things there has to be the table name so it has to be like FROM table_name WHERE customerid='$CustomerId' . if you have any more questions feel free to postq

Posted: Mon May 02, 2005 4:29 pm
by 2bran
well i would like to selct the customer ID form all three tables amd display each row in turn. sorry- im very new to PHP

Posted: Mon May 02, 2005 4:40 pm
by shiznatix
its cool do this

Code: Select all

$query1 = mysql_query("SELECT fieldA,fieldB,fieldC,fieldD FROM table_1 WHERE customerId='$customerId'");

if (!$query1)
{
    die(mysql_error());
}

$row1 = mysql_fetch_row($query1);

echo $row1[0];//fieldA (for $query1)
echo $row1[1];//fieldB (for $query1)
echo $row1[2];//fieldC (for $query1)
//ect...

$query2 = mysql_query("SELECT fieldA,fieldB,fieldC,fieldD FROM table_2 WHERE customerId='$customerId'");


if (!$query2)
{
    die(mysql_error());
}

$row2 = mysql_fetch_row($query1);

echo $row2[0];//fieldA (for $query2)
echo $row2[1];//fieldB (for $query2)
echo $row2[2];//fieldC (for $query2)
//ect...

//DO SAME THING FOR THIRD TABLE
do you understand how to do it now? feel free to post if you have problems

Posted: Mon May 02, 2005 5:19 pm
by 2bran
yeah it worked but i think the second IF query is getting in the way because the second load of information from the wanker table isn't being displayed