Jump to content

WHERE not permitting multiple values


Recommended Posts

Here's my code:


$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");

<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";
$content.='<div class="popular_item">'.$product_category.'</div>'."\n";


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.

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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


WHERE product_id=2; pull product_id 2


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


Anyway, using IN() shortens my code.

Link to comment
Share on other sites

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.




-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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.