Jump to content

Archived

This topic is now archived and is closed to further replies.

mdattani

MySql Select Statement Problem

Recommended Posts

HI,
I have a problem with my SQL query, firstly variables below all equal;
  $make = Audi
  $model = TT
  $mileagelow = 500
  $mileagehigh = 70000
  $pricelow = 1000
  $pricehigh = 20000

The SQL statement is shown below;

$query = "select * from car, cardetails, comments where car.make like '$make' and car.model like '$model'
and car.mileage >= ‘$mileagelow’ and car.mileage <= ‘$mileagehigh’
and car.price >= ‘$pricelow’ and car.price <= ‘$pricehigh’ ";

It can find a car without the mileage and price parameters, however when I include the last 2 lines, it can’t find any cars. The mileage and price variables are being posted from a Select Drop Box in a search page and the make and model variables are also being posted using a Select Drop Box. Not sure what the problem is with those two lines, any help would be much appreciated.

Thanks

Share this post


Link to post
Share on other sites
How are you supposed to be joining the car table to the cardetails and comments tables? You don't specify.

Could it be there is not data in table meeting those criteria?

BTW, you could also use

... and car.mileage BETWEEN ‘$mileagelow’ AND ‘$mileagehigh’
and car.price BETWEEN ‘$pricelow’ AND ‘$pricehigh’ ";

Share this post


Link to post
Share on other sites
I just got the hang of this, its LEFT JOIN

SELECT * FROM car LEFT JOIN cardetails ON car.fieldname = cardetails.fieldname LEFT JOIN comments ON car.fieldname = comments.fieldname WHERE car.make like '$make' AND car.model LIKE '$model'
AND car.mileage >= ‘$mileagelow’ AND car.mileage <= ‘$mileagehigh’
AND car.price >= ‘$pricelow’ AND car.price <= ‘$pricehigh’ ";

Now the fieldname I put in, needs to be the connector, to connect them together,

Share this post


Link to post
Share on other sites
Thanks 'only i can' I just tried doing it that way, and turns out, the way i was using the variables was completely wrong.
Also thanks Barnard for the BETWEEN tip, it's much more elegant to put it that way rather than  car.price >= $price and car. price etc etc etc
Thanks guys
Problem Solved

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.