plugins – How to Join wp_posts & wp_postmeta table using custom query

Question

I am trying to create pagination for product details with the help of wp_posts & wp_postmeta table for my woocommerce custom code for admin area. There are 85 products in shop & i want to display list of products with its meta values.

At present I am using the following query for joining the tables –

SELECT * FROM wp_posts as wpp,wp_postmeta as wppm where wpp.post_type="product" AND  wpp.post_status="publish" AND wpp.ID = wppm.post_id LIMIT $offset, $limit   

where value of $offset & $limit is set by pagination code.
Presently The above query returned all possible combination of Posts related to both tables (almost 1222 rows). But I want to display only 85 products with some of it’s meta values.
I am trying to display my product list like this – [Please ignore star sign in image] enter image description here

Can some one please help me in the query for this.

I am also sharing my pagination logic

 /**** Pagination Logic  */
$pagenum = isset( $_GET['pagenum'] ) ? absint( $_GET['pagenum'] ) : 1;
$limit = 10; // number of rows in page
$offset = ( $pagenum - 1 ) * $limit;
$total = $wpdb->get_var( "SELECT COUNT('ID') FROM wp_posts WHERE post_type="product"" );
$num_of_pages = ceil( $total / $limit );

$sql_product = $wpdb->get_results( "SELECT * FROM wp_posts as wpp,wp_postmeta as wppm where 
wpp.post_type="product" AND
wpp.post_status="publish" AND 
wpp.ID = wppm.post_id LIMIT $offset, $limit");


foreach ( $sql_product as $product ){
    echo '<tr><td>'.$i++.'</td>';
    echo '<td> '  . $product->post_type . '</td>';  // Product type
    echo '<td>'    . $product->ID . '</td>';    // Product ID
    echo '<td>' . $product->post_title  . '</td>'; // Product title
    echo '<td>' . $product->ID.'</td>';          // Product price
    echo '<td> <a href= #> History </a>'; 
    echo '</tr>';
}

echo "</table>";
echo '<div class="tablenav bottom"><div class="alignleft actions bulkactions">';
$page_links = paginate_links( array(
    'base' => add_query_arg( 'pagenum', '%#%' ),
    'format' => '',
    'prev_text' => __( '«', 'text-domain' ),
    'next_text' => __( '»', 'text-domain' ),
    'total' => $num_of_pages,
    'current' => $pagenum
      ) );
    if ( $page_links ) {
        echo '<div class="tablenav"><div class="tablenav-pages" style="margin: 1em 0">' . $page_links . '</div></div>';
    }
echo '</div><br class="clear"></div>';

0
Siddharth Dutt Choubey 1 week 2021-10-12T00:16:04-05:00 0 Answers 0 views 0

Leave an answer

Browse
Browse