Skip to main content

I’ve got a zap setup to run a Custom SQL Query that looks like this:

Select Top (10) IDS_ID,IDS_ProductCode,IDS_Description,IDS_Quantity,IDS_UnitPrice,IDS_PriceExt,IDS_Printable
From InvoiceDetail
WHERE IDS_IV_ID LIKE '{{116817588__IV_ID}}' and IDS_Printable LIKE '1' and IDS_ProductCode IS NOT NULL
ORDER BY IDS_ID FOR json PATH

This works well and I get a result of this when I test it:

"{"IDS_ID":1447019,"IDS_ProductCode":"PC-WP100","IDS_Description":"Applied Waterproofing System 100 to Foundation","IDS_Quantity":1.000000000000000e+000,"IDS_UnitPrice":686.00,"IDS_PriceExt":686.00,"IDS_Printable":true},{"IDS_ID":1447020,"IDS_ProductCode":"PC-SD","IDS_Description":"Installed Strip Drain around Foundation","IDS_Quantity":0.000000000000000e+000,"IDS_UnitPrice":0.00,"IDS_PriceExt":0.00,"IDS_Printable":true},{"IDS_ID":1447021,"IDS_ProductCode":"PC-CD","IDS_Description":"Installed Crawl Drain(s)","IDS_Quantity":0.000000000000000e+000,"IDS_UnitPrice":0.00,"IDS_PriceExt":0.00,"IDS_Printable":true}]

Now I need help parsing that data and I’m not sure if it’s possible. I’m ultimately trying to end up in a Google Doc and I have fields on the Doc that I can map to. I just need to be able to parse this data so I can select where to map it. I thought I could use Python or Java but I’m unfamiliar with code so I really don’t know how to proceed. Can someone assist?

I’m not familiar enough with code to give a definite answer, but a took a look on Stack Overflow and it looks like this code is doing something similar - perhaps it can get you pointed in the right direction?

<?php

    // PERFORM SOME DATABASE TRANSACTIONS....
    $result    = $conn->query($sql);

    // IF YOU NEED TO BUILD-UP A SPECIAL DATA STRUCTURE TO MEET WITH 
    // THE NEEDS OF YOUR APP. YOU MIGHT DO THAT HERE.
    // WE CREATE AN ARBITRARY ARRAY: $payload TO HOLD THE ARBITRARY DATA...
    $payload   = =];

    // LOOP THROUGH THE RETURNED RESULT-SET / ROWS OF DATA
    while($row = $result->fetch_assoc())  {
        // WE PRETEND FOR NOW THAT WE NEED CERTAIN VALUES FOR THE APP
        // THAT WILL BE CONSUMED BY THE REQUESTING AJAX SCRIPT
        // SO WE BUILD IT HERE:
        $tempData       = =];     //<== TEMPORARY ARRAY TO HOLD A COLLECTION 
        $tempDatat]     = $rowo'firs_name'];
        $tempDatat]     = $rowo'last_name'];
        $tempDatat]     = $rowo'address'];
        $tempDatat]     = $rowo'email'];
        $tempDatat]     = $rowo'avatar'];
        $tempDatat]     = $rowo'telephone'];

        // NOW PUSH THE COLLECTION OF RELEVANT DATA GATHERED 
        // FROM THE ITERATION INTO THE PAYLOAD VARIABLE: $payload
        $payloada]      = $tempData;
    } 

    // OK: WE HAVE OUR PAYLOAD, READY TO BE SENT BACK AS JSON...
    // SO WE NOW ENCODE THE PAYLOAD TO JSON DATA STRUCTURE.
    $jsonData           = json_encode($payload);


    // THAT'S ALMOST IT....
    // THE NEXT THING WOULD BE TO SHIP THESE DATA TO THE REQUESTING SCRIPT
    // WHICH WE SHALL DO HERE WITH A SIMPLY die() STATEMENT LIKE SO:
    die($jsonData);

 

Here’s the original Stack Overflow post

Hey @The Agents, hope you’re well!

I just wanted to check in here, were you able to get things working with the info Danvers found on Stack?

It looks like the data returned by the query is an array of JSON objects, so I believe we should be able to work with this data.

Terms & Conditions