XF 2.1 Inserting Data from callback

[rivacom]

Hi everyone, I have a custom table in the database and I'm trying to run a few queries and update the new table. This seems to run fine but I get zero entries into the database. Is there a new or preferred method of inserting into the database?

<?php

namespace GuildAdmin;

class HostedStream {

    public static function getHtml()
    {
        //Find donator members
        $finderuser = \XF::finder('XF:User');
        $users = $finderuser->pluckFrom('user_id')->where('secondary_group_ids', '=', '13')->fetch();

        foreach($users as $don){
       
        //Grab all users with twitch usernames in profile
        $finder = \XF::finder('XF:UserFieldValue');
        $username = $finder->pluckFrom('field_value')->where('user_id', '=', $don)->fetchOne();  
       
        //Cycle through each user with Twitch username in profile and check if they are online or offline.
            if ($username !== null) {                            
            $TwitchClientid = 'r20';
            $url = 'https://api.twitch.tv/helix/streams?user_login='. $username;
            $ch = curl_init();
            curl_setopt_array($ch, array(
            CURLOPT_HTTPHEADER => array(
                'Client-ID: ' . $TwitchClientid . '',
            ),
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_URL => $url
            ));
            $response = curl_exec($ch);
            curl_close($ch);
       
            if ($response == null) {
            //stream is offline = 2
            $db = $this->db();
            $db->insert('xf_twitch', [
            'tw_username'  => $TU,
            'on_off' => '2'
            ]);  
            } else {
            //Stream is online = 1
            $db = $this->db();
            $db->insert('xf_twitch', [
            'tw_username'  => $TU,
            'on_off' => '1'
            ]);
           
            }
        }
       
    }

    }
}

?>

 

[Snog]

There are at least three problems with your code.

1) $users = $finderuser->pluckFrom('user_id')->where('secondary_group_ids', '=', '13')->fetch();

secondary_group_ids is a comma separated field. So unless the only entry, or the first entry in the comma separated value is 13, you won't get any results.​

2) $username = $finder->pluckFrom('field_value')->where('user_id', '=', $don)->fetchOne();

You need to tell the finder which field_id you want the field_value to come from. Otherwise you're getting the first field_value of whatever field_id comes first in the table for that user.​

3) You're using $db->insert for everything. Unless you delete the entry at some point, you should update existing values.

Hope that helps a little.

 

[rivacom]

There are at least three problems with your code.

1) $users = $finderuser->pluckFrom('user_id')->where('secondary_group_ids', '=', '13')->fetch();

secondary_group_ids is a comma separated field. So unless the only entry, or the first entry in the comma separated value is 13, you won't get any results.​

2) $username = $finder->pluckFrom('field_value')->where('user_id', '=', $don)->fetchOne();

You need to tell the finder which field_id you want the field_value to come from. Otherwise you're getting the first field_value of whatever field_id comes first in the table for that user.​

3) You're using $db->insert for everything. Unless you delete the entry at some point, you should update existing values.

Hope that helps a little.

1) Def. makes sense. Are wildcards allowed? so %13% ?

2) so I see there is a way to do Where OR but is there a WHERE AND?

3) speaking of that, how I used to do this in xf 1.5 was to just clear the table, is there a easy way to do that for xf2.1?

 

[Snog]

1) Def. makes sense. Are wildcards allowed? so %13% ?

You could do something like...
$users = $finderuser->pluckFrom('user_id')->where('secondary_group_ids', 'LIKE', '%13%')->fetch();
Untested and not the best solution in the event you end up with a high number of user groups (this would fetch user group IDs 130, 131, 132...).

2) so I see there is a way to do Where OR but is there a WHERE AND?

Add a second where.

3) speaking of that, how I used to do this in xf 1.5 was to just clear the table, is there a easy way to do that for xf2.1?

$db->emptyTable('xf_twitch');

 

[Snog]

Thinking about it, this probably would work better than a "LIKE" (untested, but should work)...

$finderuser = \XF::finder('XF:User');
$finderuser->whereSql("FIND_IN_SET(" . $finderuser->quote(13) . ", " . $finderuser->columnSqlName('secondary_group_ids') . ")");
$users = $finderuser->pluckFrom('user_id')->fetch();

 

[rivacom]

Updated code, Haven't tried your Like replacement yet but still no luck. No errors from xenforo though.

<?php

namespace GuildAdmin;

class HostedStream {

    public static function getHtml()
    {
        //Find donator members
        $finderuser = \XF::finder('XF:User');
        $users = $finderuser->pluckFrom('user_id')->where('secondary_group_ids', 'LIKE', '%13%')->fetch();

        foreach($users as $don){
       
        //Grab all users with twitch usernames in profile
        $finder = \XF::finder('XF:UserFieldValue');
        $username = $finder->pluckFrom('field_value')->where('user_id', '=', $don)->where('field_id', '=', 'twitch')->fetchOne();   
       
        //Cycle through each user with Twitch username in profile and check if they are online or offline.
            if ($username !== null) {                             
            $TwitchClientid = 'r209qk6n2si03dg18736qqeq2jqi56';
            $url = 'https://api.twitch.tv/helix/streams?user_login='. $username;
            $ch = curl_init();
            curl_setopt_array($ch, array(
            CURLOPT_HTTPHEADER => array(
                'Client-ID: ' . $TwitchClientid . '',
            ),
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_URL => $url
            ));
            $response = curl_exec($ch);
            curl_close($ch);
       
            if ($response == null) {
            //stream is offline = 2
            $db = $this->db();
            $db->insert('xf_twitch', [
            'tw_username'  => $TU,
            'on_off' => '2'
            ]);   
            } else {
            //Stream is online = 1
            $db = $this->db();
            $db->insert('xf_twitch', [
            'tw_username'  => $TU,
            'on_off' => '1'
            ]);
           
            }
        }
       
    }

    }
}

?>

 

[Snog]

Do you have any users in the user group ID 13?
Do the users have their "twitch" custom user field filled out?
Are you getting an error from cURL?
Are you getting a response from cURL?

Is it possible your code isn't being called at all?

Beyond those items, I don't know what to tell you.

 

[rivacom]

Do you have any users in the user group ID 13?
Do the users have their "twitch" custom user field filled out?
Are you getting an error from cURL?
Are you getting a response from cURL?

Is it possible your code isn't being called at all?

Beyond those items, I don't know what to tell you.

Yes to group ID 13 and custom field. I'm manually runnning this as a cron job, is there anyway to show output logs or anything from within xenforo? I can test the cURL as a normal php file but I lose all the ability to test the Xenforo side.

 

[Snog]

echo, print_r, etc. all work like they would in a stand alone PHP file. You can use those with die() after each query and the cURL response to see what's going on.

 

[rivacom]

echo, print_r, etc. all work like they would in a stand alone PHP file. You can use those with die() after each query and the cURL response to see what's going on.

So turned it into a page, to test the outputs, Getting this on the DB portion when inserting.

Error: Using $this when not in object context in

 

[Snog]

So turned it into a page, to test the outputs, Getting this on the DB portion when inserting.

Error: Using $this when not in object context in

That's probably your answer try $db = \XF::db() though I'm surprised that error didn't come up in the cron task. I don't think errors are suppressed there.

 

[rivacom]

Does anyone know if finder works with custom tables?

$finder = \XF::finder('XF:Twitch');

 

[Snog]

$finder = \XF::finder('AddonName:TableName');
or
$finder = \XF::finder('AddonAuthor\AddonName:TableName');

 

[rivacom]

But nothing for just manually created items? I have no plans on making this an addon, just something for myself and added the table myself.

 

[Snog]

But nothing for just manually created items? I have no plans on making this an addon, just something for myself and added the table myself.

No.

To my knowledge finders only work with entities that are defined in an add-on or XF itself. To access a table that is not in the entity system, you would need to use database queries.

 

[rivacom]

Bummer, Is there a $db->insert( for select? like $db->select()?

 

[Snog]

Bummer, Is there a $db->insert( for select? like $db->select()?

 

[rivacom]

@Snog , Thank You for all your help with this little project of mine!