How to display prices in a WooCommerce product table using a custom query?

To display the product prices in a custom WooCommerce product table using a custom query, you can use the WPDB class to execute your custom SQL query and fetch the required product data. Here's a step-by-step guide on how to achieve this:

Step 1: Create a custom WordPress page template

Create a new PHP file in your active theme folder (e.g., codefynd-custom-product-table.php). This file will act as a custom template for displaying the product table.

Step 2: Write the custom query to fetch product data

In the codefynd-custom-product-table.php file, write your custom SQL query using the WPDB class to fetch the product data, including the price.

Template Name: Custom Product Table


global $wpdb;
$products = $wpdb->get_results("SELECT ID, post_title, meta_value AS price FROM $wpdb->posts
    INNER JOIN $wpdb->postmeta ON $wpdb->posts.ID = $wpdb->postmeta.post_id
    WHERE post_type = 'product'
    AND post_status = 'publish'
    AND meta_key = '_price'
    ORDER BY post_title ASC");

        <th>Product Name</th>
    <?php foreach ($products as $product) : ?>
            <td><?php echo $product->post_title; ?></td>
            <td><?php echo wc_price($product->price); ?></td>
    <?php endforeach; ?>

<?php get_footer(); ?>

In this example, the custom query fetches the product ID, title, and price from the wp_posts and wp_postmeta tables, respectively. We are joining the wp_posts and wp_postmeta tables based on the product ID (post_id) and the meta key (_price). Adjust the query as needed based on your specific requirements.

Step 3: Create a WordPress page and apply the custom template

In your WordPress admin dashboard, create a new page (or edit an existing one) and set the template to "Custom Product Table" (the name you used in the comment Template Name in the custom template file).

Step 4: View the product table

Visit the newly created page on the front end, and you should see the product table with product names and their respective prices.

Please note that this is a basic example, and you can customize the query further to include additional product data or add more features to the product table as per your needs. Additionally, ensure that your custom queries are optimized and safe to prevent any security vulnerabilities. Always make sure to backup your data and test thoroughly before implementing changes on a live website.

Muhammad Alfaiz
Alfaiz 7 months ago
Notify of
Inline Feedbacks
View all comments
Would love your thoughts, please comment.x