Mysql count row /adding together 2 different result on 1 query

Question

I am using php myAdmin
table name is productx and in it I have

---------------------------------------------
Id | gender | brand | category | subcategory|
---------------------------------------------
1  | women  | nike  | shoes    | sandalet   |
2  | women  | nike  | shoes    | nightshoes |
3  | women  | nike  | shoes    | sandalet   |
4  | women  | nike  | shoes    | nightshoes |
5  | women  | puma  | shoes    | sandalet   |
6  | women  | nike  | shoes    | eveningshoes|
7  | women  | puma  | shoes    | sandalet   |
8  | women  | nike  | shoes    | sandalet   |

than I have a form with checkboxes like this form submiting itself with javascript

<?php if(isset($_POST['shoes'])): ?>
      <input type="checkbox" name="eveningshoes" value="<?php echo $eveningshoes; ?>"onclick="formSubmit()"<?php if(isset($_POST['eveningshoes'])):?>checked<?php endif;?>><?php echo $eveningshoes; antalSkor_subcategory_eveningshoes($shoes_post_var,$eveningshoes);?><br>
      <input type="checkbox"name="nightshoes" value="<?php echo $nightshoes; ?>"  onclick="formSubmit()"<?php if(isset($_POST['nightshoes'])):?>checked<?php endif;?>><?php echo $nightshoes; antalSkor_subcategory_nightshoes($shoes_post_var,$nightshoes);?><br>
      <input type="checkbox" name="sandalet" value="<?php echo $sandalet; ?>"onclick="formSubmit()"<?php if(isset($_POST['sandalet'])):?>checked<?php endif;?>><?php echo $sandalet; echo antalSkor_subcategory_sandalet($shoes_post_var,$sandalet);?><br>       
<?php endif;?>

if user clicks the checkbox with value sandalet gona output the result by using this function Function is the same for the other brand just want to hold it short here

function antalSkor_nike($shoes_post_var,$sandalet_post_var,$nightshoes_post_var,$eveningshoes_post_var,$nike) {
        if(isset($shoes_post_var) && isset($sandalet_post_var)){
        //if sandalet checked counting how many NIKE sandalet we have
            $result1=mysql_query("SELECT count(*) from productsx where category='$shoes_post_var ' AND subcategory='$sandalet_post_var' AND brand ='$nike'")or die (mysql_errno());
            $total1 = mysql_result($result1, 0);    
            echo ' '.'('.$total1.')';

        }elseif(isset($shoes_post_var) && isset($nightshoes_post_var)){     
        //if night shoes checked counting how many NIKE night shoes we have
            $result2=mysql_query("SELECT count(*) from productsx where category='$shoes_post_var ' AND subcategory='$nightshoes_post_var' AND brand ='$nike'")or die (mysql_errno());
            $total2 = mysql_result($result2, 0);    
            echo ' '.'('.$total2.')';
        }elseif(isset($shoes_post_var) && isset($eveningshoes_post_var)){       
        //if evening shoes checked counting how many NIKE evening shoes we have
            $result3=mysql_query("SELECT count(*) from productsx where category='$shoes_post_var ' AND subcategory='$$eveningshoes_post_var' AND brand ='$nike'")or die (mysql_errno());
            $total3 = mysql_result($result3, 0);    
            echo ' '.'('.$total3.')';
        }else{
        if(isset($shoes_post_var)){
                $result=mysql_query("SELECT count(*) from productsx where category='$shoes_post_var ' AND brand ='$nike'")or die (mysql_errno());
            $total = mysql_result($result, 0);  
            echo ' '.'('.$total.')';
        }
    }
}

and result gona be

    <input type="checkbox" name="nike"value="<?php echo @$nike; ?>" onclick="formSubmit()"<?php    if(isset($_POST['nike'])):?>checked<?php endif;?>><?php echo @$nike; echo antalSkor_nike($shoes_post_var,$sandalet_post_var,$nightshoes_post_var,$eveningshoes_post_var,$nike);?><br>
       <input type="checkbox" name="puma"value="<?php echo @$puma; ?>" onclick="formSubmit()"<?php    if(isset($_POST['puma'])):?>checked<?php endif;?>><?php echo @$puma; echo antalSkor_puma($shoes_post_var,$sandalet_post_var,$nightshoes_post_var,$eveningshoes_post_var,$puma);?><br>

Sandalet NIKE (3)
if user check only nightshoes result gona be for nike
Nightshoes NIKE (2)

AND question is what if user checks both of them

how can I use query to count them to (5)? Thanks

Community · Accepted Answer · 2017-05-23 11:49:12Z

1

Try this:

SELECT COUNT(id) FROM productx WHERE ... WHEREs for brand, gender, and category ... AND subcategory IN ('sandalet', 'nightshoes')

Source: WHERE IN MySQL Clause

edited May 23, 2017 at 11:49

CommunityBot

11 silver badge

answered Jul 14, 2013 at 2:59

David

3,8112 gold badges31 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

samuel Over a year ago

Thank you David your code is working :) sorry I cant vote yet, need to come up 15 reputation score.

Collectives™ on Stack Overflow

Mysql count row /adding together 2 different result on 1 query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related