0

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?

Donald Duck
  • 8,409
  • 22
  • 75
  • 99
Dominik
  • 6,078
  • 8
  • 37
  • 61
  • 1
    It might interest you to know that InnoDB full-text search is available as of MySQL 5.6.4...of course that assumes you're able to upgrade the MySQL version on your server. – Matt Browne Feb 25 '13 at 05:36
  • thanks man that is a very good thing to know... I don't think I will get to upgrade though but good to know anyway! – Dominik Feb 25 '13 at 05:38
  • 1
    In that case my next suggestions are to (1) surround all your `\`field\` like` statements with parentheses, so you can add other AND conditions to your WHERE and (2) ditch the CONCAT() statements, which you can accomplish as shown here: http://stackoverflow.com/a/1786656/560114 – Matt Browne Feb 25 '13 at 05:51
  • well thats what I was looking for... you should write an answer not a comment ;) about the concat() whats wrong with it? I am using mysqli not PDO so thats where it gets ugly – Dominik Feb 25 '13 at 06:00
  • Ah ok...with mysqli I don't know, maybe you need the `concat`. I would turn my comment into an answer but I think @Passerby already did a nice job. – Matt Browne Feb 25 '13 at 06:03
  • As to what's wrong with the `concat` there's nothing wrong with it really, it's just making the database concatenate the values when you could easily (at least in theory) concatenate them yourself before sending the query to the DB. – Matt Browne Feb 25 '13 at 06:04

1 Answers1

1

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.

Passerby
  • 9,715
  • 2
  • 33
  • 50
  • so yeah basiclly what you're saying is surround my or statements in parentheses... ;) cheers I must be getting tired (and old) – Dominik Feb 25 '13 at 06:03
  • @Dominik Oops...I wasn't aware that this is what you're looking for...feels like I robbed Matt's credit. I've been using this technique for quite sometime, except that my company uses Oracle and IMO OCI provides a better bind interface than MySQLi. – Passerby Feb 25 '13 at 07:05
  • hehe no harm done man! I appreciate the help. Those trivial things sometimes just hide in the crazy of my mind. Just fyi my code to get the right query is about 200 lines so I am doing a lot of what you suggested there ;) – Dominik Feb 25 '13 at 07:25