Problem
I would like a user to be able to select multiple options to search the database, (e.g. WHERE field IN (optionA, optionB, optionC)). The idea being that they select the options they would like in an HTML form, and then these options are passed to the stored procedure as parameters to be used in the WHERE clause.
Example
Let's assume we have experts who are singular experts - that is, they can each only be an expert in one technology. (I'm aware this is a rather silly example, and one would never usually want to structure a database this way! Just an example :) )
Steps taken to develop a solution so far
The first page displays a form allowing the user to select which technologies they are interested in.
<form action="showresult.php">
<select name="technologies[]" multiple>
<option value="html">HTML</option>
<option value="css">CSS</option>
<option value="php">PHP</option>
<option value="sql">SQL</option>
</select>
<input type="submit">
</form>
showresult.php should CALL a stored procedure, passing it the parameters of all selected technologies.
*(I'm not sure how to do this)*
The stored procedure itself will take the potentially multiple technologies as an IN parameter to the stored procedure to use in a WHERE IN clause.
DELIMITER //
CREATE PROCEDURE ExpertsInTechnologies(IN technologies VARCHAR(30))
BEGIN
SELECT
Name, Technology
FROM
SingularExperts
WHERE
Technology IN ( ... technologies ... );
END //
DELIMITER ;
Research so far
I have been searching Stack Overflow and found several similar questions, however they all achieve slightly different things, apply to other flavours of SQL, and/or I struggle to understand them. This question describes a similar issue however the question (and answer provided) does not use prepared statements and is vulnerable to SQL injection.
I updated the HTML so that the name
technologiesistechnologies[]- so that it is passed as an array. This question tells me that to retrieve these values after they have been passed viaGET, they can be accessed from$_GET['technologies'].
