Querying table as if two columns were one.

[shergar1983]

I have recently implemented a 'Google Suggests' type drop-down box on my site, and developing this has thrown up an issue.

 

I have a table with three columns:

 

id (auto_inc) int 10

first_name - varchar 80

second_name - varchar 80

 

Now lets say there is a name John Smith in the table with John in the first name field and Smith in the second.

 

I need to be able to see whether the search string is like either two columns combined so if user enters 'John Sm' then John Smith is returned. The results returned also have to be distinct.

 

I've played around with concat ideas etc. However if anyone can help I'd be very grateful.]

Many thanks,

[trq]

<?php

  $search = 'John Sm';
  $searcharr = explode(' ',$search);
  $sql = "SELECT * FROM tbl WHERE first_name LIKE '%{$searcharr[0]}%' && second_name LIKE '%{$searcharr[1]}%'";

?>

 

Does this help?

[shergar1983]

Spot on, thank you you're a star

[aschk]

I would probably say it's safe to assume that they'll not be entering partial names, i.e. for John we won't enter "oh", thus you can alter your query to be partially more efficient as follows by removing the starting %

 

$sql = "SELECT * FROM tbl WHERE first_name LIKE '{$searcharr[0]}%' && second_name LIKE '{$searcharr[1]}%'";