Simple search query help

[KevinM1]

I want to search through a database of hockey players using their names.  My problem is that I don't know what to do if the user inputs only one name, or both names in the wrong order (like Bergeron, Patrice rather than Patrice Bergeron).  I'm also not sure what to do if the search brings back more than one entry (example: the user enters Patrice as the search string and both Patrice Bergeron and Patrice Brisbois are in the database).  I'm thinking that I'd have to use regex against what's stored in the database, but I'm not 100% sure how to go about it.  Please help.

Thanks :)

[thepip3r]

for your search form... name your fields FIRST NAME and LAST NAME so that you have some sort of verification process for what information the user is submitting.  Then the query is very simple:

$sql = "SELECT * FROM database WHERE fname LIKE '%".$_POST['fname']."%' AND lname LIKE '%".$_POST['lname']."%'";

[Hi I Am Timbo]

I would use the PHP string tokenizer and the SQL OR statement. 

so for each token in the string, see if it is the like the first name or the last name.

Why is it a problem if they are searching for more than one user.  Usually searches in large databases return more than on result and the user decides what they want.

[KevinM1]

[quote author=thepip3r link=topic=110594.msg447294#msg447294 date=1160065695]
for your search form... name your fields FIRST NAME and LAST NAME so that you have some sort of verification process for what information the user is submitting.  Then the query is very simple:

$sql = "SELECT * FROM database WHERE fname LIKE '%".$_POST['fname']."%' AND lname LIKE '%".$_POST['fname']."%'";
[/quote]

Ah, yeah, that makes sense. :)

But, what if I only wanted one input field, which is what I'd ultimately like to have?  I'd probably have to use regex, right?  Actually, I just thought of something.  Would the following idea work?

[code]
if(isset($_POST['submit]')){
  if(!empty($_POST['searchbox'])){
      $search = explode(' ', $_POST['searchbox']);
      $query = "SELECT * FROM database WHERE (first_name LIKE '%".$search[0]."%' OR first_name LIKE '%".$search[1]"%') AND
        (last_name LIKE '%".$search[0]"%' OR last_name LIKE '%".$search[1]"%'";
.
.
.
[/code]

[thepip3r]

something like that COULD work but you're going to get a lot of responses you're not looking for if you go that route.  I've found that trying to specify a more exacting search is much easier to handle than making the most specific your query gets in the hands of the user.  =/

[KevinM1]

[quote author=thepip3r link=topic=110594.msg447316#msg447316 date=1160067220]
something like that COULD work but you're going to get a lot of responses you're not looking for if you go that route.  I've found that trying to specify a more exacting search is much easier to handle than making the most specific your query gets in the hands of the user.  =/
[/quote]

Yeah, good point.

So, how would I use the tokenizer like Timbo suggested?  More specifically, how would I use it in conjunction with SQL?  I mean, I'm sure I'd have to do something like:

[code]
$tokens = token_get_all($_POST['searchbox']);

foreach($tokens as $token){
  //do something with SQL
}
[/code]

I'm just not sure how to run it against both name columns in the database.

[thepip3r]

don't tokenize anything.  change yoru HTML form around so you have a fname input box AND an lname input box.  That way YOU KNOW the data that's supposed to be in those boxes and can base your query off of a more structured approach.  then you change $_POST['searchBox'] to $_POST['fname'] and $_POST['lname'].