So I need to select data from a MySQL table by looking into one field and see if it has a certain word in it and do a whole lot of other things in the where clause which is dynamically generated.
In the old days with the old mysql extension I would do this:
select [bunch of stuff] left join [bunch of stuff] where
`field` rlike "(?=.*word1)(?=.*word2)(?=.*word3)..."
and [more where..] order by [order stuff]
Now of course I use mysqli and a prepared statement...
select [bunch of stuff] left join [bunch of stuff] where
match(`field`) against(?,?,?...)
and [more where..] order by [order stuff]
Unfortunately I got a InnoDB table which means I don't have full text search which would bring me to chain some like statement together like so:
select [bunch of stuff] left join [bunch of stuff] where
`field` like concat("%",?,"%") or `field` like concat("%",?,"%") ...
and [more where..] order by [order stuff]
But this would mean it breaks the "and" chain I have going here and would need to repeat [more where..] in every "or".... This has got to be wrong and I have been staring at this for too long now.
Any ideas?
You can build your query with array:
$sql="SELECT...WHERE ";
$cond=array();
$bind=array();
$subCond=array()
foreach($searchQuery as $txt) //assumed you have stored search query
{
$subCond[]="`field` LIKE concat('%',?,'%')";
$bind[]=$txt;
}
$cond[]="(".implode(" OR ",$subCond).")";
/*...continue to build conditions...*/
$sql.=implode(" AND ",$cond);
$sql.=" SORT BY ...";
$stmt=$mysqli->prepare($sql);
call_user_func_array(array($stmt,"bind_param"),array_merge(array(str_repeat("s",count($bind))),$cond));
$stmt->execute();
Noted that the above code was not tested, and may raise warning (possible due to the pass by reference issue), but it gives you the idea.
Also check out this comment for a variable-number-variable-binding solution.