Question 308221 in Wordpress Question posted in
The official Wordpress documentation can be found here.

Fetching Data from WordPress Database Using Custom PHP

Without using WordPress or writing a plugin based on WordPress, I want to be able to fetch data from WordPress database tables and also insert into it. I will be writing the the code in either procedural or OOP but it’s definitely outside of the WordPress framework.

Right now, I am able to fetch from wp_users table but trying to join wp_users with wp_usermeta is not working right. I wanted to display data relating to a particular user in the usermeta table but fetching is a bit difficuly since wp_usermeta table uses meta_key and meta_value to save these data. So, how do you fetch first_name and last_name for a user when table columns are meta_key and meta_value?

I have tried using INNER JOIN but the result is not right. I might just have one particular user on two pages (table pagination) before the next user.

I would like every user’s data to be on the same row which will also help for updating later.

Tags:

2

Answers


  1. 0

    Assuming you have a user ID and you want to retrieve the first_name and last_name for that user, you can use a query like the following:

    SELECT
  u.ID,
  u.user_login,
  u.user_email,
  MAX(CASE WHEN m.meta_key = 'first_name' THEN m.meta_value END) AS first_name,
  MAX(CASE WHEN m.meta_key = 'last_name' THEN m.meta_value END) AS last_name
FROM
  wp_users u
LEFT JOIN
  wp_usermeta m ON u.ID = m.user_id
WHERE
  u.ID = :user_id
GROUP BY
  u.ID, u.user_login, u.user_email;

    Note: Replace ‘:user_id’ with the actual user ID you want to fetch data for. Also, ensure that you have the correct table prefix in your queries, as it may vary based on your WordPress installation.

    Login or Signup to reply.
  2. 0

    To fetch first_name and last_name for a user from the WordPress database tables wp_users and wp_usermeta, you need to perform a SQL query that joins these tables correctly. Since the wp_usermeta table stores user metadata using meta_key and meta_value columns, you’ll need to use a conditional join to fetch the specific meta_value for first_name and last_name related to a particular user.

    Here’s an example of how you can achieve this:

    SELECT u.ID, u.user_login, 
       MAX(CASE WHEN um1.meta_key = 'first_name' THEN um1.meta_value END) AS first_name,
       MAX(CASE WHEN um1.meta_key = 'last_name' THEN um1.meta_value END) AS last_name
FROM wp_users u
LEFT JOIN wp_usermeta um1 ON u.ID = um1.user_id
WHERE um1.meta_key IN ('first_name', 'last_name')
GROUP BY u.ID, u.user_login;

    Explanation:

    1. We start with the wp_users table (u) and use a LEFT JOIN to connect it with the wp_usermeta table (um1) using the user_id as the common field.

    2. In the SELECT statement, we use conditional aggregation (MAX with CASE statements) to fetch the meta_value for first_name and last_name based on the meta_key.

    3. We use a WHERE clause to filter the rows in wp_usermeta where meta_key is either ‘first_name’ or ‘last_name’.

    4. Finally, we GROUP BY ID and user_login from the wp_users table to ensure that each user’s data is on the same row.

    This query will return a result set with one row for each user, with their first_name and last_name displayed as separate columns.

    You can use this SQL query within your PHP code (either procedural or OOP) to fetch the data from the WordPress database without relying on the WordPress framework.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search