How to use "IN" operator in prepared statement in php

Question

i will use "IN" operator with using parameter in prepare statement but i can't do that

$hotel_list = "SELECT DISTINCT h.hotel_id, h.hotel_name, h.hotel_address, h.hotel_image 
                FROM type_feture tf 
                JOIN type t ON t.type_id = tf.type_id 
                JOIN hotel h ON h.hotel_id=t.hotel_id 
                WHERE tf.feture_id IN ?  
                AND h.hotel_city_id=?
                GROUP BY tf.type_id, h.hotel_id, h.hotel_name, h.hotel_address HAVING COUNT(tf.type_id)>=?";
$result = $dbca->prepare($hotel_list);
$result->bind_param("sii", $feture,$city_ide,$cnt_type);
$feture=(string)$finish;
$city_ide = (int)$hotel_city_id;
$cnt_type=(int)$cn;
$result->execute();
$res = $result->get_result();
while ($obj = $res->fetch_object()) {
    $hotels[] = $obj;
}

Is `$feture` a comma-separated string? If so, see https://stackoverflow.com/questions/28639692/query-with-multiple-values-in-a-column/28639762#28639762 — Barmar, Aug 14 '18 at 05:30
And you can't use `IN` with a column name, it has to be a list of values in `()` or a subquery. — Barmar, Aug 14 '18 at 05:49
If it's a column containing a comma-separated list, use `FIND_IN_SET()`. — Barmar, Aug 14 '18 at 05:49
You need to clarify the question and show what the value of `$feture` is. — Barmar, Aug 14 '18 at 05:50

score 0 · Answer 1 · answered Aug 14 '18 at 06:06

Because it is a design requirement that you must use the IN operator and prepared statements, you need a way to convert the PHP array of (value 1, value 2, value 3, ... value n) into an SQL array (value 1, value 2, value 3, ... value n). You'll need an intermediate step, as PHP objects cannot be directly translated into SQL without the aid of functions or loops of some sort.

Your thought process should be: what do the two variables have in common? Answer: they're both lists and they can both be represented by strings. To solve your problem, then, you want to transform the PHP list into a comma-separated string, into an SQL array, and then check in the SQL query if the specified column has a value in that array.

There are only two changes you need to make to your code to accomplish this:

1) Create a variable $feature_ids_str and make it the comma-separated string version of whatever array holds your feature IDs (as per the comments on your question, it really would be helpful if you gave us some more code to explain where some of these variables were coming from). An example of the value of $feature_ids_str would be "1,5,3,7,82,4".

2) Amend your query as follows: ... WHERE tf.feture_id IN STRING_SPLIT(?, ',') ... [feture_id sic] and of course update the $result->bind_param() function accordingly.

Also, I think you're misspelling "feature".

How to use "IN" operator in prepared statement in php

1 Answers1