CODEWITHSUNDEEP
php
Johnson
Johnson

Reputation: 818

PHP: Search properly

I have this right now:

if(isset($_POST["search"]) && !empty($_POST["search"])) {
    $full_name = mysql_real_escape_string($_POST["search"]);
    $sex = mysql_real_escape_string($_POST["sex"]);
    list($firstname, $lastname) = array_map('ucfirst', explode(' ', $full_name));
    $query = "SELECT firstname, lastname, id, user_name, sex, last_access, bostadsort FROM users WHERE (firstname LIKE '$firstname' OR lastname LIKE '$lastname') AND sex = '$sex'"; 
    $result1 = mysql_query($query) or die(mysql_error());
    $count = mysql_num_rows($result1);
    while($get = mysql_fetch_array  ($result1)){
        echo $get["firstname"] . " " .$get["lastname"]."<br>";
    }
}

This is my search query. Now the form is called "Search for full names". You type in, and then it splits to $firstname , $lastname.

Works great, no problems.

Although if you ONLY enter a user´s LASTNAME, because maybe you dont remember the users firstname, then this code will take it as a firstname, because all before a space is firstname (list() line), and puts it in $firstname and will result nothing, as there's no firstname with that lastname.

How can i solve this? If you search by firstname it works fine, full name too, but not only lastname. Any smart solution?

Upvotes: 1

Views: 179

Answers (4)

NikiC
NikiC

Reputation: 101906

if (!empty($_POST["search"])) {
    $name = mysql_real_escape_string($_POST["search"]);
    $sex = mysql_real_escape_string($_POST["sex"]);
    $query = "SELECT firstname, lastname, id, user_name, sex, last_access, bostadsort
              FROM users
              WHERE (firstname = '$name'
                     OR lastname = '$name'
                     OR CONCAT(firstname, lastname) = '$name'
                     OR CONCAT(lastname, firstname) = '$name')
                    AND sex = '$sex'"; 
    $result1 = mysql_query($query) or die(mysql_error());
    $count = mysql_num_rows($result1);
    while ($get = mysql_fetch_array($result1)){
        echo $get["firstname"] . " " . $get["lastname"] . "<br>";
    }
}

I simply check whether it the search input is the first name, the last name, both first name and last name or both last name and first name.

PS: A nice side affect of this version is that it will find names like Milhouse van Houten, too. Your original version (and many of the here proposed ones) couldn't deal with this case. They would look for Milhouse and van.

PPS: You probably chose a *_ci collation for the database. Thus string comparison will be case-insensitive: You don't need to ucfirst.

Upvotes: 1

Matthew
Matthew

Reputation: 48284

If I understand correctly, this is the simplest solution:

list($firstname, $lastname) = array_map('ucfirst', explode(' ', $full_name, 2));
if (!$lastname) $lastname = $firstname;

Edit: Added a limit to the explode line.

Upvotes: 2

methodin
methodin

Reputation: 6712

You can build a series of conditions:

  1. Split the input on space
  2. For each item in the resulting array, append OR firstname='value' OR lastname='value'

So smith becomes:

SELECT * FROM table WHERE (firstname='smith' OR lastname='smith') ....

And john smith becomes:

SELECT * FROM table WHERE (firstname='john' OR lastname='john' OR firstname='smith' OR lastname='smith') ....

If that's not to your liking then you can break out the search terms so you can enter first and last name separately.

Upvotes: 2

abel
abel

Reputation: 2383

Search for both(or more) list items, since names may contain spaces and look for unique results.

$arr_name= explode(' ', $full_name);

foreach($arr_name as $name){
 $query = "SELECT firstname, lastname, id, user_name, sex, last_access, bostadsort FROM users WHERE (firstname LIKE '$name' OR lastname LIKE '$name') AND sex = '$sex'"; 
$result1 = mysql_query($query) or die(mysql_error());
...
}

Upvotes: 1

Related Questions