PHP - two multiple select dropdowns, passing user selections into a MySQL query

Question

EDIT:

The drop down menus have the following listed in them:

Typing Course Daily Marketing Course

When using the code below to add selected text form the dropdown into the MySQL statement, only the first word appears ie. 'Typing' and 'Daily', the code looks like this:

SELECT * FROM `acme` WHERE `course` IN('Typing', 'Daily')AND `date` IN('2010-08-27', '2010-08-31')

it should be this:

SELECT * FROM `acme` WHERE `course` IN('Typing Course', 'Daily Marketing Course')AND `date` IN('2010-08-27', '2010-08-31')

Original question below:

Hi all,

Ok, I'll do my best to explain what I would like to do.

I have two dropdown menus set to multiple, the first is Course and the second is Date, here is the code that populates each dropdown:

Course

echo "<select name='course' value='' multiple='multiple'>";
            // printing the list box select command
            echo "<option value=''>All</option>";
            while($ntc=mysqli_fetch_array($queryc)){//Array or records stored in $nt
            echo "<option value=$ntc[course]>$ntc[course]</option>";
            /* Option values are added by looping through the array */
            }
            echo "</select>";// Closing of list box 

Date

echo "<select name='date' value='' multiple='multiple'>";
        // printing the list box select command
        echo "<option value=''>All</option>";
        while($nt=mysqli_fetch_array($queryr)){//Array or records stored in $nt
        echo "<option value=$nt[dates]>$nt[dates]</option>";
        /* Option values are added by looping through the array */
        }
        echo "</select>";// Closing of list box 

The main problem I have is passing the results of each dropdown to a MySQL query. For example, if a user select from the Course dropdown 'Typing' AND 'Marketing' - I need the MySQL query to be:

SELECT * FROM acme WHERE course = 'Typing' OR course = 'Marketing'

In addition, I also need to add the second dropdown into the equation, so working on the assumption the user has selected 'Typing' AND 'Marketing', they then select 21-06-2010 from the Date dropdown, so the query then needs to be:

SELECT * FROM acme WHERE course = 'Typing' OR course = 'Marketing' AND date = '21-06-2010' OR date = '18-05-2010'

Clearly, I also need to build in if they select more than one date form the dropdown.

I hope I have explained clearly enough what I'm looking to achieve..any and all help gratefully received. Really struggling to get my head around this one.

Thanks in advance,

Homer.

Answer 1

Use WHERE value IN ('a', 'b'):

SELECT * FROM acme WHERE course IN ('Typing','Marketing') AND date IN ('21-06-2010', '17-09-2010');

In HTML (or the PHP that outputs HTML), add [] to fieldnames:

<select name='course[]' value='' multiple='multiple'>

in PHP:

$courses=$_POST['course'];
$courses=array_map('mysql_real_escape_string', $courses);


$dates=$_POST['date'];
$dates=array_map('mysql_real_escape_string', $dates);

$query = 'SELECT * FROM `acme` WHERE ';
$query.='`course` IN(\''. join("', '", $courses). '\')';
$query.='AND `date` IN(\''. join("', '", $dates). '\')';

Answer 2

OK, first of all, your SQL is a bit off. Rather than WHERE course = 'Typing' OR 'Marketing', you want WHERE course = 'Typing' OR course = 'Marketing'. Alternatively, you could use WHERE course IN ('Typing', 'Marketing'). You can then create this using the array_map function to add the quotes and the join function to link them together:

<?php

...

function escape_and_add_quotes($string) {
    return '\'' . mysql_real_escape_string($string) . '\'';
}

...

$sql = 'SELECT * FROM acme WHERE course IN (' . join(',', array_map('escape_and_add_quotes', $courses)) . ')';

?>