CODEWITHSUNDEEP
phpmysqlwordpress
Nitin Johnson
Nitin Johnson

Reputation: 340

Want to sort the records according to day of week?

I have a field name session_dayofweek in the database which stores day name like for example 'Mon'. I want to get the records sorted by the session_dayofweek. The records should be sorted like Sun, Mon, Tue etc. starting from sunday. I am running the query on wordpress database.

Here is my query:

$wpdb->pre_assignments = $wpdb->prefix . 'tc_pre_assignments';
$get_session_name=$wpdb->get_results("SELECT $wpdb->pre_assignments.*,$wpdb->sessions.* FROM $wpdb->pre_assignments JOIN $wpdb->sessions on $wpdb->pre_assignments.session_id = $wpdb->sessions.session_id WHERE $wpdb->sessions.session_id='".$session_id."'");

Please help me. Thanks in advance.

Upvotes: 2

Views: 56

Answers (1)

John Ruddell
John Ruddell

Reputation: 25842

you can use a conditional order by

ORDER BY CASE
   WHEN session_dayofweek = 'Sun' THEN 1
   WHEN session_dayofweek = 'Mon' THEN 2
   WHEN session_dayofweek = 'Tue' THEN 3
   WHEN session_dayofweek = 'Wed' THEN 4
   WHEN session_dayofweek = 'Thu' THEN 5
   WHEN session_dayofweek = 'Fri' THEN 6
   WHEN session_dayofweek = 'Sat' THEN 7
   ELSE 8
END

if you are looking for a pattern sort (aka sun, mon, tue, wed, thu, fri, sat, sun, mon, tue.. etc) you could do something like this

ORDER BY assignment_id, FIELD(session_dayofweek,'Sun','Mon','Tue','Wed','Thu','Fri','Sat')

sample fiddle

Upvotes: 1

Related Questions