Accessing the Infraction Table

[Chris.08i]

Hello again,

I'm doing more work on my forums and I'm looking to do something related to the infraction table.

I have already created a new column in my user table, and I plan to have the value as either 0 or 1. A 0 would mean that the user has not received a permanent infraction (never expiring) in the past year, and a 1 would mean the opposite.

I am unsure how I would go about tackling this task.

Help is appreciated! Thank you!

[MarkFL]

Let's assume you have the userid stored in $userid (and the name of your new column is "new_column), then you could use the queries:

PHP Code:

$pinfract_this_year = $vbulletin->db->query_first("
    SELECT COUNT(*) AS infract_count
    FROM " . TABLE_PREFIX . "infraction AS infraction
    WHERE userid = " . $userid . "
    AND dateline >= " . (TIMENOW - 365*86400) . "
    AND expires = 0
");

if ($pinfract_this_year['infract_count'])
{
    c_value = 1;
}
else
{
    c_value = 0;
}

$vbulletin->db->query_write(" 
    UPDATE " . TABLE_PREFIX . "user
        SET new_column = " . $c_value . "
        WHERE userid = " . $userid
); 


[Chris.08i]

Thank you so much for your quick reply.

I was hoping to implement this as a cron job, which I can then use whatever the cron job returns in my plugin.

As far as I know, you can't use userid's in a cron job?

Edit: I was doing some thinking, and would it be able to actually count the number of permanent infractions a user has that were issued within the past year and set it in the new column?

[MarkFL]

Originally Posted by Chris.08i

Thank you so much for your quick reply.

I was hoping to implement this as a cron job, which I can then use whatever the cron job returns in my plugin.

As far as I know, you can't use userid's in a cron job?

Well, what you could do is query the infraction table to get all users who have been issued a permanent infraction during the last year (then build a comma-delimited list of userids), and then query the user table to set the users in the list you built with the value of 1 in the new column and all others are set to 0.

If that sounds good, I will see if I can construct the correct code for that.

[Chris.08i]

Originally Posted by MarkFL

Well, what you could do is query the infraction table to get all users who have been issued a permanent infraction during the last year (then build a comma-delimited list of userids), and then query the user table to set the users in the list you built with the value of 1 in the new column and all others are set to 0.

If that sounds good, I will see if I can construct the correct code for that.

I did some Google, and I read your previous post.

I came up with this, but I don't know if it is correct for starters, nor how to use the comma-delimited output with the second query.

I also want to now count the number of permanent infractions the user has had in the past year and set it in the new column in the user table.

PHP Code:

$pinfracted = $vbulletin->db->query_first("
    SELECT CONCAT(userid, ',') AS puserid
    FROM " . TABLE_PREFIX . "infraction AS infraction
    AND dateline >= " . (TIMENOW - 365*86400) . "
    AND expires = 0
");
    
$pinfract_this_year = $vbulletin->db->query_first("
    SELECT COUNT(*) AS infract_count
    FROM " . TABLE_PREFIX . "infraction AS infraction
    WHERE userid = " .  $pinfracted['puserid'] . "
    AND dateline >= " . (TIMENOW - 365*86400) . "
    AND expires = 0
");

$vbulletin->db->query_write(" 
    UPDATE " . TABLE_PREFIX . "user
        SET new_column = " . $pinfract_this_year['infract_count'] . "
        WHERE userid = " . $userid
); 


I'm sorry if none of what I said makes any sense, and I wholly appreciate you taking your time to help me!

[MarkFL]

Okay, this is what I had in mind:

PHP Code:

$pinfract_this_year = $vbulletin->db->query_read("
    SELECT infraction.*
    FROM " . TABLE_PREFIX . "infraction AS infraction
    WHERE dateline >= " . (TIMENOW - 365*86400) . "
    AND expires = 0
");

$userlist = '';
while ($infract = $vbulletin->db->fetch_array($pinfract_this_year))
{
    $userlist .= ($infract['userid'] . ',');
}
$userlist = rtrim($userlist, ',');

$vbulletin->db->query_write(" 
    UPDATE " . TABLE_PREFIX . "user
        SET new_column = CASE
            WHEN userid IN (" . $userlist . ") THEN 1
            ELSE 0
        END
"); 


edit: I just now saw you posted "I also want to now count the number of permanent infractions the user has had in the past year and set it in the new column in the user table." That will require some recoding, which I will have to put off for now as it's getting late.

edit 2: I corrected a small typo in the code.

[Chris.08i]

Originally Posted by MarkFL

Okay, this is what I had in mind:

PHP Code:

$pinfract_this_year = $vbulletin->db->query_read("
    SELECT infraction.*
    FROM " . TABLE_PREFIX . "infraction AS infraction
    WHERE dateline >= " . (TIMENOW - 365*86400) . "
    AND expires = 0
");
$user_list = '';
while ($infract = $vbulletin->db->fetch_array($pinfract_this_year))
{
    $userlist .= ($infract['userid'] . ',');
}

$userlist = rtrim($userlist, ',');

$vbulletin->db->query_write(" 
    UPDATE " . TABLE_PREFIX . "user
        SET new_column = CASE
            WHEN userid IN (" . $userlist . ") THEN 1
            ELSE 0
        END
"); 


Yours looks a lot simpler than mine, and it makes sense because I can follow it through haha.

Is it possible to write the count rather than the 1 and 0?