Multiply SQL Queries Retrieving Column Information

[teensicle]

i checked the main site and it has been a whole heap of help so far. However im not sure how to accomplish a specific task and im now stuck on this. I have the following query: SELECT * FROM chjadb_vehicles WHERE v_make ='$make' AND v_year= '$year' The following statement works fine. However what i want is to add more query criterias such as body type, transmission and thing like that. How do i set something like that. I tried doing it like this: SELECT * FROM chjadb_vehicles WHERE v_make ='$make', v_year= '$year', v_transmission = '$trans' Doesn't work however any help would be appreciated!

[birbal]

you can use logical AND to add more condition http://dev.mysql.com...xpressions.html <=Resource

Edited July 11, 2012 by birbal

[teensicle]

I am developing a website where by people can buy and sell new or used cars. I want to set up a simple filter using php and sql statements and also of course html select boxes (drop down options). I have gotten all the other options to work with help and trust me it has been greatly appreciated. However i have been trying to get the "price filter" to work and been getting no where. Below is what my select box in html look like. <select name="price" size="0"> <option value="100000">Under $100,000</option><option value="<?php echo " BETWEEN '100000' AND '250000' "?>">Between $100,000 and $250,000</option><option value=">250000<500000">Between $250,000 and $500,000</option><option value=">500000<750000">Between $500, 000 and $750,000</option><option value=">7500000">Over $750,000</option> </select> This is my $_POST statement for the price variable: $price = mysql_real_escape_string($_POST['price']); This is my sql statement: $sql = sprintf("SELECT * FROM `chjadb_vehicles` WHERE `v_make` LIKE '$make'AND `v_year` = '$year'AND `v_trans` LIKE '$trans'AND `condition` LIKE '$cond' AND `b_style` LIKE '$b_style' AND `v_price` '$price'"); After which i have a basic loop that should print the results. Now from what i understand (correct or not), when the user selects a specific option from the drop down menu the value from "option value" is passed to the $_POST statement. After which it should replace $price with the actual text BETWEEN '100000' AND '250000', completing the query statement. So the final statement printed from the sql statement should be: "SELECT * FROM `chjadb_vehicles` WHERE `v_make` LIKE '$make'AND `v_year` = '$year'AND `v_trans` LIKE '$trans'AND `condition` LIKE '$cond' AND `b_style` LIKE '$b_style' AND `v_price` BETWEEN '100000' AND '250000'"` However i get this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''BETWEEN 100000 AND 250000'' at line 8 Any ideas as to where i went wrong? Thanks Regards,D. Ruddock

Edited July 11, 2012 by teensicle

[justsomeguy]

Remove the quotes around the price variable. This: `b_style` LIKE '$b_style' AND `v_price` '$price' is being replaced to this: `b_style` LIKE '$b_style' AND `v_price` 'BETWEEN '100000' AND '250000'' You can see that the error message includes that first quote before BETWEEN.

[teensicle]

My new select option:

Price	<select name="price" size="0">	<option value="< 100000">Under $100,000</option><option value=">= 100000 AND `v_price` <= 250000">Between $100,000 and $250,000</option><option value=">= 250000 AND `v_price` <= 500000">Between $250,000 and $500,000</option><option value=">= 500000 AND `v_price` <= 750000">Between $500, 000 and $750,000</option><option value=">7500000">Over $750,000</option>	</select>

PHP CODE:

$sql = sprintf("SELECT *FROM  `chjadb_vehicles`WHERE  `v_make` LIKE  '$make'AND  `v_year` =  '$year'AND  `v_trans` LIKE  '$trans'AND  `v_price` $priceAND  `condition` LIKE  '$cond'AND  `b_style` LIKE  '$b_style'");

Thanks it now works! i find that when i set a criteria and press submit the options reset itself. How can i control that? if you notice the make goes back to BMW, which is at the top of the select query list and also does the other options see example below: P.S ignore the horrible layout just trying to get the functionality working

Edited July 11, 2012 by teensicle

[justsomeguy]

When you're writing out the options for the dropdown you'll need to check for each option if the value for that option is the value that was submitted in $_POST, and if it was then add a "selected" attribute for the option to indicate that it should be selected. http://www.w3schools.com/tags/att_option_selected.asp

[teensicle]

So i know it's been a while but i'm back on the grind! . Can anyone suggest how i can include a option that will display "ALL" in a select box

[justsomeguy]

The common way would be to add an option with a blank value or something, and in the PHP code if that field is blank then don't add a filter for that field in the WHERE clause in the query. Only include filters in the WHERE clause for fields that they have chosen values for.