MySQL select count(*) 函数查找所有带 OPEN 的状态

Question

我卡在我的代码里了!

在我的数据库中，我有这些列:

id | name | mail | status | date

在 status 中，它只能是 OPEN 或 CLOSED。

现在我的数据库中有超过 100 行，我想知道有多少行OPEN 有多少行CLOSED，但我不知道该怎么做这个。

我试着在页面上写了很多帖子，但没有人给我正确的答案。

我写了这段代码:

$query = "SELECT status, COUNT(OPEN) FROM cases GROUP BY status ORDER BY COUNT(status) desc";
$result = mysql_query($query);


<?php echo($result); ?>

现在我希望得到这样的结果:

53

Answer 1

您可以使用 CASE mysql查询状态的方法

SELECT SUM(CASE WHEN status = 'OPEN' THEN 1 ELSE 0 END) AS 'Open', SUM(CASE WHEN status = 'CLOSED' THEN 1 ELSE 0 END) AS 'Closed' FROM cases

让我知道它是否适合你

In PHP

<?php 
if (mysqli_connect_errno()) { 
  echo "Failed to connect to MySQL: " . mysqli_connect_error(); 
  }
  $sql="SELECT SUM(CASE WHEN status = 'OPEN' THEN 1 ELSE 0 END) AS 'Open', SUM(CASE WHEN status = 'CLOSED' THEN 1 ELSE 0 END) AS 'Closed' FROM cases"; 
  if ($result=mysqli_query($con,$sql)) {
   // Return the number of rows in result set 
    $rowcount=mysqli_num_rows($result); 
    printf("%d",$rowcount); // Free result set
    $row = mysqli_fetch_array($result, MYSQLI_ASSOC);
    mysqli_free_result($result); 
  }
  mysqli_close($con);
?>

IN HTML

<div class="row">
<div class="col-lg-2 col-sm-4">
  <div class="circle-tile">
    <a href="#"><div class="circle-tile-heading green"><i class="fa fa-commenting-o fa-fw fa-3x"></i></div></a>
    <div class="circle-tile-content green">
      <div class="circle-tile-description text-faded"> Åbne sager</div>
      <div class="circle-tile-number text-faded "><?php echo $row['OPEN'];?></div>
      <a class="circle-tile-footer" href="#">Mere info <i class="fa fa-chevron-circle-right"></i></a>
    </div>
  </div>
</div>

<div class="col-lg-2 col-sm-4">
  <div class="circle-tile ">
    <a href="#"><div class="circle-tile-heading red"><i class="fa fa-check fa-fw fa-3x"></i></div></a>
    <div class="circle-tile-content red">
      <div class="circle-tile-description text-faded"> Lukket sager </div>
      <div class="circle-tile-number text-faded "><?php echo $row['CLOSED'];?></div>
      <a class="circle-tile-footer" href="#">Mere Info <i class="fa fa-chevron-circle-right"></i></a>
    </div>
  </div>
</div>