Jump to content

Simple search query help


KevinM1

Recommended Posts

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 :)
Link to comment
Share on other sites

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']."%'";
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

[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]
Link to comment
Share on other sites

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.  =/
Link to comment
Share on other sites

[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.
Link to comment
Share on other sites

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'].
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.