How can I count array values from mysql query in PHP and associate a second column with the count results

Question

What I am trying to do is select * from table then get the count of duplicate rows in a column but also have another column (make) associated with the results.

Eg:

Desired output:

Make Model Qty

Ford Focus 3

Ford Fiesta 5

Ford Mondeo 1

BMW M3 1

Audi A4 2

Audi A3 4

Current output:

Model Qty

Focus 3

Fiesta 5

Mondeo 1

M3 1

A4 2

A3 4

My code:

$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

$modelArray = [];

foreach ($rows as $row) {

    $modelArray[] = $row['model'];

}

$result = array_count_values($modelArray);

foreach ($result as $model=>$qty) {

    echo $model." ".$qty;

}

I don't know how to also include the 'make' column in my results using the array_count_values() function.

I think I may be going about this the wrong way as it would appear to be a fairly common task but I can't find any information on how to do it like this.

Any help would be greatly appreciated thanks.

Do you select the columns that you want in your SQL? As it seems to me you're just selection the 'model' field thous you can only display the 'model' field. — KhorneHoly
– KhorneHoly, Commented Sep 20, 2017 at 9:53
The first line of my question states I am using select * from my table. So yes the Make column is included. — Coffee bean
– Coffee bean, Commented Sep 20, 2017 at 10:22

Viki Theolorado · Accepted Answer · 2017-09-20 10:02:52Z

1

I guest what you are trying to do is a GROUP BY statement. Here
I guest your current query is SELECT make, model FROM table.
Then you count repeating data using array_count_values. I think you need to change the query using GROUP BY statement like this.

SELECT make, model, COUNT(1) AS qty FROM table GROUP BY make, model

Using that query, you can echo the result using this code.

foreach ($rows as $row) {

    echo $row['make'] . " " . $row['model'] . " " . $row['qty'];

}

answered Sep 20, 2017 at 10:02

Viki Theolorado

5865 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Coffee bean Over a year ago

I thought there must be a simpler way to it and this is it. Thank you this is perfect. Tested and working.

AmRit GhiMire · Accepted Answer · 2017-09-20 09:59:22Z

1

Try this

<?php 
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $modelArray = [];
    $makes = []
    foreach ($rows as $row) {

        $modelArray[] = $row['model'];
        $makes[$row['model']]=$row['make'];

    }

    $result = array_count_values($modelArray);

    foreach ($result as $model=>$qty) {

        echo $makes[$model]." ".$model." ".$qty;

    }
?>

answered Sep 20, 2017 at 9:59

AmRit GhiMire

431 silver badge7 bronze badges

2 Comments

B001ᛦ Over a year ago

Why should the OP "try this"? A good answer will always have an explanation of what was done and why it was done in such a manner, not only for the OP but for future visitors to SO.

Coffee bean Over a year ago

Thanks, this did work but the accepted answer was better because of the explanation and changing the database query was the better approach.

Collectives™ on Stack Overflow

How can I count array values from mysql query in PHP and associate a second column with the count results

Desired output:

Current output:

My code:

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Desired output:

Current output:

My code:

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related