CODEWITHSUNDEEP
phpmysql
Marcialoo
Marcialoo

Reputation: 37

mysql how to shorten count 5 results

I need to make 5 count results from mysql, and I wanna ask, maybe exist shorten code for all results? My code now:

$result1 = mysqli_query($con,"SELECT * FROM bets WHERE status='Waiting'");
$row_cnt1 = $result1->num_rows;

$result2 = mysqli_query($con,"SELECT * FROM bets WHERE status='Win'");
$row_cnt2 = $result2->num_rows;

$result3 = mysqli_query($con,"SELECT * FROM bets WHERE status='Lost'");
$row_cnt3 = $result3->num_rows;

$result4 = mysqli_query($con,"SELECT * FROM bets WHERE status='Void'");
$row_cnt4 = $result4->num_rows;

And I display with this code:

    <b>Total wins:</b> <?php echo $row_cnt1;?> <br />
<b>Total voids:</b> <?php echo $row_cnt2;?> <br />
<b>Total losts:</b> <?php echo $row_cnt3;?> <br />
<b>Total waiting:</b> <?php echo $row_cnt4;?> <br />

Upvotes: 0

Views: 97

Answers (3)

Phan Hero
Phan Hero

Reputation: 325

Try this code. It uses GROUP BY clause in SQL query and a while loop to shorten your code.

    <?php
    $qry = "SELECT COUNT(*) AS CNT, status FROM bets  GROUP by status";
    $result1 = mysqli_query($con, $qry );
    while($row = mysqli_fetch_array(result1, MYSQLI_ASSOC))
    {

    ?>
      <b>Total $row['status']:</b> <?php echo $row['CNT'];?> <br />
    <?php 
   }
   ?>

Upvotes: 0

zsilbi
zsilbi

Reputation: 131

$query = mysqli_query($con,"SELECT status, COUNT(*) as number FROM bets WHERE status IN ('Waiting','Win','Lost','Void') GROUP BY status");

You will have 4 rows, with 2 columns, from one query.

Note: If you don't have anymore statuses, the WHERE statement is unnecessary.

You can display your results like this:

while ($row = mysqli_fetch_assoc($query)) {
    echo '<b>Total ' . $row['status'] . ': ' . $row['number'] . '</b><br/>';
}

Upvotes: 1

AdamMc331
AdamMc331

Reputation: 16690

You should be able to use the COUNT() function if you group like this:

SELECT status, COUNT(*) AS numForStatus
FROM bets
GROUP BY status;

So, this will display a table like this:

|  status  | numForStatus |
+----------+--------------+
|  waiting |      5       |
|  win     |      4       |
|  lost    |      3       |
|  void    |      2       |

That's one way to simplify your query, and can hopefully make your output a little easier as well.

Upvotes: 3

Related Questions