B4J Question Json parser scrambles map keys from php results

le_toubib

Active Member
Licensed User
Longtime User
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

Member
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?
 
Upvote 0

mcqueccu

Well-Known Member
Licensed User
Longtime User
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
 
Upvote 0

le_toubib

Active Member
Licensed User
Longtime User
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
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
Upvote 0

Anno Jo

Member
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
B4X:
        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
 
Upvote 0

le_toubib

Active Member
Licensed User
Longtime User
Can you post the php code (the select statement plus your fetch loop)? I use php, MySQL and Jsons a lot.
PHP:
$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 ?
 
Upvote 0

DonManfred

Expert
Licensed User
Longtime User
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.

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