Page 1 of 1

[Solved] SQL Syntax when using HTML <Select>

Posted: Sat Apr 06, 2013 6:08 am
by tmac25
Hey,

I'm using SQL Server 2008 R2

I'm a little stumped on how to properly coordinate a SQL query involving HTML selects as filters for the query. Now, I have no issues using the filters if I want something specific from both. (Querying a rental database and I want a blue boat, easy.) However, the issue comes in when I want all colors available to a specific vehicle. (a green, blue or red boat.)

The main point to focus on is the "All" options. The value of the options are blank. So, essentially, if I left the filters to "All" and "All", it should pull the entire database (I have other criteria I'll work in for my actual project, but this part is the tricky one for me.) Can I change that to something else better for the SQL statement?

JSFIDDLE
SQLFIDDLE

My current PHP syntax is simply this, but I can't use the "ALL"s in the selects.

Code: Select all

<?php
$color=$_POST['color'];
$type=$_POST['type'];

$sql = "SELECT * FROM dbo.rentals
WHERE (color=$color AND type=$type)";
?>
An IF statement? Case? I've heard these terms but I'm not sure where to start.

Any help is appreciated!

Re: SQL Syntax when using HTML <Select>

Posted: Sat Apr 06, 2013 6:48 am
by requinix
Instead of trying to search for "all", only add a search condition when the value is not "all". No condition means it'll return all the results.

Re: SQL Syntax when using HTML <Select>

Posted: Sat Apr 06, 2013 9:20 pm
by tmac25
requinix wrote:Instead of trying to search for "all", only add a search condition when the value is not "all". No condition means it'll return all the results.
Hello!

Would it be more appropriate to do this on an SQL level or PHP (I'm leaning toward PHP). Would you recommend an IF statement? Just trying to visualize what you mean by "only add a search condition when..."

Thanks!

Re: SQL Syntax when using HTML <Select>

Posted: Sat Apr 06, 2013 11:06 pm
by requinix
PHP. Very.

Code: Select all

$conditions = array()
if (should search on color AND color != "all") {
    $conditions[] = matching color
}
if (should search on type AND type != "all") {
    $conditions[] = matching type
}

$query = "SELECT fields FROM table";
if (have any $conditions) {
    $query .= " WHERE " . implode(" AND ", $conditions);
}
execute $query

Re: SQL Syntax when using HTML <Select>

Posted: Sun Apr 07, 2013 4:38 am
by tmac25
You sir (or madam), are a gentleman (or lady) and a scholar, this worked perfectly by amending it to the end of the query and have was able to retrieve EXACTLY the desired results with the filtering

Thank you so much!