table join 'the where statment is ambigous'

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
2bran
Forum Commoner
Posts: 38
Joined: Fri Mar 04, 2005 7:03 pm

table join 'the where statment is ambigous'

Post 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
The Monkey
Forum Contributor
Posts: 168
Joined: Tue Mar 09, 2004 9:05 am
Location: Arkansas, USA

Post by The Monkey »

Yup, just say ' WHERE customer.CustomerID = '$CustomerID'

;)

Awesome article on MySQL Joins
2bran
Forum Commoner
Posts: 38
Joined: Fri Mar 04, 2005 7:03 pm

Post 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.
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post 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?
2bran
Forum Commoner
Posts: 38
Joined: Fri Mar 04, 2005 7:03 pm

Post 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.
2bran
Forum Commoner
Posts: 38
Joined: Fri Mar 04, 2005 7:03 pm

Post by 2bran »

cheers for the help, you are right. I'm not being rude, sorry.
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post 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
2bran
Forum Commoner
Posts: 38
Joined: Fri Mar 04, 2005 7:03 pm

Post 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
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post 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
2bran
Forum Commoner
Posts: 38
Joined: Fri Mar 04, 2005 7:03 pm

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