Hey,
I've come to a bit of a problem - I'm updating a website for users to upload their own games, animations ("toons") and artwork. The trouble is, I've made separate tables for each one, each with very similar structure
What I want to do is show, say, the first 10 entries of ANY media type (so, entries spread across all 3 tables) - it would show the latest 10 entries, regardless of whether they were games, toons or art, and sorted in the order that they were created.
Each table has the primary key "id" (auto-increment), but I can't find any way to put these tables together (ordered by the datetime each row was created, to avoid id conflicts) and take the first 10 items.
This would have been easier if I created them as one table.
I'm aware that this is a tricky task, tho, and there is another option:
I could merge these 3 tables together in one go with a PHP script, clear all the data associated with them in other tables (this would include users' favourites list and comments), then edit the scripts which use these tables to only reference to 1 main table. The reason this is an option is because all 3 tables already have a lot of data inside them, and I don't want to lose it as it would cause major inconvenience for the users. It would also mean sorting them like mentionned before would be a lot easier
So, is there any way to accomplish this? Preferably I'd like to keep the tables how they are because it would get very complicated otherwise, editing existing pages. I've tried UNION but it just tags the 2nd table after the 1st, and the 3rd after the 2nd, so you can't sort them all together by their datetime column.
Thanks for your time!
~Chris