PHP/MySQL - AND/OR problems

What is wrong with this code. I seem to have the same problems everytime I try to use an AND or an OR in a mysql statement.

I am obviously not understanding how this is being interpreted as I can never get it to work.

SELECT *
  FROM customer
 WHERE (record_number = '{$_POST['record_number']}')
    OR (po = '{$_POST['record_number']}) 
LIMIT 1

Even when I enter the correct data, it comes back as nothing foun开发者_如何学God. What is the proper syntax for this - drives me insane. I have tried this 10 different ways, including no ( brackets, brakets in different areas of the statement etc.

You didn't close the quote on OR (po='{$_POST['record_number']}).

On a side note - this is hugely opened to sql injection attack - might want to look into sanitizing your inputs.

Assuming this is being done from php

$sql = "SELECT * FROM customer WHERE (record_number='". mysql_real_escape_string($_POST['record_number'])."') OR (po='". mysql_real_escape_string($_POST['record_number'])."') LIMIT 1";

Should work and is escaped from mysql injection.

The problem is not because of the structure of your query, but because you are breaking it with your single colons.

(record_number='{$_POST['record_number']}') OR (po='{$_POST['record_number']})

In PHP, you are allowed to not use single colons in array indexes, so try this:

(record_number='{$_POST[record_number]}') OR (po='{$_POST[record_number]}')

Although I consider it to be bad practice to not use single colons in arrays and there are other ways to build your query string.

Use:

$query = sprintf("SELECT c.*
                    FROM CUSTOMER c
                   WHERE c.record_number = '%s'
                      OR c.po = '%s'
                   LIMIT 1",
                 mysql_real_escape_string($_POST["record_number"]),
                 mysql_real_escape_string($_POST["record_number"]));

$result = mysql_query($query);

• You were missing a single quote for the po comparison
• The brackets are not necessary in this query
• The query you posted is susceptible to SQL injection attacks - use prepared statements

A couple of problems. One is that you are using the same style of quotes (single quotes) for both the SQL literals and the PHP string being used to index into the $_POST array. Use double quotes for the PHP string: "record_number" instead of 'record_number'.

But beware! Blindly including input from $_POST inside a MySQL query is dangerous, as it exposes your code to SQL injection attacks. At the very least you should ensure that $_POST["record_number"] only contains digits. Better is to write your query using parameters and pass the values for those parameters using the mechanisms defined by whatever driver or library you're using.

Also, it's a good practice to use parentheses whenever you've got an OR clause in your WHERE condition. In your example it's not a problem, but this:

WHERE x = 1 AND y = 2 OR z = 3

evaluates to this:

WHERE (x = 1 AND y = 2) OR (z = 3)

when you might have meant this:

WHERE (x = 1) AND (y = 2 OR z = 3)