SHARE
TWEET

Untitled

a guest May 19th, 2017 161 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. I'm trying to work out an mysql issue, but not sure of how to do it.
  2. I have two databases from which I need to select certain fields.
  3.  
  4. DB1.
  5. mysql> select * from users;
  6. +----+--------------------------------------+
  7. | id | uuid                                 |
  8. +----+--------------------------------------+
  9. |  1 | 05c9fe42-8d89-401d-a9a5-2d82af58e16f |
  10. +----+--------------------------------------+
  11.  
  12. DB2.
  13. mysql> select * from news;
  14. +----+---------------------+-------------------+
  15. | id | datum               | news              |
  16. +----+---------------------+-------------------+
  17. |  1 | 2010-04-14 11:36:21 | FIRST TEST NEWS!  |
  18. +----+---------------------+-------------------+
  19.  
  20. mysql> select * from read_news;
  21. +----+---------+--------------------------------------+------+
  22. | id | news_id | uuid                                 | read |
  23. +----+---------+--------------------------------------+------+
  24.  
  25.  
  26. select
  27.     n.FIELDS, rn.FIELDS, u.FIELDS
  28. from
  29.     db2.news n
  30.     left join db2.read_news rn on n.id = rn.id
  31.     join db1.users u on u.uuid = rn.uuid
  32.  
  33. 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.  
  34. I'm using a left join between news and read_news to get all news with a separate row for any user.
  35. 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
  36.  
  37.  
  38. The last table, "read_news", is meant to sort out which users have read the news or not.
  39.  
  40. The relation between read_news and news are supposed to be news.id <-> read_news.news_id.
  41. What I want to do now, is to have an PHP script to pick out the news for me. The variables given to
  42. php will be site.php?uuid=X
  43.  
  44. I know how to do the php, but I am not too familiar with sql joins. Especialy not across databases.
  45. My thoughts is this:
  46. Every now and then, an user accesses the php script, which I want to do the following:
  47.  
  48. Output any news (complete with id, datum and news), that does not not have any markings for the uuid in the table "read news".
  49. (When an user have read the news, another script will put in the read_news the following 'nn', 'news_id', 'uuid', '1' )
  50.  
  51. I hope I am making myself understood, if not, please do ask me for clarifications. :)
RAW Paste Data
Top