Looping through data from multiple tables PHP/Mysql
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 metho开发者_如何学God(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
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
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.
精彩评论