Sorting by the day of the week

[Howlin1]

Hello,

I am wondering is there any way to sort a list by the days of the week (i.e. it should list Monday, Tuesday, Wednesday, Thursday and Friday)?

I am trying to make timetable of free rooms in a building and list the weekdays (Monday - Friday), Floors (Ground, first and second) and then the time (9 am until 4pm, but I have that solved by using the 24 hour time).

 

What I have at the moment is:

$result= mysql_query( "SELECT * FROM free_rooms ORDER BY Day, Floor, Time ASC") or die("SELECT Error: ".mysql_error());

        echo '<p>This is a list of all the rooms that are free and on what day.</p>
   <table border="1" width="400">
   <tr align="center">
   <td><b>Day</b></td>
   <td><b>Floor</b></td>
   <td><b>Room</b></td>
   <td><b>Time</b></td>
   </tr>';

   while ($get_info = mysql_fetch_row($result))
   {
        print '<tr align="center">
   <td><b>' . $get_info[1] . '</b></td>
   <td>' . $get_info[4] . '</td>
   <td>' . $get_info[2] . '</td>
   <td>' . $get_info[3] . ':15</td>
   </tr>';   
   }
   print '
        </table>';
   mysql_close($link);
        ?>

(The result of the above is Attachment 1)

 

(I'm assuming the get_info is a 2d array) So what I would need to do is to sort the get_info[1] array so Monday is first, Tuesday, Wednesday, Thursday and finally Friday. Then sort the floor (get_info[4]) by Ground, First and Second, while keeping Monday first.

 

On the order by clause I did try putting in ('Monday') for the day, but it only brought one or two of the Mondays to the top. I don't know enough about php (I'm a novice really) to do what I would like to do.

 

The other way I did think of doing it was like:

$result= mysql_query( "SELECT * FROM college_free_rooms") or die("SELECT Error: ".mysql_error());
$num_rows = mysql_num_rows($result);
        echo '
        <p>This is a list of all the rooms that are free and on what day.</p>

<table border="1" width="400">
<tr align="center">
<td><b>Day</b></td>
<td><b>Floor</b></td>
<td><b>Room</b></td>
<td><b>Time</b></td>
</tr>';

while ($get_info = mysql_fetch_row($result))
{
	if($get_info[1] == 'Monday')
	{
	print '
        <tr align="center">
	<td><b>' . $get_info[1] . '</b></td>
	<td>' . $get_info[4] . '</td>
	<td>' . $get_info[2] . '</td>
	<td>' . $get_info[3] . ':15</td>
	</tr>';
	}

	if($get_info[1] == 'Tuesday')
	{
	print '
        <tr align="center">
	<td><b>' . $get_info[1] . '</b></td>
	<td>' . $get_info[4] . '</td>
	<td>' . $get_info[2] . '</td>
	<td>' . $get_info[3] . ':15</td>
	</tr>';
	}

	if($get_info[1] == 'Wednesday')
	{
	print '
        <tr align="center">
	<td><b>' . $get_info[1] . '</b></td>
	<td>' . $get_info[4] . '</td>
	<td>' . $get_info[2] . '</td>
	<td>' . $get_info[3] . ':15</td>
	</tr>';
	}

	if($get_info[1] == 'Thursday')
	{
	print '
        <tr align="center">
	<td><b>' . $get_info[1] . '</b></td>
	<td>' . $get_info[4] . '</td>
	<td>' . $get_info[2] . '</td>
	<td>' . $get_info[3] . ':15</td>
	</tr>';
	}

	if($get_info[1] == 'Friday')
	{
	print '
        <tr align="center">
	<td><b>' . $get_info[1] . '</b></td>
	<td>' . $get_info[4] . '</td>
	<td>' . $get_info[2] . '</td>
	<td>' . $get_info[3] . ':15</td>
	</tr>';
	}
        }
print '
        </table>';
mysql_close($link);
        ?>

But that didn't work either, but I did it on the assumption since I wasn't specifically telling the sql statement to order the list in any way, that the result would print out all the Monday entries first, then the Tuesday entries etc, but it didn't work that way (attachment 2).

 

Is there anyway that what I want done, can be done (wholly or partially)?

 

[attachment deleted by admin]

[void]

If there was a proper "date" or "datetime" field in your table (instead of just varchar "Monday"), then I would use DATE_FORMAT to get a 1-7 integer representing the day of the week.

 

If there isn't one, FIND_IN_SET would probably sort this out, something like ORDER BY FIND_IN_SET(Day, 'Monday,Tuesday,Wednesday.........') ASC

[Howlin1]

If there was a proper "date" or "datetime" field in your table (instead of just varchar "Monday"), then I would use DATE_FORMAT to get a 1-7 integer representing the day of the week.

I'm not quiet sure how that would help?

 

If there isn't one, FIND_IN_SET would probably sort this out, something like ORDER BY FIND_IN_SET(Day, 'Monday,Tuesday,Wednesday.........') ASC

I tried that, but it didn't help, I tried using ASC, DESC and I even left it out asc or desc and it didn't change anything. I tried using different days, but nothing?

[PFMaBiSmAd]

I tried that, but it didn't help

 

You would need to post the actual query you tried and what result you got.

[xyph]

Use an ENUM column for day of the week. 1-Monday,2-Tuesday,3-Wednesday etc.

 

Then you can simply sort/group by the column.

[Howlin1]

@PFMaBiSmAd Sorry.

I tried

$result= mysql_query( "SELECT * FROM college_free_rooms ORDER BY FIND_IN_SET('Monday, Tuesday, Wednesday, Thursday, Friday', Day) ASC ") or die("SELECT Error: ".mysql_error());

and got attachment 1.

I tried

$result= mysql_query( "SELECT * FROM college_free_rooms ORDER BY FIND_IN_SET('Monday, Tuesday, Wednesday, Thursday, Friday', Day) DESC ") or die("SELECT Error: ".mysql_error());

and I got attachment 2.

 

I tried

$result= mysql_query( "SELECT * FROM college_free_rooms ORDER BY FIND_IN_SET('Monday, Tuesday, Wednesday, Thursday, Friday', Day) DESC ") or die("SELECT Error: ".mysql_error());

and I got attachment 3.

 

I tried

$result= mysql_query( "SELECT * FROM college_free_rooms ORDER BY FIND_IN_SET('Monday', Day) ") or die("SELECT Error: ".mysql_error());

and I got attachment 4.

 

 

EDIT:

Use an ENUM column for day of the week. 1-Monday,2-Tuesday,3-Wednesday etc.

 

Then you can simply sort/group by the column.

What are they? I haven't come across them.

 

[attachment deleted by admin]

[xyph]

http://dev.mysql.com/doc/refman/5.0/en/enum.html

 

Sample table dump

CREATE TABLE IF NOT EXISTS `enum_sample` (
  `data` varchar(10) NOT NULL,
  `day` enum('Monday','Tuesday','Wednesday','Thursday','Friday') NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `enum_sample`
--

INSERT INTO `enum_sample` (`data`, `day`) VALUES
('foo', 'Monday'),
('bar', 'Thursday'),
('hello', 'Tuesday'),
('world', 'Monday'),
('baz', 'Tuesday'),
('mumble', 'Wednesday');

 

Sample query

mysql> SELECT * FROM `enum_sample` ORDER BY `day`;

 

Result

+--------+-----------+
| data   | day       |
+--------+-----------+
| foo    | Monday    |
| world  | Monday    |
| hello  | Tuesday   |
| baz    | Tuesday   |
| mumble | Wednesday |
| bar    | Thursday  |
+--------+-----------+
6 rows in set (0.00 sec)

[sasa]

FIND_IN_SET(Day, 'Monday,Tuesday,Wednesday,Thursday,Friday')

[xyph]

Or do it right in the first place and use a date column [ DATE_FORMAT( `date`, '%W' ) ] or use an ENUM column.

 

FIND_IN_SET() is slow, unless using a SET column type.

[Howlin1]

Thanks all who helped, I have gone with the ENUM method because it will allow me to do what I want.

I have attached a picture of the result. It is exactly what I wanted! Thanks again

 

[attachment deleted by admin]

[xyph]

Glad it helped, and glad you learned a new, useful feature of database engines.