What I am trying to achieve is that In my appointment system, I want to limit people from making more than 2 appointments without completing the already booked ones.
For that I have two tables 1: customer_bookings 2: appointments
The "customer_bookings" table has three columns appointmentid, customerid & status The appointments table has id, status, bookingstart, bookingend
In the first table we have appointmentid which is id in appointments table.
In order to achieve what I want, I figured out to run the following query to fetch the latest 5 appointments
SELECT * FROM `customer_bookings` WHERE `customerId`= $idd->id AND `status`= 1 ORDER BY ID DESC LIMIT 5
Now running another query to search for the appointmentid from the first query result and checking whether the bookingstart is greater than NOW()
SELECT * FROM `appointments` WHERE id = ?? bookingStart > NOW();
Here, how do I pass the appointmentid value from the first query and search for all the 5 results or how can I build a query to display results which has
appointmentid | customerid | bookingstart | status so that I can easily check whether bookingstart is greater than now() for the customerid
I am quite new to sql and I am not able to figure out how to do it. Any help is greatly appreciated.
There are a few other questions that answers how to join two queries however I have where clause in my queries because I am passing the customerid from php to run the query to display results of just that particular customer id. I am not able to use JOIN and UNION with multiple WHERE conditions.
