I've got a database-related problem: I'm currently writing a guest list for events. I've got two tables: One for all the accounts there are (including names, addresses etc.) and one for each of the events (including date of signup, status of payment etc.). The only connection they have is a userid field, so each entry in the event list corresponds with an entry in the accounts list.
The list I'm writing offers the option to sort entries by name, city, status etc. The problem is that those fields come from both tables, so if I want a list ordered by name and then by status I'm running into problems.
What I'm currently doing is creating a temporary table, then ordering the first table by the selected criteria, writing those entries into the temp table along with the corresponding fields from the other table and then sorting the newly created table by the other criterias.
And after the values are output the temp table gets deleted again.
Is there a simpler way to do this?
I'm worried that the process will take much more time once there 500 guests or so on the list. I've also tried to implement some sort of caching mechanism so that the temp table only gets recreated when entries in one of the two tables change, but detecting that change is difficult. I've tried to use "show table status", but the "last update" entry doesn't reflect the real table changes I suppose.
Thanks for reading my long post
The list I'm writing offers the option to sort entries by name, city, status etc. The problem is that those fields come from both tables, so if I want a list ordered by name and then by status I'm running into problems.
What I'm currently doing is creating a temporary table, then ordering the first table by the selected criteria, writing those entries into the temp table along with the corresponding fields from the other table and then sorting the newly created table by the other criterias.
And after the values are output the temp table gets deleted again.
Is there a simpler way to do this?
I'm worried that the process will take much more time once there 500 guests or so on the list. I've also tried to implement some sort of caching mechanism so that the temp table only gets recreated when entries in one of the two tables change, but detecting that change is difficult. I've tried to use "show table status", but the "last update" entry doesn't reflect the real table changes I suppose.
Thanks for reading my long post