Charles Tapley Hoyt

The [International Society of Biocuration
(ISB)](https://www.biocuration.org/) partners with the journal
[Database](https://academic.oup.com/database) to get discounts for its
members when they publish there. This means the ISB's executive
committee needs to send a member list to the journal's editor.
Historically, this has been done manually by exporting the list from the
membership management plugin in the ISB Wordpress blog once per month
and emailing it to th This post is about my journey trying to automate
it

# 1. There must be an API for this

Wordpress has a [programmatic
API](https://developer.wordpress.org/rest-api/), and specifically an
endpoint to list
[users](https://developer.wordpress.org/rest-api/reference/users/).

After logging into the ISB's Wordpress site, I was able to list users by
navigating to the endpoint in browser
https://biocuration.org/wp-json/wp/v2/users. Note: this page won't work
for you unless you're on the EC and have admin powers. I wanted to
replicate accessing this page through a Python script, so I was
suggested by the [official Wordpress
documentation](https://developer.wordpress.org/rest-api/using-the-rest-api/authentication/)
to create an application password and then use simple HTTP
authentication. The docs said try this, after replacing the username
with the Wordpress account (for me, that's `cthoyt`{.language-plaintext
.highlighter-rouge}) and the application password, which is a string
with the form of `XXXX XXXX XXXX XXXX XXXX XXXX`{.language-plaintext
.highlighter-rouge}. The documentation suggested doing the following
curl command, which works:

:::: {.language-shell .highlighter-rouge}
::: {.highlight}
``` highlight
curl --user "USERNAME:PASSWORD" https://biocuration.org/wp-json/wp/v2/users
```
:::
::::

If you want to translate this to Python, it looks like this.

:::: {.language-python .highlighter-rouge}
::: {.highlight}
``` highlight
import pystow, base64, requests

username = pystow.get_config("isb", "wordpress_username", raise_on_missing=True)
password = pystow.get_config("isb", "wordpress_password", raise_on_missing=True)

token = base64.b64encode(f"{username}:{password}".encode()).decode("utf-8")

res = requests.get(
    'https://www.biocuration.org/wp-json/wp/v2/users',
    headers={"Authorization": f"Basic {token}", "user-agent": "curl"},
)
```
:::
::::

Luckily, encoding the username/password string with basic authorization
is such a common pattern that the `request`{.language-plaintext
.highlighter-rouge} (and any other modern library) has a nice par for
this.

:::: {.language-python .highlighter-rouge}
::: {.highlight}
``` highlight
import pystow, requests

username = pystow.get_config("isb", "wordpress_username", raise_on_missing=True)
password = pystow.get_config("isb", "wordpress_password", raise_on_missing=True)

res = requests.get(
    'https://www.biocuration.org/wp-json/wp/v2/users',
    auth=(username, password),
    headers={"user-agent": "curl"}
)
```
:::
::::

I burned a ton of time with this because it turns out that Wordpress
blocks both the user agents for the `request`{.language-plaintext
.highlighter-rouge} and `httpx`{.language-plaintext .highlighter-rouge}
libraries. Therefore, you have to explicitly set the
`user-agent`{.language-plaintext .highlighter-rouge} header to something
else, or you get a HTTP 403 forbidden error.

# 2. The API doesn't do what I wanted

It turns out that by default, all the users that never made a post get
filtered out. This is bad since the only users who are making posts on
the ISB are the handful who have or are currently on the executive
committee.

So, obviously, the next step is to start injecting in PHP code to change
how the API works. I found a
[comment](https://github.com/WP-API/WP-API/issues/2300#issuecomment-299202391)
from Tim Jensen, a Wordpress developer, that suggests adding the
following hooks into the Wordpress theme's
`functions.php`{.language-plaintext .highlighter-rouge} file by
navigating to
https://www.biocuration.org/wp-admin/theme-editor.php?file=functions.php&theme=executive:

:::: {.language-php .highlighter-rouge}
::: {.highlight}
``` highlight
function remove_has_published_posts_from_api_user_query($prepared_args, $request)
{
    unset($prepared_args['has_published_posts']);
    return $prepared_args;
}
add_filter('rest_user_query', 'remove_has_published_posts_from_api_user_query', 10, 2);
```
:::
::::

This actually worked! But, it wasn't the end of the story.

# 3. Full names are part of the plugin's metadata

It turns out that the user data model isn't all that comprehensive in
Wordpress. What we really needed was the full name and email address for
each person, and that was stored in an auxillary SQL table created and
managed by the memberships plugin. Note that the plugin lives on top of
Wordpress's first-party users list, and doesn't modify the API access to
the user list.

So obviously, the next step was to add new API endpoints. I learned PHP
at the ripe age of 14 and when I learned some other languages, I never
looked back, so this wasn't so easy. Plus, Wordpress has a huge set of
functions and idioms on top. With a little help from ChatGPT, I was able
to write a new API endpoint to add to
`functions.php`{.language-plaintext .highlighter-rouge} that got users
whose membership expiration date is in the future and joined in the
metadata for their first and last names:

:::: {.language-php .highlighter-rouge}
::: {.highlight}
``` highlight
function export_pmp_members_to_csv() {
    global $wpdb;

    // Set headers to make the response downloadable as a CSV file
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="members_export.csv";');
    header('Pragma: no-cache');
    header('Expires: 0');

    // Open output stream (in-memory file)
    $output = fopen('php://output', 'w');

    if (!$output) {
        return new WP_Error('file_error', 'Unable to generate CSV file.');
    }

    // Add CSV header row
    fputcsv($output, ['id', 'first', 'last', 'email', 'level', 'start', 'ends']);

    // Query to get PMP members and their membership details
    $results = $wpdb->get_results("
        SELECT 
            u.ID, 
            um_first_name.meta_value AS first_name, 
            um_last_name.meta_value AS last_name, 
            u.user_email, ml.name as membership_name, 
            mu.startdate, 
            mu.enddate
        FROM {$wpdb->users} u
        LEFT JOIN {$wpdb->prefix}pmpro_memberships_users mu ON u.ID = mu.user_id
        LEFT JOIN {$wpdb->prefix}pmpro_membership_levels ml ON mu.membership_id = ml.id
        LEFT JOIN {$wpdb->prefix}usermeta um_first_name ON u.ID = um_first_name.user_id 
            AND um_first_name.meta_key = 'first_name'
        LEFT JOIN {$wpdb->prefix}usermeta um_last_name ON u.ID = um_last_name.user_id 
            AND um_last_name.meta_key = 'last_name'
        WHERE mu.membership_id IS NOT NULL
            AND (mu.enddate > CURDATE() OR mu.enddate IS NULL)
    ");

    // Loop through results and write each row to the CSV
    foreach ($results as $row) {
        fputcsv($output, [
            $row->ID, 
            $row->first_name,
            $row->last_name,
            $row->user_email, 
            $row->membership_name, 
            $row->startdate, 
            $row->enddate
        ]);
    }

    // Close the output stream
    fclose($output);

    // Terminate script to ensure no extra output is sent
    exit;
}

function register_pmp_export_route() {
    // the actual path to the endpoint isn't important here
    register_rest_route('path/to/endpoint/', '/export-members', [
        'methods' => 'GET',
        'callback' => 'export_pmp_members_to_csv'
        'permission_callback' => function() {
            // Optionally, restrict access to logged-in users with specific capabilities
            return current_user_can('manage_options'); // Restrict to admin users
        }
    ]);
}
add_action('rest_api_init', 'register_pmp_export_route');
```
:::
::::

# 4. Automate it

Why stop at just being able to export the sheet? I wanted to go another
mile and make sure that Wordpress sends an email to the right person at
the journal on a monthly basis.

:::: {.language-php .highlighter-rouge}
::: {.highlight}
``` highlight
/ Function to generate and send the CSV via email
function send_pmp_members_csv_via_email() {
    global $wpdb;

    // Create a temporary file to store the CSV
    $tmp_file = tempnam(sys_get_temp_dir(), 'pmp_csv_') . '.csv';
    $output = fopen($tmp_file, 'w');

    if (!$output) {
        return; // Handle error appropriately
    }

    // Add CSV header row
    fputcsv($output, ['ID', 'Username', 'First Name', 'Last Name', 'Email', 'Membership Level Name', 'Joined Date', 'Expires Date']);

    // Query to get PMP members and their membership details
    $results = $wpdb->get_results("
        SELECT u.ID, 
            u.user_login, 
            um_first_name.meta_value AS first_name, 
            um_last_name.meta_value AS last_name,
            u.user_email, 
            ml.name as membership_name
            mu.startdate,
            mu.enddate,
        FROM {$wpdb->users} u
        LEFT JOIN {$wpdb->prefix}pmpro_memberships_users mu ON u.ID = mu.user_id
        LEFT JOIN {$wpdb->prefix}pmpro_membership_levels ml ON mu.membership_id = ml.id
        LEFT JOIN {$wpdb->prefix}usermeta um_first_name ON u.ID = um_first_name.user_id 
            AND um_first_name.meta_key = 'first_name'
        LEFT JOIN {$wpdb->prefix}usermeta um_last_name ON u.ID = um_last_name.user_id 
            AND um_last_name.meta_key = 'last_name'
        WHERE mu.membership_id IS NOT NULL
        AND (mu.enddate > CURDATE() OR mu.enddate IS NULL)
    ");

    // Write data to CSV
    foreach ($results as $row) {
        fputcsv($output, [
            $row->ID, 
            $row->user_login, 
            $row->first_name,
            $row->last_name,
            $row->user_email, 
            $row->membership_name, 
            $row->startdate, 
            $row->enddate
        ]);
    }

    // Close the file
    fclose($output);

    // Prepare email
    $to = ''; // Replace with recipient email
    $subject = 'ISB Monthly Members Report';
    $message = 'Please find the attached CSV file containing the list of active members of the International Society of Biocuration as of today.';
    $headers = array('Content-Type: text/csv; charset=UTF-8');

    // Send email with attachment
    wp_mail($to, $subject, $message, $headers, $tmp_file);

    // Clean up temporary file
    unlink($tmp_file);
}

// Schedule the cron job to run monthly
function schedule_monthly_csv_email() {
    if (!wp_next_scheduled('monthly_pmp_csv_email')) {
        wp_schedule_event(time(), 'monthly', 'monthly_pmp_csv_email');
    }
}
add_action('wp', 'schedule_monthly_csv_email');

// Hook the function to the cron event
add_action('monthly_pmp_csv_email', 'send_pmp_members_csv_via_email');
```
:::
::::

Hopefully I don't have to write anymore PHP for a long time :)


The International Society of Biocuration (ISB) partners with the journal Database to get discounts for its members when they publish there. This means the ISB’s executive committee needs to send a member list to the journal’s editor.

Programmatic Access to a Wordpress User List

Stephen Royle

If I struggle to find the answer to something, it must be worth a post
to help out others. So, here's where to find the `php.ini` file on a
standard WordPress install on Raspberry Pi.

I installed a LAMP stack and WordPress on a Raspberry Pi following these
excellent
[instructions](https://raspberrytips.com/wordpress-on-raspberry-pi/).
The aim was to import our lab's ELN onto a fresh WordPress install. To
do this, go to the dashboard of your old WordPress and select Tools \>
Export from the sidebar and select All. This will download an XML file
that can be used to import posts, comments, media etc. into another
WordPress installation. After setting up my new WordPress on the Pi, I
chose Tools \> Import... and...

## The problem {#the-problem .wp-block-heading}

On a fresh install, the limit for the XML import is 2MB. So, a large XML
file will not import. The following error pops up.

::: {.wp-block-syntaxhighlighter-code}
``` {.brush: .plain; .title: .; .notranslate title=""}
File is empty. Please upload something more substantial. This error could also be caused by uploads being disabled in your php.ini or by post_max_size being defined as smaller than upload_max_filesize in php.ini.
```
:::

It's just a matter of editing the `php.ini` file. **But where is it?**

## The answer {#the-answer .wp-block-heading}

It's in `/etc/php/7.3/apache2/php.ini`

To edit it do

::: {.wp-block-syntaxhighlighter-code}
``` {.brush: .bash; .title: .; .notranslate title=""}
sudo nano /etc/php/7.3/apache2/php.ini
```
:::

and then ctrl+W to find the parameters that need changing, I went for:

::: {.wp-block-syntaxhighlighter-code}
``` {.brush: .plain; .title: .; .notranslate title=""}
upload_max_filesize = 128M
post_max_size = 128M
```
:::

ctrl+X and Y to save. Then you need to restart apache2 with
`sudo service apache2 restart` or perform a reboot.

## The wrong answers {#the-wrong-answers .wp-block-heading}

1.  There is no `php.ini` file created by a WordPress installationand
    there are plenty of posts out there which tell you to create one in
    `wp-admin` or `wp-content` or even in root folder
    (`/var/www/html/`). This did not work for me.
2.  Locate `php.ini` on the command line. Over ssh, you can do
    `php -i | grep ini` and this will tell you which `php.ini` file is
    being used *on the command line*. I got `/etc/php/7.3/cli/php.ini`
    but editing this did not work.
3.  Many answers on the web refer to other types of installations. My
    answer above works for WordPress on a Raspberry Pi LAMP stack. If
    you are running windows or at a webhost, the answer maybe different.

How did I find the php.ini file? I made a file called `temp.php` in
`/var/www/html/` that said

::: {.wp-block-syntaxhighlighter-code}
``` {.brush: .php; .title: .; .notranslate title=""}
<?php phpinfo(); ?>
```
:::

And then in a browser, I went to `http://myipaddress/temp.php` where
myipaddress is the ip of my Raspberry Pi that runs WordPress. The
browser shows a table with lots of info. Look for **Loaded Configuration
File** and you will see `/etc/php/7.3/apache2/php.ini` further down you
will see the attributes that you need to change. As you update the file
and restart the service, you can refresh this page to check your
settings have taken hold.

---

This post is part of a [series of
tips](https://quantixed.org/tag/tftb/).


If I struggle to find the answer to something, it must be worth a post to help out others. So, here’s where to find the php.ini file on a standard WordPress install on Raspberry Pi.  I installed a LAMP stack and WordPress on a Raspberry Pi following these excellent instructions. The aim was to import our lab’s ELN onto a fresh WordPress install.

Tips from the Blog XV: locating the php.ini file for WordPress on Raspberry Pi

Björn Brembs

After [Mike
Taylor](https://svpow.com/ "Sauropod vertebra picture of the week"){target="_blank"}
kept complaining about how horrible my old [e107
platform](https://e107.org) was (which obviously it isn't: I love this
CMS), I finally gave in and moved everything over to a new home. The
entire [brembs.net](http://brembs.net "brembs.net") domain with all its
subdomains is no longer hosted in the good old US of A, where its home
was since about 2000, when I moved there myself. After almost ten years
back in Germany, I've now brought my online presence back home as well.
As of this post, all my blogging will be done on WordPress.

The 912 old posts from the last ten years are still accessible, of
course, at
[blogarchive.brembs.net](http://blogarchive.brembs.net "blogarchive"). I
should have everything set up in a way that even all the original links
should be redirected to the archive, such that nobody should really
notice much more than a slight difference in appearance.

I'll now slowly adjust all the content and design on this place, until
the required functionality and look-and-feel is in place. Let me know if
there is anything you'd like to see here in terms of plug-ins or other
functionality, or if any of the design is off-putting. Also, if you
will, could you test the commenting system and let me know if anything,
what needs to be modified/adjusted?


After Mike Taylor kept complaining about how horrible my old e107 platform was (which obviously it isn’t: I love this CMS), I finally gave in and moved everything over to a new home. The entire brembs.net domain with all its subdomains is no longer hosted in the good old US of A, where its home was since about 2000, when I moved there myself. After almost ten years back in Germany, I’ve now brought my online presence back home as well.

Messaggi di Rogue Scholar

Programmatic Access to a Wordpress User List

Tips from the Blog XV: locating the php.ini file for WordPress on Raspberry Pi

A new home for bjoern.brembs.blog