How to get data from multiple mysql tables and make one JSON output

Question

I have this PHP code:

 try {
      $result = $db->prepare('SELECT ID,masina,marka,model,reg_br,istek_reg,servis FROM mehanizacija WHERE user_id=:user_id');
      $result->bindParam(':user_id', $user_id); 
      $result->execute();

        foreach($result as $r) {
          $temp = array();
          $temp[] = (int) $r['ID']; 
          $temp[] = (string) $r['masina'];
          $temp[] = (string) $r['marka']; 
          $temp[] = $r['model'];
          $temp[] = (string) $r['reg_br'];
          $temp[] = 'Date('.str_replace('-',', ',($r['istek_reg'])).')';
          $temp[] = 'Date('.str_replace('-',', ',($r['servis'])).')';

        $rs = $db->prepare('SELECT sum(radnih_sati) FROM track_meh WHERE user_id=:user_id AND id_meh=:id_meh');
          $rs->bindParam(':user_id', $user_id); 
          $rs->bindParam(':id_meh', $r['ID']); 
          //$rs->bindParam(':radnici', $radnici); 
          $rs->execute();
          $sumRows = $rs->fetchColumn(); 
          $temp[] = array('v' => (int) $sumRows);
 $rs1 = $db->prepare('SELECT sum(kolicina) FROM track_meh WHERE user_id=:user_id AND id_meh=:id_meh');
          $rs1->bindParam(':user_id', $user_id); 
          $rs1->bindParam(':id_meh', $r['ID']); 
          $rs1->execute();
          $sumRows1 = $rs1->fetchColumn(); 
          $temp[] = array('v' => (int) $sumRows1);
          $prosecna = $sumRows1/$sumRows;
          $temp[] = array('v' => (float) $prosecna. ' l/h');

          $rows[] = $temp;
        }
    $table['data'] = $rows;
    $jsonTable = json_encode($table);
    } catch(PDOException $e) {
        echo 'ERROR: ' . $e->getMessage();
    }
    echo $jsonTable;

and now I have this JSON output:

{"data":[[1,"traktor","IMT","510-td","BG-657 UI","Date(2014, 04, 24)","Date(2014, 02, 18)",53,95,"1.79245283019 l\/h"],[3,"Vrsalica","John Deer","n7","BG 777 hb","Date(2014, 07, 18)","Date(2014, 01, 20)",24,66,"2.75 l\/h"],[4,"prikolica","nema","nema","","Date(0000, 00, 00)","Date(0000, 00, 00)",0,0,"0 l\/h"],[5,"traktor","","","","Date(0000, 00, 00)","Date(0000, 00, 00)",0,0,"0 l\/h"],[6,"kombajn","Novi","NN","Pi 001 ZN","Date(0000, 00, 00)","Date(0000, 00, 00)",24,50,"2.08333333333 l\/h"],[7,"traktor","mnm","","","Date(0000, 00, 00)","Date(0000, 00, 00)",6,9,"1.5 l\/h"],[8,"traktor","","","","Date(0000, 00, 00)","Date(0000, 00, 00)",0,0,"0 l\/h"],[9,"alat","","","","Date(0000, 00, 00)","Date(0000, 00, 00)",0,0,"0 l\/h"],[10,"traktor","","","","Date(0000, 00, 00)","Date(0000, 00, 00)",0,0,"0 l\/h"],[11,"setvo-spremac","","","","Date(0000, 00, 00)","Date(0000, 00, 00)",0,0,"0 l\/h"],[12,"traktor","","","","Date(0000, 00, 00)","Date(0000, 00, 00)",0,0,"0 l\/h"]]}

What I need to do to transform my JSON output format to this:

{"data":[{"ID":"1","masina":"traktor","marka":"IMT","model":"510-td","reg_br":"BG-657 UI","istek_reg":"2014-04-24","servis":"2014-02-18","sumRows":"555", "sumRows1":"333","prosecna":"2.0999"}, etc ...

SO as you see my JSON output is wrong, becouse dataTable plugin need data in format like I put above, becouse of that I need to find a way to transform that data in follow format.

Any idea how to do that? I try many different ways but I cant solve this problem

MrCode · Accepted Answer · 2015-01-30 19:34:56Z

1

The use of $temp[] is giving you a numerical keyed array, which is why the end result is an array of arrays. According to your desired output, you need an array of associative arrays, which will ultimately give you an array of objects (as a string) after the JSON transformation.

Replace each $temp[] with $temp['some_key'], using the relevant key.

$temp['ID'] = (int) $r['ID']; 
$temp['traktor'] = (string) $r['masina'];

edited Jan 30, 2015 at 19:34

answered Jan 30, 2015 at 17:22

MrCode

64.6k10 gold badges95 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MrCode Over a year ago

So don't store them in $temp then. Remove any that you don't want to appear in the JSON.

Collectives™ on Stack Overflow

How to get data from multiple mysql tables and make one JSON output

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related