Just starting out? Need help? Post your questions and find answers here.
User avatar
Peter
 
Posts: 650
Joined: Mon Feb 24, 2014 10:17 pm
Location: 127.0.0.1:9080

Re: mysql

by Peter Thu Jun 27, 2019 9:02 am

If you create a structure in SB that matches your JSON data, you can use ExtractJSONStructure().
saboteur
 
Posts: 19
Joined: Mon Jan 08, 2018 10:25 am

Re: mysql

by saboteur Thu Jun 27, 2019 9:41 am

I realised not long after posting that my PHP script needs to encode output into JSON.

Just working through some PHP JSON examples so think I should 'get it' soon :)

Thanks again
saboteur
 
Posts: 19
Joined: Mon Jan 08, 2018 10:25 am

Re: mysql

by saboteur Thu Jun 27, 2019 2:21 pm

I'm struggling to get a listicon to populate using JSON.

the JSON string being returned is :

Code: Select all
[{"part_number":"1a105"},{"part_number":"1A239"},{"part_number":"1A255"},{"part_number":"AC-03"},{"part_number":"AC-06S"},{"part_number":"AC-08BA"},{"part_number":"AR-0036"},{"part_number":"AR-0039"},{"part_number":"AR-0045"},{"part_number":"AR-0046"}]


The code i came up with is :

Code: Select all
NewList STRU_parts.s()

ParseJSON(0, Result$)
ExtractJSONList(JSONValue(0), STRU_parts())
   
x = 1
ForEach STRU_parts()
AddGadgetItem(GVAR_a, x, STRU_parts())
 x + 1
Next


Not sure if its the format of the returned string or something else but this doesn't do anything, nor are any errors thrown.

cheers
User avatar
Peter
 
Posts: 650
Joined: Mon Feb 24, 2014 10:17 pm
Location: 127.0.0.1:9080

Re: mysql

by Peter Thu Jun 27, 2019 2:40 pm

Your JSON is an array of objects each containing a string. You must design your structure accordingly.

Code: Select all
EnableExplicit

Define JsonResult.s
Define Counter
Define Json

Structure sParts
  part_number.s
EndStructure

JsonResult = ~"[" +
             ~"{\"part_number\":\"1a105\"},"   +
             ~"{\"part_number\":\"1A239\"},"   +
             ~"{\"part_number\":\"1A255\"},"   +
             ~"{\"part_number\":\"AC-03\"},"   +
             ~"{\"part_number\":\"AC-06S\"},"  +
             ~"{\"part_number\":\"AC-08BA\"}," +
             ~"{\"part_number\":\"AR-0036\"}," +
             ~"{\"part_number\":\"AR-0039\"}," +
             ~"{\"part_number\":\"AR-0045\"}," +
             ~"{\"part_number\":\"AR-0046\"}"  +
             ~"]"

Json = ParseJSON(#PB_Any, JsonResult)

If Json <> 0
 
  Dim Parts.sParts(0)
 
  ExtractJSONArray(JSONValue(Json), Parts())
 
  FreeJSON(Json)
 
  For Counter = 0 To  ArraySize(Parts())
    Debug "Part-Number: " + Parts(Counter)\part_number
  Next
 
Else
 
  Debug "JSONErrorMessage: " + JSONErrorMessage()
 
EndIf
saboteur
 
Posts: 19
Joined: Mon Jan 08, 2018 10:25 am

Re: mysql

by saboteur Fri Jun 28, 2019 8:16 am

Thanks for the demo code, and it does work as advertised.

However.... it will not parse the result from the PHP script and I cannot for the life of me think why.

Image

As can be seen from the screenshot, the raw info looks ok, but when it's parsed it's just blank.

Any idea where i'm going wrong ?

Code: Select all

Structure STRU_parts
  part_no.s
EndStructure

Procedure HttpGetEvent(Success, Result$, UserData)
 
  If Success
   
    SetGadgetText(GVAR_d, Result$)
   
    Json = ParseJSON(#PB_Any, Result$)
   
    If Json <> 0
     
      Dim LIST_parts.STRU_parts(0)
     
      ExtractJSONArray(JSONValue(Json), LIST_parts())
     
      FreeJSON(Json)
     
      For LVAR_counter = 0 To ArraySize(LIST_parts())
        AddGadgetItem(GVAR_a, -1, LIST_parts(LVAR_counter)\part_no + Chr(10) + Str(Date()) )
      Next   
    Else
      AddGadgetItem(GVAR_a, 1, "JSONErrorMessage: " + JSONErrorMessage())
    EndIf
   
  Else
    SetGadgetText(GVAR_a, "HTTPRequest(): Error")
  EndIf
 
EndProcedure


PHP file - i've tried with or without the utf8 function.

Code: Select all
function utf8ize($d)
{
    if (is_array($d) || is_object($d))
        foreach ($d as &$v) $v = utf8ize($v);
    else
        return utf8_encode($d);

    return $d;
}


$mysqli = new mysqli("myserver", "myuser", "mypassword", "mydatabase");
$myArray = array();
if ($result = $mysqli->query("SELECT part_number FROM tbl_product LIMIT 10")) {

    while($row = $result->fetch_array(MYSQLI_ASSOC)) {
            $myArray[] = $row;
    }
    //echo json_encode(utf8ize($myArray));
   echo json_encode($myArray);
   //echo $myArray;
}

$result->close();
$mysqli->close();


cheers
User avatar
Peter
 
Posts: 650
Joined: Mon Feb 24, 2014 10:17 pm
Location: 127.0.0.1:9080

Re: mysql

by Peter Fri Jun 28, 2019 8:42 am

the members of the structure must be named in the same way as those of the JSON:

Code: Select all
Structure STRU_parts
  part_number.s
EndStructure
saboteur
 
Posts: 19
Joined: Mon Jan 08, 2018 10:25 am

Re: mysql

by saboteur Fri Jun 28, 2019 9:04 am

:oops: :oops: :oops: :oops: :oops: :oops: :oops:

Thankyou sooooo much Peter, I have literally been through so many iterations of this code trying to figure it out and I hadn't noticed that at all.
Return to Coding Questions

Who is online

Users browsing this forum: No registered users and 3 guests