Help with making an SQL query based on some rules

Question

Hello there.

I've got this HTML form where I have multiple input elements: text, radio, and so on. These are intended to be options, conditional items to apply in an SQL query in order to pull more specific data from a database.

For example, the first input field is a textbox, the second a radio button, and the third a SELECT with two options. With the first, I would add a LIKE %name% sentence in the SQL query. The second is a radio button group with, let's say, a color, so I would add a WHERE color = $item comparing the one chosen with the database column. The third would pretty much be just like the second.

Now, I guess I could just use if sentences comparing the three items, in order to know which one to add to the SQL query, but my question is: is there a smarter way to do it? Like an array checking if those variables are set (I'm still using an if here, so nevermind).

I program simple things from time to time, since I've never done anything significantly complex, but sometimes, even for simple things, no matter how hard I strive to design something, I just can't.

In this case, I really can't figure (imagine, visualize) how would I structure the PHP code along with the SQL query, in order to add the these three conditions to the WHERE clause.

I'd greatly appreciate if you can help me out here. If you need more details, just let me know.

Answer 1

You can just build your sql statement as you go.

A simple example:

$sql = "SELECT ... WHERE 1=1";      // 1=1 is just an example to get the WHERE out of the way

if (first condition / certain $_POST variable is set)
{
    $sql .= " AND LIKE %...%";
}
if (second condition)
{
    $sql .= " AND something=...";
}
// etc.

// run query

Answer 2

Just for adding another solution, I can suggest you using stored procedure.

http://www.mysqltutorial.org/mysql-stored-procedure-tutorial.aspx

http://www.brainbell.com/tutorials/MySQL/Using_Stored_Procedures.htm

You'll just need to pass values to a sp and generate where condition inside it.

There's another option to generate parameterized query in PHP to prevent SQL Injections.

Here are some links on this topic.

http://us2.php.net/manual/en/security.database.php

http://www.roscripts.com/PHP_MySQL_by_examples-193.html

http://www.webmasterworld.com/php/3110596.htm

http://am.php.net/mysql_real_escape_string