Shopify Order Create Webhook Inserting Into Mysql Using PHP

Question

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,

Answer 1

Update, managed to figure this out and for those of you potentially struggling with the following this is how i solved.

There are 2 aspect here!

 1. Setting up the webhook [Shopify -> Notifications -> webhooks].
 2. The php file that processes the webhook.

1. -> Create Webhook in shopify and point to where you php url [example.com/Process-webhook.php]
2. -> Process-webhook.php

php code

 // 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";
 $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();
 }

 //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'];
   $pick_status = $webhook_content['NULL']; 
   $pack_status = $webhook_content['NULL']; 
   $fulfill_status = $webhook_content['NULL']; 
   $order_value = $webhook_content['total_price'];
   $order_status = $webhook_content['NULL']; 
   $shipping_province = $webhook_content['shipping_address']['province'];

   // I wanted to insert the variant_id's and quantity as a string in one column. With this i can unserialise and use when needed 
   $items = [];
   foreach($webhook_content["line_items"] as $item) {
            $items[$item["variant_id"]]['quantity'] = $item["quantity"];
        }
        $items = serialize($items);

   $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, 
   items,
   created_at, 
   updated_at, 
   shipping_method)

   VALUES ('$order_id', 
  '$order_number', 
  '$f_name', 
  '$payment_gateway', 
  '$financial_status', 
  '$order_value', 
  '$order_status', 
  '$shipping_province', 
  '$items',
  '$created_at', 
  '$updated_at', 
  '$shipping_method')");

 ?>