Sorting by the contents of a table

[rebuke]

I have a table which has several fields in, for simplicity I'll call them field1, field2, field3, field4. These fields contain grades

The grades can be any of the following:

A*
A
B
C
D
E
F
G
U

what I am trying to do is produce a list that shows this:

Field Name: Grade

I am using asort to sort them, however, it places A* after A, and I need to get it before as this is the order the grades go in. Does anybody have any idea how I can do this?

Thanks,
Alex Brett
[e-mail]

[Lars_Berg]

I did not understand what you meant by your first question, but the sorting can be accomplished using a case expression.

select * from t 
order by case when field1 = 'A*' then 'A'
                       when field1 = 'A' then 'A*'
                       else field1 end

Case expressions are not supported by all dbms. Depending on which dbms there may be similiar functionality.

[rebuke]

Thanks for your help so far,

It isn't getting MySQL to sort it, as MySQL just returns me one result for the specific person, in this format:

ID Number, Name, Subject1, Subject2, Subject3, Subject4 e.t.c.

What I want to do is sort the order I print them out to the browser, the code I am using is basically this (I've taken out the bits that have nothing to do with sorting them):

$result = mysql_query ("SELECT * FROM Results WHERE ID = '$id'");
$row = mysql_fetch_array($result);
asort ($row);
reset ($row);
while (list ($key, $val) = each ($row)) {
$sub = "";
if ($key == "Maths") { $sub = "Maths"; }
if ($key == "ELang") { $sub = "English Language"; }
if ($key == "ELit") { $sub = "English Literature"; }
if ($key == "French") { $sub = "French"; }
if ($key == "Spanish") { $sub = "Spanish"; }
if ($key == "German") { $sub = "German"; }
if ($key == "IT") { $sub = "I.T."; }
if ($key == "Hist") { $sub = "History"; }
if ($key == "Bio") { $sub = "Biology"; }
if ($key == "Phys") { $sub = "Physics"; }
if ($key == "Chem") { $sub = "Chemistry"; }
if ($key == "Geog") { $sub = "Geography"; }
if ($key == "DT") { $sub = "D.T."; }
if ($key == "Art") { $sub = "Art"; }
if ($key == "RS") { $sub = "R.S."; }
if ($key == "Latin") { $sub = "Latin"; }
if ($key == "Music") { $sub = "Music"; }
if ($key == "Other") { $sub = "Other"; }
if ($sub != "") {
if ($val != "") {
if ($val != "1") { print "<p><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"4\">$sub: <font color = \"#0000FF\">$val</font></font></p>"; } } }
}

This works, and produces me a list, e.g.:

History: A

Maths: A

French: A*

Geography: B

Music: D

What I want to do is find some way of sorting it so that the A grade for French goes before the A grades for History and Maths as A is better than A (This is for the UK GCSE system).

Thanks,
Alex Brett
[e-mail]

[Lars_Berg]

Your datamodel is not normalized. You should not have multiple subject columns, they should be in a separate table.

i.e.

student

id / primary key /
name

subject

id / primary key /
name

studentSubject

studentId / foreign key to student /
subjectId / foreign key to subject /
grade

Then if you join the tables you can use the order by clause in my previous post and there is no need to do any sorting in the PHP code.

[jasper]

A quick and dirty solution for the grades problem is to create a new table grade_definitions with (id, name) and fill it with ((1,a*),(2,a),(3,b)) etc. This ensures a correct ordering IF you don't have to change the grade ordering afterwards. Than add grades for students in a different table (say: id, course_id, student_id, grade_id), and make 'grade_id' a foreign key to 'id' in grade_definitions and sort on that grade_id-field.