Jump to content

Archived

This topic is now archived and is closed to further replies.

CanMan2004

COUNT Statement

Recommended Posts

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites

×

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.