Retrieving data from a WordPress site can be a nightmare. Site data could reside in several places. Pulling from these various sources isn’t easy.
Websites that feature eCommerce or memberships are prime examples. Each plugin may store data in a different spot. Some create separate database tables. Others use the default WordPress locales.
Export plugins exist that help in some – but not all – circumstances. For example, you might be able to export customer orders. However, grabbing additional data not directly related to those orders may not be possible.
Writing a custom query is another option. But you’ll need to know your way around PHP and MySQL. Not everyone has the expertise to do so.
Artificial intelligence (AI) can help. As I discovered, tools like ChatGPT can do the dirty work. It simplified the process and saved me loads of time. Perhaps it saved my sanity as well.
Here’s how to use AI to get what you need from the WordPress database.
Before We Get Started
There are a few items we should mention before starting:
- Back up Your Site’s Database or Create a Local Copy of Your Site
None of what we’re doing here should hurt your database. However, create a fresh backup – just in case. - You can also create a local copy of your site for experimenting. That enables you to work without the risk of harming your production website.
- Have Access to a ChatGPT Account
We’re using ChatGPT for this demo. Therefore, you’ll want to have access to a free account. You can follow along and experiment.
Other AI tools, such as Google Gemini, may also work. The point is to use an AI tool that will generate code.
Also, a warning: Use caution when generating code. AI tools can make mistakes! Review the code before using it.
What Are You Doing with the Data?
There are a couple of things you can do with the data you retrieve:
- Display the data on the front end of your site;
- Download a CSV file that contains the data;
You’ll want to determine this beforehand. The process is a bit different depending on your choice.
Oh, and you’ll also need access to your site’s database. Access to your theme is required for displaying data on the front end.
Retrieving User Data from Different Plugins
Now, onto our experiment. Let’s take a common sticking point and try to make sense of it.
Our fictional website has two purposes. We sell various products with WooCommerce. Customers come to our site, create accounts, and order from us.
On the other side of the coin, we also sell memberships. Our members pay for access to exclusive content. We use the Restrict Content Pro plugin to power this feature.
Both WooCommerce and Restrict Content Pro have reporting features. For example, we can find out how many members we have. Or the number of WooCommerce customers.
But what if we want to combine this data? There’s no straightforward way to do this. So, let’s see if AI can help.
Step 1: Find the Data You Want
The first step is to determine what data you want to retrieve. Then, you’ll need to find the data inside the WordPress database.
Our goal is to create a CSV export file that includes users who are:
- A customer in our WooCommerce shop AND…
- Have an active membership via Restrict Content Pro;
Data isn’t always easy to locate. In some cases, you may need to reference plugin documentation. Or contact their technical support. A thorough database search can help if all else fails.
In our case, WooCommerce and Restrict Content Pro create custom database tables. That will make our job a bit easier.
- WooCommerce table names use the ‘
wp_wc‘ prefix. - Restrict Content Pro table names use the ‘
wp_rcp‘ prefix.
We’ll also want to bring in data from the ‘wp_users‘ and ‘wp_usermeta‘ tables. That allows us to tie customer and membership data to specific users.
With that in mind, we’ll look for the following information:
| Data Type | Database Table | Column |
|---|---|---|
| User ID | wp_users | ID |
| First Name | wp_usermeta | Meta Key: first_name |
| Last Name | wp_usermeta | Meta Key: last_name |
| Customer ID | wp_wc_customer_lookup | customer_id |
| Membership Status | wp_rcp_memberships | status |
| Membership Level ID | wp_rcp_memberships | object_id |
Step 2: Ask ChatGPT to Create a Database Query
Next, we’ll ask ChatGPT for some help. We’ll want to provide a clear explanation of the task.
Here’s the prompt I used:
I'd like to create a MySQL database query for a WordPress website. I only want to retrieve users who have both a WooCommerce Customer ID and a membership via Restrict Content Pro.
Here is the data I'd like to retrieve:
From the wp_users database table:
ID
From the wp_usermeta database table:
Meta Key: first_name
Meta Key: last_name
From the wp_wc_customer_lookup database table:
customer_id
From the wp_rcp_memberships database table:
status
object_idStep 3: Get the Response
ChatGPT’s response included a code snippet and a detailed explanation of it (view the full response). The snippet joins multiple database tables together to produce a single report.
So, how do we see if it works? We can run the query in a tool that connects to the site’s database. I’m using a local website that includes AdminerEvo.
You could also use phpMyAdmin, MySQL Workbench, or similar apps. ChatGPT can even provide instructions for whatever app you’re using.
I’ve pasted the query into the SQL Command screen inside AdminerEvo.
Step 4: Study the Results
The results are in! Our fictional website has 198 users. 15 of them have a paid membership. There are also 200 product orders.
So, how many are WooCommerce customers and Restrict Content Pro members? A grand total of seven.
The query results returned the columns we requested. From here, we can tweak things further or export the data.
Going Further with AI
Our example query may seem simple. However, bringing together data from multiple plugins is challenging. A custom solution is often the only solution.
I’m no coding expert. Thus, figuring out how to get data became frustrating. Thankfully, AI has proven to be a terrific tool for the job.
It’s one of the areas where this technology shines. Many of us struggle to write this sort of code on our own. Writing a prompt describing what we want isn’t so bad, however.
There are also opportunities to take things further. We could change our prompt to display data on a WordPress page. Or return data relevant to the currently logged-in user.
A working knowledge of PHP and MySQL can take you a long way. So, experiment and see what you can accomplish. And be sure to add up all the time you save in the process!
The post Use ChatGPT to Export Data from a WordPress Database appeared first on Speckyboy Design Magazine.
0 Commentaires