Can't use AND Operator on a mysqli_query?

Question

I need to filter my query with more than one argument but I can't do this using mysqli_query, instead of mysql_query.

My code works perfectly if I don't use the AND Operator, for example:

$results = mysqli_query($connecDB,"SELECT * FROM table WHERE company='Company1' ORDER BY id DESC");

But if I use the AND Operator:

 $results = mysqli_query($connecDB,"SELECT * FROM table WHERE company='Company1' AND company='Company2' ORDER BY id DESC");

The code doesn't work anymore. I search around and it looks like i need Prepared Statements but I can't find a way to make my code work.

Someone know what i'm doing wrong ?

can you please provide the error that it outputs with the AND so that we can determine why its not working. — azngunit81, Jul 02 '14 at 15:38
Nothing to do with prepared statements: a company cannot be both Company1 and Company2 at the same time.... learn the difference between AND and OR — Mark Baker, Jul 02 '14 at 15:39
As already stated above. However, if you wanted to use `AND`, you would need to use something like `WHERE company='Company1' AND city='City1'` — Funk Forty Niner, Jul 02 '14 at 15:40

score 5 · Accepted Answer · answered Jul 02 '14 at 15:40

5

Just try with:

SELECT * FROM table WHERE company IN ( 'Company1', 'Company2' ) ORDER BY id DESC

You can't have company that is simultaneously Company1 and Company2.

answered Jul 02 '14 at 15:40

Danijel

12,408
5
38
54

1

Also, in this basic case, IN will perform better than OR. See [MYSQL OR vs IN performance](http://stackoverflow.com/questions/782915/mysql-or-vs-in-performance) – Sarthaz Jul 02 '14 at 15:48
@Sarthaz, i did not know that, it just seems more simple and logical than OR clause – Danijel Jul 02 '14 at 15:51
@Danijel, Thanks for the reply, it worked as I needed. I was wrong thinking that the OR would exclude the other results. – suicidebilly Jul 02 '14 at 16:00

score 1 · Answer 2 · answered Jul 02 '14 at 15:58

Firstly, there are absolutely no rows for which both predicates will evaluate to true.

MySQL is looking at each individual row, and checking whether the row satisfies the predicates, in this case, whether the result of the entire WHERE clause returns TRUE.

If there's a row that has a value of 'Company1' in the company column, then the first predicate:

company = 'Company1'

will evaluate to TRUE; and the second predicate:

company = 'Company2'

is will evaluate to FALSE.

According to logic of the boolean AND operator, TRUE AND FALSE evaluates to FALSE, so the row will not be returned.

This should be sufficient to explain why the query is returning 0 rows.

If you want the query to return a row when value of the company column matches either 'Company1' or 'Company2', the two predicates can be combined with a boolean OR operator.

score 0 · Answer 3 · answered Jul 02 '14 at 15:40

0

Use OR instead of AND:

SELECT * FROM table WHERE (company='Company1' OR company='Company2') ORDER BY id DESC

answered Jul 02 '14 at 15:40

SeanWM

16,789
7
51
83

1

While the solution is correct I feel a short description of why it works should follow along, even though it's dead-drop-simple. – Jonast92 Jul 02 '14 at 15:47

score 0 · Answer 4 · answered Jul 02 '14 at 15:59

Actually, its problem with your logic. You must be using OR instead of AND because because company field could contain only one company out of the two and company field can't contain both the companies simultaneously. So, you must change your $query to the following:

SELECT * FROM table WHERE (company='Company1' OR company='Company2') ORDER BY id DESC

or

SELECT * FROM table WHERE company IN ( 'Company1', 'Company2' ) ORDER BY id DESC

Can't use AND Operator on a mysqli_query?

4 Answers4