php join 2 tables by date problem

So i have 2 tables which does not have any column in common and i want to store them by their date column

So table1 is like:

table1

• id
• post_id
• post_date

table2

• id
• comment_id
• comment_date

what i want to show is everything from table1,table2 and sort it by date

i tried something like

SELECT开发者_Python百科 * FROM table1 INNER JOIN table2 ORDER BY post_date DESC, comment_date DESC

the problem is that i dont know how to identify which item(post or comment) i am using inside the while(rows = mysql_fetch_assoc()) since i have different column names.

Solution was:

                SELECT * FROM (
                SELECT 1 AS `table`, `col1` AS `userid`, `col2` AS `cat`, `col3` AS `item_id`, `title` AS `title`, etc... , `date` AS `date` FROM `table1`
                UNION
                SELECT 2 AS `table`, `col1` AS `userid`, `col2` AS `cat`, `col3` AS `item_id`, NULL AS `title`, etc... , `date` AS `date` FROM `table2`
            ) AS tb
            ORDER BY `date` DESC

Try a UNION, with a new constant column indicating which table is being output and make the column names the same using AS.

A surrounding SELECT might allow you to order it together.

SELECT * FROM (
    (SELECT 1 AS `table`, `id`, `post_id` AS `table_id`, `post_date` AS `date` FROM `table1`)
    UNION
    (SELECT 2 AS `table`, `id`, `comment_id` AS `table_id`, `comment_date` AS `date` FROM `table2`)
)
ORDER BY `date` DESC

That would need testing, not sure if it is allowed.

You'll have to redesign your database if you want to link posts to comments.

Given these tables

table1

    id
    post_id
    post_date

table2

    id
    comment_id
    comment_date

And the fact that comments should be comments belonging to posts, you need to change the tables, so that its structure becomes:

table post

    id         /*id of a post*/
    user_id    /*which user posted this*/
    post_date  /*when?*/
    post_text  /*the text inside the post*/

table comments

    id            /*id of a comment*/
    post_id       /*which post does this comment belong to*/
    user_id       /*who posted this*/
    comment_date  /*when*/
    comment_text  /*text of the comment*/

Now you can join this by doing:

$post_id = mysql_real_escape_string($_GET['post_id']);

/*select all comments belonging to a post*/
$query = "SELECT c.user_id, c.comment_date, c.comment_text FROM posts p
          INNER JOIN comments c ON (c.post_id = p.id)
          WHERE p.id = '$post_id'";
....

In your current design there is no way to reliably join them because there is no relation between the two.

                SELECT * FROM (
            SELECT 1 AS `table`, `col1` AS `userid`, `col2` AS `cat`, `col3` AS `item_id`, `title` AS `title`, etc... , `date` AS `date` FROM `table1`
            UNION
            SELECT 2 AS `table`, `col1` AS `userid`, `col2` AS `cat`, `col3` AS `item_id`, NULL AS `title`, etc... , `date` AS `date` FROM `table2`
        ) AS tb
        ORDER BY `date` DESC