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