Count Occurances of Different Enum Values in One Query

[casbboy]

Here is what I have:

$selectt = mysqli_query($dbh,"SELECT status,COUNT(*) as totalcount
    FROM mod_ff
    WHERE mod_id=$modid
    GROUP BY status
    ORDER BY status ASC");

Now, the possible ENUM options are enum(‘d’, ‘s’, ‘a’).

It looks like the ORDER BY creates the order they are listed, instead of a, d, s status ASC results in d, s, a.

This always the case? Is there a better way to count occurrences of ENUM values?

I follow through with…

while($f[] = mysqli_fetch_array($selectt));
In Progress: <b><?=$f[0]['totalcount']?></b><br>
  Submitted: <b><?=$f[1]['totalcount']?></b><br>
  Approved: <b><?=$f[2]['totalcount']?></b>

Cheers!
Ryan

[r937]

indeed, in order by their actual values (0,1,2,3…)

best advice i can offer is never use the dreaded, evil ENUM (i’ve been saying this here in sitepoint for almost 10 years)

here’s an article – http://komlenic.com/244/8-reasons-why-mysqls-enum-data-type-is-evil/

any chance you can change the table?

[casbboy]

Interesting. I use enum quite a bit. So the other option as a substitution to ENUM is TINYINT?

I can definitely update the table I’m discussing, as it has zero rows. But I have some tables with 150,000 rows with 2 option to 20 option enum columns.

Hmmmmmm

Cheers
Ryan

[r937]

@casbboy

So the other option as a substitution to ENUM is TINYINT?

that’s only part of the story… the true implementation is a foreign key to a separate table

[code]CREATE TABLE statuses
( status TINYINT NOT NULL PRIMARY KEY
, statusname VARCHAR(99)
);
INSERT INTO statuses VALUES
( 0, ‘In Progress’ )
,( 1, ‘Submitted’ )
,( 2, ‘Approved’ )
;

CREATE TABLE mod_ff
( …
, mid_id INTEGER
, status TINYINT
, …
, CONSTRAINT validstatus
FOREIGN KEY ( status )
REFERENCES statuses ( status )
);

SELECT s.statusname
, COUNT(*) AS totalcount
FROM mod_ff
INNER
JOIN statuses AS s
ON s.status = mod_ff.status
WHERE mod_ff.mod_id = $modid
GROUP
BY s.statusname
ORDER
BY s.statusname ASC
[/code]

the FK ensures that you will never have an invalid status in the mod_ff table