Unanswered: How do I write a query that joins/searches 90 small tables?
I'm creating an electronic calendar customers can use to schedule appointments by clicking a button with their mouse.
I wrote php code that uses a for loop to create the days of one calendar month. I wrote a separate for loop that creates one mysql table per day of that same month. Each table represents one day of the month, and holds scheduled appointments for that day.
A registration table holds data customers send to it by submitting the registration form. The customer user id is the primary key for the registration table.
The appointment time is the primary key for the daily appointment tables. The user id is the foreign key for these tables.
I want to run one query that searches these tables for appointments that have been scheduled (inserted) into these tables. This query will be run on customer login.
Searching just one month worth of daily tables requires a query that can join 31 tables for a search of January appointments (for example), or even 90+ tables for a search of Jan, Feb and March tables.
I've been reading up on queries, and most of what I read explains how to join two or three tables. How do I write a query that joins 31 or 9o+ tables?