vb.org Archive - Accessing the Infraction Table

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB4 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=252)

- - Accessing the Infraction Table (https://vborg.vbsupport.ru/showthread.php?t=321095)

Accessing the Infraction Table

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!

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
);

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?

Quote:

Originally Posted by Chris.08i (Post 2560496)

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. :)

Quote:

Originally Posted by MarkFL (Post 2560497)

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!

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. :o

Quote:

Originally Posted by MarkFL (Post 2560499)

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?

X vBulletin 3.8.12 by vBS Debug Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (4)bbcode_php_printable (3)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (7)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages:

Phrase Groups Available:

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.02790 seconds Memory Usage 1,769KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (4)bbcode_php_printable (3)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (7)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: