mdattani Posted September 19, 2006 Share Posted September 19, 2006 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 = 20000The 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted September 19, 2006 Share Posted September 19, 2006 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’ "; Quote Link to comment Share on other sites More sharing options...
onlyican Posted September 19, 2006 Share Posted September 19, 2006 I just got the hang of this, its LEFT JOINSELECT * 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, Quote Link to comment Share on other sites More sharing options...
mdattani Posted September 19, 2006 Author Share Posted September 19, 2006 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 etcThanks guysProblem Solved Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.