Sorting in PHP and mySQL

Question

Everyone: I need some help in my current project which is using PHP and MYSQL. I have a database that includes a lot of user information.

For example:

report year=2012

Name:Chan Tai Man

Age:20

School: Science school

school period:2011-2015

Chinese:1

English:2

overall grading:B

The thing I want to do is sorting by several priority for:

Since I only use the report which is the current year(2012), first check if it is the current year;
Check whether it's the period over 3, if yes,continue;
Then sorting ASC for the overall grading; --->if the overall grading is the same,then sort with the English; --->if the English is also the same,then sort with the Chinese;
Show the table with all information after sorting by the above priority;

Now,I can show the table of information with the current year and check whether the period is above 3 but I am confuse how can I compare the data from the previous sorting but not from the table?

eg.

$sorting_year=mysql_query("SELECT * FROM $tbl_name where year = $current_year");

( want to continue sort the data which conform the above condition ....)

Thank you~~

Thank you for everyone, it can sort the data which conform the above conditions.

However, the remaining data which did not conform the above conditions,I also want to show them in the table but put all these data in the last of the table and group by the school.How can I select them and sort to be the last?

example: (Lastly all the data will be shown on the table)

1.John period>3 B A A 2011 //since second A is higher than B

2.Betty period>3 B B A 2011 //period>3 and 2011

3.Louis (period<3) A A A 2011 //period<3 but is 2011

4.Mary period>3 A A A (2010) //2010=[ although period>3

check whether is the period over 3: school period is a single field that contains a string that has to be evaluated? So the query SELECT `school period` from Foo would return "2011-2015" for your example record? — VolkerK
– VolkerK, Commented Aug 1, 2012 at 8:12
Thank you for everyone, it can sort the data which conform the above conditions. However, the remaining data which did not conform the above conditions,I also want to show them in the table but put all these data in the last of the table and group by the school.How can I select them and sort to be the last? — newComer
– newComer, Commented Aug 1, 2012 at 9:15

juergen d · Accepted Answer · 2012-08-01 07:07:59Z

1

SELECT * FROM $tbl_name 
where year = $current_year
and period > 3
order by `overall grading` asc, english asc, chinese asc

answered Aug 1, 2012 at 7:07

juergen d

205k40 gold badges303 silver badges377 bronze badges

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

1 Comment

newComer Over a year ago

May I know how can I choose the remaining data then order them in the last of the table and group by the school?

sel · Accepted Answer · 2012-08-02 03:14:08Z

1

$sorting_year=mysql_query("SELECT * FROM $tbl_name where year = $current_year ORDER BY `overall grading`,`English`,`Chinese`");

For the add on question:

$sorting_year=mysql_query("
       (SELECT * FROM $tbl_name where year = $current_year 
        ORDER BY `overall grading`,`English`,`Chinese`) 
       UNION
       (SELECT * FROM  $tbl_name where year <> $current_year
        GROUP BY school)
");

edited Aug 2, 2012 at 3:14

answered Aug 1, 2012 at 7:07

sel

4,9421 gold badge18 silver badges23 bronze badges

5 Comments

newComer Over a year ago

May I know how can I choose the remaining data then order them in the last of the table and group by the school?

sel Over a year ago

what do you meant by choose the remaining data?

sel Over a year ago

If the remaining data you meant is those data not equal to current_year. i have amended the answer.

newComer Over a year ago

Sorry for confusing.The remaining data refers to those data which also equal to current year but not is ranged in the period.Since the variable of the period is actually calculated from two other variables( period_start and period_end), it has to re-loop all data again to obtain the period

sel Over a year ago

USE UNION. one query with where year = $current_year and period > 3 and one with where year = $current_year and period <= 3

Eugene H · Accepted Answer · 2012-08-02 10:13:09Z

0

You can do 1,3 and 4 using sql and loop through the records using php and filter out those that does not support the condition 2.

This is the SQL statement:

SELECT * FROM table_name where report_year = $current_year order by overall_grading asc, English desc, Chinese desc

edited Aug 2, 2012 at 10:13

answered Aug 1, 2012 at 7:25

Eugene H

4253 silver badges7 bronze badges

7 Comments

John V. Over a year ago

How is this any different than the other two answers? And 2 can be done in MySQL.

Eugene H Over a year ago

I assumed that the period he mentioned above is a text string "2011-2015" so that can't be done in mysql. Am i wrong?

John V. Over a year ago

See @juergen d's answer, although now that you mention it you might be right. Also, I'd suggest you put the query in a code box (four spaces before the query).

newComer Over a year ago

May I know how can I choose the remaining data then order them in the last of the table and group by the school?

Eugene H Over a year ago

If you want to group by school, then it would make sense to show aggregated data. Do you need average age or something?

|

Collectives™ on Stack Overflow

Sorting in PHP and mySQL

3 Answers 3

1 Comment

5 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

5 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related