homepage Welcome to WebmasterWorld Guest from 54.234.0.85
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Trying to get rows from multiple tables
edzillion

5+ Year Member



 
Msg#: 3751303 posted 4:30 pm on Sep 24, 2008 (gmt 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

 

ZydoSEO

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 3751303 posted 6:03 pm on Sep 24, 2008 (gmt 0)

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.

edzillion

5+ Year Member



 
Msg#: 3751303 posted 9:22 am on Sep 25, 2008 (gmt 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?

edzillion

5+ Year Member



 
Msg#: 3751303 posted 8:52 am on Sep 26, 2008 (gmt 0)

Anyone?

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

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 3751303 posted 3:50 pm on Sep 26, 2008 (gmt 0)

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved