DB Query

Started by Guest, 10 years ago

previous topic - next topic

0 Members and 1 Guest are viewing this topic.

Guest

*
10 years ago
I run post competitions on some of my sites and would like to know all members that have posted at least 30 times in October 2008. What would be the the sql syntax needed for that?

Thanks in advance  :D

Guest

*
#1
10 years ago
Bump, Anyone??

codebirth

*
#2
10 years ago
Code Select
SELECT id_member, COUNT( id_msg ) AS 'nposts'
FROM `smf_messages`
WHERE poster_time >= 1222812000
AND poster_time < 1225494000
GROUP BY id_member
ORDER BY nposts DESC


That would give you the list of members that posted in october, ordered by number of posts, from max to min. You should use the timestamps corresponding to your timezone. The ones I posted are for CET timezone.

c o d e b i r t h
http://codebirth.com/smf

Feline

*
#3
10 years ago
Here a example for SMF 2.0 ..

Code Select
global $smcFunc;

$request = $smcFunc['db_query']('', '
SELECT id_member, poster_name, COUNT(id_msg) AS nposts
FROM {db_prefix}messages
WHERE poster_time BETWEEN {int:firstdate} AND {int:lastdate}
GROUP BY id_member
ORDER BY nposts DESC',
array(
'firstdate' => mktime(0,0,0,10,1,2008),
'lastdate' =>  mktime(0,0,0,10,31,2008),
)
);

echo 'Posts between '. date("M-d-Y, H:i:s", mktime(0,0,0,10,1,2008)) .' and '. date("M-d-Y, H:i:s", mktime(0,0,0,10,31,2008)) .'<br />';
while($row = $smcFunc['db_fetch_assoc']($request))
echo 'Member '. $row['poster_name'] .' (id:'. $row['id_member'] .') have '. $row['nposts'] .' posts<br />';

$smcFunc['db_free_result']($request);


Shows as:
Posts between Oct-01-2008, 00:00:00 and Oct-31-2008, 00:00:00
Member admin (id:1) have 7 posts
Member cat (id:2) have 1 posts

Fel
Many are stubborn in relation to the way, but less in relation to the target.

codebirth

*
#4
10 years ago
Nice code Fel, only last date should be like this to include all messages posted in the last day of the month

Code Select
'lastdate' =>  mktime(23,59,59,10,31,2008),

c o d e b i r t h
http://codebirth.com/smf

Feline

*
#5
10 years ago
You can set the data as you want.
The format is always "mktime(hour, minute, second, month, day, year)" all in numeric format.

Fel
Many are stubborn in relation to the way, but less in relation to the target.

Guest

*
#6
10 years ago
Thanks to both of you for your help 8)

I did it like this in the end;
Code Select
SELECT poster_name, COUNT( id_msg ) AS 'nposts'
FROM `smf_messages`
WHERE poster_time >=1222812000
AND poster_time <1225494000
GROUP BY poster_name
ORDER BY nposts DESC

* Shouts

Feline
9 months ago
Today we update our Forum to the Version 1.45
Feline
a year ago
Today we update our Forum to Version 1.4
This release is full compatible with the EU GDPR
Feline
a year ago
Update for PortaMx 1.54 ecl released.
With this updates the Portal works with PHP 7.x and SMF 2.0.15
Feline
a year ago
PortaMx Forum 1.3
released !

*
Feline
a year ago
Today we switched to PHP 7.2 and activate Opcode caching.
Fast .. Very fast *
Feline
2 years ago
Today we upgrade our old SMF 2.0 Forum to PortaMx Forum *

* Calendar

Su
Mo
Tu
We
Th
Fr
Sa
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30