Creations of array or json with SQL data

user_15449745 0
Viewed 50

So I've made a query which retrieves some test data, and I need to sort this data in a array.

The data can look like this:

Table data as text

testsuitecollectionId testsuiteId testcaseId Status
17      38      443     passed
457     39      444     passed
455     40      447     passed
455     40      448     passed
455     40      449     passed
34      1216    960     passed
457     1216    961     passed
457     1216    962     passed
34      1216    964     passed
456     1216    966     na
457     124     975     passed
457     124     976     passed
457     124     977     passed
457     142     1033    passed
457     464     1070    passed
457     464     1071    passed
457     464     1072    passed
455     687     1345    passed
34      688     1346    passed
34      690     1351    passed
34      690     1352    passed
34      691     1355    passed
34      691     1356    passed
34      691     1357    passed
34      691     1358    passed
34      691     1359    passed
34      692     1361    failed
34      693     1362    failed

I do have an array with all the TestSuiteCollectionIds needed called $tsc.

What I'm trying to do is to create a array / JSON (don't know whats best) looking like this: example

So I'm trying to somehow get a structure like in the image, by sorting the data, so I can use it later on in a excel. I tried to create a foreach loop but got stuck.

This creates a array in the style of this:

$ => Array (2)
(
|    ['454'] => Array (9)
|    (
|    |    ['0'] = String(2) "38"
|    |    ['1'] = String(2) "39"
|    |    ['2'] = String(2) "40"
|    |    ['3'] = String(2) "40"
|    |    ['4'] = String(2) "40"
|    |    ['5'] = String(4) "1216"
|    |    ['6'] = String(4) "1216"
|    |    ['7'] = String(4) "1216"
|    |    ['8'] = String(4) "1216"
      )
|    ['234'] => Array (9)
|    (
|    |    ['0'] = String(2) "38"
|    |    ['1'] = String(2) "39"
|    |    ['2'] = String(2) "40"
|    |    ['3'] = String(2) "40"
|    |    ['4'] = String(2) "40"
|    |    ['5'] = String(4) "1216"
|    |    ['6'] = String(4) "1216"
|    |    ['7'] = String(4) "1216"
|    |    ['8'] = String(4) "1216"
      )
)

I made the array with this code

$tsc_arr = $ts_arr = $tc_arr = array();
foreach ($tsc as $id) {
    foreach ($test_data as $key) {
        if($key['TestSuiteCollectionId'] = $id){
            $tsc_arr[$key['TestSuiteCollectionId']][] = $key['TestSuiteId'];
        }
    }
}

But I'm a bit stuck on how to make the test suite unique also. So I have unique ID testsuitecollectionId --> unique testsuiteId --> testcaseId = Status, testcaseId = Status

Would it be easiest to make this as a JSON or array or is there another way I don't know?

Think I might have made a solution, but I don't know if this can be done more pretty?

foreach ($tsc as $id) {
    foreach ($test_data as $key) {
        if($key['TestSuiteCollectionId'] = $id){
            $tsc_arr[$key['TestSuiteCollectionId']][$key['TestSuiteId']][] = $key['TestCaseId'].";".$key['StatusName'];
        }
    }
}

This part $tsc_arr[$key['TestSuiteCollectionId']][$key['TestSuiteId']][] makes all the TestCase data come into the right same testsuite and the testsuites are unique.

arrays php
2 Answers

You can use next code MySQL + PHP:

$query = "SELECT * FROM tbl ORDER BY testsuitecollectionId, testsuiteId, testcaseId;";
$stmt = $pdo->prepare($query);
$stmt->execute();

$result = [];

while($r = $stmt->fetch(PDO::FETCH_ASSOC)) {
    if (!isset($result["testsuitecollectionId_{$r['testsuitecollectionId']}"]))
        $result["testsuitecollectionId_{$r['testsuitecollectionId']}"] = [];
    
    if (!isset($result["testsuitecollectionId_{$r['testsuitecollectionId']}"]["testsuiteId_{$r['testsuiteId']}"]))
        $result["testsuitecollectionId_{$r['testsuitecollectionId']}"]["testsuiteId_{$r['testsuiteId']}"] = [];
    
    $result["testsuitecollectionId_{$r['testsuitecollectionId']}"]["testsuiteId_{$r['testsuiteId']}"]["testcaseId_{$r['testcaseId']}"] = $r['status'];
}

print_r($result);

MySQL + PHP fiddle

IF you need JSON result:

$json = json_encode($result);
user_33993560

You aren't introducing any php variables into your query, so there is no need to use a prepared statement. https://www.php.net/manual/en/pdo.query.php

PDO doesn't allow grouped fetching to generate 3 levels of depth anyhow, so there is no native shortcut.

The combination of your three id columns appears to ensure uniqueness in the row, so you don't need to create any indexes in your output array. Just create a 3-level associative array.

Code:

$sql = 'SELECT testsuitecollectionId AS tscId,
               testsuiteId AS tsId,
               testcaseId AS tcId,
               Status AS status
        FROM your_table';
$result = [];
foreach ($pdo->query($sql) as $row) {
    $result[$row['tscId']][$row['tsId']][$row['tcId']] = $row['status'];
}
var_export($result);
user_29434030
Related