Need Help, Posters in period

[Powerbob]

I used this query last time for extracting the posters over a certain period, but do not know how to adjust the time/date? I need it from the 28th July up to and including to the 31st of August.

SELECT posterName, COUNT( ID_MSG ) AS 'nposts'
FROM `smf_messages`
WHERE posterTime >=1222812000
AND posterTime <1225494000
GROUP BY posterName
ORDER BY nposts DESC

thanks for your help
Bob

[JBlaze]

Code Select Expand

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

In 2.0, all camel-cased fields were removed.

[Powerbob]

Can you please explain in "english" please

[Arantor]

What JBlaze is saying is that in 2.0 all the fields were renamed to remove the 'camel case', or mixed case fields. So posterTime became poster_time.

[Powerbob]

Ok thanks, that I can understand.

Can you or someone tell me how to work out the dates please?

Cheers
Bob

[Arantor]

Put the times into http://www.onlineconversion.com/unix_time.htm and it'll give you the 10-digit timestamps you need to do the comparisons on.

[Powerbob]

Thank you very much

Bob

[Powerbob]

I was a little to fast in marking this topic as solved

Unfortunately the code returns a blank list!
Can someone help me here?
I need to retrieve the posters between, and including two dates? what sql code is needed ?

Thanks for your help
Bob

[Arantor]

The above code will do it if given the correct timestamps - note that the times are in forum-time not your own time necessarily, so you may be off by a few hours.

[Powerbob]

Yes thanks. I understand that.

[Arantor]

Does this solve the issue?

[Powerbob]

No, the query still returns a blank list!

[Arantor]

Then the timestamps you're using are wrong; the server's own time is used, not that set in any of the offsets (which are display only)

The query is precisely what you need, but you need to adjust the two timestamps to match.

What query gives you an empty response?

[Powerbob]

This one;

SELECT poster_name, COUNT(id_msg) AS 'nposts'
FROM `smf_messages`
WHERE poster_time >=1248760800
AND poster_time <1251763200
GROUP BY poster_name
ORDER BY nposts DESC

thanks, Bob

[Arantor]

I'm guessing your forum did have posts between July 28th and September 1st.

Where are you running that query?

[Powerbob]

Yes it did

in phpmyadmin, under Sql.

Sorry for causing probs!

[Arantor]

For now, can you test using the following query:

Code Select Expand

SELECT poster_name, COUNT(id_msg) AS 'nposts'
FROM `smf_messages`
GROUP BY poster_name
ORDER BY nposts DESC

This will establish whether it's the WHERE part that's giving it problems or not.

[Powerbob]

Yes that worked. Definitely the "WHERE" giving problems.

Bob

[Arantor]

Hmm.

OK, now let's try:

Code Select Expand

SELECT poster_name, COUNT(id_msg) AS 'nposts'
FROM `smf_messages`
WHERE poster_time >= 1248760800
GROUP BY poster_name
ORDER BY nposts DESC

This should get everything after July 28.

[Powerbob]

No sorry did not work, returned this;

[Arantor]

Weird.

OK, let's try this.

Code Select Expand

SELECT poster_name, COUNT(id_msg) AS 'nposts', MAX(poster_time)
FROM `smf_messages`
GROUP BY poster_name
ORDER BY nposts DESC

This will get us the last post time per person.

[Powerbob]

Yes that worked ok.

Bob

[Arantor]

I was hoping you were going to give me a few numbers from the right hand column too.

[Powerbob]

Sorry, here are the details;

[Powerbob]

I think I am going crazy here!

Just tried it again and the query worked! Go figure, it's beyond me?
Topic is now solved, thanks for all your help and sorry I took up so much of your valuable time.

Thanks
Bob

[Arantor]

No worries to take up time - if you've got a problem with SMF, no matter how small or huge, that is what this forum is for, so we can figure it out.

I'm not sure what went on there, but if it's working now, that's all good.