CODEWITHSUNDEEP
php
user8846148
user8846148

Reputation:

PHP- show results in a specific order

I need my booking system to display bookings in order from the earliest time. So if someone booked an equipment from 12:00 to 2:00, then the next person to use the system books the same equipment on the same day from 10:00 to 11:00. Following this a third person books the same equipment on the same day from 11.00 to 12.00.

The booking system works fine, but displays it in the order of bookings. So with the above example, the starting times would show up as:

-12.00 -10.00 -11.00

What I would like to do is display the starting times in order of earliest. So the above example would be displayed as following:

-10.00 -11.00 -12.00

 $sqlEvent="select * from bookings where selected_date='".$month."/".$day."/".$year."'";
 $resultEvents=mysqli_query($dbconnection,$sqlEvent);
 echo "<br>";

 while($events=mysqli_fetch_array($resultEvents)){

 echo "<span class='one'><span class='oneagain'>Equipment:</span> ".htmlspecialchars($events['equipment'])."</span><br>"; 
 echo "<span class='two'><span class='twoagain'>Start Time:</span> ".htmlspecialchars($events['start_time'])."</span><br>";
 echo "<span class='three'><span class='threeagain'>End Time:</span> ".htmlspecialchars($events['end_time'])."</span><br>";

 }

}

I tried the following, but nothing worked:

$sqlEvent="select * from bookings ORDER BY start_time ASC where selected_date='".$month."/".$day."/".$year."'";

Upvotes: 0

Views: 42

Answers (1)

I&#39;m Joe Too
I&#39;m Joe Too

Reputation: 5860

You need to rethink the structure of your database. Your field selected_date should not be stored as a string like "MM/DD/YYYY", but should be stored in one of the native date formats like UNIX timestamp or DATETIME. Then, you can sort by that field properly and reformat it on the way in and out of the database. So something like:

"SELECT *, DATE_FORMAT(selected_date, '%m/%d/%Y') as formatted_date FROM BOOKINGS WHERE DATE(selected_date)='$year-$month-$day' ORDER BY selected_date ASC"

This would give you your ordered results and formatted_date as the nicely displayed date. You'll need to modify this to fit your actual code and structure, but this is the way to query dates if you need to do any date-related work (like sorting or comparing dates).

Upvotes: 1

Related Questions