Poll Sorting Question

[Hent]

Hi everyone,

I've searched Google and looked in the documentation but to no avail. Here's what I need:

When I create polls, I need to know the order of people that voted. Currently, it's displayed alphabetically. What I need is for the list to be displayed from first voter to last voter, by time. Is this possible?

Thanks in advance.

[Jake Bunce]

That is possible since the date of each vote is recorded in the pollvote.votedate field, but there is no built in option to sort by this field. Custom code is required (www.vbulletin.org).

[Hent]

Is anyone available to do this quickly and what would you charge? Please contact [email protected] if you can.

[Jake Bunce]

There is a Service Requests forum on www.vbulletin.org for paid requests.

[Lats]

If you're happy to run a query, try this...

SELECT
 pollvote.pollid, 
 user.username, 
 FROM_UNIXTIME(votedate,'%D %M %x %h:%i:%s') AS whenvoted
FROM
 pollvote
LEFT JOIN
 user
ON
 user.userid = pollvote.userid
WHERE
 pollid = [COLOR=blue]3[/COLOR]
ORDER BY
 votedate

Change the 3 to whatever your pollid is.

[Hent]

Thanks Lats,I'll give it a shot. Is there a way to do this for every poll, every time?

[Lats]

Not without making a small edit to poll.php - look for if ($pollinfo['public'])

[testerv]

I ran the query in Lats' post ( #5). Where does the time stamp get the time? It doesn't look like the time I have set for the forums.

Also, how can I tell if the time is AM or PM?

[Lats]

The timestamp IS the time :-)

The format ( '%D %M %x %h:%i:%s' ) can be whatever you like, check here...

http://php.net/manual/en/function.date.php

[testerv]

Regarding the time, I meant what source is the time stamp getting its time? I voted in a poll then I ran the database query. The query results showed the time of my vote from a different time zone than I am in.

Also, I didn't happen to see the format character "x" in the linked page to the PHP manual.

What value do the "x" and the "%" return ?

[Lats]

Sorry, I linked you to the php date formats, see this for mysql date formats...

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

The result of the query returns the timezone the server is set to.

[testerv]

Thanks, that makes more sense. I still don't see how the %x returned the year value. From the mysql date formats, it looks like %Y would be the specifier to use but the %x works. Hmmmmmmmm.

Also, if my server's time zone is two hours later than my time, is there a way to subtract 2 hours (-2) in the query so it displays my time?

[Lats]

Try this...

SELECT
 pollvote.pollid, 
 user.username, 
 FROM_UNIXTIME(votedate - 7200,'%d %m %Y %h:%i:%s') AS whenvoted
FROM
 pollvote
LEFT JOIN
 user
ON
 user.userid = pollvote.userid
WHERE
 pollid = 2
ORDER BY
 votedate