WooCommerce: remove empty SKU products

Posted on November 21, 2014 by rolandow

At work, we got ourselves a nice WooCommerce shop. Using WP All Import, it automagically imports the products and updates stock. But somehow in we ended up with products in our database with no SKU.

Removing one of those products, and then rerun the import, seemed to work. So maybe these products where in the product feed once without a SKU. Who knows. Anyways, I wanted them gone, and didn’t want to delete the entire database, afraid of what Google may think of that.

So I checked the database a bit and came up with a SQL query. First, to check if you have products with empty SKU’s as well, you could run something like this:

   SELECT p.ID, 
	      p.post_title, 
	      p.post_type, 
	      pm.meta_value

     FROM wp_posts p

LEFT JOIN wp_postmeta pm
       ON pm.post_id = p.ID
      AND pm.meta_key = '_sku'

    WHERE p.post_type = 'product'
      AND (pm.meta_value = '' 
       OR pm.meta_value IS NULL)

SELECT p.ID,

p.post_title,

p.post_type,

pm.meta_value

FROM wp_posts p

LEFT JOIN wp_postmeta pm

ON pm.post_id = p.ID

AND pm.meta_key = '_sku'

WHERE p.post_type = 'product'

AND (pm.meta_value = ''

OR pm.meta_value IS NULL)

To remove the products, I think this should do. Of course this wouldn’t delete the files in your upload folder. If you want to delete those as well, you might want to write a script that selects the records, and then removes the images as well.

       DELETE wp_posts, 
       		  wp_postmeta,
       		  wp_comments,
       		  wp_commentmeta

         FROM wp_posts

	LEFT JOIN wp_postmeta
		   ON wp_postmeta.post_id = wp_posts.ID
          AND wp_postmeta.meta_key = '_sku'

    LEFT JOIN wp_comments
           ON wp_comments.comment_post_ID = wp_posts.ID

    LEFT JOIN wp_commentmeta
           ON wp_commentmeta.comment_id = wp_comments.comment_ID

    LEFT JOIN wp_postmeta wpm2
           ON wpm2.post_id = wp_posts.ID

		WHERE wp_posts.post_type = 'product'
  		  AND (wp_postmeta.meta_value= '' OR wp_postmeta.meta_value IS NULL)

DELETE wp_posts,

wp_postmeta,

wp_comments,

wp_commentmeta

FROM wp_posts

LEFT JOIN wp_postmeta

ON wp_postmeta.post_id = wp_posts.ID

AND wp_postmeta.meta_key = '_sku'

LEFT JOIN wp_comments

ON wp_comments.comment_post_ID = wp_posts.ID

LEFT JOIN wp_commentmeta

ON wp_commentmeta.comment_id = wp_comments.comment_ID

LEFT JOIN wp_postmeta wpm2

ON wpm2.post_id = wp_posts.ID

WHERE wp_posts.post_type = 'product'

AND (wp_postmeta.meta_value= '' OR wp_postmeta.meta_value IS NULL)

Raw mysql in Doctrine

Posted on October 1, 2013 by rolandow

To perform a raw mysql statement in symfony through Doctrine:

$sql = 'SELECT c.CampaignId as campaignId,
               c.ProfileId as profileId,
               p.Name as name
          FROM Profile.ProfileCampaign c
    INNER JOIN Profile.Profile p
            ON p.ProfileId = c.ProfileId
         WHERE c.CampaignId IN (:campaignIdList)
           AND p.Deleted = 0
           AND p.Active = 1';

$stmt = $this->getDoctrine()
    ->getManager()
    ->getConnection()
    ->prepare($sql);
$stmt->bindValue('campaignIdList', implode(",", $campaignIdList));
$stmt->execute();

$sql = 'SELECT c.CampaignId as campaignId,

c.ProfileId as profileId,

p.Name as name

FROM Profile.ProfileCampaign c

INNER JOIN Profile.Profile p

ON p.ProfileId = c.ProfileId

WHERE c.CampaignId IN (:campaignIdList)

AND p.Deleted = 0

AND p.Active = 1';

$stmt = $this->getDoctrine()

->getManager()

->getConnection()

->prepare($sql);

$stmt->bindValue('campaignIdList', implode(",", $campaignIdList));

$stmt->execute();

This example was being used in a controller.

Force Doctrine to close mysql connections

Posted on July 18, 2013 by rolandow

So I wrote a nice shell within app/console, which also used pcntl_fork(). Of course there was a very old archive somewhere in my mind that knew that this would mean trouble when you use mysql connections. But since I don’t explicitly use mysql_connect anymore, because Doctrine does this for me, I didn’t realise I faced the same problem as years ago.

When you fork a process with pcntl_fork(), it will copy the current process. This also contains the handles to your mysql connection. When the first child process dies, it will clean up these handles, and therefore close the connection.

Simple solution: make sure all connections are closed before you fork a child. That way, the child is forced to create a new connection.

I solved this using the following piece of code in the script.

// Close connections
$connections = $this->getContainer()
    ->get('doctrine')
    ->getConnections();
foreach($connections as $conn) {
    $this->logger->debug("Closing connection");
    $conn->close();
}

// Close connections

$connections = $this->getContainer()

->get('doctrine')

->getConnections();

foreach($connections as $conn) {

$this->logger->debug("Closing connection");

$conn->close();

}

Rolandow's development blog

my external memory

Category Archives: MySQL

WooCommerce: remove empty SKU products

Raw mysql in Doctrine

Force Doctrine to close mysql connections