Jump to content

MySql Select Statement Problem


mdattani

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

Link to comment
https://forums.phpfreaks.com/topic/21257-mysql-select-statement-problem/
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’ ";
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,
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

Archived

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

×
×
  • Create New...

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.