Well, I would like to know if there are limits to the number of logical operators I can use in a MySQL query, for example:
$sql = "SELECT * FROM usuarios WHERE nome = 'Fulano' OR email = '[email protected]' AND id <> '1'";
I would also like to know if this query is valid. Thanks!
There are no operator limits, however they directly affect query performance, including the order in which they are placed.
Your query is valid, but may not bring you the desired result by the order of the operators. The AND will always be processed first than the OR , so the results returned by the query, have the name equal to 'So-and-so' or the email equal to the one entered and id other than 1.
To correct this problem, use parentheses to tell which will be processed first, like this:
$sql = "SELECT * FROM usuarios WHERE (nome = 'Fulano' OR email = '[email protected]') AND id <> 1";
If your id field is numeric, it does not have to be enclosed in quotation marks ('1') the bank will need to perform text conversion to number unnecessarily.
Still on the operators, in mathematical logic the AND operator corresponds to the multiplication, and the OR to the addition. So order, just math.
You can add as many operators as you need in your query, there are no limits, and you can improve conditions by isolating each one by placing them in parentheses.
Your example:
$sql = "SELECT * FROM usuarios WHERE nome = 'Fulano' OR email = '[email protected]' AND id <> '1'";
It may not bring the desired result, the most convenient would be:
$sql = "SELECT * FROM usuarios WHERE (nome = 'Fulano') OR (email = '[email protected]') AND (id <> 1)";
Just as another result could be different ( could , not a statement):
$sql = "SELECT * FROM usuarios WHERE (nome = 'Fulano' OR email = '[email protected]') AND (id <> 1)";
Your query performance may be better if you start with the least likely condition.