COUNT Statement

[CanMan2004]

Hi all

I have a count statement, but it's counting too many rows, can anyone see why?

[code]$results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM images WHERE view = '1' AND type1 = '".$_GET['type1']."' OR type1a = '".$_GET['type1']."' OR type1b = '".$_GET['type1']."' OR type1c = '".$_GET['type1']."' AND type2 = '".$_GET['type2']."' OR type2a = '".$_GET['type2']."' OR type2b = '".$_GET['type2']."' OR type2c = '".$_GET['type2']."'", $connection),0);[/code]

Basically there is 2 drop downs in a form, one called 'type1' and another called 'type2', when the form is submitted, the above statement is supposed to check to see if what was selected in 'type1' appears anywhere in the colums

type1 OR
type1a OR
type1b OR
type1c OR

and also check that what was selected in 'type2' appears anywhere in colum

type2 OR
type2a OR
type2b OR
type2c OR

Can anyone help at all?

Thanks in advance

Ed

[akitchin]

you've got to group your conditions based on how you want them processed.  from what i can see, you want the following:

view must = 1
one of the type1s must = $_GET['type1']
one of the type2s must = $_GET['type2']

in this case, you need to group the type1s into parenthesis, and group the type2s into parenthesis.  otherwise you are saying "view must be 1 and type1 must be $_GET['type'] 1, or we can abandon the whole process if type1a is $_GET['type1']."  conditions are parsed left to right (i believe), and will run the statement (in this case, count the row) as soon as the condition is true.

[code]view='1' AND (type1='{$_GET['type1']}' OR ...) AND (type2='{$_GET['type2']}' ...)[/code]

give that a shot.

[CanMan2004]

Hi

Thanks for that, I tried adjusting the code to

[code]$results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM mirrorimages WHERE view='1' AND (type1='{$_GET['type1']}' OR type1a='{$_GET['type1']}' OR type1b='{$_GET['type1']}' OR type1c='{$_GET['type1']}') AND (type2='{$_GET['type2']}' OR type2a='{$_GET['type2']}' OR type2b='{$_GET['type2']}' OR type2c='{$_GET['type2']}')", $connection),0);[/code]

But that returns 0 no matter how many results should be found.

Can anyone shed anymore light on getting it to count correct?

THanks in advance

Ed

[akitchin]

the first question i guess, is are you SURE that there are rows matching all three?  ie. view is 1, one of the type1s is equal to the value from select box 1, and that one of the type2s matches the second select box as well?