Grouping MySQL output

[ocon]

I am having the most difficult time outputting information from my database in the format I need.

My database contains many rows with this information:

Language, Speak, Read, Write
French, 0, 3, 8
French, 2, 2, 8
French, 3, 2, 9
French, 0, 4, 4
Spanish, 7, 3, 8
Spanish, 0, 0, 1
...

Basically, the Language row contains the name of the language, with various values for different language skills on a scale between 0 and 10.

I'm trying to generate a report with grouped skills using a scale:

If the value is 0, I need that to equal NONE
If the value is between 1 and 4, I need that to equal GOOD
If the value is between 5 and 7, I need that to equal PROFICIENT
If the value is between 8 and 10, I need that to equal FLUENT

The output I'm looking for is in this format:

Language/SPEAK/READ/WRITE/COUNT
French/NONE/GOOD/FLUENT/1
French/GOOD/GOOD/FLUENT/2
French/NONE/GOOD/GOOD/1
Spanish/PROFICIENT/GOOD/FLUENT/1
Spanish/NONE/NONE/GOOD/1

I'm starting off with:

$result=mysql_query("SELECT *, COUNT(*) AS count FROM database GROUP BY language");

But beyond that I don't know. I can group them at the beginning, but basically I need to group ranges of numbers, not the actual numbers. If I convert the numbers after I select them using PHP, then I don't know what to do with it.

Any help is appreciated. Thanks.

[Demaestro]

You want to use a case/switch statement.

It looks like something this

SELECT
Language,

CASE @Speak
WHEN 0 THEN 'None'
WHEN < 5 THEN 'Good'
WHEN < 8 THEN 'Proficient'
WHEN < 11 THEN 'Fluent'
ELSE 'None'
END as speak,

CASE @Read
WHEN 0 THEN 'None'
WHEN < 5 THEN 'Good'
WHEN < 8 THEN 'Proficient'
WHEN < 11 THEN 'Fluent'
ELSE 'None'
END as read,

CASE @Write
WHEN 0 THEN 'None'
WHEN < 5 THEN 'Good'
WHEN < 8 THEN 'Proficient'
WHEN < 11 THEN 'Fluent'
ELSE 'None'
END as write

FROM
database

It has been a while so my syntax may be off, but if you find a guide for the database you are using and do a search for 'case' or 'switch' examples that is what you want to be using.

[brotherhood of LAN]

Something similar to Damaestro's post, within a query:

SELECT
`language`,
IF(`speak` = 0,'NONE',IF(`speak` < 5,'GOOD',IF(`speak` < 8,'PROFICIENT','FLUENT'))) AS speakgrade,
IF(`read` = 0,'NONE',IF(`read` < 5,'GOOD',IF(`read` < 8,'PROFICIENT','FLUENT'))) AS readgrade,
IF(`write` = 0,'NONE',IF(`write` < 5,'GOOD',IF(`write` < 8,'PROFICIENT','FLUENT'))) AS writegrade,
COUNT(*)
FROM database
GROUP BY language,speakgrade,readgrade,writegrade

The IF statements evaluate the number and output text according to whatever range the number falls in. I've grouped by language and the results of those evaluations.

[Demaestro]

Wouldn't a case statement be more efficient? Mine will work within a query. The reason I think a case is more efficient is once it finds the right value it stops evaluating, whereas in an if statement, it will iterate each row and perform all evals on them.

I could be wrong. My optimizing methods are dated.

[brotherhood of LAN]

>more efficient

It could possibly be, I've never tested. I've got used to using IFs for that kind of output.

If there's nothing out there on the subject it's something worth testing.

[ocon]

WOW!

Thank you both. I've been struggling with this all day. I was outputting the information into an array, and then trying to manipulate the array, struggling to get anything, including my bloating code, to work.

The script above worked perfectly.

Thank you