CODEWITHSUNDEEP
phpmysql
twigg
twigg

Reputation: 3993

get mysql COUNT in PHP

I'm running the a query to return all results where a serial number has been entered more than once as follows:

SELECT * FROM spin
            WHERE `serial` in (
            SELECT `serial` from spin group by `serial`
            HAVING count(*) > 1
            ) ORDER BY date ASC

    $query = $this->db->prepare($sql);
    $query->execute();
    return $query->fetchAll();

It works as expected but I want to filter this out to only show one of the values with its count of occurrences. I'm guessing it will need some sort of distinct adding to the query to get a unique value back but not sure how to implement this in the best way?

For anyone intrest this is how I'm returning the data (code shortened):

                        <thead>
                        <tr>
                            <th>Serial</th>
                            <th>Date</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($spins as $spin) { ?>
                            <tr>
                                <td><?php if (isset($spin->serial)) echo $spin->serial; ?></td>
                                <td><?php if (isset($spin->test_started)) echo $spin->date; ?></td>
                            </tr>
                        <?php } ?>
                    </tbody>

Upvotes: 0

Views: 103

Answers (3)

ymas
ymas

Reputation: 574

Here are my assumptions, you want to return a list of serials that are repeated in your spin table, however, you only to show the repeated serial once. With your current query, you are fetching all the records in your spin table with duplicate serials.

The query below will return all the serials that have a count > 1, however, it will only show each serial once.

SELECT `serial`, COUNT(`serial`) AS serial_count, `date`
FROM `spin`
GROUP BY `serial`
HAVING serial_count > 1
ORDER BY `date` ASC;

If you want to see all the dates, try this:

SELECT `serial`, COUNT(`serial`) AS serial_count, GROUP_CONCAT(`date`) AS dates
FROM `spin`
GROUP BY `serial`
HAVING serial_count > 1
ORDER BY `date` ASC;

You could add a LIMIT at the end if you want to restrict the number of records your query returns.

Upvotes: 0

server_kitten
server_kitten

Reputation: 386

To avoid nested select statements, use GROUP BY.

SELECT serial, COUNT(*) AS count FROM spin 
     GROUP BY serial HAVING count > 1 
     ORDER BY date ASC LIMIT 1;

Upvotes: 0

Barmar
Barmar

Reputation: 780769

This query will do it:

SELECT *, COUNT(*) AS count
FROM spin
GROUP BY serial
HAVING count > 1
LIMIT 1

Upvotes: 2

Related Questions