SQL WHERE condition depending on form input

Question

I have a form where the user can enter multiple months and my SQL query returns the data for those months.

For example, if the user enters January the SQL should be:

SELECT * FROM table WHERE month = 'January'

If the user enters March, August, October the SQL should be:

SELECT * FROM table WHERE (month = 'March' OR month = 'August' OR month = 'October')

How would I go about making the WHERE conditions dynamic like that because the user can enter however many months we wants? Its like an ad-hoc query. Thanks!

http://stackoverflow.com/questions/920353/php-pdo-can-i-bind-an-array-to-an-in-condition — user2246674, Sep 08 '13 at 01:14

DevlshOne · Answer 1 · 2013-09-08T01:44:08.803

2

Create a comma-separated string from the array of months that are submitted and store it into variable $months. For example:

$form_months = array('September','November','December');
$months = join(',', $form_months);

Resulting in...

'September','November','December'

You can then use:

$sql = "SELECT * FROM table WHERE month IN (" . $months . ")";

Oh yeah.. and somebody said "beware SQL injections, blah, blah" - but that's you're call, not for me to wrap you in a user-safe cocoon for you to feel warm and cozy.

edited Sep 08 '13 at 01:44

answered Sep 08 '13 at 01:00

DevlshOne

8,357
1
29
37

1

I sure wish we could delete other's comments. It is not my responsibility to purchase and advise the OP to wear a bullet-proof vest. If he chooses to do so with the K.I.S.S. answer I have provided, he is welcome to do so. – DevlshOne Sep 08 '13 at 01:38

score 1 · Answer 2 · edited May 23 '17 at 12:20

I would recommend using placeholders. Still.

What? Whoa, heck yeah! Still no worry about SQL injection (yay!) and the problem can be approached in nice little pieces - building the dynamic SQL and then binding the relevant value(s).

Imagine that we start with an array of data:

$stuff = array("January", "February");

Then we just need to create the appropriate template with placeholders dynamically, say:

.. WHERE (month = ? OR month = ?)
.. WHERE month IN (?, ?)           -- or

(The only thing we needed here was to know how many elements were in the array. These can be generated using a simple loop without actually caring about the values in the array! Should be simple to work out :-)

And then bind the values from the array as shown in the (mysqli_stmt_bind) examples (e.g. use call_user_func_array). Also see Can I bind an array to an IN() condition? which provides a specific walk-through for this use case (note the named parameters).

You misspelled "Janurary" AND "Februrary"! – DevlshOne Sep 08 '13 at 01:40 — DevlshOne, Sep 08 '13 at 01:40
@DevlshOne I fail English :> Thanks for the corrections. – user2246674 Sep 08 '13 at 01:42 — user2246674, Sep 08 '13 at 01:42

score 0 · Answer 3 · answered Sep 08 '13 at 00:59

0

For PL/SQL, you can try the IN clause.

SELECT * FROM table WHERE month IN (/*list of months from user input*/);

I hope you're protecting against SQL Injection!

answered Sep 08 '13 at 00:59

lebolo

2,120
4
29
44

score 0 · Answer 4 · edited May 23 '17 at 12:28

You may need pass the months as an array through $_POST or $_GET

For example consider that this is the part of the month in your html Form and you will submit it via POST

<input type="checkbox" name="month[]" value="January">January<br>
<input type="checkbox" name="month[]" value="February">February<br>
[...]

In your php processor you have this

$moth = isset($_POST['month']) ? $_POST['month'] : NULL;//get the array

if(!empty($month)){
 foreach ($month as $month_val) {
    $months_val[] = "'" . mysqli_real_escape_string($dbcon,$month_val). "'";
    //using mysqli
  //$dbcon is the conection parameter
 }
  $months_val_insert = implode(',', $months_val);    


  $month_values = $months_val_insert;


$query = "SELECT * FROM table WHERE month IN ($month_values )";
 }

This is the idea

Thanks to @user2246674 who point me out with this reference

The word you're looking for there is "reference", not "referece". — DevlshOne, Sep 08 '13 at 01:41

SQL WHERE condition depending on form input

4 Answers4