php mysql order by relevance

Question

$fields = array('surname', 'firstname', 'maiden', 'birth', 'death', 'obittext'); $conditions = array();

foreach($fields as $field){

    if(isset($_POST[$field]) && $_POST[$field] != '') {
        $conditions[] = "`$field` LIKE '%" . $_POST[$field] . "%'";
    }
}

$sql = "SELECT * FROM obits ";
if(count($conditions) > 0) {
    $sql .= "WHERE " . implode (' AND ', $conditions); 
}
$result = mysqli_query($con, $sql);

So far this allows me to search either one or more than one field like a surname and/or a date of birth. What I would like to do is also sort this by relevance as it currently sorts by the primary ID (ex: I want smith to come before klingensmith if I search smith).

I am really new at this, getting this far required much gnashing of teeth so please explain like I'm 5. I already tried:

$result = mysqli_query($con, $sql, ' ORDER BY relevance DESC ');

which just broke it. I suspect I need to add another condition but I don't know what or where. Very much thanks, and much respect to all you people who understand this stuff.

Have you tried ' ORDER BY firstname DESC, surname DESC ' instead of ' ORDER BY relevance DESC '? — Zoran, Aug 26 '16 at 16:58
That did yield different results and didn't break it. I'm not quite sure it's the result I want if someone is only using one search field but it works well for multi-field searching. — aloeveraking24, Aug 26 '16 at 17:19
No when I use OR it would, for example, bring up all of the smith's who died in any year instead of just smith's who died in 1910 if someone were searching for smith and 1910 — aloeveraking24, Aug 26 '16 at 17:30
I think, and I'm not sure if I'm stating this correctly, I want it to order the search by the fields that the searcher is using. So if they're using surname, order by surname. If they're using first name, order by that. — aloeveraking24, Aug 26 '16 at 17:36
Well, then build the order by in the foreach loop (like you do the conditions) eg: $orderBy[] = " {$field} DESC"; and then append it to the end of the query after the conditions: $sql .= " ORDER BY " . implode (',', $orderBy); — Zoran, Aug 26 '16 at 17:47

php mysql order by relevance

0 Answers0