hi all..
This gonna be long and complicated & not for newbies... you've been warned...
How would you solve such problem?
Imagine you have 3 sites/databases:
- alpha.site.com
- beta.site.com
- gamma.site.com
The sites have news, articles and stuff. Let's say like Slashdot.
On site alpha last news were added 3 days ago, on site beta 2 days ago and on site gamma today.
I would like to build on every site HOT TOPICS - some table where all the latest news topics are listed and you can click between sites.
The news table could look like that:
id int(10)
date timestamp(14)
title varchar(255)
body text
Now, to select _in_one_query_ all news out of all databases you could write :
SELECT * FROM db_aplha.news, db_beta.news, db_gamma.news;
thats simple!
but this is not good way, especially if you have 10.000 news in one db, and you just wanna get latest news topics of given db - this is what I'm trying to do...
normally to get all news from the last day you could write:
$last_news = SELECT max(date) from news
SELECT title FROM news WHERE DATE_FORMAT(date, '%Y%m%d') = DATE_FORMAT('".$last_news."', '%Y%m%d')
but how to combined it into one query?
because for every site the $last_news variable or max(date) could be different...
I wanna do it in _one_query_ because I dont have 3 databases/sites but 14.. and more to come.... and doing this in loop - 2 queries for every db really makes mysql scream...
any help would be appr.
(Please, check the 'email replies' box.)
TIA
aLEczapKA
------------------
#/etc/hosts.allow
ALL:ALL EXCEPT .microsoft.com
This gonna be long and complicated & not for newbies... you've been warned...
How would you solve such problem?
Imagine you have 3 sites/databases:
- alpha.site.com
- beta.site.com
- gamma.site.com
The sites have news, articles and stuff. Let's say like Slashdot.
On site alpha last news were added 3 days ago, on site beta 2 days ago and on site gamma today.
I would like to build on every site HOT TOPICS - some table where all the latest news topics are listed and you can click between sites.
The news table could look like that:
id int(10)
date timestamp(14)
title varchar(255)
body text
Now, to select _in_one_query_ all news out of all databases you could write :
SELECT * FROM db_aplha.news, db_beta.news, db_gamma.news;
thats simple!
but this is not good way, especially if you have 10.000 news in one db, and you just wanna get latest news topics of given db - this is what I'm trying to do...
normally to get all news from the last day you could write:
$last_news = SELECT max(date) from news
SELECT title FROM news WHERE DATE_FORMAT(date, '%Y%m%d') = DATE_FORMAT('".$last_news."', '%Y%m%d')
but how to combined it into one query?
because for every site the $last_news variable or max(date) could be different...
I wanna do it in _one_query_ because I dont have 3 databases/sites but 14.. and more to come.... and doing this in loop - 2 queries for every db really makes mysql scream...
any help would be appr.
(Please, check the 'email replies' box.)
TIA
aLEczapKA
------------------
#/etc/hosts.allow
ALL:ALL EXCEPT .microsoft.com