Reputation: 5860
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
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
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
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