[Solved] SQL Syntax when using HTML <Select>

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
tmac25
Forum Newbie
Posts: 21
Joined: Fri Dec 28, 2012 5:24 pm

[Solved] SQL Syntax when using HTML <Select>

Post 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!
Last edited by tmac25 on Sun Apr 07, 2013 4:38 am, edited 1 time in total.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: SQL Syntax when using HTML <Select>

Post 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.
tmac25
Forum Newbie
Posts: 21
Joined: Fri Dec 28, 2012 5:24 pm

Re: SQL Syntax when using HTML <Select>

Post 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!
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: SQL Syntax when using HTML <Select>

Post 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
tmac25
Forum Newbie
Posts: 21
Joined: Fri Dec 28, 2012 5:24 pm

Re: SQL Syntax when using HTML <Select>

Post 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!
Post Reply