[RESOLVED] sorting query results from form variable plus a twist

[lala]

Hello -

I have a form that allows the user to select a sorting option. One of the options is a day of the week. (The table is a list of courses and their times/locations/days.)

I can get the results to sort by user option, but how do I make the days sort chronological rather than alpha if this is the option selected?

This is my code:

$query = "SELECT * FROM classes ORDER BY '$schedule'";

The user selects which way to display the data through the $schedule variable.

Any have an idea?

Thanks!

[nemonoman]

How are you storing the times/days information?

[lala]

The information for all the classes is in a database.

Here's the page in question:

http://www.musictogethernewton.com/scheduletest.php

Thanks!

[nemonoman]

You are storing the day names as text strings. This makes sorting in M,T,W,T,F difficult.

You have on the page some sort of pulldown menu that probably changes a part of your SQL. I'm going to assume that column name for that table is days -- so -- Probably the code looks something like

if($days){
   $sortstr="ORDER BY `days`";
}

What you will need is a smater sorting algorithm. Instead of
ORDER BY days

use:

ORDER BY CASE
WHEN days LIKE 'M%' THEN 1
WHEN days LIKE 'Tu%' THEN 2
WHEN days LIKE 'W%' THEN 3
WHEN days LIKE 'Th%' THEN 4
WHEN days LIKE 'F%' THEN 5
END

[lala]

I've tried what you suggested but I'm still not getting it. The pulldown menu allows the site visitor to select how they want the data sorted, by location, time or day of the week.

The select variable is called $schedule. So the form looks like this:

Show classes by:
<select name="schedule">
<option value="">Please Select One</option>
<option value="location">Location</option>
<option value="day">Day</option>
<option value="time">Time</option>
</select>

And the query looks like this:

$query = "SELECT * FROM classes ORDER BY '$schedule'";

For all other instances, the data displays correctly. But when you select to sort the data by the day column, it sorts alpha.

Would the if/sortstr statement work in this case? If so, where would I need to put this in the code to make it work with the query statement?

Any other help you can give would be greatly appreciated. I am learning a lot, but this is beyond my scope to date.

Thanks!

[Sxooter]

There's a mysql function that can pull the day of the week out of a date (it's extract or date_part in postgresql) use that in the order by. something like (but not exactly)

order by extract('dow' from datefield);

[nemonoman]

The function described won't work because the data isn't being stored as a date varibable.

Instead of:
$query = "SELECT FROM classes ORDER BY '$schedule'";

if($schedule=='day'){
    $query = "SELECT * FROM classes 
                    ORDER BY CASE 
                    WHEN `day` LIKE 'M%' THEN 1
                    WHEN `day` LIKE 'Tu%' THEN 2
                    WHEN `day` LIKE 'W%' THEN 3
                    WHEN `day` LIKE 'Th%' THEN 4
                    WHEN `day` LIKE 'F%' THEN 5
                    END
}
else{
     $query = "SELECT * FROM classes ORDER BY '$schedule'"; 
}

[lala]

That did it! Thanks so much.