how to display product image from wordpress database

3 Views

I want to display woocommerce product image from wordpress mysql database. for example, if a product has id 11 and its image, attribute, price etc are stored in wp-postmeta table in the database. The image is stored in someother id and the id value is given in image of the product 10. Now how to fetch image from the table.

for single product,

Read More
    $img = mysql_query("SELECT meta_value FROM wp_postmeta WHERE meta_key ='_thumbnail_id' AND post_id='11'",$con);
    $res= mysql_fetch_array($img);
    $img2 = mysql_query("SELECT meta_value FROM wp_postmeta WHERE meta_key ='_wp_attached_file' AND post_id='".$res['meta_value']."'",$con);
    $res2= mysql_fetch_array($img2);
    echo $res2['metavalue'];

displays the image of single product, but how to fetch data from mysql database for multiple products?

Post Views: 3

Related posts

Leave a Reply

4 comments

  1. I made something like this for getting images for all woocommerce products

    $query = "SELECT * FROM wp_posts WHERE post_status = 'publish' AND post_type LIKE 'product%'";

if ($result = $mysqli->query($query)) {
    printf("<br>Select returned %d rows.n", $result->num_rows);
    echo "<table>";
    echo "<thead>";
    echo "<tr>";
    echo "<td>ID</td><td>Termék neve</td><td>Termék linkje</td><td>Termék kép</td>";
    echo "</tr>"; 
    echo "<thead>";
    while ($row = $result->fetch_assoc()) {
        $post_id = $row["ID"];

        echo "<tr>";
        echo "<td>".$post_id."</td>";
        echo "<td>".$row["post_title"]."</td>";
        echo "<td>".$row["guid"]."</td>";
        $query_img = "SELECT meta_value FROM wp_postmeta WHERE meta_key ='_thumbnail_id' AND post_id = $post_id";
        $result_img = $mysqli->query($query_img);
        $img = $result_img -> fetch_assoc();
        $query_img_2 = "SELECT meta_value FROM wp_postmeta WHERE meta_key ='_wp_attached_file' AND post_id = ".$img['meta_value']."";
        $result_img_2 = $mysqli->query($query_img_2);
        $img_2 = $result_img_2 -> fetch_assoc();
        echo "<td>".$img_2["meta_value"]."</td>";
        echo "</tr>";
    }       
    echo "</table>";

    /* free result set */
    $result->close();
}

  2.  //  to get product list category wise in place of $cat you can paste you categoy id

$sql="SELECT wp.* ,wpml.* , am.meta_value from wp_posts wp 
        LEFT JOIN wp_postmeta pm ON pm.post_id = wp.ID AND pm.meta_key = '_thumbnail_id'
        LEFT JOIN wp_postmeta am ON  am.post_id = pm.meta_value AND  am.meta_key = '_wp_attached_file'
        left join wp_wc_product_meta_lookup wpml on wpml.product_id = wp.ID
        where wp.post_status ='publish' and wp.ID In (select object_id  from wp_term_relationships where term_taxonomy_id = $cat_id)";

    // to get the multiple images of one product , post_paent value should be the produt id

    select guid,post_parent,ID,post_type from wp_posts where post_type="attachment" and post_parent="1401";

  3. SHOW ALL PRODUCTS WITH PICTURE AND GALLERY PICTURES

<style>
table, th, td {
  border: 1px solid;
}
</style>
<?php
$servername = "localhost";
$username = "";
$password = "";
$dbname = "";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}



$query = "SELECT * FROM wp_posts WHERE post_status = 'publish' AND  post_type = 'product' ";
//$result = $conn->query($sql);

if ($result = $conn->query($query))
    {
    printf("<br>Select returned %d rows.n", $result->num_rows);
    echo "<table>";
    echo "<thead>";
    echo "<tr>";
    echo "<td>ID</td>
    <td>Title Products</td>
    <td>Link Pictures</td>
    <td>Link Gallery</td>";
    echo "</tr>"; 
    echo "<thead>";
    $count=1;
    while ($row = $result->fetch_assoc()) {
        $post_id = $row["ID"];

        echo "<tr>";
        echo "<td>".$post_id."</td>";
        echo "<td>".$row["post_title"]."</td>";
        
        $query_img = "SELECT meta_value FROM wp_postmeta WHERE meta_key ='_thumbnail_id' AND post_id = $post_id";
        $result_img = $conn->query($query_img);
        if($result_img->num_rows==0) { echo "<td>NO</td>";} else {
        $img = $result_img -> fetch_assoc();
        $query_img_2 = "SELECT meta_value FROM wp_postmeta WHERE meta_key ='_wp_attached_file' AND post_id = ".$img['meta_value']."";
        $result_img_2 = $conn->query($query_img_2);
        $img_2 = $result_img_2 -> fetch_assoc();
        echo "<td><a href='https://yoursite/wp-content/uploads/".$img_2["meta_value"]."' target='_blank'>link</a></td>";
        }
        
        
        $links='';
        $query_gallery = "SELECT meta_value FROM wp_postmeta WHERE meta_key='_product_image_gallery' AND post_id =".$post_id."";
        $result_gallery = $conn->query($query_gallery);
        if($result_gallery->num_rows==0) { echo "<td>NO GALLERY</td>";} else {
            $gll = $result_gallery -> fetch_assoc();
            $arr = explode(',',$gll['meta_value']);
            $counts=1;
            foreach ($arr as $ar)
            {
                $query_select = "SELECT meta_value FROM wp_postmeta WHERE meta_key='_wp_attached_file' AND post_id=".$ar."";
                $gallery_select = $conn->query($query_select);
                if($gallery_select->num_rows==0) { } else 
                {
                    $sll = $gallery_select -> fetch_assoc();
                    $links .= "  <a href='https://yousite/wp-content/uploads/".$sll['meta_value']."' target='_blank'>LINK $counts</a>";
                }
            $counts++;
            }
            echo "<td>$links</td>";
        }
        
        echo "</tr>";
        $count++;
    }       
    echo "</table>";

    /* free result set */
    $result->close();
}




?>