My Blog
Find all woocommerce products that are draft or publish with mysql or php
Another useful article for die-hards who work with excel lists, google spreadsheets or third party services etc.
Quickly find your woocommerce products that are in draft (draft, non-public) or publish (published) status. You must have entered product code by SKU before running the following.
In case you wish to do it directly through a mysql database manager with phpmyadmin for example, select the database you want and in the command execution give the following:
For all products in draft status:
SELECT wp_postmeta.meta_value FROM wp_posts LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id WHERE wp_postmeta.meta_key = '_sku' AND wp_posts.post_status = 'draft' AND wp_p$ AND wp_postmeta.meta_value <> ''
For all products in publish status:
SELECT wp_postmeta.meta_value FROM wp_posts LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id WHERE wp_postmeta.meta_key = '_sku' AND wp_posts.post_status = 'publish' AND wp_p$ AND wp_postmeta.meta_value <> ''
Now if you want this in a php file to be pulled as an html table by some service
eg google spreadsheet, create a new file with the extension .php on your server.
Inside put the following (don’t forget to put database details):
For all products in draft status:
<!DOCTYPE html>
<html>
<body>
<?php $servername = "localhost"; $username = "ONOMA_XRHSTH"; $password = "KWDIKOS"; $dbname = "ONOMA_BASHS"; // database creation $conn = new mysqli($servername, $username, $password, $dbname); // database connection check if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT wp_postmeta.meta_value FROM wp_posts
LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
WHERE wp_postmeta.meta_key = '_sku' AND wp_posts.post_status = 'draft' AND wp_posts.post_type = 'product'
AND wp_postmeta.meta_value <> ''";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// extract data from each line
echo "
<table>";
while($row = $result->fetch_assoc()) {
echo "
<tr>
<td>". $row["meta_value"]. "</td>
</tr>
";
}
} else {
echo "0 results";
}
echo "</table>
";
$conn->close();
?>
</body>
</html>
For all products in publish status:
<!DOCTYPE html>
<html>
<body>
<?php $servername = "localhost"; $username = "ONOMA_XRHSTH"; $password = "KWDIKOS"; $dbname = "ONOMA_BASHS"; // database creation $conn = new mysqli($servername, $username, $password, $dbname); // database connection check if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT wp_postmeta.meta_value FROM wp_posts
LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
WHERE wp_postmeta.meta_key = '_sku' AND wp_posts.post_status = 'publish' AND wp_posts.post_type = 'product'
AND wp_postmeta.meta_value <> ''";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// extract data from each line
echo "
<table>";
while($row = $result->fetch_assoc()) {
echo "
<tr>
<td>". $row["meta_value"]. "</td>
</tr>
";
}
} else {
echo "0 results";
}
echo "</table>
";
$conn->close();
?>
</body>
</html>
By Nicolas, enjoy 😉
This post is also available in:
Ελληνικά