从sql count（）获取个人答案到php

Question

I have a sql database where I request the following bit of code;
SELECT albumtype, COUNT(*) FROM albumdata GROUP BY albumtype

The response in phpMyAdmin is the following table

• albumtype | COUNT(*)
• Album | 4
• EP | 1
• Single | 1

Then I have in my php file the following code that will return the complete count (6).

$stmt = $con->prepare('SELECT albumtype, COUNT(*) FROM albumdata GROUP BY albumtype');
$stmt->execute() or die("Invalid query");
$result = $stmt->get_result();
$row = $result->fetch_assoc();
$row_cnt = mysqli_num_rows($result);

I used this code on another page, but now I want to select a specific part of the "count()" table.

I tried to display a single result with $row_cnt = $row['Album'];, but as it turns out, this returns "Array" for some reason. Here is my php call:

$stmt = $con->prepare('SELECT albumtype, COUNT(*) FROM albumdata GROUP BY albumtype');
$stmt->execute() or die("Invalid query");
$result = $stmt->get_result();
$row = $result->fetch_assoc();
$row_cnt = $row['Album'];

How can I grab a single row, for example the number of how much the database could find Album (4 times) and put it in a php variable? I tried searching it on here, but didn't get any further.

Answer 1

1.If you want only specific albumType ten you can directly change your query like this:-

$stmt = $con->prepare("SELECT albumtype, COUNT(*) as counts FROM albumdata WHERE albumtype = 'Album'");
$stmt->execute() or die("Invalid query");
$result = $stmt->get_result();
$row = $result->fetch_assoc();
$album_cnt = $row['counts'];
echo $album_cnt;

But if you want all then,you need to do it like below:-

$stmt = $con->prepare('SELECT albumtype, COUNT(*) as counts FROM albumdata GROUP BY albumtype');
$stmt->execute() or die("Invalid query");
$result = $stmt->get_result();
$row_cnt = array();

while($row = $result->fetch_assoc()){
  $row_cnt[$row['albumtype']] = $row['counts'];
}

echo "<pre/>";print_r($row_cnt); 
// you have all data in array so you can use it now like below

foreach($row_cnt as $key=>$value){
  echo "Album type ".$key." has ".$value." counts"."<br/>";
}

//form this all data if you want to compare specific albumType then do like below:-

foreach ($row_cnt as $key=>$value) {
    if($key == 'Album'){
        echo $value;
    }
}