CODEWITHSUNDEEP
phpmysqlsorting
newComer
newComer

Reputation: 1

Sorting in PHP and mySQL

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:

  1. Since I only use the report which is the current year(2012), first check if it is the current year;

  2. Check whether it's the period over 3, if yes,continue;

  3. 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;

  4. 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

Upvotes: 0

Views: 321

Answers (3)

Eugene H
Eugene H

Reputation: 425

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

Upvotes: 0

sel
sel

Reputation: 4957

$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)
");

Upvotes: 1

juergen d
juergen d

Reputation: 204766

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

Upvotes: 1

Related Questions