using SELECT to get id from table

[botcha]

HI , newbie here !

 

I have 4 tables ( event, price, vehicle, town)

 

I want to select from a database when a user chooses the town, event and number of passengers (which I have got the form working ok)

 

when displaying results I can get it to retrieve the $town and $event details fine,I  just need  the vehicleID column in the vehicle table to match the price.vehicleID when it is equal to $pax

 

 

 

$calc = ("

SELECT* FROM price, vehicle WHERE price.townID = $town AND price.eventID = $event AND $pax == vehicle.passenger AND price.vehicleID = vehicle.vehicleID

");

 

if you have a look at www.hirealimo.com.au you will see what m trying to do.

 

thanks in advance

 

 

 

[dzelenika]

there's no == operator in mysql?

 

 

 

$calc = ("

SELECT* FROM price, vehicle WHERE price.townID = $town AND price.eventID = $event AND $pax == vehicle.passenger AND price.vehicleID = vehicle.vehicleID

");

 

[botcha]

thanks for the quick response,

ive removed the ==

 

now i get:

 

Unknown column 'vehicle.passenger' in 'where clause'

 

the only other way i can think of doing it is to run a SELECT query to retrive the vehicle.vehicleID (based on the input from the number of passengers selected) then run my other query again.

 

[dzelenika]

do you have  passenger  column in your vehicle table?

[botcha]

 

price table

priceID vehicleID townID eventID price

1               1     1               1      444

2 2 1 2 555

3 1 1 3 345

4 1 2 1 457

5 4 2 2 127

6 1 2 3 333

7 4 3 1 880

8 1 3 1 1500

9 2 3 1 900

vehicle table

vehicleID vehtype passengers    description

1              limo          14              stretched territor\

2              limo          9              stretched fairlane

3              sedan      4              fairlane sedan

4              limo        12              Ford FG stretch limo

 

 

this is what i have so far:

$calc = ("SELECT * FROM price WHERE price.townID = $town AND price.eventID = $event INNER JOIN vehicle ON price.vehicleID = vehicle.vehicleID WHERE vehicle.passengers >= $pax ");

 

my goal is to show each vehicle option as a result, based upon the TOWN they choose, the EVENT (eg wedding) and number of pax

 

price.vehicleID is a foreign key from vehicle.vehicleID

[botcha]

link is

 

hirealimo.com.au/code1.php

[Pikachu2000]

You're getting the unknown column error because the field name is passengers.

[botcha]

Thanks Pikachu2000, but i dont understand?

 

can you hit refresh on this:

 

hirealimo.com.au/code1.php

 

is it ok to use 2 WHERE 's in one select query?

[El Chupacodra]

Sure you can, you use WHERE a=b AND c=d.

What Pikachu says is that your column is called passengers and the query in your first post says passenger.

Look at the last letter - they're not the same.

[botcha]

ok cool, thanks, i fixed that earlier on.

 

I have fixed my problem using the USING thingo:

 

 

SELECT * FROM price INNER JOIN vehicle USING (vehicleID) WHERE vehicle.passengers >= 1 AND price.townID = 1 AND price.eventID = 1

 

works great !!