Jump to content


MySql Select Statement Problem

  • Please log in to reply
3 replies to this topic

#1 mdattani

  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 19 September 2006 - 10:45 AM

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.


#2 Barand

  • Moderators
  • Sen . ( ile || sei )
  • 18,041 posts

Posted 19 September 2006 - 11:15 AM

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’ ";
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received


|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 onlyican

  • Members
  • PipPipPip
  • Advanced Member
  • 921 posts
  • LocationHants - UK

Posted 19 September 2006 - 11:21 AM

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,

Tell me the problem, I will try tell you the solution

#4 mdattani

  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 19 September 2006 - 05:31 PM

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

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users