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
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’ ";
Link to comment
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,
Link to comment
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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.