CODEWITHSUNDEEP
phpmysqlsearchsql-like
Ashley Staggs
Ashley Staggs

Reputation: 1567

PHP MySQL Search Combined Columns

I had just finished my search functionality for a users system when I found out that it didn't search the way I wanted it to.

If I have a datebase table with 2 columns called 'fname' and 'lname'.

In one row, 'fname' has a value of 'Ashley' and 'lname' has a value of 'Staggs'.

I can search for either 'Ashley' or 'Staggs' and I will get the results correctly, but if I search for 'Ashley Staggs', no results are displayed.

How would I do this properly?

My SELECT query is as follows:

SELECT * FROM `users` WHERE fname LIKE '%" . protect($_GET['s']) . "%' OR lname LIKE '%" . protect($_GET['s']) . "%'

I knew something like this would happen, but this time I can't figure it out.

Thanks, Ashley

Upvotes: 1

Views: 2831

Answers (6)

TheCrazyProfessor
TheCrazyProfessor

Reputation: 949

I will just do

SELECT * FROM users WHERE CONCAT(firstname, ' ', lastname) LIKE '%{$search}%'

Upvotes: 0

Ravinder Payal
Ravinder Payal

Reputation: 3031

hey i want to sugest a stronger more strong search but it required MyISAM table code for this is

$q="you search string";
$searchArray = explode(" ", $q);
$query="SELECT * FROM cmusers WHERE  MATCH (`firstname`, `lastname`, `email`) AGAINST ('";
$i=0;
foreach ($searchArray as $word) {
$query .= "+".$word."* ";
}
$query .= "' IN BOOLEAN MODE)";
$result=mysql_query($query) or die("Error founded:".mysql_error()."there is problem existing we feels a very great sorry");
$finded=mysql_num_rows($result);

working of this can be seen at http://www.funnenjoy.com

Upvotes: 0

sp00m
sp00m

Reputation: 48817

'Ashley Staggs' is neither in fname, nor in lname, so your request doesn't return anything. You could try to concatenate your MySQL fields:

SELECT * FROM `users` WHERE fname LIKE '%" . $_GET['s'] . "%' OR lname LIKE '%" . $_GET['s'] . "%' OR CONCAT(fname, ' ', lname) LIKE '%" . $_GET['s'] . "%'

[EDIT] Even better:

SELECT * FROM `users`
WHERE REPLACE(CONCAT(fname, lname, fname), ' ', '')
LIKE '%" . str_replace(' ', '', protect($_GET['s'])) . "%'

Upvotes: 3

Pateman
Pateman

Reputation: 2757

Regarding sp00m answer, I have a slightly different approach, but built on the same concept.

$search = preg_replace ( "/\s\s+/" , " " , $_GET['s']);

And then use this query:

"SELECT * FROM `users` WHERE CONCAT(fname, ' ', lname) LIKE '%" . $search . "%' OR CONCAT(lname, ' ', fname) LIKE '%" . $search . "%'"

EDIT

Just had an idea you could use. Basically, you could create two additional fields in the table - fname_lname and lname_fname , use the regex I mentioned before to get rid of unnecessary spaces, use explode() to check the word count. If you have two words, then you can use these two new fields, giving you only two conditions in the query. When you have only one word, you still have two conditions in the query.

Upvotes: 0

rawb
rawb

Reputation: 2426

You might try something like this - it'll just split the search string by spaces and search for each word:

$search = explode(' ', $_GET['s']);
$query = 'SELECT * FROM `users` WHERE 0';
foreach ($search as $v)
{
  $v = mysql_real_escape_string($v);
  $query .= " OR (`fname` LIKE '%{$v}%' OR `lname` LIKE '%{$v}%')";
}
// echo $query;

Upvotes: 0

Donovant
Donovant

Reputation: 3161

SELECT fname_lname FROM ( SELECT CONCAT(fname, ' ', lname) fname_lname FROM users ) users
WHERE fname_lname LIKE '%" . $_GET['s'] . "%'

Upvotes: 0

Related Questions