Shopify Order Create Webhook Inserting Into Mysql Using PHP (Private App)

Highlighted
Tourist
5 0 2

Hi There,

I have been building a private web based app for my shopify store that caters more towards our business needs. While i am able to do a dump of "all-orders", or "all-products", etc to Mysql, i haven't been able to figure out executing the shopify order creation webhook to insert a new order when created in Shopify to a Mysql database.

Instead i would need to run my script every "x" times to see if there is a new order (This could if i'm not mistaken lead to exceeding my API limit if i am running other API calls concurrently).

I understand the process of events however i am struggling to execute!

 1. New order created in Shopify by Customer &or Admin.
 2. Triggers webhook and sends Json to desired url i.e(https://mydomain//new-order.php). -> [Struggling]
 3. When this happens the Json is decoded. -> [Struggling]
 4. Assigned to a variable. -> [This i can do]
 5. Inserted into a Mysql database. -> [This i can do]

=> Question:
   How do you once you have created the webhook (in Shopify) get it to trigger your code to run thereafter and execute?

below is the code that i have put together, but when i sent a test hook the database isn't being updated.

All in the [new-orders.php] file (Broken up to show my train of thought):

[1] Private app credentials for connecting to Shopify store.

<?php
   $api_url = https://apikey:password@my-store.shopify.com';
   $shopify = $api_url . '/admin/webhooks.json';

[2] Create an array for the webhook argumnets when the webhook is triggered & assign to variable $webhooks.

   $arguments = array(
      'topic' => 'order/creation',
      'address' => 'https://mydomain//new-order.php'
    );

    $webhooks = $api_url . '/admin/webhooks.json', $arguments;

[3] Decode the webhook data.

    $webhook_content = '';
    $webhook = fopen('php://input' , 'rb');
    while(!feof($webhook)){ //loop through the input stream while the end of file is not reached
        $webhook_content .= fread($webhook, 4096); //append the content on the current iteration
    }
    fclose($webhook); //close the resource

    $orders = json_decode($webhook_content, true); //convert the json to array

[4] Add the new order to the Mysql database table.

      // not sure if a foreach loop is necessary in this case?
      foreach($orders as $order){ 

        $servername = "mysql.servername.com";
        $database = "database_name";
        $username = "user_name";
        $password = "password";
        $sql = "mysql:host=$servername;dbname=$database;";


        // Create a new connection to the MySQL database using PDO, $my_Db_Connection is an object
        try { 
          $db = new PDO($sql, $username, $password);
          //echo "<p> DB Connect = Success.</p>";
        } catch (PDOException $error) {
          echo 'Connection error: ' . $error->getMessage();
        }
                $order_id = $order['id'];
                $order_number = $order['name'];
                $f_name = $order['billing_address']['name'];
                $payment_gateway = $order['gateway'];
                $financial_status = $order['financial_status'];
                $order_value = $order['total_price'];
                $order_status = $order['#'];
                $shipping_province = $order['shipping_address']['province'];
                $created_at = $order['created_at'];
                $updated_at = $order['updated_at'];
                $shipping_method = $order['shipping_lines'][0]['title'];

                $stmt = $db->query("INSERT INTO orders(order_id, order_number, cust_fname, payment_gateway, financial_status, order_value, order_status, ship_to, created_at, updated_at, shipping_method)
                                    VALUES ('$created_at', '$order_id', '$order_number', '$f_name', '$payment_gateway', '$financial_status', '$order_value', '$order_status', '$shipping_province', '$created_at', '$updated_at', '$shipping_method')");

            }

    ?>

Any help would be greatly appreciated and i hope i have given enough context to the issue i am currently facing. If any other information is required i will try my best to explain why i have done something the way i have.

Regards,

0 Likes
Shopify Staff
Shopify Staff
939 2 117

Is the confusion simply over mapping the JSON body of an order creation webhook into a MySQL database? This seems like something less to do with Shopify and more to do with integrating this general functionality into a PHP app. I'd recommend a venue such as StackOverflow for questions like these, as I feel you'll have better luck there.

0 Likes
Tourist
5 0 2

Hi Alex,

I uploaded the same question to StackOverflow. There were a few errors in the above code,

1. I manually created a webhook in my shop admin instead of trying to use the API to create one

2. The foreach loop is not required as the webhook fires a a single order off (as much as i'm aware of). 

The mapping was correct but i could only see that once i had sorted out the issues before. Below is the snippet i used to add orders to an external Mysql db using a webhook created in the admin section of my store and a private app:

<?php

    // Load variables
$webhook_content = NULL;

// Get webhook content from the POST
$webhook = fopen('php://input' , 'rb');
while (!feof($webhook)) {
  $webhook_content .= fread($webhook, 4096);
}
fclose($webhook);

// Decode Shopify POST
$webhook_content = json_decode($webhook_content, TRUE);

	$servername = "server_name";
	$database = "database_name";
	$username = "username";
	$password = "password";
	$sql = "mysql:host=$servername;dbname=$database;";


	// Create a new connection to the MySQL database using PDO, $my_Db_Connection is an object
	try { 
	  $db = new PDO($sql, $username, $password);
	  //echo "<p> DB Connect = Success.</p>";
	} catch (PDOException $error) {
	  echo 'Connection error: ' . $error->getMessage();
	}

//Assign to variable 
			$order_id = $webhook_content['id'];
			$order_number = $webhook_content['name'];
			$f_name = $webhook_content['billing_address']['name'];
			$payment_gateway = $webhook_content['gateway'];
			$financial_status = $webhook_content['financial_status'];
			$order_value = $webhook_content['total_price'];
			$order_status = $webhook_content['#'];
			$shipping_province = $webhook_content['shipping_address']['province'];
			$created_at = $webhook_content['created_at'];
			$updated_at = $webhook_content['updated_at'];
			$shipping_method = $webhook_content['shipping_lines'][0]['title'];

			$stmt = $db->query("INSERT INTO orders(order_id, order_number, cust_fname, payment_gateway, financial_status, order_value, order_status, ship_to, created_at, updated_at, shipping_method)
								VALUES ('$order_id', '$order_number', '$f_name', '$payment_gateway', '$financial_status', '$order_value', '$order_status', '$shipping_province', '$created_at', '$updated_at', '$shipping_method')");

?>

I still need to include the verification part to confirm that the request came from the webhook and nothing else.

Regards

Simon

2 Likes

Hello Simon,

Can you please post PHP code for registering webhook for app/uninstalled. Because i'm stuck in it.

0 Likes