如何从多个mysql表中获取数据并生成一个JSON输出

I have this PHP code:

我有这个PHP代码:

 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:

现在我有了这个JSON输出:

{"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:

我需要做什么才能将我的JSON输出格式转换为:

{"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.

因为你看到我的JSON输出是错误的,因为dataTable插件需要像我上面那样的格式数据,因为我需要找到一种方法来按照以下格式转换数据。

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

知道怎么做吗?我尝试了很多不同的方法,但我无法解决这个问题

1 个解决方案

#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.

$ temp []的使用给你一个数字键控数组,这就是最终结果是一个数组数组的原因。根据您所需的输出,您需要一组关联数组,最终会在JSON转换后为您提供一个对象数组(作为字符串)。

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

使用相关密钥将每个$ temp []替换为$ temp ['some_key']。

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