damiencarbery/list-product-stock-loop.php

## list-product-stock-loop.php
<?php
// This is the core of the code that uses direct database access to retrieve stock information.
foreach ($product_ids as $product_id) {
	// Retrieve IDs of the product variations.
	$results = $wpdb->get_results( $wpdb->prepare( "SELECT `ID` from {$wpdb->prefix}posts WHERE `post_parent` = '%s' AND `post_type` = 'product_variation'", $product_id ) );
	// Extract the variation IDs from the results into an array.
	$variation_ids = array();
	foreach ( $results as $row ) {
		$variation_ids[] = $row->ID;
	}
	// Only search for variation stock info if some variations were found.
	if ( ! empty( $variation_ids ) ) {
		// Get stock info about the variations. This is not generic code as it looks for a specific attribute: 'attribute_pa_size'.
		$results = $wpdb->get_results( "SELECT * from {$wpdb->prefix}postmeta WHERE `post_id` in (".implode( ',', $variation_ids ).") AND `meta_key` in ('attribute_pa_size','_stock_status','_stock')" );

		// Determine which variations are in stock. The results are spread across
		// multiple rows so must be examined and the data stored.
		$variation_stock = array();
		$variation_slug = array();
		foreach ( $results as $row ) {
			//echo "<pre>$product_id | $row->post_id | $row->meta_key | $row->meta_value</pre>\n";
			if ( 'attribute_pa_size' == $row->meta_key ) {
				$variation_slug[ $row->post_id ] = $row->meta_value;  // e.g. variation_id = '5-6-years'.
			}
			// Stock can be 'instock' or 'outofstock'.
			if ( '_stock_status' == $row->meta_key ) {
				$variation_stock[ $row->post_id ] = $row->meta_value;  // e.g. variation_id = '5-6-years'.
			}
		}

		// Store processed variation info in main variable.
		foreach ( $variation_stock as $variation_id => $status ) {
			// Check in $variation_slug to exclude products that don't use 'attribute_pa_size'
			if ( 'instock' == $status  && array_key_exists( $variation_id, $variation_slug ) ) {
				$variations_in_stock[ $variation_slug[ $variation_id ] ][] = $product_id;

			}
			else {
				// Product is out of stock so don't do anything - the $variations_in_stock array only records products in stock.
			}
		}
	}
	$product_count++;
}

## list-product-stock-with-api-loop.php
<?php
// This is the core of the code that uses the WooCommerce API to retrieve stock information.
foreach ($product_ids as $id) {
	$product = wc_get_product( $id );
	echo "<p>Product name: ", $product->get_name(), "<br/>Type: ", $product->get_type(), "</p>";
	if ( 'variable' == $product->get_type( ) ) {
		$variation_attributes = $product->get_variation_attributes(); // Get titles of the variations.
		$attributes = array_keys( $variation_attributes );
		$available_variations = $product->get_available_variations();
		echo '<ul>';
		foreach ( array_keys( $available_variations ) as $index ) {
			if ( true == $available_variations[ $index ][ 'is_in_stock' ] ) {
				echo "<li><strong>", $variation_attributes[ $attributes[ 0 ] ][ $index ], "</strong> is in stock (", $available_variations[ $index ][ 'max_qty' ], " left).</li>";
			}
		}
		echo '</ul>';

		$i++;
		if ( 2 == $i ) {
			break;
		}
	}
}

## list-product-stock-with-api.php
<!DOCTYPE html>
<html lang="en-US">
<head>
<meta charset="UTF-8" />
<title>List Product Stock</title>
<meta name="viewport" content="width=device-width, initial-scale=1" />
</head>
<body>
<h1>List Product Stock</h1>
<?php
set_time_limit( 360 );

define('SAVEQUERIES', true);
define('WP_USE_THEMES', false);

/** Loads the WordPress Environment and Template */
require( dirname( __FILE__ ) . '/wp-blog-header.php' );

// Retrieve only product IDs.
$args = array('post_type'=>'product', 'posts_per_page' => -1, 'fields' => 'ids', 'no_found_rows' => true, 'update_post_term_cache' => false, 'update_post_meta_cache' => false);
$products_query = new WP_Query($args);

$product_ids = array();
$product_ids = $products_query->posts;
wp_reset_postdata();

$product_ids = array_slice( $product_ids, 0, 1 );


$i = 0;
foreach ($product_ids as $id) {
	$product = wc_get_product( $id );
	echo "<p>Product name: ", $product->get_name(), "<br/>Type: ", $product->get_type(), "</p>";
	if ( 'variable' == $product->get_type( ) ) {
		$variation_attributes = $product->get_variation_attributes(); // Get titles of the variations.
		$attributes = array_keys( $variation_attributes );
		$available_variations = $product->get_available_variations();
		echo '<ul>';
		foreach ( array_keys( $available_variations ) as $index ) {
			if ( true == $available_variations[ $index ][ 'is_in_stock' ] ) {
				echo "<li><strong>", $variation_attributes[ $attributes[ 0 ] ][ $index ], "</strong> is in stock (", $available_variations[ $index ][ 'max_qty' ], " left).</li>";
			}

		}
		echo '</ul>';

		$i++;
		if ( 2 == $i ) {
			break;
		}
	}
}

echo '<p>', get_num_queries (), ' SQL queries done.';
echo '<br />Page generation took ', timer_stop(), ' seconds.', '</p>';
?>
</body>

## list-product-stock.php
<?php
// Restrict who can access this script.
$permitted_ips = array('12.34.56.78', '34.56.78.90');
if (in_array($_SERVER['REMOTE_ADDR'], $permitted_ips) == false) {
    header('HTTP/1.0 403 Forbidden');
    die();
}
?>
<!DOCTYPE html>
<html lang="en-US">
<head>
<meta charset="UTF-8" />
<title>List Product Stock</title>
<meta name="viewport" content="width=device-width, initial-scale=1" />
</head>
<body>
<h1>List Product Stock</h1>
<?php
//set_time_limit( 360 );

//define('SAVEQUERIES', true);
define('WP_USE_THEMES', false);

/** Loads the WordPress Environment and Template */
require( dirname( __FILE__ ) . '/wp-blog-header.php' );


// Get IDs of all products.
$args = array('post_type'=>'product', 'posts_per_page' => -1, 'fields' => 'ids', 'no_found_rows' => true, 'update_post_term_cache' => false, 'update_post_meta_cache' => false);
$products_query = new WP_Query($args);

$product_ids = array();
$product_ids = $products_query->posts;
wp_reset_postdata();

/*
  $variations_in_stock[ $variation ] = array( $product_id1, $product_id2 ... );
*/
$variations_in_stock = array();

$product_count = 0;
foreach ($product_ids as $product_id) {
	// Retrieve IDs of the product variations.
	$results = $wpdb->get_results( $wpdb->prepare( "SELECT `ID` from {$wpdb->prefix}posts WHERE `post_parent` = '%s' AND `post_type` = 'product_variation'", $product_id ) );

	// Extract the variation IDs from the results into an array.
	$variation_ids = array();
	foreach ( $results as $row ) {
		$variation_ids[] = $row->ID;
	}

	// Only search for variation stock info if some variations were found.
	if ( ! empty( $variation_ids ) ) {
		// Get stock info about the variations. This is not generic code as it looks for a specific attribute: 'attribute_pa_size'.
		$results = $wpdb->get_results( "SELECT * from {$wpdb->prefix}postmeta WHERE `post_id` in (".implode( ',', $variation_ids ).") AND `meta_key` in ('attribute_pa_size','_stock_status','_stock')" );

		// Determine which variations are in stock. The results are spread across
		// multiple rows so must be examined and the data stored.
		$variation_stock = array();
		$variation_slug = array();
		foreach ( $results as $row ) {
			//echo "<pre>$product_id | $row->post_id | $row->meta_key | $row->meta_value</pre>\n";
			if ( 'attribute_pa_size' == $row->meta_key ) {
				$variation_slug[ $row->post_id ] = $row->meta_value;  // e.g. variation_id = '5-6-years'.
			}
			// Stock can be 'instock' or 'outofstock'.
			if ( '_stock_status' == $row->meta_key ) {
				$variation_stock[ $row->post_id ] = $row->meta_value;  // e.g. variation_id = '5-6-years'.
			}
		}

		// Store processed variation info in main variable.
		foreach ( $variation_stock as $variation_id => $status ) {
			// Check in $variation_slug to exclude products that don't use 'attribute_pa_size'
			if ( 'instock' == $status  && array_key_exists( $variation_id, $variation_slug ) ) {
				$variations_in_stock[ $variation_slug[ $variation_id ] ][] = $product_id;

			}
			else {
				// Product is out of stock so don't do anything - the $variations_in_stock array only records products in stock.
			}
		}
	}
	$product_count++;
}

foreach ( $variations_in_stock as $variation => $ids ) {
	update_post_meta( 1, 'size-'. $variation, implode( ',', array_unique( $ids ) ) );
}
?>
</body>

## results-with-api.html

<!DOCTYPE html>
<html lang="en-US">
<head>
<meta charset="UTF-8" />
<title>List Product Stock</title>
<meta name="viewport" content="width=device-width, initial-scale=1" />
</head>
<body>
<h1>List Product Stock</h1>
<p>Product name: SURPRISE BAG - €50
<br/>Type: variable</p>

<ul>
  <li><strong>5-6-years</strong> is in stock (23 left).</li>
  <li><strong>4-5-years</strong> is in stock (25 left).</li>
  <li><strong>3-4-years-2</strong> is in stock (24 left).</li>
  <li><strong>2-3-years-2</strong> is in stock (20 left).</li>
  <li><strong>1-2-years</strong> is in stock (17 left).</li>
</ul>
<p>63 SQL queries done.
<br />Page generation took 8.880 seconds.</p>
</body>

## sns-size-stock-shortcode.php
<?php
/*
Plugin Name: Size in stock shortcode
Plugin URI: https://www.damiencarbery.com/2018/02/list-woocommerce-products-by-sizes/
Description: Shortcode to list products of a specific size in stock.
Author: Damien Carbery
Version: 0.1
*/

add_shortcode('size-in-stock', 'sns_size_in_stock_shortcode');
function sns_size_in_stock_shortcode( $atts, $content, $code ) {
	extract(shortcode_atts(array(
	  'size' => '',
	), $atts));

	if ( '' != $size ) {
		$product_ids = get_post_meta( 1, 'size-'.$size, true );
		if ( '' == $product_ids ) {
			// Return list of available sizes if an invalid size was specified.
			$all_meta = get_post_meta( 1 );
			$available_sizes = array();
			foreach ( array_keys( $all_meta ) as $key ) {
				if ( 0 === strpos( $key, 'size-' ) ) {
					$available_sizes[] = substr( $key, 5 );
				}
			}
			return '<p>ERROR: Invalid size specified in shortcode.</p><p>It must be one of:<br /><em>' . implode( ', ', $available_sizes ) . '</em></p>';
		}
		else {
			return do_shortcode( '[products columns="3" ids="'.$product_ids.'"]' );
			// Could add: orderby="popularity" order="DESC"
		}
	}
	else {
		return '<p>ERROR: You must specify a size in the shortcode.</p>';
	}
}
	<?php
	// This is the core of the code that uses direct database access to retrieve stock information.
	foreach ($product_ids as $product_id) {
	// Retrieve IDs of the product variations.
	$results = $wpdb->get_results( $wpdb->prepare( "SELECT `ID` from {$wpdb->prefix}posts WHERE `post_parent` = '%s' AND `post_type` = 'product_variation'", $product_id ) );
	// Extract the variation IDs from the results into an array.
	$variation_ids = array();
	foreach ( $results as $row ) {
	$variation_ids[] = $row->ID;
	}
	// Only search for variation stock info if some variations were found.
	if ( ! empty( $variation_ids ) ) {
	// Get stock info about the variations. This is not generic code as it looks for a specific attribute: 'attribute_pa_size'.
	$results = $wpdb->get_results( "SELECT * from {$wpdb->prefix}postmeta WHERE `post_id` in (".implode( ',', $variation_ids ).") AND `meta_key` in ('attribute_pa_size','_stock_status','_stock')" );

	// Determine which variations are in stock. The results are spread across
	// multiple rows so must be examined and the data stored.
	$variation_stock = array();
	$variation_slug = array();
	foreach ( $results as $row ) {
	//echo "<pre>$product_id \| $row->post_id \| $row->meta_key \| $row->meta_value</pre>\n";
	if ( 'attribute_pa_size' == $row->meta_key ) {
	$variation_slug[ $row->post_id ] = $row->meta_value; // e.g. variation_id = '5-6-years'.
	}
	// Stock can be 'instock' or 'outofstock'.
	if ( '_stock_status' == $row->meta_key ) {
	$variation_stock[ $row->post_id ] = $row->meta_value; // e.g. variation_id = '5-6-years'.
	}
	}

	// Store processed variation info in main variable.
	foreach ( $variation_stock as $variation_id => $status ) {
	// Check in $variation_slug to exclude products that don't use 'attribute_pa_size'
	if ( 'instock' == $status && array_key_exists( $variation_id, $variation_slug ) ) {
	$variations_in_stock[ $variation_slug[ $variation_id ] ][] = $product_id;

	}
	else {
	// Product is out of stock so don't do anything - the $variations_in_stock array only records products in stock.
	}
	}
	}
	$product_count++;
	}
	<?php
	// This is the core of the code that uses the WooCommerce API to retrieve stock information.
	foreach ($product_ids as $id) {
	$product = wc_get_product( $id );
	echo "<p>Product name: ", $product->get_name(), "<br/>Type: ", $product->get_type(), "</p>";
	if ( 'variable' == $product->get_type( ) ) {
	$variation_attributes = $product->get_variation_attributes(); // Get titles of the variations.
	$attributes = array_keys( $variation_attributes );
	$available_variations = $product->get_available_variations();
	echo '<ul>';
	foreach ( array_keys( $available_variations ) as $index ) {
	if ( true == $available_variations[ $index ][ 'is_in_stock' ] ) {
	echo "<li><strong>", $variation_attributes[ $attributes[ 0 ] ][ $index ], "</strong> is in stock (", $available_variations[ $index ][ 'max_qty' ], " left).</li>";
	}
	}
	echo '</ul>';

	$i++;
	if ( 2 == $i ) {
	break;
	}
	}
	}
	<!DOCTYPE html>
	<html lang="en-US">
	<head>
	<meta charset="UTF-8" />
	<title>List Product Stock</title>
	<meta name="viewport" content="width=device-width, initial-scale=1" />
	</head>
	<body>
	<h1>List Product Stock</h1>
	<?php
	set_time_limit( 360 );

	define('SAVEQUERIES', true);
	define('WP_USE_THEMES', false);

	/** Loads the WordPress Environment and Template */
	require( dirname( __FILE__ ) . '/wp-blog-header.php' );

	// Retrieve only product IDs.
	$args = array('post_type'=>'product', 'posts_per_page' => -1, 'fields' => 'ids', 'no_found_rows' => true, 'update_post_term_cache' => false, 'update_post_meta_cache' => false);
	$products_query = new WP_Query($args);

	$product_ids = array();
	$product_ids = $products_query->posts;
	wp_reset_postdata();

	$product_ids = array_slice( $product_ids, 0, 1 );


	$i = 0;
	foreach ($product_ids as $id) {
	$product = wc_get_product( $id );
	echo "<p>Product name: ", $product->get_name(), "<br/>Type: ", $product->get_type(), "</p>";
	if ( 'variable' == $product->get_type( ) ) {
	$variation_attributes = $product->get_variation_attributes(); // Get titles of the variations.
	$attributes = array_keys( $variation_attributes );
	$available_variations = $product->get_available_variations();
	echo '<ul>';
	foreach ( array_keys( $available_variations ) as $index ) {
	if ( true == $available_variations[ $index ][ 'is_in_stock' ] ) {
	echo "<li><strong>", $variation_attributes[ $attributes[ 0 ] ][ $index ], "</strong> is in stock (", $available_variations[ $index ][ 'max_qty' ], " left).</li>";
	}

	}
	echo '</ul>';

	$i++;
	if ( 2 == $i ) {
	break;
	}
	}
	}

	echo '<p>', get_num_queries (), ' SQL queries done.';
	echo '<br />Page generation took ', timer_stop(), ' seconds.', '</p>';
	?>
	</body>
	<?php
	// Restrict who can access this script.
	$permitted_ips = array('12.34.56.78', '34.56.78.90');
	if (in_array($_SERVER['REMOTE_ADDR'], $permitted_ips) == false) {
	header('HTTP/1.0 403 Forbidden');
	die();
	}
	?>
	<!DOCTYPE html>
	<html lang="en-US">
	<head>
	<meta charset="UTF-8" />
	<title>List Product Stock</title>
	<meta name="viewport" content="width=device-width, initial-scale=1" />
	</head>
	<body>
	<h1>List Product Stock</h1>
	<?php
	//set_time_limit( 360 );

	//define('SAVEQUERIES', true);
	define('WP_USE_THEMES', false);

	/** Loads the WordPress Environment and Template */
	require( dirname( __FILE__ ) . '/wp-blog-header.php' );


	// Get IDs of all products.
	$args = array('post_type'=>'product', 'posts_per_page' => -1, 'fields' => 'ids', 'no_found_rows' => true, 'update_post_term_cache' => false, 'update_post_meta_cache' => false);
	$products_query = new WP_Query($args);

	$product_ids = array();
	$product_ids = $products_query->posts;
	wp_reset_postdata();

	/*
	$variations_in_stock[ $variation ] = array( $product_id1, $product_id2 ... );
	*/
	$variations_in_stock = array();

	$product_count = 0;
	foreach ($product_ids as $product_id) {
	// Retrieve IDs of the product variations.
	$results = $wpdb->get_results( $wpdb->prepare( "SELECT `ID` from {$wpdb->prefix}posts WHERE `post_parent` = '%s' AND `post_type` = 'product_variation'", $product_id ) );

	// Extract the variation IDs from the results into an array.
	$variation_ids = array();
	foreach ( $results as $row ) {
	$variation_ids[] = $row->ID;
	}

	// Only search for variation stock info if some variations were found.
	if ( ! empty( $variation_ids ) ) {
	// Get stock info about the variations. This is not generic code as it looks for a specific attribute: 'attribute_pa_size'.
	$results = $wpdb->get_results( "SELECT * from {$wpdb->prefix}postmeta WHERE `post_id` in (".implode( ',', $variation_ids ).") AND `meta_key` in ('attribute_pa_size','_stock_status','_stock')" );

	// Determine which variations are in stock. The results are spread across
	// multiple rows so must be examined and the data stored.
	$variation_stock = array();
	$variation_slug = array();
	foreach ( $results as $row ) {
	//echo "<pre>$product_id \| $row->post_id \| $row->meta_key \| $row->meta_value</pre>\n";
	if ( 'attribute_pa_size' == $row->meta_key ) {
	$variation_slug[ $row->post_id ] = $row->meta_value; // e.g. variation_id = '5-6-years'.
	}
	// Stock can be 'instock' or 'outofstock'.
	if ( '_stock_status' == $row->meta_key ) {
	$variation_stock[ $row->post_id ] = $row->meta_value; // e.g. variation_id = '5-6-years'.
	}
	}

	// Store processed variation info in main variable.
	foreach ( $variation_stock as $variation_id => $status ) {
	// Check in $variation_slug to exclude products that don't use 'attribute_pa_size'
	if ( 'instock' == $status && array_key_exists( $variation_id, $variation_slug ) ) {
	$variations_in_stock[ $variation_slug[ $variation_id ] ][] = $product_id;

	}
	else {
	// Product is out of stock so don't do anything - the $variations_in_stock array only records products in stock.
	}
	}
	}
	$product_count++;
	}

	foreach ( $variations_in_stock as $variation => $ids ) {
	update_post_meta( 1, 'size-'. $variation, implode( ',', array_unique( $ids ) ) );
	}
	?>
	</body>
	<?php
	/*
	Plugin Name: Size in stock shortcode
	Plugin URI: https://www.damiencarbery.com/2018/02/list-woocommerce-products-by-sizes/
	Description: Shortcode to list products of a specific size in stock.
	Author: Damien Carbery
	Version: 0.1
	*/

	add_shortcode('size-in-stock', 'sns_size_in_stock_shortcode');
	function sns_size_in_stock_shortcode( $atts, $content, $code ) {
	extract(shortcode_atts(array(
	'size' => '',
	), $atts));

	if ( '' != $size ) {
	$product_ids = get_post_meta( 1, 'size-'.$size, true );
	if ( '' == $product_ids ) {
	// Return list of available sizes if an invalid size was specified.
	$all_meta = get_post_meta( 1 );
	$available_sizes = array();
	foreach ( array_keys( $all_meta ) as $key ) {
	if ( 0 === strpos( $key, 'size-' ) ) {
	$available_sizes[] = substr( $key, 5 );
	}
	}
	return '<p>ERROR: Invalid size specified in shortcode.</p><p>It must be one of:<br /><em>' . implode( ', ', $available_sizes ) . '</em></p>';
	}
	else {
	return do_shortcode( '[products columns="3" ids="'.$product_ids.'"]' );
	// Could add: orderby="popularity" order="DESC"
	}
	}
	else {
	return '<p>ERROR: You must specify a size in the shortcode.</p>';
	}
	}