Geek asks and answers

Php join 2 tables by date

So, I have 2 tables that do not have a common column, and I want to save them by date column

So table1 is like:

Table 1

  • ID
  • post_id
  • POST_DATE

table 2

  • ID
  • comment_id
  • COMMENT_DATE

what i want to show is all from table1, table2 and sort 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 don’t know how to determine which element (post or comment) I use inside while (rows = mysql_fetch_assoc ()), since I have different column names.

The 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
+3
source share
3 answers
                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
0
source

Try UNIONwith a new column of constants indicating which table will be displayed, and make the column names the same with AS.

SELECT .

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

, , .

+3

, .

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 modify the tables so that their structure becomes the following:

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 following these steps:

$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'";
....

There is no way in your current project to reliably join them, because there is no connection between them.

+1
source

More articles: