The requirement to get all collection on a database appeared while creating a private app for shopify where i need to populate all the available collections under a dropdown to allow admin select collection. Now the script i’m have written will import once i.e. all the available active collections inside a MySQL database. You might be wondering what if new collections added or removed later, well we have webhooks for that. The collection creation and collection deletion webhook will update our database table.

The first thing is i will be using this private app skeleton https://github.com/phpish/shopify_private_app-skeleton

Below are few of the major things that are taken care on the code block below.

1. Connect to database to store
2. Make use of shopify collection api reference
3. Shopify limits to fetch to 50 products at once and 250 at max but we are looping the request to fetch all collections at once, this is bit tricky.
4. The global variables shopify api key, app password are configured on conf.php from private app skeleton
5. I’m just extracting collection id, collection title and handle. You can access more data.
6. Cheers !!

Major ALERT : There are two types of collections smart collections and custom collections on shopify. And as there exists different api endpoints for each of them so we will extract seperately. In my case i had shown as smart collections list and custom collections list.

Below blog of code saves all the smart collection on your database.

Smart Collection

	session_start();

	require __DIR__.'/vendor/autoload.php';
	use phpish\shopify;

	require __DIR__.'/conf.php';

	$servername = "localhost";
	$username = "{{ your-db-username }}";
	$password = "{{ your-db-password }}";
	$database = '{{ your-db-name }}';
	$db = new mysqli($servername, $username, $password,$database);
	if ($db->connect_error) {
	    die("Connection failed: " . $db->connect_error);
	}

	$shopify = shopify\client(SHOPIFY_SHOP, SHOPIFY_APP_API_KEY, SHOPIFY_APP_PASSWORD, true);

	try
	{
		# Making an API request can throw an exception
		//$products = $shopify('GET /admin/products/6631114246.json', array('published_status'=>'published'));
		$products = $shopify('GET /admin/smart_collections/count.json', array());
		
		$totalproducts = $shopify('GET /admin/smart_collections/count.json', array());
		$limit = 50;
		
		$totalpage = ceil($totalproducts/$limit);
		for($i=1; $i<=$totalpage; $i++){ //$products = $shopify('GET /admin/products.json?limit='.$limit.'&page='.$i, array('published_status'=>'published'));
			$products = $shopify('GET /admin/smart_collections.json?limit='.$limit.'&page='.$i, array('published_status'=>'published'));
			foreach($products as $product){
				$product = (object)$product;

					//insert into db table
					$sqlc = "INSERT INTO `collections` (`collection_id`,`collection_title`,`collection_handle`)VALUES ('$product->id', '$product->title','$product->handle')";
					
					if(!$result = $db->query($sqlc)){
					    die('There was an error running the query [' . $db->error . ']');
					}
			}
		}
	}
	catch (shopify\ApiException $e)
	{
		# HTTP status code was >= 400 or response contained the key 'errors'
		echo $e;
		print_R($e->getRequest());
		print_R($e->getResponse());
	}
	catch (shopify\CurlException $e)
	{
		# cURL error
		echo $e;
		print_R($e->getRequest());
		print_R($e->getResponse());
	}

And below is block of code to save custom Custom collections on your database

Custom collection

	session_start();

	require __DIR__.'/vendor/autoload.php';
	use phpish\shopify;

	require __DIR__.'/conf.php';

	$servername = "localhost";
	$username = "{{ your-db-username }}";
	$password = "{{ your-db-password }}";
	$database = '{{ your-db-name }}';
	$db = new mysqli($servername, $username, $password,$database);
	if ($db->connect_error) {
	    die("Connection failed: " . $db->connect_error);
	}

	$shopify = shopify\client(SHOPIFY_SHOP, SHOPIFY_APP_API_KEY, SHOPIFY_APP_PASSWORD, true);

	try
	{
		# Making an API request can throw an exception
		//$products = $shopify('GET /admin/products/6631114246.json', array('published_status'=>'published'));
		$products = $shopify('GET /admin/custom_collections/count.json', array());
		
		$totalproducts = $shopify('GET /admin/custom_collections/count.json', array());
		$limit = 50;
		$totalpage = ceil($totalproducts/$limit);
		for($i=1; $i<=$totalpage; $i++){ //$products = $shopify('GET /admin/products.json?limit='.$limit.'&page='.$i, array('published_status'=>'published'));
			$products = $shopify('GET /admin/custom_collections.json?limit=50&page='.$i, array('published_status'=>'published'));
			foreach($products as $product){
				$product = (object)$product;
					//insert into db table
					$sqlc = "INSERT INTO `collections` (`collection_id`,`collection_title`,`collection_handle`,`is_custom`)VALUES ('$product->id', '$product->title','$product->handle',1)";
					
					if(!$result = $db->query($sqlc)){
					    die('There was an error running the query [' . $db->error . ']');
					}
			}
		}
	}
	catch (shopify\ApiException $e)
	{
		# HTTP status code was >= 400 or response contained the key 'errors'
		echo $e;
		print_R($e->getRequest());
		print_R($e->getResponse());
	}
	catch (shopify\CurlException $e)
	{
		# cURL error
		echo $e;
		print_R($e->getRequest());
		print_R($e->getResponse());
	}

To add webhook to update observe collection addition or deletion. Navigate to “Settings > Notifications > Webhooks > Create webhook”
Select collection creation event and give a complete path to your file as Callbackurl and format as JSON. Callback url in my case is complete path to a php file on another server. Below is the block of code i have on that webhook file

require 'dbconnect.php';


define('SHOPIFY_APP_SECRET', '{{ your-app-secret-key }}');

function verify_webhook($data, $hmac_header)
{
  $calculated_hmac = base64_encode(hash_hmac('sha256', $data, SHOPIFY_APP_SECRET, true));
  return ($hmac_header == $calculated_hmac);
}

$hmac_header = $_SERVER['HTTP_X_SHOPIFY_HMAC_SHA256'];
$data = file_get_contents('php://input');
$verified = verify_webhook($data, $hmac_header);

if($verified){
	$data = json_decode($data, TRUE);

	//$myfile = fopen("collection-creation.txt", "w") or die("Unable to open file!");
	$title = mysqli_real_escape_string($db, $data['title']);
	$colid = $data['id'];
	$handle = $data['handle'];	

	$sqlc = "INSERT INTO `collections` (`collection_id`,`collection_title`,`collection_handle`)VALUES ('$colid', '$title','$handle')";
	if(!$result = $db->query($sqlc)){
	    die('There was an error running the query [' . $db->error . ']');
	}
	
}

Similarly on deletion of collection

require 'dbconnect.php';


define('SHOPIFY_APP_SECRET', '{{ your-app-secret-key }}');

function verify_webhook($data, $hmac_header)
{
  $calculated_hmac = base64_encode(hash_hmac('sha256', $data, SHOPIFY_APP_SECRET, true));
  return ($hmac_header == $calculated_hmac);
}


$hmac_header = $_SERVER['HTTP_X_SHOPIFY_HMAC_SHA256'];
$data = file_get_contents('php://input');
$verified = verify_webhook($data, $hmac_header);

if($verified){
	$data = json_decode($data, TRUE);

	//$myfile = fopen("collection-delete.txt", "w") or die("Unable to open file!");
	$id = $data['id'];

	$sqlc = "DELETE FROM `collections` WHERE collection_id=$id";
	if(!$result = $db->query($sqlc)){
	    die('There was an error running the query [' . $db->error . ']');
	}
	
}

Suman K.C

eCommerce Developer, writes about stuff related to eCommerce development. Currently based in Kathmandu, loves reading about startups & new technology, introvert nature, fond of traveling & playing futsal.

More Posts