Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- I'm trying to work out an mysql issue, but not sure of how to do it.
- I have two databases from which I need to select certain fields.
- DB1.
- mysql> select * from users;
- +----+--------------------------------------+
- | id | uuid |
- +----+--------------------------------------+
- | 1 | 05c9fe42-8d89-401d-a9a5-2d82af58e16f |
- +----+--------------------------------------+
- DB2.
- mysql> select * from news;
- +----+---------------------+-------------------+
- | id | datum | news |
- +----+---------------------+-------------------+
- | 1 | 2010-04-14 11:36:21 | FIRST TEST NEWS! |
- +----+---------------------+-------------------+
- mysql> select * from read_news;
- +----+---------+--------------------------------------+------+
- | id | news_id | uuid | read |
- +----+---------+--------------------------------------+------+
- select
- n.FIELDS, rn.FIELDS, u.FIELDS
- from
- db2.news n
- left join db2.read_news rn on n.id = rn.id
- join db1.users u on u.uuid = rn.uuid
- This will give you all of the news, one row for not read and one row for each time it was read with the users data for that read.
- I'm using a left join between news and read_news to get all news with a separate row for any user.
- I'm using a full join between read_news and users because there should be at least one row in users for any row in read news. If you have a read news with no uuid in users that row will not show up
- The last table, "read_news", is meant to sort out which users have read the news or not.
- The relation between read_news and news are supposed to be news.id <-> read_news.news_id.
- What I want to do now, is to have an PHP script to pick out the news for me. The variables given to
- php will be site.php?uuid=X
- I know how to do the php, but I am not too familiar with sql joins. Especialy not across databases.
- My thoughts is this:
- Every now and then, an user accesses the php script, which I want to do the following:
- Output any news (complete with id, datum and news), that does not not have any markings for the uuid in the table "read news".
- (When an user have read the news, another script will put in the read_news the following 'nn', 'news_id', 'uuid', '1' )
- I hope I am making myself understood, if not, please do ask me for clarifications. :)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement