Welcome to WebmasterWorld Guest from 54.162.117.84

Forum Moderators: open

Message Too Old, No Replies

Trying to get rows from multiple tables

     
4:30 pm on Sep 24, 2008 (gmt 0)

Junior Member

5+ Year Member

joined:Feb 4, 2008
posts: 80
votes: 0


I was given some code a while ago to get all the rows between certain timestamps and then order them by timestamp:

$QueryHIST_QUOTES = "
SELECT TIMEDIFF(TIMESTAMP, $starttime) AS diff, t.*
FROM $currentTable t
WHERE TIMESTAMP BETWEEN $starttime AND $endtime
ORDER BY TIMESTAMP";

Originally I had the variable $currentTable = "PRICES_2008" but I now want to get rows from multiple tables, so I tried:

$currentTable = "PRICES_2003, PRICES_2004, PRICES_2005, PRICES_2006, PRICES_2007";

The thing is, that each table contains the same fields, and the exact same type of data, just for different years. I am beginning to think that this was not such a good idea, and that even if the tables end up being huge (the above five tables could possibly have 18.4 million rows and would grow over time) it is a lot more manageable to just have one.

What do you think? Can anyone help here?

Cheers
Ed

6:03 pm on Sept 24, 2008 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member 5+ Year Member

joined:Nov 11, 2007
posts:769
votes: 1


It depends on you application, amount of data, etc. but there be nothing to stop you from having a table for each year. You can do this using UNION. You can select from all 5 5ables individually and then UNION the 5 result sets together. For example, if you want to search across all 5 years worth of data to find rows in a time period you could do something like:

SELECT TIMEDIFF(TIMESTAMP, $starttime) AS diff, t.*
FROM PRICES_2003 t
WHERE TIMESTAMP BETWEEN $starttime AND $endtime
UNION
SELECT TIMEDIFF(TIMESTAMP, $starttime) AS diff, t.*
FROM PRICES_2004 t
WHERE TIMESTAMP BETWEEN $starttime AND $endtime
UNION
SELECT TIMEDIFF(TIMESTAMP, $starttime) AS diff, t.*
FROM PRICES_2005 t
WHERE TIMESTAMP BETWEEN $starttime AND $endtime
UNION
SELECT TIMEDIFF(TIMESTAMP, $starttime) AS diff, t.*
FROM PRICES_2006 t
WHERE TIMESTAMP BETWEEN $starttime AND $endtime
UNION
SELECT TIMEDIFF(TIMESTAMP, $starttime) AS diff, t.*
FROM PRICES_2007 t
WHERE TIMESTAMP BETWEEN $starttime AND $endtime

As long as each individual select that gets UNIONed returns the same number of columns with the same names and same datatypes, you should be good.

Sorry. Couldn't test this. I don't have MySQL on my desktop. But off the top of my head, if you want to do selects over multiple tables with the same schema, UNION is probably the way to go.

PS: Put it in a stored procedure and call the stored proc from your code. It's typically a lot easier to modify procs. Each year you'll just have to go add the next year to the proc. Unless you're really good and make the proc look for table names of the form PRICE_YYYY and dynamically query them all writing results of each to a single temp table and then select the final result set from the temp table to return to the caller. Just and idea.

9:22 am on Sept 25, 2008 (gmt 0)

Junior Member

5+ Year Member

joined:Feb 4, 2008
posts:80
votes: 0


hmmm
The logic makes sense but there are no rows returned when I run this:

"SELECT TIMEDIFF(TIMESTAMP, 1064444400) AS diff, t.* FROM PRICES_2007 t WHERE TIMESTAMP BETWEEN 1064444400 AND 1222332699 ORDER BY TIMESTAMP UNION SELECT TIMEDIFF(TIMESTAMP, 1064444400) AS diff, t.* FROM PRICES_2008 t WHERE TIMESTAMP BETWEEN 1064444400 AND 1222332699 ORDER BY TIMESTAMP";

Anyone know whats going wrong?

8:52 am on Sept 26, 2008 (gmt 0)

Junior Member

5+ Year Member

joined:Feb 4, 2008
posts: 80
votes: 0


Anyone?

To clarify: All the tables have the same number of columns with the same names and same datatypes.

3:50 pm on Sept 26, 2008 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5618
votes: 44


Do you get any error message? What if you just SELECT on one table- is there actually data?