CODEWITHSUNDEEP
phpmysqli
pruoccojr
pruoccojr

Reputation: 482

How can I simplify these mySQLi queries?

I have 5 similar queries which output a single number. The payment_type determines the output. Basically it will show how many transactions were done with Cash, Check, Credit, or Autopay for the year.

$getTxnX = $con->query("SELECT COUNT(*) AS num FROM transaction WHERE YEAR(paid_on) LIKE '%".date ('Y')."%'");
$getTxnC = $con->query("SELECT COUNT(*) AS num FROM transaction WHERE YEAR(paid_on) LIKE '%".date ('Y')."%' AND payment_type='C'");
$getTxnK = $con->query("SELECT COUNT(*) AS num FROM transaction WHERE YEAR(paid_on) LIKE '%".date ('Y')."%' AND payment_type='K'");
$getTxnO = $con->query("SELECT COUNT(*) AS num FROM transaction WHERE YEAR(paid_on) LIKE '%".date ('Y')."%' AND payment_type='O'");
$getTxnA = $con->query("SELECT COUNT(*) AS num FROM transaction WHERE YEAR(paid_on) LIKE '%".date ('Y')."%' AND payment_type='A'");

I am outputing the numbers using

if ( $row = $getTxnX->fetch_assoc() ) { 
    echo $row['num']; 
}
if ( $row = $getTxnC->fetch_assoc() ) { 
    echo $row['num']; 
}
if ( $row = $getTxnK->fetch_assoc() ) { 
    echo $row['num']; 
}
if ( $row = $getTxnO->fetch_assoc() ) { 
    echo $row['num']; 
}
if ( $row = $getTxnA->fetch_assoc() ) { 
    echo $row['num']; 
}

It works fine, but is there a way to simplify the queries into one, then use something like the following?:

if ( $row = $getNewTxnQueryHere->fetch_assoc() ) { 
    echo $row['X']; 
    echo $row['C']; 
    echo $row['K']; 
    echo $row['O']; 
    echo $row['A']; 
}

I plan to wrap if ( $row = $getNewTxnQueryHere->fetch_assoc() ) { ... } around my entire results area and use the echo $row['X']; in various places within.

Upvotes: 0

Views: 45

Answers (2)

Max P.
Max P.

Reputation: 5679

You can use one query

$query = $con->query("SELECT payment_type, COUNT(*) as cnt AS num FROM transaction WHERE YEAR(paid_on) LIKE '%".date ('Y')."%' group by payment_type");
$data = array();
while($row = $query->fetch_assoc()) { 
    $data[$row['payment_type']] = $row['cnt'];
}

all data will be in array $data, ex: echo $data['C']; or echo $data['A']; or for total echo array_sum($data);

Upvotes: 1

Marc B
Marc B

Reputation: 360572

Why not use a single grouped query?

SELECT payment_type, count(*)
FROM transaction
WHERE payment_type IN ('X', 'C', 'K', 'O', 'A')
    AND YEAR(paid_on) = YEAR(curdate())
GROUP BY payment_Type

Upvotes: 4

Related Questions