php - 循环遍历来自多个表的数据 PHP/Mysql

Question

大家好，

我什至想弄清楚如何将其转化为文字，但我想知道如何在“多表”mysql 查询中分别格式化每个表的输出。 table1“wall”的输出在 while 循环中格式化，但 table2“actions”的内容在插入表(action_body 列)之前已经格式化(作为带有链接的 1 行文本)，因此在循环 我只会输出操作表中的 action_date 和 action_body 列。

对于我需要的结果，我可能没有使用正确的 sql 方法(如果我做任何事情都是正确的，那就是)，所以请随时纠正我的新手示例，或建议一种新的方法来解决这个问题。

查询:

$query = "SELECT wall.wall_id, wall.wall_owner_id, wall.wall_user_id,
    wall.wall_post_date, wall.wall_post_content, actions.action_id, 
    actions.action_date, actions.action_user_id, actions.action_title, 
actions.action_body FROM wall, actions 
ORDER BY wall.wall_post_date, actions.action_date DESC";
$result = mysql_query($query);
while( $rows = mysql_fetch_assoc($result) {
// What to put here
}

感谢任何帮助，谢谢，Lea

Answer 1

评论后更新

SELECT w.* FROM (
    (SELECT 
        'w' as type, 
        wall_id as id,
        wall_owner_id as owner_id,
        wall_user_id as user_id,
        wall_post_date as post_date,
        NULL as title,
        wall_post_content as content
     FROM wall
     WHERE wall_owner_id = x # user id of owner
    )
    UNION
    (SELECT
         'a' as type,
         action_id as id,
         action_user_id as owner_id,
         NULL as user_id,
         action_post_date as post_date,
         action_title as title,
         action_body as content
      FROM actions
      WHERE action_user_id = x # user id of owner
     )
) w 
ORDER BY w.post_date DESC