CODEWITHSUNDEEP
mysqljoin
stergosz
stergosz

Reputation: 5860

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

table2

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

i tried something like

SELECT * 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

Upvotes: 3

Views: 443

Answers (3)

stergosz
stergosz

Reputation: 5860

                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

Upvotes: 0

Johan
Johan

Reputation: 76753

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.

Upvotes: 1

Orbling
Orbling

Reputation: 20612

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.

Upvotes: 3

Related Questions