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

Nevermind, I'm going with merging the 3 tables into 1. it seems the most practical, hopefully it's the right choice!

Did you look into joins?

Hi,

Aren't joins only useful for data that's related?

They have NOTHING in common?
I guess I just don't understand your application, sorry.

hi
you can also take a look at relational database design
and the use of foreign and primary keys this can offer some help

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.