DB Query

Started by Powerbob, 2008 November 02, 04:02:34 am

previous topic - next topic

0 Members and 1 Guest are viewing this topic.

Powerbob

*
2008 November 02, 04:02:34 am
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
*
My test Site;http://pplb.net/smf21/index.php
It's nice to be nice :)

Powerbob

*
#1
2008 November 03, 02:23:57 am
Bump, Anyone??
*
My test Site;http://pplb.net/smf21/index.php
It's nice to be nice :)

codebirth

*
#2
2008 November 03, 10:33:36 am
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
2008 November 03, 03:30:32 pm
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
2008 November 03, 04:03:46 pm
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
2008 November 03, 05:05:46 pm
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.

Powerbob

*
#6
2008 November 04, 01:57:25 am
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
*
My test Site;http://pplb.net/smf21/index.php
It's nice to be nice :)

* Shouts

Feline
2018 Aug. 21, 08:10:46 pm
Today we update our Forum to the Version 1.45
Feline
2018 Jun. 09, 07:54:13 pm
Today we update our Forum to Version 1.4
This release is full compatible with the EU GDPR
Feline
2018 Mar. 28, 11:56:21 am
Update for PortaMx 1.54 ecl released.
With this updates the Portal works with PHP 7.x and SMF 2.0.15
Feline
2018 Mar. 17, 10:35:03 pm
PortaMx Forum 1.3
released !

*
Feline
2017 Dec. 10, 04:30:54 am
Today we switched to PHP 7.2 and activate Opcode caching.
Fast .. Very fast *
Feline
2017 Mar. 03, 11:54:35 pm
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
31