select from 10 similar tables

nappel

hey,
here's my problem:
i have 10 similar tables. each of these contains following fields:
ID,title,description,imagepath,created.
created means the date (timestamp).
now i want to select from these 10 tables the youngest 5 entries. but what's the right code for it??
this does not work:

("SELECT table4.ID,
table6.ID,
table4.title,
table6.title,
table4.description,
table6.description,
table4.imagepath,
table6.imagepath
FROM $table4 as table4,$table6 as table6
ORDER BY
table4.created,table6.created DESC limit 0,5")

here i tried with only two tables...
any ideas??

thanx for help!

bad76

Hi,
if you have mysql 4.0.0 or more try to look UNION.

Else you can post the first five from each table in an array, then sort it, and get the first five from array...

see you

OhLordy

Why are they in different tables? If they are that similar surely they could all be in the same table with one extra attribute to denote which type they are. With the right sort of indexing this structure would be quite a bit quicker. It would also make this particular query a lot easier.
Selecting the timestamp on it's own will give you an ambiguos column error. What exactly are your tables being used for. There may be a much tidier way of structuring your database.
Cheers
Rob

nappel

i cannot change database-structure - my customer wants to have records in different tables...
all this stuff is for a site with free-software, the tables are:
freegames, sharegames, screensavers and so on... they all have a few fields (ID,title,description,created) but also many different fields so it was not so senseful to post records in one table.

UNION - i never heard, still using mysql 3.2. do you mean a temporary table? does it work under this version? please give me a short script snippet using UNION!

thanx a lot!

OhLordy

can you create an extra table? You could have a very light lookup table which will just have id and timestamp in for every product. Then this could be linked to all the tables.
Rob

superwormy

Other options might be to just run 10 queries, one for each table.

OR keep a seperate table that gets updated once a day or something, with just the records:

ID, table_this_record_was_from, ID_of_record_in_real_table, title, description, created

This table gets reloaded every day with fresh data from each of the other tables. Then use that table for lookups when the search or wahtever, and the real tables when they're actually viewing the details stuff. If that made sense...

bad76

Originally posted by nappel
UNION - i never heard, still using mysql 3.2. do you mean a temporary table? does it work under this version? please give me a short script snippet using UNION!

I mean this, but it work only from mysql 4.0.0 ... but at the bottom there is this usefull link http://jinxidoru.com/tutorials/union.html

nappel

ok,
the solution with UNION (mysql 4.0) sounds well so i'll try it...
if this doesn't work i can create an extra table for the date each record was created and refer the youngest 5 entries from there.
hope i get it ... ;-)

thanx for your help!

Sxooter

Originally posted by nappel
i cannot change database-structure - my customer wants to have records in different tables...
all this stuff is for a site with free-software, the tables are:
freegames, sharegames, screensavers and so on... they all have a few fields (ID,title,description,created) but also many different fields so it was not so senseful to post records in one table.

UNION - i never heard, still using mysql 3.2. do you mean a temporary table? does it work under this version? please give me a short script snippet using UNION!

thanx a lot!

You need to make is clear to your customer that it's not their job to define the tables you're going to use, but only the data and how it is to be treated (integrity wise). If they insist on having ten tables, then tell them why this limits the design and implementation of the database and why it's better to use one table with a seperate table to define type. i.e. they're screwing up the normalization of the data because they don't understand database theory and it's your job to explain the mistake they're making and talk them out of it if possible.

nappel

yes, you're absolutely right!
but in this case it's a little more complicated and i can't explain it in english...

Sxooter

Oh, I know what you're talking about. I have those kinds of discussions all the time with folks 🙂 Sometimes it helps to point out how difficult they're making your life, hence costing them more money to change / upgrade their site in the future etc...

I.e. nothing's free, and their insistence on defining the parameters of the system in ways that they shouldn't be defining them will cost them money.

Then again, it's just more money / job security in your pocket, so if that's what they really want...