mysql

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

Re: mysql

Post by Peter »

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

Re: mysql

Post 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
saboteur
Posts: 27
Joined: Mon Jan 08, 2018 10:25 am

Re: mysql

Post 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
User avatar
Peter
Posts: 1086
Joined: Mon Feb 24, 2014 10:17 pm
Location: 127.0.0.1:9080
Contact:

Re: mysql

Post 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
saboteur
Posts: 27
Joined: Mon Jan 08, 2018 10:25 am

Re: mysql

Post 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
User avatar
Peter
Posts: 1086
Joined: Mon Feb 24, 2014 10:17 pm
Location: 127.0.0.1:9080
Contact:

Re: mysql

Post 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
saboteur
Posts: 27
Joined: Mon Jan 08, 2018 10:25 am

Re: mysql

Post 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.
Post Reply