CODEWITHSUNDEEP
phpmysql
Grogu
Grogu

Reputation: 2475

Selecting entries by date and by time seperately with MySQL

I have this query that selects every appointment with status Pending. Works well. The problem with this query it will also select appointments that are pending in the past. I only want to display those that are either today at a later hour than current_time or simply at a later date. Time and date are in a different column. In the example below only the second and third row should be returned. I'm giving you the full query as it is used and working in my app right now. How can this be achieved?

user_schedule table

id | customer_id | date            | time     | cleaning_status 
1  | 345         | 2020-06-09      | 08:00:00 | Pending
2  | 768         | 2020-06-09      | 19:00:00 | Pending
3  | 913         | 2020-06-11      | 07:00:00 | Pending

PHP

   if(!empty($_POST)){

    //variables
    $current_time ='16:00:00';
    $current_date ='2020-06-09';
    $my_city ='Miami';
    $sstatus_o = 'Pending';

    //query
    $data = $conn->prepare("select *,us.id as orderid,us.customer_id 
                            as ownerId from user_schedule us 
                            left join users u
                            on us.customer_id=u.id
                            LEFT JOIN user_avatar ua 
                            ON us.customer_id=ua.user_id 
                            and ua.last_update = (select 
                            max(last_update) 
                            from user_avatar ua1 where 
                            ua.user_id=ua1.user_id)
                            left join user_address uad
                            on us.customer_id=uad.user_id
                            where (uad.city LIKE ?) AND 
                            us.cleaning_status=? ORDER BY us.id DESC");
    $data->bind_param('ss',$my_city,$sstatus_o);
    $data->execute();
    $result_data = $data->get_result(); 

    }

Upvotes: 0

Views: 21

Answers (1)

Nick
Nick

Reputation: 147146

You can add another constraint to your query that checks whether the timestamp formed from your date and time values is greater than your $current_date and $current_time values i.e.

WHERE uad.city LIKE ?
  AND us.cleaning_status = ?
  AND TIMESTAMP(us.date, us.time) > TIMESTAMP(?, ?)

and then add the $current_date and $current_time variables to the bind_param i.e.

$data->bind_param('ssss', $my_city, $sstatus_o, $current_date, $current_time);

Upvotes: 1

Related Questions