PHP: 'IN' clause not working in MySQLi prepared statements

Question

I am trying to make a live search for my website.But the mysqli prepared statement for getting the results is not working. I have the following code:

$value = $_POST['value'];
$query = $db->prepare("
select title from forums where title IN(?)
");
$value = explode(' ',$value);
$value = "'" . implode("','", $value) . "'";
$query -> bind_param('s',$value);
$query -> execute();
$query -> store_result();
$number = $query -> num_rows();
if($number == 0){
echo "No results found.";
}else{
    $query -> bind_result($title);
while($query -> fetch()){
    echo $title.'<hr>';
}

I dont know how many words there are going to be in the search phrase so i cannot use:

IN(?,?,?,...)

I have a record in my forums table with title 'This is a test forum'. But When I type the name the result does not show. Am i doing the prepared statement correctly.Please help

UPDATE

Thanks to all who have answered and commented. I finally ended up using the following code:

   $value = $_POST['value'];
   $query = $db->prepare("
     select title from forums where match(title) against(?) OR name LIKE CONCAT('%',?,'%')
   ");
   $query -> bind_param('ss',$value,$value);
   $query -> execute();
   $query -> store_result();
   $numrows = $query -> num_rows();
   if($numrows == 0){
     echo "No results found";
   }
   else{
     $query -> bind_result($title);
     while($query -> fetch()){
       echo '<hr>';
       echo $title;
       echo '<hr>';
     }
   }

I believe for each item you want in `IN` needs to have a ? eg: `IN(?,?,?...)` — Class, Aug 24 '13 at 07:03
@Gumbo I dont know how how many words there are going to be in the search phrase. So, I cannot use one placeholder for one word — user1763032, Aug 24 '13 at 07:04
why not explode check value size and create x ?'s then insert that into `IN` — Class, Aug 24 '13 at 07:06
@user1763032 But there are ways to know it during runtime and there are ways to repeat instructions for a given number of times. — Gumbo, Aug 24 '13 at 07:17

score 0 · Answer 1 · answered Aug 24 '13 at 07:09

0

I believe for each item you want in IN needs to have a ? eg: IN(?,?,?...).

Replace

$value = explode(' ',$value);
$value = "'" . implode("','", $value) . "'";
$query -> bind_param('s',$value);

With

$value = explode(' ',$value);
$valuestr = "";
foreach($value as $val) {
  $valuestr .= "'" . $val . "',";
}
$valuestr=substr($valuestr,0,-1);
$query -> bind_param('s',$valuestr);

answered Aug 24 '13 at 07:09

rakeshjain

1,791
11
11

When you have a record with title "This is a test forum", what are you typing in form(basically $value). It it full "This is a test forum" or some words out of this like "test forum". If second is the case, you need to use LIKE construct rather than IN construct in the query. Let me know if you need any help – rakeshjain Aug 24 '13 at 07:21
why was it down voted? The poster had a issue in query(i.e the set being used in IN clause always had one entry only), I corrected it. I am pretty sure what he is trying to achieve is to be done using LIKE construct and not IN construct. See my previous comment. – rakeshjain Aug 24 '13 at 07:25
I already tried using LIKE but LIKE only works when the user types in the search in the exact order of words in the table. – user1763032 Aug 24 '13 at 07:25
What is your final requirement? If user types "This is", you want all rows which contain either "This" or "is"? – rakeshjain Aug 24 '13 at 07:29
@rakeshjain Your solution doesn’t work. The resulting expression would be something equivalent to `… IN ('\'a\',\'b\',\'c\'')`, so a single string of quoted strings. – Gumbo Aug 24 '13 at 07:30
May be you was downvoted because have no idea how mysqli works? – Your Common Sense Aug 24 '13 at 07:30
@rakeshjain Yes. Exactly – user1763032 Aug 24 '13 at 07:30
@user1763032 Then IN clause will not help you. If you type "this is", the query will be like title in ('this','is') which will fail for "This is a test forum" which should not happne....right? – rakeshjain Aug 24 '13 at 07:40
@rakeshjain Can you explain why it would fail? – user1763032 Aug 24 '13 at 07:41
@user1763032 If your column contained "This is a test forum" and your set(in IN clause) contained ('this','This is a test forum','those')", then it will succeed. Which will not be the situation in your case. Because each element in the set of your IN clause will be single words while your column value will be a multi word string – rakeshjain Aug 24 '13 at 07:48
1

@user1763032 You will probably need to use this kind of query(MYSQL) to achieve your goal. "select title from forums where title REGEXP '$exp'". Where $exp will be regular expression like 'this|that|these' if you want all titles that contain either this or that or these – rakeshjain Aug 24 '13 at 09:32
@user1763032 Could you find solution to your problem with my above comment? – rakeshjain Aug 24 '13 at 16:14

score 0 · Answer 2 · answered Aug 24 '13 at 07:17

0

Something like this might work:

$value = explode(' ',$value);
#you might want to check size of value?
$qmark = str_repeat("?,", count($value)-1) . "?";
$query = $db->prepare("select title from forums where title IN(" . $qmark . ")");
foreach ($value as $item){
    $query->bind_param('s', $item);
}

answered Aug 24 '13 at 07:17

Class

3,149
3
22
31

I wonder how many people will try to answer this question having no experience with mysqli. – Your Common Sense Aug 24 '13 at 07:22
@YourCommonSense are you talking about using `call_user_func_array` like in http://stackoverflow.com/a/793535/1700963 – Class Aug 24 '13 at 07:42
1

I am rather talking of answering questions without having proper knowledge on them. – Your Common Sense Aug 24 '13 at 07:44

PHP: 'IN' clause not working in MySQLi prepared statements

UPDATE

2 Answers2