Want to sort the records according to day of week?

1 Views

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:

Read More
$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.

Post Views: 1

Related posts

1 comment

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

Comments are closed.