I have a bot which collects activity from certain users and logs posts they make on one of my websites. I have a list of moderators on this website and I would like to use MySQL to return information about their activity. At the moment, I do the following:
SELECT count( num ) AS posts, username
FROM `logs`
WHERE username
IN (
'username1', 'username2', 'username3', 'username4', 'username5',
)
AND FROM_UNIXTIME( epoch )
BETWEEN "2015-05-26"
AND "2015-05-29"
GROUP BY username
ORDER BY posts DESC
LIMIT 0 , 30
Some sample output from the above query:
username1 100
username2 50
username3 25
What I want to output: How do I do this?
username1 100
username2 50
username3 25
username4 0
username5 0
Table structure:
num (int, key)
username (varchar)
epoch (varchar)
msg (varchar)
There's the IFNULL function that I think applies in this case
SELECT IFNULL(count( num ),0) AS posts, username
FROM `logs` ...
If the value of the count is null the posts will return 0
MySQL has some limitations that make queries like this particularly hard. What's hard is creating a temporary list of usernames that you need to join to make your query work
There is only one way that I know to do this. It involves storing the data in a temporary table and then querying with that table.
CREATE temporary table temp_users (name VARCHAR(30))
INSERT INTO temp_users (name) VALUES('username 1'),('username 2'),('username 3');
SELECT count( num ) AS posts, temp_username.username
FROM temp_username LEFT JOIN `logs` ON logs.username=temp_username.username
AND FROM_UNIXTIME( epoch )
BETWEEN "2015-05-26" AND "2015-05-29"
GROUP BY username
ORDER BY posts DESC
LIMIT 0 , 30)
Here's an example that's been simplified http://sqlfiddle.com/#!9/cc4ef1/11/0
The table is deleted at the end of the session
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments