I have about 40 tables, all with the same fields but different data in it.
I have another table (Table_Names) which holds the table names of these 40 tables.

I want to be able to select the last record from each table that I select from Table_Names so that I get one lot of results I can look at.

Is that possible?
Does that make sense?
Ta

That's a very poorly designed database! You need to do some reading on Database Normalization.

http://dev.mysql.com/doc/refman/5.1...
http://dev.mysql.com/doc/refman/5.1...
http://dev.mysql.com/doc/refman/5.1...

http://dev.mysql.com/doc/refman/5.1...

Hi FishMonger,
Thanks for the links.

I have to admit I am not the most experienced with databases and have only recently started PHP web development linked to MySQL databases due to a change in jobs.

I have developed a content management system for our websites so a user can edit a webpage without any web programming experience.

At the moment I have a table for ever page on the site (Page_<page name>) and insert a new record for every change the user makes. This means that we have a complete history of changes so we can go back if we need to.

I also have a table (Page_List) which holds the name, filename and table name of all the pages in the site.

I use this for the sitemap and to search the site.
The search function reads through the Page_List table and for each table name it finds (Page_<page name>) selects the last record from the from this page table.
If the search string exists I display it on the search page.

You say its poorly designed, can you explain why? Can you give me some idea how to improve it?

Thanks for your help

A database should be designed to have a set number of tables. Having to add a table simply because you added a page to the site means that the structure of the database is in a constant flux. What do you do when a page is no longer needed and is deleted; do you delete the table? Since each of those tables have the same fields, you now have unneeded and unwanted redundancy which means your database is not normalized.

Did you read the article at the link that I posted? You should do a google search and read up on on “Database Normalization”. You should also pick up a book on designing relational databases.

Your comment about having a history of changes that can be used to revert back to a prior version would be better handled by using CVS.