Mark Hensler — Tue, 25 Nov 2003 17:45:16 +0000

<?php
$where = array();
if ($make!='') $where[] = \"listing.Make_ID LIKE '$make'\";
if ($year!='') $where[] = \"listing.`Year`<$year\";
if ($style!='') $where[] = \"listing.style_ID LIKE '$style'\";
if ($price!='') $where[] = \"listing.price LIKE '$price'\";
if ($milage!='') $where[] = \"listing.milage LIKE '$milage'\";
if ($price!='') $where[] = \"listing.price LIKE '$price'\";
$where = implode(' AND ', $where);
//mysql_query
die(\"
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
    LEFT JOIN body_style ON listing.style_ID=body_style.ID
WHERE $where\");
?>

Take the output from this and try it in phpMyAdmin.

druagord — Tue, 25 Nov 2003 13:49:11 +0000

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

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

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

Updated - Multiple condition select from MySQL

mizzy — Tue, 25 Nov 2003 11:01:41 +0000

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

Mark Hensler — Mon, 24 Nov 2003 17:41:23 +0000

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.

druagord — Mon, 24 Nov 2003 14:21:57 +0000

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

Suzanne — Sun, 23 Nov 2003 22:33:20 +0000

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 — Sun, 23 Nov 2003 19:40:06 +0000

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

Suzanne — Sun, 23 Nov 2003 18:17:40 +0000

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?