Building MySQL searching query using LIKE in PHP

Question

I make a PHP MySQL search, the custom may be type something like word or word1 word2 or word1 word2 word3... I need to get the final query like

$qry = "SELECT title,content,date 
FROM articles WHERE 
(title like '%$word1%' and title '%$word2%') 
OR 
(content like '%$word1%' and content title '%$word2%')"
OR
(title like '%$word1%' and content title '%$word2%')
OR
(title like '%$word2%' and content title '%$word1%'); // make sure custom type words all match in database column title and content, maybe only '%$word1%', or maybe multi words '%$word1%', '%$word2%', '%$word3%'...

I use some code below, but it could not reach my request. how to make it right? Thanks.

$qry = "SELECT title,content,date FROM articles";
if($_REQUEST['search']!=""){
    $searchText = $_REQUEST['search'];
    $words = preg_split("/\s+/",$searchText); 
    $uniqueWords = array_keys(array_flip($words)); 
    $parts = '';
    foreach($uniqueWords as $word){     
    $parts[] = " content like '%$word%' ";
    } 
    $where = implode(" AND ", $parts);
    foreach($uniqueWords as $word){     
    $parts[] = " title like '%$word%' ";
    } 
    $where1 = implode(" AND ", $parts);
    foreach($uniqueWords as $word){     
    $parts[] = " title like '%$word%' OR content like '%$word%' ";
    } 
    $where2 = implode(" AND ", $parts);
    $qry .=" WHERE $where OR $where1 OR $where2 Order By date DESC ";
}

Why aren't you using MySQL full text searching? dev.mysql.com/doc/refman/5.5/en/fulltext-search.html — Treffynnon
– Treffynnon, Commented Apr 7, 2011 at 9:23
Full-text Search - dev.mysql.com/doc/refman/5.0/en/fulltext-search.html - may be along the lines of what you need. — Luke Stevenson
– Luke Stevenson, Commented Apr 7, 2011 at 9:24
@Treffynnon: Great minds thing alike. Your comment only appeared after I left mine. Oh well. — Luke Stevenson
– Luke Stevenson, Commented Apr 7, 2011 at 10:54

Adam · Accepted Answer · 2011-04-07 09:41:59Z

2

I'm not sure exactly what you're after (are you wanting to match any keyword in title or contents or match both keywords to both columns...?) But what about something like this:

$keywords = explode(' ',mysql_real_escape_string($_REQUEST['search']));
$qry = "SELECT title,content,date FROM articles WHERE (";
$qry2 = '';
foreach($keywords as $n => $word)
{
    $qry2 .= " title LIKE '%$word%' OR content LIKE '%$word%' OR";
}
$qry .= trim($qry2, 'OR');
$qry .= ") ORDER BY title";

Not tested this, but it seems ok.

answered Apr 7, 2011 at 9:41

Adam

1,1081 gold badge8 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Building MySQL searching query using LIKE in PHP

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related