PHP: Searching in database

Question

I have made an normal form that you can enter a user´s fullname in and choose sex.

Now to the PHP for the searching in the database.

$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);
echo $count;

If i enter Jenni Jackson (that exists) and choose "male", i still get 1 in $count, but Jenni Jackson is a female, and has female in the sex column.. I tried do echo $sex to see the value of $sex if it really is what you chose in the form, and it is correct.

i.e. mysqli . btw is there a question or you just wnat to show that you some bits of php? — Déjà vu
– Déjà vu, Commented Sep 29, 2010 at 13:57

codaddict · Accepted Answer · 2010-09-29 13:56:51Z

3

You need to use parenthesis in your query as:

WHERE (firstname LIKE '$firstname' OR lastname LIKE '$lastname') AND sex='$sex'";

without the parenthesis your query is equivalent to:

WHERE firstname LIKE '$firstname' OR (lastname LIKE '$lastname' AND sex='$sex')";

and since firstname condition matches, the row is returned.

This happens because AND is having precedence over OR

answered Sep 29, 2010 at 13:56

codaddict

457k83 gold badges502 silver badges537 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Johnson Over a year ago

The firstname works fine, but when I search by only the lastname.. e.g "Jackson" then it returns 0. so the OR doesnt work right?

codaddict Over a year ago

Adding parenthesis says that the sex must match and either of firstname or the lastname should match.

Shikiryu Over a year ago

Isn't it useless to use LIKE without % or _? What other purpose can it have ?

Johnson Over a year ago

I have problems using %, but this is another question.

Johnson Over a year ago

@Codaddict - doubletested too. I get zero results when I only type the lastname. Something must be wrong?

|

aeon · Accepted Answer · 2010-09-29 13:59:01Z

1

Try adding some brackets

$query = "
    SELECT 
         firstname, 
         lastname, 
         id, 
         user_name, 
         sex, 
         last_access, 
         bostadsort 
     FROM users 
     WHERE 
        (
         firstname LIKE '$firstname' 
         OR lastname LIKE '$lastname'
        ) 
        AND sex = '$sex'";

answered Sep 29, 2010 at 13:59

aeon

891 bronze badge

Comments

Gus · Accepted Answer · 2010-09-29 13:57:07Z

0

You need brackets in your WHERE clause so that the database knows which takes precedence (AND or OR):

WHERE (firstname LIKE '$firstname' OR lastname LIKE '$lastname') AND sex = '$sex'

answered Sep 29, 2010 at 13:57

Gus

7,3792 gold badges29 silver badges23 bronze badges

Comments

Mark Baker · Accepted Answer · 2010-09-29 13:57:12Z

0

You're combining AND and OR conditions in the same WHERE clause

Do you mean

(firstname LIKE '$firstname' OR lastname LIKE '$lastname') AND sex = '$sex'

or

firstname LIKE '$firstname' OR (lastname LIKE '$lastname' AND sex = '$sex')

answered Sep 29, 2010 at 13:57

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

4 Comments

Mischa Over a year ago

He means the first one or else he wouldn't be asking this question ;-)

Mark Baker Over a year ago

If I have to ask which of the possibilities is intended, then the SQL parser is going to have to guess as well (except SQL has a precedence rule to make it's guess with)... I rest my case

Mischa Over a year ago

I just wanted to jokingly point out that you don't have to ask because a) SQL has a precedence rule and b) TS is not getting the result he expected... I rest my case

Mark Baker Over a year ago

@captaintokyo - I should probably have added a :) after my comment... I didn't perceive your comment as a criticism, and my own reponse to that was intended jokingly as well

Collectives™ on Stack Overflow

PHP: Searching in database

4 Answers 4

6 Comments

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related