Page 2 of 2

Re: mysql

Posted: Thu Jun 27, 2019 9:02 am
by Peter
If you create a structure in SB that matches your JSON data, you can use ExtractJSONStructure().

Re: mysql

Posted: Thu Jun 27, 2019 9:41 am
by saboteur
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

Re: mysql

Posted: Thu Jun 27, 2019 2:21 pm
by saboteur
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

Re: mysql

Posted: Thu Jun 27, 2019 2:40 pm
by Peter
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

Re: mysql

Posted: Fri Jun 28, 2019 8:16 am
by saboteur
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

Re: mysql

Posted: Fri Jun 28, 2019 8:42 am
by Peter
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

Re: mysql

Posted: Fri Jun 28, 2019 9:04 am
by saboteur
: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.