Okay so right now for a social network project I'm working on I want to implement a feature that shows all the activity that happened on the site since a user last logged in.
I have two tables, one for posts and one for replies to posts.
Posts:
+----------+----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(256) | YES | | NULL | |
| comment | varchar(10000) | YES | | NULL | |
| date | datetime | YES | | NULL | |
| likes | int(11) | YES | | 0 | |
| deleted | int(4) | YES | | 0 | |
+----------+----------------+------+-----+---------+----------------+
Replies:
+----------+----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| post | int(11) | NO | | NULL | |
| username | varchar(256) | YES | | NULL | |
| reply | varchar(10000) | YES | | NULL | |
| date | datetime | YES | | NULL | |
| deleted | int(4) | YES | | 0 | |
+----------+----------------+------+-----+---------+----------------+
(The post in the Replies table links it to the id of the post it is replying to — sorry for any confusion)
The date is set as soon as they submit the post or the reply.
What I'm trying to do is get all posts and replies that were made while the user was not on the site to show the activity of the site while they were away.
Trying to generate something like
Username1 posted on the Main board Username2 commented on a post by Username1
Something like that. With the posts and replies dispersed according to their date.
So I was going to query from these tables:
SELECT * FROM replies
WHERE date > arbitrary_date AND deleted=0
UNION ALL
SELECT * FROM posts
WHERE date > arbitrary_date AND deleted=0
ORDER BY date DESC;
I'm just trying to get a result, ordered by date, of all replies and posts so that I can show them in order by when they were created with the most recent on top. But for some reason I can't figure out how to get the right result. Either the data gets scrambled on the query or it's just flat out wrong.
The query is the part I really need help with. I am not that great with SQL and I can't seem to figure this out despite extensive googling.
If you need clarification, please let me know. I will be more than happy to give more details.
Just make them return the same columns, eg:
SELECT CONCAT(username, ' posted on ', board_name_column) as msg FROM posts ...
UNION
SELECT CONCAT(username, ' commented on ', board_name_column) as msg FROM replies JOIN posts ON replies.post = post.id ...
Another option:
SELECT 'post' AS type, username, board_name_column FROM posts ...
UNION
SELECT 'reply' AS type, username, board_name_column FROM replies JOIN posts ON replies.post = post.id ...