How to Instantly Delete All Your WooCommerce Products With MySQL

They may come a time when you need to remove all the products from your WooCommerce store, in fact, we had to do just that recently! There isn’t an easy way to do this natively in WooCommerce and if you have a lot of products, removing them in steps can be very time-consuming. When deleting products, you need to ensure you’re deleting all the additional data related to the product such as the taxonomy, relationships and so on. Basically, anything that is connecting to the products.

So to remove all your products safely and cleanly, you will need access your phpMyAdmin so that you can run a script on your database.

1. Login to Your phpMyAdmin

Let’s login to phpMyAdmin, before proceeding, make sure you are going to run the script on the correct database. As always, take a backup prior to running the script. To ensure you have the correct database loaded; you can always look for the table wp_options and ensure it has the correct URL for your website.

2. Run the SQL Statement

Ready to remove all your products in an instance? Then now you can click on the SQL tab and you should end up on a page like this:

Now simply paste the following code into the MySQL query to delete all products. NOTE: Ensure you take a backup of the database before proceeding, just in case.


DELETE relations.*, taxes.*, terms.*
FROM wp_term_relationships AS relations
INNER JOIN wp_term_taxonomy AS taxes
ON relations.term_taxonomy_id=taxes.term_taxonomy_id
INNER JOIN wp_terms AS terms
ON taxes.term_id=terms.term_id
WHERE object_id IN (SELECT ID FROM wp_posts WHERE post_type='product');

DELETE FROM wp_postmeta WHERE post_id IN (SELECT ID FROM wp_posts WHERE post_type = 'product');
DELETE FROM wp_posts WHERE post_type = 'product';

Press the ‘Go’ button to execute the above SQL statement.

Another note, if your database prefix is not the default wp_, you will need to replace this with the database prefix you are using.

3. Ensure All Products are Removed

If it all worked out, if you navigate to the products page, you should now end up with an empty Products list. Awesome right?

If you are removing products because you ran an importer than didn’t quite work out, you will now find that you may have duplicate media products if you were to run a fresh import. To remove the duplicated images, have a look at this post and it will solve your issue: How to Clean up Your WordPress Media Library.

As always, we hope you found this tutorial useful if you’ve used it for a project or was not able to run it successfully, let us know in the comments below!

Nathan da Silva - Profile

Posted by: Nathan da Silva

Nathan is the Founder of Silva Web Designs. He is passionate about web development, website design and basically anything digital related. His main expertise is with WordPress, Magento, Shopify as well as many other frameworks. Whether you need responsive design, SEO, speed optimisation or anything else in the world of digital then get in touch. If you would like to work with Nathan, simply drop him an email at [email protected]

It’s good to share

How to Upload a CSV file into a MySQL Database Using PHP

Are you wondering how you can upload a CSV file into a MySQL database using PHP? Well if you are, you are in the right place!

In our example, we are going to be using Bootstrap and mysqli for the database connection. See below the full script needed in order to complete this action:


<div id="primary" class="content-area">
	<main id="main" class="site-main" role="main">

    <div class="container">

      <div class="section">

        <?php

        $db = new mysqli('localhost', 'username', 'password', 'databasename');

        if($db->connect_errno > 0){
            die('Unable to connect to database [' . $db->connect_error . ']');
        } 


          // File Upload

          if (isset($_POST['submit'])) {
             
                   
              // Import the uploaded file to the database

              $handle = fopen($_FILES['filename']['tmp_name'], "r");

               while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

                  $sql = "INSERT INTO `tablename` (`field1`, `field2`, `field3`, `field4`, `field5`, `field6`, `field7`) VALUES ($data[0], '$data[1]', '$data[2]', '$data[3]', '$data[4]', '$data[5]', $data[6]);";

                  if(!$result = $db->query($sql)){
                      die('There was an error running the query [' . $db->error . ']');
                  }
              }

              fclose($handle);

              echo "Import Completed Successfully"; 
              mysqli_close($db);
          }

          else {
                
             echo "<center>";
              echo "<div class='col-md-12'>";

              echo "<h1>Upload Vouchers CSV File. </h1><br />\n"; 
              echo "<form enctype='multipart/form-data' action='/voucher-import/' method='post'>"; 
              echo "File name to import:<br /><br />\n";
              echo '<input type="file" class="filestyle" name="filename" data-iconName="glyphicon-inbox" required><p>&nbsp;&nbsp;</p><p></p>';
              echo "<input class='btn'  type='submit' name='submit' value='Import'><p></p></form>";
          }
             echo "</div>";
             echo "</center>";
          ?>
          </div>

      </div>


	</main><!-- #main -->
</div><!-- #primary -->

In the example provided above, we are inserting a CSV file with 7 columns where all rows within the CSV are looped and inserted into the database.

The main things that will need editing are first the database connection, so you will need to replace the following line:


$db = new mysqli('localhost', 'username', 'password', 'databasename');

The other part that will need changing is INSERT statement shown below:


$sql = "INSERT INTO `tablename` (`field1`, `field2`, `field3`, `field4`, `field5`, `field6`, `field7`) VALUES ($data[0], '$data[1]', '$data[2]', '$data[3]', '$data[4]', '$data[5]', $data[6]);";

So firstly, you would change the tablename to match the table you would like to insert the data to. If you have less/more columns, you will have to remove/add fields and values to match how many columns you have in your database.

If you are inserting NULL values, you can replace the $data[x] value with NULL. Let’s say your first column is an ID (auto-increment), you can simply replace $data[0] with NULL.

Do remember, for this to work, you will need to upload CSV files that are comma-separated.

And just like that, you now have an awesome CSV upload script!

If this has helped you out, leave a comment below. If for some reason you cannot get this to work, get in touch, we always love helping people!

Nathan da Silva - Profile

Posted by: Nathan da Silva

Nathan is the Founder of Silva Web Designs. He is passionate about web development, website design and basically anything digital related. His main expertise is with WordPress, Magento, Shopify as well as many other frameworks. Whether you need responsive design, SEO, speed optimisation or anything else in the world of digital then get in touch. If you would like to work with Nathan, simply drop him an email at [email protected]

It’s good to share

How to insert if something does not exist in MySQL database

When we create a database table we try to make sure that on certain tables we only allow the user to insert a unique record. To achieve this, we assign a primary key to the table but sometimes duplicate records can be inserted as there may not be any validations in place.

The code below allows you to insert records only if it doesn’t already exist in the table:


INSERT INTO user (Name,username,password)
SELECT * FROM (SELECT 'Name', 'Address', 'Postcode') 
AS tmp WHERE NOT EXISTS 	
(SELECT Name,username,password 
FROM user WHERE Name = 'Name' and username = 'Address' and password = 'Postcode');
Nathan da Silva - Profile

Posted by: Nathan da Silva

Nathan is the Founder of Silva Web Designs. He is passionate about web development, website design and basically anything digital related. His main expertise is with WordPress, Magento, Shopify as well as many other frameworks. Whether you need responsive design, SEO, speed optimisation or anything else in the world of digital then get in touch. If you would like to work with Nathan, simply drop him an email at [email protected]

It’s good to share

How to grant MySQL database table privileges

When creating a database-driven windows application it is very important to create a database user with specific privileges.

The reason for granting specific privileges to the user account is to assure that if your application falls a victim of disaster/hack, the data is safe and secure.

When you have created a database, create a user that will have access to it. Once you have that created, specify the grants which enable that user to Select, Insert, Update, Delete and etc specific to the database table.

In our example we are going to assume we have a database called “db_Sales” which has 3 tables:

1). Address
2). Orders
3). Deliveries

We have database user as “User” and password as “Password”


GRANT ALL PRIVILEGES ON db_Sales.Address TO 'User'@'%';
GRANT SELECT,INSERT, DELETE ON db_Sales.Orders TO 'User'@'%';
GRANT SELECT,INSERT ON db_Sales.Deliveries TO 'User'@'%';

The code above means that the user will be able to do everything from selecting to deleting a record in the Address table. However, the user will only be allowed to Select, Insert and Delete records on Orders and on Deliveries table, the user can only Select and Insert records. All other commands will be denied keeping the database safe from getting misused.

Nathan da Silva - Profile

Posted by: Nathan da Silva

Nathan is the Founder of Silva Web Designs. He is passionate about web development, website design and basically anything digital related. His main expertise is with WordPress, Magento, Shopify as well as many other frameworks. Whether you need responsive design, SEO, speed optimisation or anything else in the world of digital then get in touch. If you would like to work with Nathan, simply drop him an email at [email protected]

It’s good to share