[SOLVED] WHERE clause works with OR but not AND

[tryingtolearn]

Still working on my form with checkboxes

 

I have a table full of records

Then a table full of categories

 

Then a table to associate the

record ID with a Category ID

 

So a record can have multiple categories.

 

----

Now I have a form that lists all the categories with checkboxes and I want the user to be able to select as many categories as they want and return ONLY the records that have all of the selected categories.

 

This is the code that Im using

if (isset($_POST['submitted'])) {
if (!empty($_POST['cat_x'])) {		
	$sc = $_POST['cat_x'];
} else {
	$sc = FALSE;
	echo '<p><font color="red">Please select a Category!</font></p>';
	include ('./includes/footer.html');
	exit(); // Quit the script.
}


foreach($sc as $value){
    $where_list[] = " tca.id = '".$value."'";
}

if(!empty($where_list)) {
    $where = "AND (";
    $where .= implode(" AND ",$where_list);
$where .= ")";
}




$query = "SELECT DISTINCT t.title
FROM templates AS t, associations AS tca
WHERE t.approved='Y' AND tca.id=t.id $where";
$result = mysql_query ($query);
$num = mysql_num_rows($result);
if ($num >=1) {
while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) {
echo "{$row['title']}<br>";
}

}else{
echo "No Records Available At This Time.";
}

}//end if post submitted
else{//if post wasnt submitted

echo '<div class="Error">We did not receive your search criteria.</div><br>Please <a href="index.php">Try Again</a>';

}//end if post wasnt submitted

 

But if multiple checkboxes are selected It always returns

No Records Available At This Time

 

If I change

if(!empty($where_list)) {
    $where = "AND (";
    $where .= implode(" AND ",$where_list);
$where .= ")";
}

to

if(!empty($where_list)) {
    $where = "AND (";
    $where .= implode(" OR ",$where_list);
$where .= ")";
}

The AND to OR

 

It works as expected,

It returns all the records that have the selected categories associated with them

 

But I am looking for and exact match

 

Any ideas??

[thedarkwinter]

can you echo out the $where and post it here, for both AND + OR

[tryingtolearn]

With And

WHERE t.tem_approved='Y' AND tca.tem_id=t.tem_id AND tca.tcat_id = c.tcat_id AND ( tca.tcat_id = '32' AND tca.tcat_id = '54' AND tca.tcat_id = '47')

 

With OR

WHERE t.tem_approved='Y' AND tca.tem_id=t.tem_id AND tca.tcat_id = c.tcat_id AND ( tca.tcat_id = '32' OR tca.tcat_id = '54' OR tca.tcat_id = '47')

 

The database its pulling from looks like this

temcat_id  tem_id  tcat_id

      35    77    47

      34    77    54

      33    77    32

      32    76    50

      31    76    52

      30    76    54

      29    76    53

      28    76    30

      23    75    34

      22    75    31

      21    75    30

      20    75    33

      19    75    32

 

The OR

returns 75, 76, 77

 

The AND should return 77 but I get the

No Records Available At This Time instead.

 

I also tried it with the where like this

 

 WHERE t.tem_approved='Y' AND tca.tem_id=t.tem_id AND tca.tcat_id = c.tcat_id AND ( tca.tcat_id = '32' ) AND ( tca.tcat_id = '54' ) AND ( tca.tcat_id = '47')

but got the same result.

[tryingtolearn]

Maybe this will be less confusing.

 

If the database table called temcat_associations is set up like this

 

temcat_id  tem_id  tcat_id 

      35    77    47

      34    77    54

      33    77    32

      32    76    50

      31    76    52

      30    76    54

      29    76    53

      28    76    30

      23    75    34

      22    75    31

      21    75    30

      20    75    33

      19    75    32

 

What would the WHERE clause be to just have tem_id 77 returned?

 

I tried this - just coded in

$query = "SELECT tem_id FROM temcat_associations WHERE (tcat_id=54) AND (tcat_id=47) AND (tcat_id=32)";
$result = mysql_query ($query);
$num = mysql_num_rows($result);
if ($num >=1) {
while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) {
echo "{$row['tem_id']}<br>";
}

}else{
echo "No Records Available At This Time.<br><br><br>";
}

 

But still get the No Records Available At This Time

 

If I can get a working where clause on this Im sure I can adapt the previous code...

[pikemsu28]

.....

[akitchin]

the WHERE clause you're attempting won't work, because it expects to evaluate a WHERE clause using only one row of data.  it sounds like you'll need to incorporate an IF() and a subquery into the query in order to account for this; if you can't run subqueries, you'll need to run an external query for the WHERE clause:

 

SELECT tem_id AS alias_for_tem_id WHERE stuff AND IF((SELECT COUNT(tem_id) FROM table WHERE tem_id=alias_for_tem_id AND tcat_id IN (54,47,32)) = 3, 1, 0)

 

this will select the row containing the tem_id where it's got tcat_id's 54, 47 and 32.  it checks whether the number of rows matching the currently studied tem_id in the parent SELECT and having a COUNT() of three for those three tcat_id's.  hopefully - i haven't tested it and this seems a pretty involved query (and something i haven't done before).

[tryingtolearn]

I will try it out and get back to you.

Thanks for responding!

[tryingtolearn]

Hmm well I must not be doing something right with that

I just keep getting a not a valid resource error

(Ill be honest - Thats way over my head to even know where to start putting what!)

 

Thanks though.

[akitchin]

i've probably screwed up the syntax.  check for what the error is by using a die clause:

 

$resource = mysql_query($query) or die('problem with the query '.$query.': mysql said '.mysql_error());

[tryingtolearn]

Is this what you mean?

problem with the query SELECT tem_id AS alias_for_tem_id WHERE (tcat_id=54) AND (tcat_id=47) AND (tcat_id=32) AND IF((SELECT COUNT(tem_id) FROM temcat_associations WHERE tem_id=alias_for_tem_id AND tcat_id IN (54,47,32)) = 3, 1, 0): mysql said You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (tcat_id=54) AND (tcat_id=47) AND (tcat_id=32) AND IF((SELECT COUNT(tem_id' at line 1

[tryingtolearn]

Ahh Ok I get it now

 

But I still get the same result

 

This was the query

SELECT tem_id AS alias_for_tem_id FROM temcat_associations WHERE (tcat_id=54) AND (tcat_id=47) AND (tcat_id=32) AND IF((SELECT COUNT(tem_id) FROM temcat_associations WHERE tem_id=alias_for_tem_id AND tcat_id IN (54,47,32)) = 3, 1, 0)

[akitchin]

my syntax is probably wrong, but you're still misunderstanding the WHERE clause you're using.  with your WHERE clause, it will only select the rows that have a tcat_id value of 54, 47 AND 32.  this isn't possible for one single row, because each row has one and only one tcat_id.  to run a WHERE clause that will encompass several rows, you'll need to introduce a count of some sort.  try this (it just dawned on me that this is a far simpler method):

 

SELECT tem_id, COUNT(tem_id) AS total FROM table WHERE tcat_id IN (54,47,32) GROUP BY tem_id HAVING total = 3 ORDER BY tem_id

 

if fenway were around, he could really clear this up.  i'm moving this to MySQL, since it belongs there a little bit more.

[tryingtolearn]

Thanks

That worked alot easier (I sort of understand it too!)

Now I will have to try and get it to work with the original code by getting the array from the form.

 

1 question though -

Im using Larry Ulmans PHP MYSQL book and the example in the book is

SELECT * FROM users WHERE (user_id >= 10) AND (user_id <= 20)

 

Does the use of >= vs just = make the difference????

since

SELECT tem_id FROM temcat_associations WHERE (tcat_id = 54) AND (tcat_id = 47) AND (tcat_id = 32)

doesnt work

Or is there another difference between the two?

[akitchin]

there is a significant other difference, and it isn't in the = operator.  the query you're running, in english, is:

 

grab "tem_id" from any row in "temcat_associations" where THAT ROW has a "tcat_id" of 54, 47 and 32.

 

you'll note that a single row will never have tcat_id values of 54, 47, and 32; this is because each row will only have one value of tcat_id.  you need to use an aggregate (multi-row calculation) function of some sort in order to take a multi-row condition into account.

[tryingtolearn]

Thanks for the help akitchin

 

I do appreciate it.

Day by day I learn a little more.