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:
1 2 3 4 | SELECT wp_postmeta.meta_value FROM wp_postsLEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_idWHERE 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:
1 2 3 4 | SELECT wp_postmeta.meta_value FROM wp_postsLEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_idWHERE 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | <!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_postsLEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_idWHERE 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 lineecho "<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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | <!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_postsLEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_idWHERE 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 lineecho "<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:
Ελληνικά