Ecommerce Shopify WordPress Discussion

Fetching Data from WordPress Database Using Custom PHP [closed]

Closed. This question needs details or clarity. It is not currently accepting answers. Want to improve this question? Add details and clarify the problem by editing this post. Closed 9 days ago. This post was edited and submitted for review 9 days ago. Improve this question 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? Current Query: SELECT a.*, b1.meta_value AS first_name, b1.*, b2.meta_value AS last_name, b2.* FROM wp_users a INNER JOIN wp_usermeta b1 ON b1.user_id = a.ID AND b1.meta_key = 'first_name' INNER JOIN wp_usermeta b2 ON b2.user_id = a.ID AND b2.meta_key = 'last_name' WHERE (b1.meta_value LIKE '%_%' OR b2.meta_value LIKE '%_%' OR a.user_email LIKE '%_%') Looping: <tr> <td><?= $row['ID']; ?></td> <td><?= $row['user_login']; ?></td> <td><?= $row['user_email']; ?></td> <td><?= $row['first_name']; ?></td> <td><?= $row['last_name']; ?></td> <td><?= $row['phone']; ?></td> <td> <?php if ( $row['user_id_verified'] == 1 ){ echo "Yes"; } else { echo "No"; } ?> </td> <td><?= date('M j, Y h:i A', strtotime($row['user_registered'])); ?></td> </tr> Output: 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.
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; To get all information for the user: Assuming you have a user ID and you want to retrieve following details: ID Username Email First name Last name Phone Roles Date You can use this updated query like the following: SELECT u.ID, u.user_login AS Username, u.user_email AS Email, MAX(CASE WHEN m1.meta_key = 'first_name' THEN m1.meta_value END) AS First_name, MAX(CASE WHEN m1.meta_key = 'last_name' THEN m1.meta_value END) AS Last_name, MAX(CASE WHEN m2.meta_key = 'phone' THEN m2.meta_value END) AS Phone, CASE WHEN r.meta_value LIKE '%administrator%' THEN 'Administrator' WHEN r.meta_value LIKE '%editor%' THEN 'Editor' WHEN r.meta_value LIKE '%author%' THEN 'Author' WHEN r.meta_value LIKE '%contributor%' THEN 'Contributor' WHEN r.meta_value LIKE '%subscriber%' THEN 'Subscriber' ELSE 'Unknown' END AS Status, u.user_registered AS Date FROM wp_users u LEFT JOIN wp_usermeta m1 ON u.ID = m1.user_id LEFT JOIN wp_usermeta m2 ON u.ID = m2.user_id LEFT JOIN wp_usermeta r ON u.ID = r.user_id AND r.meta_key = 'wp_capabilities' WHERE u.ID = :user_id GROUP BY u.ID, u.user_login, u.user_email, u.user_registered; 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.

December 30, 2023

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: 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. 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. We use a WHERE clause to filter the rows in wp_usermeta where meta_key is either 'first_name' or 'last_name'. 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.

December 30, 2023

TurboCommerce make the better internet purchasing globaly

Turbo Multi-language Translator

Make the better internet purchasing globaly

Turbosify SEO Speed Booster

5.0 (7) Free plan available
Get better conversions by improving store loading speed Installed

Turbo Multi-language Chat - AI Customer service in one hand

TurboCommerce make the better internet purchasing globaly
Our products

The help you need, when you need it

App by Turbo Engine

3 apps • 5.0 average rating

Turbosify Speed Booster

5.0 (7)
Get better conversions by optimizing shopify store Google page speed Installed

Turbosify Translator for Wordpress Woocommerce

5.0 (74) Free Wordpress Woocommerce Plugin
Translate your wordpress website to multiple language within 1 click, no configuration needed, no No technical required

Grow your business here

Whether you want to sell products down the street or around the world, we have all the tools you need.