SQL从数据库中获取Woocommerce产品缩略图

Question

I've been searching through the past two days to find out where exactly could the Woocommerce products thumbnails (images) texts and URL's are stored inside the database tables, but still cannot figure this out!

I'm in a situation where I must use SQL queries to move the products data into another tables, and I have to implement the process from my phpmyadmin panel.

I already searched the wp_posts and wp_postmeta tables, wp_posts contains a guide column for the url to the product which post_type like 'product%', So far I know that in general the post stores it's thumbnalis link inside one of the posts with a type of attachment , while I need the posts with a post_type of product or like so.

Hope I can find some answers here, Thanks.

Answer 1

You can use the Wordpress WP_Query to get the thumbnail image of product.

$args = array(
        'post_type'      => 'product',   //post type of product
        'posts_per_page' => -1
    );

    $query= new WP_Query( $args );

    while ( $query->have_posts() ) : $query->the_post();
        global $product;
      //woocommerce_get_product_thumbnail is use to get the product thumbnail link
        echo '<br /><a href="'.get_permalink().'">' . woocommerce_get_product_thumbnail().' '.get_the_title().'</a>';
    endwhile;

    wp_reset_query();

Solution 2- using the custom query as you mention in query.

//Using custom query get the details from wp_postmeta and wp_posts
//_wp_attached_file - meta key for image
$querystr = "SELECT p.*, pm2.meta_value as product_image FROM wp_posts p LEFT JOIN
wp_postmeta pm ON (
pm.post_id = p.id
AND pm.meta_value IS NOT NULL
AND pm.meta_key = '_thumbnail_id'
)
LEFT JOIN wp_postmeta pm2 ON (pm.meta_value = pm2.post_id AND pm2.meta_key = '_wp_attached_file'
AND pm2.meta_value IS NOT NULL) WHERE p.post_status='publish' AND p.post_type='product'
ORDER BY p.post_date DESC";



$upload_dir   = wp_upload_dir();
//get wp_upload url
$wp_upload_url = $upload_dir['baseurl'];
$pageposts = $wpdb->get_results($querystr, OBJECT);
foreach ($pageposts as $post){

        echo "<br /><a href='".$post->guid."'><img src='". $wp_upload_url.'/'.$post->product_image."'>".$post->post_title."</a>";

}