how to display product image from wordpress database

Question

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,

    $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?

Solution

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();
}

Comments

[kbuilds]

This surly was not helpful

[Mnyikka]

Hi, you have to select from wp_post the post_id whose post_parent is of type product as well, Then go to wp_postmeta