Mysql计数行/在1个查询上一起添加2个不同的结果

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

Answer 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