Get custom profile field value into scheduled task

[Kolya]

SMF 2.0.17

I'm in an existing scheduled task and I would like to get a custom profile field value, for all users.
But I'm not quite clear where the values for custom profile fields are stored.
Or if I should use custom profile fields for this at all.

EDIT: I found the log_actions table that stores the custom fields data. Bit of a weird data format in the 'extra' field. This is a users custom amazon wishlist:

Code Select Expand

a:3:{s:8:\"previous\";s:0:\"\";s:3:\"new\";s:53:\"http://www.amazon.it/registry/wishlist/34324234/\";s:10:\"applicator\";s:4:\"2839\";}

Can somebody explain this format? Specifically the initial "a:3" and "s:53" which holds the value?

[Pipke]

https://www.php.net/manual/en/function.serialize.php

https://www.w3schools.com/php/phptryit.asp?filename=tryphp_func_var_serialize
then the other way around
https://www.w3schools.com/php/phptryit.asp?filename=tryphp_func_var_unserialize

[Kolya]

Thanks, that explains it.

[Kolya]

What I'm doing now to get all user IDs who had checked a custom profile checkbox:

Code Select Expand


$request = $smcFunc['db_query']('', '
SELECT id_member
FROM {db_prefix}log_actions
WHERE action = {string:customfield_cust_inacti}
AND extra LIKE {string:needle}',
array(
'customfield_cust_inacti' => 'customfield_cust_inacti',
'needle' => '%;s:3:"new";i:1;%'
)
);
while ($row = $smcFunc['db_fetch_assoc']($request))
{
$userIDs[] = (int) $row['id_member'];
}
$smcFunc['db_free_result']($request);


This is admittedly a bit of a hack, because I'm never unserializing the value from the extra column and doing a proper check on the resulting array.
Instead I'm simply looking for a string (needle) in the query which proves that the "new" integer in the extra column is "1" (ie the checkbox is set).
This should save me a bunch of code and an additional database query.

[SychO]

Careful there, the custom fields are stored in the themes table, not the log_actions table

The log_actions table contains all sorts of logs including custom profile value changes per user.

The theme table contains custom profile field values where variable names start with cust_

[Kolya]

Oh wow, this makes it so much easier. I was wondering why you would store these values only in a logging table!
I even searched for another table that would hold this data, but apparently not very well.
Thank you a lot for this hint, SychO.

Code Select Expand


$request = $smcFunc['db_query']('', '
SELECT id_member
FROM {db_prefix}themes
WHERE variable = {string:cust_inacti}
AND value = {int:val}',
array(
'cust_inacti' => 'cust_inacti',
'val' => 1
)
);
while ($row = $smcFunc['db_fetch_assoc']($request))
{
$userIDs[] = (int) $row['id_member'];
}
$smcFunc['db_free_result']($request);