Jump to content
teensicle

Multiply SQL Queries Retrieving Column Information

Recommended Posts

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!

Share this post


Link to post
Share on other sites

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

Edited by birbal

Share this post


Link to post
Share on other sites

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 by teensicle

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 criteria.jpg

Edited by teensicle

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×