skip to Main Content

I have been trying to get a query output formatted in a specific way but I am surely doing something wrong. Could anyone that knows what I am doing wrong give me a hand? Thanks a lot.

I have the following db call:

     $sql = "SELECT tbl1.spec_num As spec_num,
                    IF(tbl1.spec_fld, 'TRUE', 'FALSE') As spec_fld,
                    tbl2.auth_id As auth_id
            FROM spec_table tbl1
                JOIN spec_auth tbl2 ON tbl1.id=tbl2.spec_table_id
            WHERE tbl1.spec_fld=1
            AND tbl2.enb=1;";
                      

If I run this query in the db (mysql) I get this:

spec_num                                spec_fld    auth_id
123413253242135234213432112345DDDDDDDG  TRUE        1234567
123413253242135234213432112345DDDDDDDG  TRUE        3423435
123413253242135234213432112345DDDDDDDG  TRUE        9234245

When I make a call to the DB in PHP using PDO I do the following:

    $stmt = $connection->prepare($sql); 
    $stmt->execute();
    while ($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $result_json = json_encode($result);
        echo $result_json;
    } 

My echo inside the loop shows this:

{"spec_num":"123413253242135234213432112345DDDDDDDG","spec_fld":"TRUE","auth_id":"3423435"}
{"spec_num":"123413253242135234213432112345DDDDDDDG","spec_fld":"TRUE","auth_id":"9234245"}

But what I need now is to create a variable ($dtp) outside the while loop that looks like this:

$dtp = [    
   'spec_num' => '123413253242135234213432112345DDDDDDDG',
   'spec_fld' => TRUE,  
   'auth_ids' => [          
      '1234567',        
      '3423435', 
      '9234245',        
      ],    
   ];

Any ideas on the best way to do this? Thanks again in advance.

2

Answers


  1. Dont convert things to JSON until you have completed building the array or object

    $stmt = $connection->prepare($sql); 
    $stmt->execute();
    while ($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $rows[] = $result;
    } 
    echo json_encode($rows);
    

    You could simplify that to

    $stmt = $connection->prepare($sql); 
    $stmt->execute();
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) {
    echo json_encode($rows);
    

    PART 2

    First sort the resultset in spec_num order

    $sql = "SELECT tbl1.spec_num As spec_num,
                        IF(tbl1.spec_fld, 'TRUE', 'FALSE') As spec_fld,
                        tbl2.auth_id As auth_id
            FROM spec_table tbl1
                JOIN spec_auth tbl2 ON tbl1.id = tbl2.spec_table_id
            WHERE tbl1.spec_fld=1
            AND tbl2.enb=1
            ORDER BY spec_num";
    

    Then the code changes. Build the dtp array bit by bit, I made this able to cope with more than one spec_num being selected

    $stmt = $connection->prepare($sql); 
    $stmt->execute();
    
    $last_spec_num  = NULL;
    $dtp = [];
    $tmp = [];
    
    while ($row= $stmt->fetch(PDO::FETCH_ASSOC)) {
    
        if ( $last_spec_num !== NULL ) {
            // first time through to create a $tmp
            $tmp = ['spec_num' => $row['spec_num'],
                    'spec_fld' => $row['spec_fld'],  
                    'auth_ids' => $row['auth_id']
                   ];
            $last_spec_num = $row['spec_num'];
        }
    
        if ( $last_spec_num != $row['spec_num'] ) {
            // got new spec_num
            $dtp[] = $tmp;
            $tmp = [];      // init tmp
            // load this row into the new $tmp
            $tmp = ['spec_num' => $row['spec_num'],
                    'spec_fld' => $row['spec_fld'],  
                    'auth_ids' => $row['auth_id']
                   ];
            // set the new las spec num
            $last_spec_num = $row['spec_num'];
        } else {
            // same spec_num
            $tmp['auth_ids'][] = $row['auth_id'];
        }       
    }
    // echo json_encode($dtp); here if thats what you want to do
    
    Login or Signup to reply.
  2. Just use

    $arr = $stmt->fetchAll(PDO::FETCH_ASSOC);
    

    and you get an array like

    $arr = [
    ["spec_num" => "123413253242135234213432112345DDDDDDDG","spec_fld" => TRUE,"auth_id" => "1234567"],
    ["spec_num" => "123413253242135234213432112345DDDDDDDG","spec_fld" => TRUE,"auth_id" => "3423435"],
    ["spec_num" => "123413253242135234213432112345DDDDDDDG","spec_fld" => TRUE,"auth_id" => "9234245"],
    ];
    

    You want to create such an array:

    $dtp = [    
       'spec_num' => '123413253242135234213432112345DDDDDDDG',
       'spec_fld' => TRUE,  
       'auth_ids' => [          
          '1234567',        
          '3423435', 
          '9234245',        
          ],    
       ];
    

    It only makes sense to create this structure it if the SQL query ensures that spec_num and spec_fld are identical in all lines.

    The solution for this special case is very simple:

    $dtp = [
      "spec_num" => $arr[0]["spec_num"],
      "spec_fld" => $arr[0]["spec_fld"],
      "auth_id" => []
    ];
    foreach($arr as $key => $row){
      $dtp["auth_id"][] = $row["auth_id"];
    }
    

    If spec_num and spec_fld are not identical in all lines, you must first group by spec_num and then by spec_fld. The solution is then a 4 dimensional array.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search