B4J Question Json parser scrambles map keys from php results

[le_toubib]

Hi folks
Using a complex query from multiple tables , I get a result string json encoded from php ..
I use our b4x json parser and everything works fine except that the fields are scrambled I.e not in the same order of the coming query and string result.
P.s :
1- post string results Comes in the correct order , so the scramble happens at parsing/ accessing the maps.
2- this is a query from multiple tables so I can't query the columns table (show columns , or can I??)
3- query and tables are dynamic, so I can't do it manually. And I need to keep the order to throw the results directly into the table, without having to set columns for every single request.
Any help ?

 

[Anno Jo]

Agree, the parser make the column order scramble. You can iterate the map to catch the key/column name and the value.

Or this is what I did sometimes:
1. In query select I make alias column name. Something like this:
“Select id kd0, name kd1, address kd2 from table”
2. make array in job success. Get the last letter of key name by substring(2). Then put the value to the array.
3. now you have values in array that the index just the same like the column order from query although the parser make it unsorted.
Array(0)=kd0
Array(1)=kd1..

maybe anyone have other idea?

 

[Erel]

Why waste your time with PHP? Use jRDC2. It will be simpler, faster and more secure.

If you do want to use PHP then add a list to the json with the ordered field names.

 

[mcqueccu]

In as much as

Why waste your time with PHP? Use jRDC2. It will be simpler, faster and more secure.

In as much as we will like to use highly recommended jrdc2, most of us have cheap hosting servers and shared hosting. JRDC2 as I understand works with VPS so some of us are stuck with using php or other means to interact with our servers

 

[Erel]

You can get a low cost VPS in almost the same price as a shared hosting.

 

[mcqueccu]

You can get a low cost VPS in almost the same price as a shared hosting.

Thats great to hear. Perhaps a recommendation (link) will be nice. Thank you

 

[le_toubib]

Agree, the parser make the column order scramble. You can iterate the map to catch the key/column name and the value.

Or this is what I did sometimes:
1. In query select I make alias column name. Something like this:
“Select id kd0, name kd1, address kd2 from table”
2. make array in job success. Get the last letter of key name by substring(2). Then put the value to the array.
3. now you have values in array that the index just the same like the column order from query although the parser make it unsorted.
Array(0)=kd0
Array(1)=kd1..

maybe anyone have other idea?

thank you , for ur reply ,
1- i use alias in the query but that wont change a thing, columns are still not in the same order as in the query and the result json.
2- if i understand u correctly , this would necessitate that i already know the expected order of columns , but as i said the query is dynamic , and serves several different tasks.
3- for each iterating keys also brings srambled columns

 

[OliverA]

i use alias in the query but that wont change a thing, columns are still not in the same order as in the query and the result json.

Maps in JSON are not required to remain in order. Arrays are. Switch your maps to arrays. It's pretty much the same issue as was encountered here: https://www.b4x.com/android/forum/threads/for-each-statement-difference-between-b4j-and-b4a.129515/

 

[KMatle]

Can you post the php code (the select statement plus your fetch loop)? I use php, MySQL and Jsons a lot.

 

[Anno Jo]

thank you , for ur reply ,
1- i use alias in the query but that wont change a thing, columns are still not in the same order as in the query and the result json.
2- if i understand u correctly , this would necessitate that i already know the expected order of columns , but as i said the query is dynamic , and serves several different tasks.
3- for each iterating keys also brings srambled columns

ones, I did because I want to make a timeline. I want to get only values from query, day by day and put it in tableview.
something like this

        Dim datadil As List
        Dim idx As String
                    
        datadil = parser.NextArray
        Log(datadil)
        For i = 0 To datadil.Size - 1
            Dim m As Map
            m.Initialize
            m = datadil.Get(i)
                        
            Dim listDataTime(32) As String
            For j=0 To m.Size-1

                idx=m.GetKeyAt(j)
                idx=idx.SubString(2)
                            
                listDataTime(idx)=m.GetValueAt(j)
            Next
            tvTimeline.Items.Add(listDataTime)
        Next

the idx variable get the last number of key. to get 0 of kd0, 1 of kd1....
so we have array listDataTime(0)= value of kd0, listDataTime(1)= value of kd1......

it's just an example & maybe it's not the best way, but I need it sometime

Regard
Anno

 

[aeric]

Thats great to hear. Perhaps a recommendation (link) will be nice. Thank you

I think this question has already been asked several times. I can recommend hostinger which I am using now.

 

[mcqueccu]

I think this question has already been asked several times. I can recommend hostinger which I am using now.

I have also seen linode, vultr and Digital Ocean and their prices are okay

 

[le_toubib]

Can you post the php code (the select statement plus your fetch loop)? I use php, MySQL and Jsons a lot.

$stmt = $conn->prepare("
select `YEAR`, `Groups` , count(*) as Total from clients where YEAR > ? GROUP BY `YEAR` with ROLLUP ") ;
$stmt->bind_param("s",  $Date );
        $stmt->execute();
        $stmt->store_result();
        $resultrow = array();
        stmt_bind_assoc($stmt, $resultrow);
        if ($stmt->num_rows > 0)
        {
            while($stmt->fetch())
            {
                print json_encode($resultrow);
            }
        } else {
            print json_encode("Not found");
        }
        $stmt->close();

function stmt_bind_assoc(&$stmt, &$out)
    {
        $data = mysqli_stmt_result_metadata($stmt);
        $fields = array();
        $out = array();
        $fields[0]=$stmt;
        $count=1;
        while ($field = mysqli_fetch_field($data))
        {
            $fields[$count] = &$out[$field->name];
            $count++;
        }
        call_user_func_array('mysqli_stmt_bind_result', $fields);
    }

i'd expect the resulting map to be : YEAR -> Groups -> Total
just like the generated JSON
but actually it s not ..
p.s : @Erel : how to add a list of the sorted fields ?
@OliverA : how to switch the map to array here ?

 

[DonManfred]

just like the generated JSON

can you post such a generated json? This would help to understand your code. And probably the problem too.

i would use a array in php and add all the results to this array. and then output this array.

$results = Array();
while($stmt->fetch())            {
  $results[] = $resultrow; # Add the resultrow to the array
}
print json_encode($results); # Output the hole array at once