Need Help: Mysql Database Query;

[dilbert]

Hi i am new to this, first time joined a forum. Anyway my problem is i have two tables:

table1: establishment(estNo mediumint, estName varchar, other details..) Primary key=estNo;

table2: foodType(food varchar(), estNo mediumint) Foreign key=estNo, Primary Key=(food, estNo);

user inputs: inputEstName for establishment and inputFood for foodType;

im trying to set up a query via php that does the following:
SELECT all establishment details from establisment
ONLY IF
establishment.estName=inputEstName;
foodType.estNo=establishment.estNo && foodType.food = inputFood;
(where I get the estNo when a record has been found for the estName input)

the query in php that i have got so far looks like this:
$select= "select estNo, estName, details from establishment
where estName=" . $inputEstName .
"AND establishment.estNo=foodType.estNo
AND foodType.food=" . $ inputFood;

Help would be much appreciated,

Eric
When you are in a forum the very first time: READ THE POSTING GUIDELINES!!, especially the part on enclsosing code with php, html ot code tags!! - Ronald

[cassbiz]

$select= "select estNo, estName, details from establishment
where estName=" . $inputEstName .
"AND establishment.estNo=foodType.estNo
AND foodType.food=" . $ inputFood;

You can try to enter your select string like this. I have found it to work for me.

$select= ("SELECT estNo, estName, details FROM establishment
WHERE estName='$inputEstName' AND establishment.estNo=foodType.estNo
AND foodType.food='$inputFood'");
 

[dilbert]

in the where query part:

WHERE estName=$inputEstName AND establishment.estNo=foodType.estNo
AND foodType.food=$inputFood";
 

I get an error reporting "unknown column foodType.estNo in where clause".

I presume i need to define somewhere a connection to the foodType table to access the foodType.estNo field...

Eric

[cassbiz]

in the where query part:

WHERE estName=$inputEstName AND establishment.estNo=foodType.estNo
AND foodType.food=$inputFood";
 

I get an error reporting "unknown column foodType.estNo in where clause".

I presume i need to define somewhere a connection to the foodType table to access the foodType.estNo field...

Eric

Try this code

$select= ("SELECT establishment.estNo, foodType.estNo, establishment.details FROM establishment,
foodType WHERE establishment.estName='$inputEstName' AND establishment.estNo=foodType.estNo
AND foodType.food='$inputFood'");
 

[cassbiz]

I got the following code from

builder.com

[dilbert]

cheers that worked perfectly