PHP - How can I foreach an SQL Statement by conditional where

Question

I want to know how can I foreach a sql count statement depending on its status. When I tried to echo it, all statuses turned into number 1. I want to put it inside a loop instead of doing the statement five times.

<?php
$statuses = array("In-process", "Approved", "Draft", "Approved" , "Rejected"); 

foreach ($statuses as $stat) {  
    $stmt = $db->prepare(
        "SELECT COUNT(status) FROM `m2_exception_requests`
         WHERE created_by= ? and   status = ?"
    ) or die($db->error);  

    $stmt->bind_param("ss", $user_id , $stat);
    $stmt->execute();
    $result = $stmt->store_result(); 

    if  ($stat = 'In-process') {
        $Inprocess1 =  $result;
    }
    if  ($stat = 'Approved') {
        $Approved1 =  $result;
    }
    if  ($stat = 'Draft') {
        $Draft1 = $result;
    }      
}

Possible duplicate of [How to get multiple counts with one SQL query?](https://stackoverflow.com/questions/12789396/how-to-get-multiple-counts-with-one-sql-query) — Twisty, Oct 10 '18 at 07:01
Check the type of equals you use in things like `$stat = 'In-process'` - this is assignment and not equal to (`==`) — Nigel Ren, Oct 10 '18 at 07:07

score 2 · Answer 1 · answered Oct 10 '18 at 07:06

You can make use of GROUP BY to get count of record in each status; below is simple SQL query; you convert it in Parameter bindings;

SELECT status, COUNT(status) FROM `m2_exception_requests` WHERE `created_by` = ? AND `status` IN ("In-process", "Approved", "Draft", "Approved1" , "Rejected") GROUP BY `status`

score 2 · Accepted Answer · answered Oct 10 '18 at 07:23

Here is a dynamic and SQL Injection safe solution:

This will use the IN and the GROUP BY clauses to generate your results.

$statuses = array("In-process", "Approved", "Draft", "Approved" , "Rejected");

$clause = implode(',', array_fill(0, count($statuses), '?')); //create your question marks
$types = 's' . str_repeat('s', count($statuses)); //create you bindings

$query = "SELECT COUNT(status) 
  FROM `m2_exception_requests`
  WHERE created_by = ? 
  AND status IN($clause)
  GROUP BY status";

$stmt = $db->prepare($query);
$stmt->bind_param($types, $user_id, ...$statuses);
$stmt->execute();
$resArr = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
if(!$resArr) exit('No rows');
var_export($resArr);
$stmt->close();

Good luck!

it works but can I ask how can i echo it? I tried working with `implode(" ",$resArr[1]);` however if its null the array adjust — Patricia Mari Maclang, Oct 10 '18 at 07:41
That should have displayed an array for you. When you reference `$resArr[1]` you are saying that you want to implode the `[1]` element of the array. You probably need to change it to: `echo implode(' ', $resArr);` that way you are referencing the entire array. — Joseph_J, Oct 10 '18 at 07:51

Lakhwinder Singh · Answer 3 · 2018-10-10T07:15:40.463

0

you can try this.

<?php
$statuses = array("In-process", "Approved", "Draft", "Approved1" , "Rejected"); 

$statusVal = [];
foreach ($statuses as $stat) {  
    $stmt = $db->prepare(
        "SELECT COUNT(status) FROM 
            `m2_exception_requests`
            WHERE created_by= ? 
            and   status = ?"
    ) or die($db->error);  

    $stmt->bind_param("ss", $user_id , $stat);
    $stmt->execute();
    $count = $stmt->rowCount(); 

    $statusVal[$stat] = $count;
}

var_dump($statusVal);

you will get values like this

[
    "In-process" => 1,
    "Approved" => 2,
    "Draft" => 3,
    "Approved1" => 4,
    "Rejected" => 5
]

edited Oct 10 '18 at 07:15

answered Oct 10 '18 at 06:56

Lakhwinder Singh

5,536
5
27
52

I dont know why it is showing `array(5) { ["In-process"]=> bool(true) ["Approved"]=> bool(true) ["Draft"]=> bool(true) ["Approved1"]=> bool(true) ["Rejected"]=> bool(true) }` – Patricia Mari Maclang Oct 10 '18 at 07:12
1

are you using PDO? – Lakhwinder Singh Oct 10 '18 at 07:14
im sorry im using mysqli – Patricia Mari Maclang Oct 10 '18 at 07:22

PHP - How can I foreach an SQL Statement by conditional where

3 Answers3