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.

aularon · Accepted Answer · 2010-09-03 13:42:08Z

2

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). '\')';

edited Sep 3, 2010 at 13:42

answered Sep 3, 2010 at 11:48

aularon

11.1k4 gold badges38 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Homer_J Over a year ago

Hi aularon, Thanks for the code, having a problem though: The output from the above is: SELECT * FROM acme WHERE course IN(Text', 'Another)AND date IN(2010-08-27', '2010-08-31) Missing some apostrophes I think?

aularon Over a year ago

yes, it misses an apostrophe, change 'AND course` IN('` INTO 'AND course` IN(\''` and the last '\')' into '\')', same for the date line.

aularon Over a year ago

I edited my answer to include the fixes, since putting them in comments doesn't format well.

Homer_J Over a year ago

That works a treat....however, just come across another problem. If the dropdown has the following in it 'Test Course', when I select it, only 'Text' appears in the MySQL string?

aularon Over a year ago

if you can explain more so I can help.

|

Samir Talwar · Accepted Answer · 2010-09-03 11:52:31Z

1

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)) . ')';

?>

answered Sep 3, 2010 at 11:52

Samir Talwar

14.4k3 gold badges45 silver badges65 bronze badges

1 Comment

Homer_J Over a year ago

Thanks Samir, just updated the question - I'm still not quite getting it to work though.

Collectives™ on Stack Overflow

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

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related