Sql search combination

Question

What's the best way to get the search result for a combination search of both 'f_name'` and 'l_name'

At the moment, f_name and l_name when searched individually pulls up the search result. However, combining f_name and l_name in one search query brings up nothing. Mayber my operators are wrong?

Please see the sql part of the code.

$search = filter_input(INPUT_POST,'search',FILTER_CALLBACK,array('options'=>'alpha_num'));

$name   =   (@$_GET['fname'])?(($_GET['fname'] == 'asc')?('fname=desc'):('fname=asc')):('fname=asc');
$age    =   (@$_GET['age'])?(($_GET['age'] == 'asc')?('age=desc'):('age=asc')):('age=asc');
$iso    =   (@$_GET['iso'])?(($_GET['iso'] == 'asc')?('iso=desc'):('iso=asc')):('iso=asc');
$org    =   (@$_GET['org'])?(($_GET['org'] == 'asc')?('org=desc'):('org=asc')):('org=asc');
$csc    =   (@$_GET['csc'])?(($_GET['csc'] == 'asc')?('csc=desc'):('csc=asc')):('csc=asc');

if(!empty($search)) {
        $results = $wpdb->get_results("SELECT * FROM `wp_plegde` WHERE `active` = '1' AND `delete` = '0' AND (`f_name` LIKE '%$search%' OR `l_name` LIKE '%$search%' OR `age` LIKE '%$search%' OR `i_shout` LIKE '%$search%' OR `org_name` LIKE '%$search%' OR `city` LIKE '%$search%' OR `state` LIKE '%$search%') ORDER BY f_name asc", ARRAY_A);
} else {
        $results = $wpdb->get_results("SELECT * FROM `wp_plegde` WHERE `active` = '1' AND `delete` = '0' ".orderBy(), ARRAY_A);
}

Have you tried the query with a db manager tool? Like phpMyAdmin? — Marco Mura, Dec 02 '14 at 21:58
I'm actually accessing the data and using it in a wordpress template on this page (http://www.i-shout-out.org/shout-out-wall/). Try searching for Robyn, then Weber. When you try searching Robyn Weber nothing shows — user3689387, Dec 02 '14 at 22:03
You do use wildcard + var + wildcard. But if you search the exact name it won't extract it, right-o? — Marco Mura, Dec 02 '14 at 22:06
you need to search even %name , name%, name and %name%, those are all the option i think — Marco Mura, Dec 02 '14 at 22:07

score 0 · Answer 1 · edited May 23 '17 at 11:50

Assuming that function alpha_num used to filter input leaves only characters and digits intact your $search variable is equal to RobynWeber if you search for both words and there is no l_name or f_name that matches that search criteria.
Depending on how you want your search to work, you might want to split $search into separate words and then search for all of them in both l_name and f_name fields using technique described in MySQL LIKE IN()?

Sql search combination

1 Answers1