I am working on a Office Management System in php and I want to create two appointment datatables. One will show previous appointments in descending order and other will show upcoming appointments in ascending order of date and time. Now in my MySQL Database I have date and time as different parameters. Now should I fetch all entries from database and filter using php and show them in different datatables or should I fetch entries using a filtered query and then show them in different datatables. I have tried these filtered queries but these are not working:
For Upcoming
$sql = "SELECT * FROM p_appointment WHERE UNIX_TIMESTAMP(STR_TO_DATE(CONCAT(date,' ',time), '%Y-%m-%d %H:%i:%s')) >= UNIX_TIMESTAMP(now()) ORDER BY date ASC
For Previous
$sql = "SELECT * FROM p_appointment WHERE UNIX_TIMESTAMP(STR_TO_DATE(CONCAT(date,' ',time), '%Y-%m-%d %H:%i:%s')) < UNIX_TIMESTAMP(now()) ORDER BY date DESC
date is being stored in format 28/07/2021 and time as 2:25 PM Any solution using php or MySQL will be helpful.
You can get cuttent_date() and current_time() like this:
$sql = "SELECT * FROM p_appointment WHERE date >= current_date() AND time >= current_time() ORDER BY date,time ASC
Also you shouldn't fetch all data from database if you have a lot of records (>100). It is a good practice to use LIMIT to get only part of records and perform pagination for records.