douci1677 2012-08-19 00:33
浏览 46
已采纳

mysql / php - 确定数据的来源

I need to determine what table data is from for a news feed. The feed must say something like "Person has uploaded a video" or "Person has updated their bio". Therefore I need to determine where data came from as different types of data are in different tables, obviously. I am hoping you can do this with SQL but probably not so PHP is the option. I have no idea how to do this so just need pointing in the right direction.

I'll briefly describe the database as I don't have time to make a diagram.

1.There is a table titled members with all basic info such as email, password and ID. The ID is the primary key.

  1. All other tables have foreign keys for the ID linking to the ID in the members table.

  2. Other tables include; tracks, status, pics, videos. All pretty self explanatory from there.

I need to determine somehow what table the updated data comes from so I can then tell the user what so and so has done. Preferably I would want only one SQL statement for the whole feed so all the tables are joined and ordered by timestamp making everything much simpler for me. Hopefully I can do both but as I said really not sure.

A basic outline of the statement, will be longer but have simplified;

SELECT N.article,  N.ID, A.ID, A.name,a.url, N.timestamp
FROM news N
LEFT JOIN artists A ON N.ID = A.ID
WHERE N.ID = A.ID
ORDER BY N.timestamp DESC
LIMIT 10

Members table;

CREATE TABLE `members` (
`ID` int(111) NOT NULL AUTO_INCREMENT,
`email` varchar(100) COLLATE latin1_general_ci NOT NULL,
`password` varchar(100) COLLATE latin1_general_ci NOT NULL,
`FNAME` varchar(100) COLLATE latin1_general_ci NOT NULL,
`SURNAME` varchar(100) COLLATE latin1_general_ci NOT NULL,
`timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`),
UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

Tracks table, all other tables are pretty much the same;

CREATE TABLE `tracks` (
`ID` int(11) NOT NULL,
`url` varchar(200) COLLATE latin1_general_ci NOT NULL,
`name` varchar(100) COLLATE latin1_general_ci NOT NULL,
`timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
`track_ID` int(11) NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`track_ID`),
 UNIQUE KEY `url` (`url`),
 UNIQUE KEY `track_ID` (`track_ID`),
 KEY `ID` (`ID`),
 CONSTRAINT `tracks_ibfk_1` FOREIGN KEY (`ID`) REFERENCES `members` (`ID`)
 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

Before I have tried using a mysql query for each table and putting everything into an array and echoing it out. This seemed long and tiresome and I had no luck with it. I have now deleted all that code as it was a week or so ago.

Please do not feel you have to go into depth with this just point me in the right direction.

ADDITION:

Here is the sql query i have made for a trigger that was suggested. Not sure what is wrong as have never used trigger before. When inserting something into tracks this error comes up

#1054 - Unknown column 'test' in 'field list' 

The values in the query are just for testing at the moment

 delimiter $$

 CREATE

 TRIGGER tracks_event AFTER INSERT
 ON tracks FOR EACH ROW 

   BEGIN

INSERT into events(ID, action)
    VALUES (3, test);

   END$$
delimiter ;

UPDATE!

I have now created a table called events as suggested and used triggers to update it AFTER an insert in one of several tables.

Here is the query I have tried but it is wrong. The query needs to get info referenced in the events table from all the other tables and order by timestamp.

SELECT T.url, E.ID, T.ID, E.action, T.name, T.timestamp
FROM tracks T
LEFT JOIN events E ON T.ID = E.ID
WHERE T.ID = E.ID
ORDER BY T.timestamp DESC

In that query I have only include the events and tracks table for simplicity as the problem is still there. There will be many more tables so the problem will worsen.

It's hard to describe the problem but basically because there is an ID in every table and one ID can do several actions, the action can be shown with the wrong outcome, in this case url.

I will explain what's in the events table and the tracks table and give the outcome to further explain.

In the events table;

      4 has uploaded a track.
      3 has some news.
      4 has become an NBS artist.

In the tracks;

      2 uploads/abc.wav Cannonballs & Stones    2012-08-20 23:59:59 1
      3 uploads/19c9aa51c821952c81be46ca9b2e9056.mp3    test    2012-08-31 23:59:59 2
      4 uploads/2b412dd197d464fedcecb1e244e18faf.mp3    testing 2012-08-31 00:32:56 3
      4 111 111111  0000-00-00 00:00:00 111111

Outcome of query;

uploads/19c9aa51c821952c81be46ca9b2e9056.mp3    3   3   has some news.  test    2012-08-31 23:59:59    
uploads/2b412dd197d464fedcecb1e244e18faf.mp3    4   4   has uploaded a track.   testing 2012-08-31 00:32:56
uploads/2b412dd197d464fedcecb1e244e18faf.mp3    4   4   has become an NBS artist.   testing 2012-08-31 00:32:56
111 4   4   has become an NBS artist.   111111  0000-00-00 00:00:00
111 4   4   has uploaded a track.   111111  0000-00-00 00:00:00

As you can see the query gives unwanted results. The action for each ID is given on each url so the url can be shown more than once and with the wrong action. Because there is only the tracks table in that query, the only action i would want showing is 'has uploaded a track.'

  • 写回答

1条回答 默认 最新

  • doucheng7534 2012-08-19 03:00
    关注

    It's hard to provide the statement you want without the full details of your schema. For example, the question refers to a news table and an artists table, but doesn't provide the schemas for those, or indicate how the statement that contains those references relate to any of the other tables mentioned in the question.

    Still, I think what you want can be done entirely in MySQL, without any fun PHP tricks, especially if there are common fields in each of the various tables.

    But first: this might not be the answer you're really wanting, but using triggers on your various tables to update an "events feed" table is likely the best solution. i.e., when an insert or update happens on the "status" table, have a trigger on the status table that inserts into the "events feed" table the ID of the person, and their type of action. You could have a separate insert and update trigger to indicate different events for the same data type.

    Then it'd be super-easy to have an events feed, because you're just selecting straight from that events feed table.

    Check out the create trigger syntax.

    That said, I think you might have a look at the CASE and UNION keywords.

    You can then construct a query that grabs data from all tables and outputs strings indicating something. You could then turn that query into a view, and use that as an "events feed" table to select directly from.

    Say you have a list of members (which you do), and the various tables that contain actions from those members (i.e., tracks, status, pics, videos), which all have a key pointing back to your members table. You don't need to select from members to generate a list of activity, then; you can just UNION together the tables that have certain events.

    SELECT
        events.member_id
      , events.table_id
      , events.table
      , events.action
      , events.when_it_happened
      , CASE
          WHEN events.table = "tracks" THEN "Did something with tracks"
          WHEN events.table = "status" THEN "Did something with status"
        END
        AS feed_description
    FROM (
      SELECT
          tracks.ID AS member_id
        , tracks.track_ID AS table_id
        , "tracks" AS table
        , CONCAT(tracks.url, ' ', tracks.name) AS action
        , tracks.timestamp AS when_it_happened
      ORDER BY tracks.timestamp DESC
      LIMIT 10
    
      UNION
    
      SELECT
          status.ID as member_id
        , status.status_id AS table_id
        , "status" AS table
        , status.value AS action
        , status.timestamp AS when_it_happened
      ORDER BY status.timestamp DESC
      LIMIT 10
    
      UNION
      ...
    
    ) events
    ORDER BY events.when_it_happened DESC
    

    I still think you'd be better off creating a feed table built by triggers, because it'll perform a lot better if you're querying for the feed more often than generating events.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题