Wordpress Question posted in
The official Wordpress documentation can be found here.

WordPress – woocommerce show two postmeta table values in my custom page page like sku, price, quantity of product variations

In woocommerce how make to show two postmeta table values in my custom page php like quantity, sku, price.

database table.
database table

help for this issue

resume:
woocommerce show two postmeta table values ​​namely quantity, price, sku of the variable products

my code php mysql show i try to fetch data

<!DOCTYPE html>
<html>
<head>
<title></title>
</head>
<body>
    
    <br>
    <div>
        <table border="1">
            <thead>
                <th>ID</th>
                <th>codice</th>
                <th>nome prodotto</th>
                <th>quantit</th>
                    <th>Prezzo al pubblico</th>
                    <th>Prezzo di acquisto</th>
                    <th>Categoria</th>
                        <th>Produttore</th>
                    <th>Data</th>
                <th></th>
            </thead>
            <tbody>
                <?php
                    include('conn.php');
                    $interroga=mysqli_query($connettere,"




SELECT  *,
            
            (IF(pm.meta_key LIKE '_sku', pm.meta_value, NULL)) AS skuu
    
            
FROM wp_posts wp
INNER JOIN wp_term_relationships r ON wp.ID = r.object_id
INNER JOIN wp_term_taxonomy tt ON r.term_taxonomy_id = tt.term_taxonomy_id 



INNER JOIN wp_terms t ON t.term_id = tt.term_id 


INNER JOIN wp_postmeta pm ON pm.post_id=wp.ID
INNER JOIN wp_posts wpv ON wp.ID= wpv.post_parent
INNER JOIN wp_wc_product_meta_lookup wl ON wl.product_id=wpv.ID and pm.meta_key IN ('_stock','instock','_sku','_price')  

and  wpv.post_type IN ('product_variation','product') 
                    
                    GROUP BY wpv.post_title 
                    ");
                    while($row=mysqli_fetch_array($interroga)){
                        ?>
                        <tr>
                                <td><?php echo $row['product_id']; ?></td>
                            <td><?php echo $row['skuu']; ?></td>
                            <td><?php echo $row['post_title']; ?></td>
                            <td><?php echo $row['skuu']; ?></td>
                            <td><?php echo $row['min_price']; ?></td>
                            <td><?php echo $row['meta_value']; ?></td>
                            <td><?php echo $row['name']; ?></td>
                                <td><?php echo $row['meta_key']; ?></td>
                            <td><?php echo $row['post_date']; ?></td>
                            
                        </tr>
                        <?php
                    }
                ?>
            </tbody>
        </table>
    </div>
    
    
    
    


</body>
</html>
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I saw that your script you sent me above works. What if I want to change the table values? I'm doing these tests. See codes below edit.php

            <?php
    include('conn.php');
    $id=$_GET['ID'];
     $codicearticolo = mysqli_real_escape_string($conn,$_POST['sku']);
      $nome = mysqli_real_escape_string($conn,$_POST['post_title']);
    $query=mysqli_query($conn,"SELECT 
            p.ID,
            p.post_title,
            MAX(CASE WHEN pm.meta_key = '_sku' THEN pm.meta_value END) AS sku,
            MAX(CASE WHEN pm.meta_key = '_price' THEN pm.meta_value END) AS price,
            MAX(CASE WHEN pm.meta_key = '_stock' THEN pm.meta_value END) AS quantity
        FROM wp_posts p
        LEFT JOIN wp_postmeta pm ON p.ID = pm.post_id
                    
                    where   ID='$id' ");
    $row=mysqli_fetch_array($query);
?>
<!DOCTYPE html>
<html>
<head>
<title>Prova</title>
</head>
<body>
    <h2>Modifica</h2>
    <form method="POST" action="update.php?ID=<?php echo $id; ?>">
        <label>codicearticolo:</label><input type="text" value="<?php echo $row['sku']; ?>" name="sku">
        <label>nome:</label><input type="text" value="<?php echo $row['post_title']; ?>" name="post_title">
        <input type="submit" name="submit">
        <a href="index.php">Ritorna alla home</a>
    </form>
</body>
</html>


update.php

update.php
<?php
include('conn.php');
$id=$_GET['ID'];

$codicearticolo=$_POST['sku'];
$nome=$_POST['post_title'];

mysqli_query($conn,"update p.ID,
        p.post_title, 
        MAX(CASE WHEN pm.meta_key = '_sku' THEN pm.meta_value END) AS sku,
        MAX(CASE WHEN pm.meta_key = '_price' THEN pm.meta_value END) AS price,
        MAX(CASE WHEN pm.meta_key = '_stock' THEN pm.meta_value END) AS quantity
    FROM wp_posts p
    LEFT JOIN wp_postmeta pm ON p.ID = pm.post_id set sku='$codicearticolo', post_title='$nome' where ID='$id' ");
header('location:index.php');

    ?>


  2. 0

    This can be done by using the Left JOIN with wp_posts and wp_postmeta table.

    <?php
include('conn.php');

$query = "
        SELECT 
            p.ID,
            p.post_title,
            MAX(CASE WHEN pm.meta_key = '_sku' THEN pm.meta_value END) AS sku,
            MAX(CASE WHEN pm.meta_key = '_price' THEN pm.meta_value END) AS price,
            MAX(CASE WHEN pm.meta_key = '_stock' THEN pm.meta_value END) AS quantity
        FROM wp_posts p
        LEFT JOIN wp_postmeta pm ON p.ID = pm.post_id
        WHERE p.post_type = 'product' AND p.post_status = 'publish'
        GROUP BY p.ID
        ";

$result = $connettere->query($query);

if ( $result->num_rows > 0 ) {
    while ( $row = $result->fetch_assoc() ) {
        echo "<tr>";
        echo "<td>" . $row['ID'] . "</td>";
        echo "<td>" . $row['sku'] . "</td>";
        echo "<td>" . $row['post_title'] . "</td>";
        echo "<td>" . $row['quantity'] . "</td>";
        echo "<td>" . $row['price'] . "</td>";
        // Here we can add additional fields if necessary.
        echo "</tr>";
    }
} else {
    echo "<tr><td colspan='9'>No products found</td></tr>";
}

$connettere->close();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search