Moderate topics via MySQL table

[ntldr1962]

hello!

i want to control the posts via MySQL i mean if i add a row, via MySQL and i put visible to Zero value i dont see the post, but if i try to enter to "moderator tools" and i want to see the posts pendient for moderation i did not found anything, which other table i will need to modify?

thanks in advance

[Lynne]

You also need to modify the thread table. And add post count to the user table.

[ntldr1962]

Thank you Lynne.

I have a little issue, what is the post count i cannot find it in the tables, maybe you make reference to the parent id column?, can you say to me the exactly name of the header of column? and in what table is it?. Or maybe you are saying that i need to add one new column in the users table where a counter of total post of each user are keeped?

Also, if you have not inconvenience, can you say to me how can i convert the values of the date of the column dateline?. I have read a little in internet and i have found that is necessary to make one conversion to the vBulletin format, and it is possible to make in:

http://www.unixtimestamp.com/index.php
http://www.4webhelp.net/us/timestamp...573&timezone=0
http://www.onlineconversion.com/unix_time.htm
http://www.csgnetwork.com/time2unixdscalc.html

I am right? Its possible to make it in other fast way for bulk conversion?

Thanks in advance

[ntldr1962]

In internet you can find many times this formula:

=(((A1/60)/60)/24)
or
=(((A1/60)/60)/24)+DATE(1970,1,1)
or
=(((A1/60)/60)/24)+DATE(1970,1,1)+(-5/24)
or
=(A1/86400)+25569+(-5/24)

If somebody have the same problem as me, is necessary to know that this number in the datacolumn is the Unix time is defined as the number of seconds since midnight (GMT time) on January 1, 1970 -- also known as the Unix epoch.

The correct formula to calculate it is:

=(Y2-$Y$1)*86400 -> in excel 2010 where Y2= cell containing the most recent date
Y1= cell with the value 01/01/1970 00:00:00

Remember that is important to use the same format on date cells and in the result cell to have the format of number

I leave all this here in order to help to somebody who can need it.

[kh99]

Sorry I didn't notice your question before, but I thought I'd point out that you can use the mysql function FROM_UNIXTIME() to convert the field, like:

SELECT FROM_UNIXTIME( lastpost ) AS lastpost_date
FROM `thread`


There's also the date() function in php which can canvert a unix time and format it just about any way you can think of (although you did way you wanted to do it in sql).

[ntldr1962]

Thank you for replies.

And now... we have the real big problem:

If the values in the column dateline are in russian language... like:

01 дек 2009, 22:22

How can i convert these data to the Unix time?,because the system not recognize the cirilic characters, means that first i need to change them to the format 01/12/2009 for example. Of course i am using the UTF-8 charset.

If somebody is so valerous to reply to this question i will be grateful for all life :-)

Thanks in advance.

[kh99]

I don't know much about different character sets, but there is also a mysql function called UNIX_TIMESTAMP() which converts a date string to a timestamp, and maybe if your mysql server is set for Russian dates, it will know how to do the conversion.

Here's a page that shows the date functions available: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_from-unixtime

and here's a page that talks about setting your server date/time language: http://dev.mysql.com/doc/refman/5.0/en/locale-support.html

[Lynne]

Thank you Lynne.

I have a little issue, what is the post count i cannot find it in the tables, maybe you make reference to the parent id column?, can you say to me the exactly name of the header of column? and in what table is it?. Or maybe you are saying that i need to add one new column in the users table where a counter of total post of each user are keeped?

User table, field "posts" is where the post count is kept. But remember, some forums are set to not increment the post count, so you will need to check that also if you have any forums set that way.