Looping through data from multiple tables PHP/Mysql

Question

HI all,

I am trying to figure out how to put this into words even, but I am wanting to know how to format the output from each table separately in a "multiple table" mysql query. The output from the table1 "wall" is formatted within a while loop, but the content from table2 "actions" is already formatted(as 1 line of text with links) before it is inserted into the table(column action_body), so inside the loop I would only be outputting the action_date and action_body columns from the actions table.

I am probably not using the correct sql method(if Im doing anything right at all, that is) for the results I need, so feel free to correct my novice example, or suggest a new way to approach this.

Query:

$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
}

Any help is appreciated, thanks, Lea

Answer 1

Update after comments

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

Answer 2

Because you don't JOIN on a specific field, you're gonna get every row-row combination of the two tables, which is a whole lot more data than you probably want.

You'd be better of by doing 2 queries, one for each table. While looping through the result of each table, you can collect the data you want in one array, with the field you want to sort it by as array key.

Then you sort the array, and loop through it to print it out.