Multiple condition select from MySQL

why are you using the table name when you're only selecting from one table? oh, you're using two tables but not stating the other one...

FROM listing, body_style

Why is only Year back-ticked?

listing.price should be less than or equal to $price, same with mileage.

What errors are you getting and what language are you using? This post may have better response in the scripting forum, would you like me to move it?

Moving it will be fine if that will help...
1. I am using 2 tables but just removed the deffinition of the other one for troubleshooting purposes.
I am only using the field from the "body_style" table for one field which is not one of the search parameters so I removed it just in case it interfered with my output

2. I am using PHP. I don't get any error messages... I parses fine... just that the output I am looking for is not there.

Any help would be great
Thanks

If you're looking for body_style.ID and body_style.style, I think you need to add body_style to the FROM part.

I will move the post as the folks who hang in the Server-Side Programming forum are fantastic with this.

Do you have a sample URL? And code? .phps sort of thing (with passwords removed, of course) and some more information on your table structure may help.

mizzy wrote:
//***********************
mysql_query("SELECT listing.ID, listing.vehicle, listing.price, listing.Make_ID, listing.style_ID, listing.`Year`, listing.milage, listing.image, listing.color, listing.fuel, listing.enginesize, listing.transmission, listing.aircon, listing.sunroof, listing.airbags, listing.powersteering, listing.alloywheels, listing.featured, listing.other, body_style.ID AS sID, body_style.style, listing.reg
FROM listing
WHERE listing.Make_ID LIKE '$make' AND listing.`Year`<'$year' AND listing.style_ID LIKE '$style' AND listing.price<'$price' AND listing.milage<'$milage'");
//***********************

What column type are listing.Make_ID ,listing.`Year`,listing.price AND listing.milage
- you cannot use < or > on varchar char or text fields if they are not text then don't use ' around the values in the WHERE
- Suzanne is right you have to put body style in the FROM
- is listing.style_ID a text or varchar or char if not LIKE wont work

Its better to ommit a field than use "LIKE '%'".

Year must be in backticks because Year is also a mysql function. Enclosing it in backticks tells mysql you want the field.

Do you get a mysql error? Try having PHP print out the query being performed, then copy and paste it into a mysql client (phpMyAdmin or whatever) and run it.

This is my table structure for the two tables in the query:

//**********************************
CREATE TABLE `listing` (
`ID` int(11) NOT NULL auto_increment,
`vehicle` varchar(32) default NULL,
`price` int(11) NOT NULL default '0',
`Make_ID` tinyint(4) NOT NULL default '0',
`style_ID` tinyint(4) NOT NULL default '0',
`Year` int(9) default NULL,
`reg` varchar(16) default NULL,
`milage` int(11) default NULL,
`image` varchar(150) default 'notavailable.jpg',
`color` varchar(32) default NULL,
`fuel` varchar(32) default NULL,
`enginesize` varchar(32) default NULL,
`transmission` varchar(32) default NULL,
`aircon` tinyint(4) NOT NULL default '0',
`sunroof` tinyint(4) NOT NULL default '0',
`airbags` tinyint(4) NOT NULL default '0',
`powersteering` tinyint(4) NOT NULL default '0',
`alloywheels` tinyint(4) NOT NULL default '0',
`featured` tinyint(4) NOT NULL default '0',
`other` text,
PRIMARY KEY (`ID`)
) TYPE=MyISAM;

CREATE TABLE `body_style` (
`ID` int(11) NOT NULL auto_increment,
`style` char(32) default NULL,
PRIMARY KEY (`ID`)
) TYPE=MyISAM;

//***************************************
And my query before I removed anything for troubleshooting is as follows:

//***************************************
mysql_query("SELECT listing.ID, listing.vehicle, listing.price, listing.Make_ID, listing.style_ID, listing.`Year`, listing.milage, listing.image, listing.color, listing.fuel, listing.enginesize, listing.transmission, listing.aircon, listing.sunroof, listing.airbags, listing.powersteering, listing.alloywheels, listing.featured, listing.other, body_style.ID AS sID, body_style.style, listing.reg
FROM listing, body_style
WHERE Make_ID LIKE '$make' AND `Year`<'$year' AND style_ID LIKE '$style' AND price<'$price' AND milage<'$milage' AND body_style.ID LIKE listing.style_ID");

//***************************************

My search form posts the following field:
$make - (ID number of a car make accoring to the "car_make" table)
$year - (Lates year of cars they want to view)
$style - (This is an integer value for the style ID which will call the style name from the "body_style" table)
$price - (Highest price of cars they want to view)
$milage - (Highest milage of cars they want to view)

I use the LIKE condition so that I can use % for the default field just in case they want to view all cars will all milages and all prices etc.... For the ones that have < conditions, I use 9999999 as a default so that if they want to view all cars at any price, they would view all cars less than $9,999,999 etc.

I hope this makes it easier for you to look at and make suggestions on how it can be done...
Thanks for the input so far....
Mizzy

ok so first you cant use LIKE on int values and don't use ' around the values like Mark said the backticks around the field names are ok but the quotes around numerical values are not. also when using more then one table you have to name the table before the fields using a dot
so change this

Quote:
WHERE Make_ID LIKE '$make' AND `Year`<'$year' AND style_ID LIKE '$style' AND price<'$price' AND milage<'$milage' AND body_style.ID LIKE listing.style_ID");

to this

IMO you should change the price to decimal(7,2) so you can have more price precision but that a personnal opinion