WHERE not permitting multiple values

[HDFilmMaker2112]

Here's my code:

 

<?php
$popular="product_id='1'"; //AND product_id='2' AND product_id='1' AND product_id='1' AND product_id='1'";
$host="localhost"; // Host name 
$username="username"; // Mysql username 
$password="password"; // Mysql password 
$db_name="zyquo_ghp"; // Database name 
$tbl_name="products"; // Table name 

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

$date_main="5/5/2011";
$content='
<div class="content_text">
<div class="content_header">Welcome to Ghost Hunter\'s Portal</div>
<p>We are real paranormal investigators selling equipment we use every week on investigations. 
We offer email support for all of our products. We sell more than just ghost hunting equipment, so you can get all 
your paranormal supplies all in one place. The advice on how to use the items we sell will be 
answered by real researchers. These are the tools that we personally use regularly on investigations, so we won\'t 
sell anything that doesn\'t work properly in the field.</p>

<p>We not only focus on gearing paranormal groups, but also individuals. If you believe you\'re sharing space with 
an entity, let us help you find out for sure. Whether you\'re a staunch believer, a hardcore skeptic or just curious 
about ghost hunting, our equipment line features just the ghost hunting tools you\'ll need on your quest for answers.</p>
<br />
<div class="content_header">Our Popular Products</div>
<div class="popular_products">';
$sql="SELECT * FROM $tbl_name WHERE $popular";
$result=mysql_query($sql);
while($row=mysql_fetch_array($result)){
$product_category=$row['product_category'];
$content.='<div class="popular_item">'.$product_category.'</div>'."\n";
}
$content.='
</div>
</div>
';
?>

 

When I alter this (which works):

 

$popular="product_id='1'"; //AND product_id='2' AND product_id='1' AND product_id='1' AND product_id='1'"; 

 

to this:

 

$popular="product_id='1' AND product_id='2'"; // AND product_id='1' AND product_id='1' AND product_id='1'";

 

This doesn't work. It returns no values at all. I know for a fact that there are product_ids of both 1 and 2.

[gizmola]

You want to use OR.

[HDFilmMaker2112]

Wouldn't OR only display one entry? I need to pull five entries. All from the same table, based on their product_id.

[PFMaBiSmAd]

A logical AND requires all the conditions to be TRUE at the same time, which they cannot be for one field.

 

You actually need to use an OR (the field is one value OR another value OR another value.)

 

mysql has an IN() conditional test that will let you easily match a field against a list of values -

 

WHERE product_id IN(1,2)

[HDFilmMaker2112]

IN() is exactly what I was looking for. Thanks.

[gizmola]

In ( 1, 2) is exactly the same as product_id = 1 OR product_id = 2.

[HDFilmMaker2112]

In ( 1, 2) is exactly the same as product_id = 1 OR product_id = 2.

 

Your right, but that doesn't make much sense to me.

 

To me OR would be a logical operator meaning it would pull only product_id=1 or product_id=2 based on some other argument in the code. Not pull both of them. That's at least the way it functions in PHP.

 

Basically What I get out of using OR here is,

 

WHERE product_id=1; pull product_id 1

or

WHERE product_id=2; pull product_id 2

 

but never both, to me AND would mean pull both.

 

Anyway, using IN() shortens my code.

[gizmola]

The way to look at it, is that the WHERE clause is used to attempt to find a match for *any single row* based on the criteria included.

 

So when you say WHERE product_id = 1 OR product_id = 2, you're saying

 

- check the product_id, Does it equal 1?  If so then add the row to the result set.

 

OR

 

-check the product_id, Does it equal 2?  If so, then add the row to the result set.

 

When you have AND in the criteria, it means that for that one row, both criteria must be TRUE in order to add the row.  So for a row, user_Id = 1 AND user_id = 2 is an impossibility.  You would never get a row back where a column that has an atomic value could be equal to 2 different values.  That is why you got an empty result set.

 

And really exists for queries like:

 

SELECT * from users WHERE gender = "Male" AND status = "Single"

 

Which you can think of as ... get me all the Men who are also Single.  We want only men in this result, but only if they are single.

[HDFilmMaker2112]

Alright, so it's (AND) a logical operator per row, not to get more than one row.

[gizmola]

Right.