php - 从两个mysql表中获取数据并导出为JSON

Question

我需要创建这种格式的 json 数据:

{
    "cols": [{
        "label": "ID",
        "type": "number"
    }, {
        "label": "Vrsta",
        "type": "string"
    }, {
        "label": "Artikl",
        "type": "string"
    }, {
        "label": "Neto kol",
        "type": "number"
    }, {
        "label": "Bruto kol",
        "type": "number"
    }, {
        "label": "Mera",
        "type": "string"
    }, {
        "label": "Neto iznos",
        "type": "number"
    }, {
        "label": "Bruto",
        "type": "number"
    }, {
        "label": "Porez",
        "type": "number"
    }, {
        "label": "Dobavljac",
        "type": "string"
    }, {
        "label": "Datum",
        "type": "date"
    }, {
        "label": "Skladiste",
        "type": "string"
    }, {
        "label": "Valuta",
        "type": "string"
    }, {
        "label": "Placeno",
        "type": "string"
    }, {
        "label": "Nacin placanja",
        "type": "string"
    }, {
        "label": "Placeni iznos",
        "type": "number"
    }, {
        "label": "Opis",
        "type": "string"
    }],
    "rows": [{
        "c": [{
            "v": 1
        }, {
            "v": "gorivo"
        }, {
            "v": "biodizel"
        }, {
            "v": 22
        }, {
            "v": 22
        }, {
            "v": "l"
        }, {
            "v": 29
        }, {
            "v": 36
        }, {
            "v": 20
        }, {
            "v": "Nis"
        }, {
            "v": "Date(2014, 02, 25)"
        }, {
            "v": "Bure 5"
        }, {
            "v": "e"
        }, {
            "v": "Da"
        }, {
            "v": "gotovinsko"
        }, {
            "v": 36
        }, {
            "v": "kupljeno gorivo za setvu"
        }]
    }, {
        "c": [{
            "v": 2
        }, {
            "v": "djubrivo"
        }, {
            "v": "b432"
        }, {
            "v": 5
        }, {
            "v": 5
        }, {
            "v": "m3"
        }, {
            "v": 100
        }, {
            "v": 120
        }, {
            "v": 20
        }, {
            "v": "STR polja"
        }, {
            "v": "Date(2014, 02, 25)"
        }, {
            "v": "dvoriste iza"
        }, {
            "v": "e"
        }, {
            "v": "da"
        }, {
            "v": "kompenzacija"
        }, {
            "v": 120
        }, {
            "v": "Djubrenje"
        }]
    }]
}

但我使用一个带有以下代码的表来执行此操作:

try {
      /* Establish the database connection */
      $conn = new PDO("mysql:host=localhost;dbname=$dbname", $username, $password);
      $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

      /* select all the weekly tasks from the table googlechart */
      $result = $conn->query('SELECT * FROM zalihe');

      $rows = array();
      $table = array();
      $table['cols'] = array(

        array('label' => 'ID', 'type' => 'number'),
        array('label' => 'Vrsta', 'type' => 'string'),
    array('label' => 'Artikl', 'type' => 'string'),
    array('label' => 'Neto kol', 'type' => 'number'),
        array('label' => 'Bruto kol', 'type' => 'number'),
    array('label' => 'Mera', 'type' => 'string'),
        array('label' => 'Neto iznos', 'type' => 'number'),
    array('label' => 'Bruto', 'type' => 'number'),
        array('label' => 'Porez', 'type' => 'number'),  
        array('label' => 'Dobavljac', 'type' => 'string'),
        array('label' => 'Datum', 'type' => 'date'),
        array('label' => 'Skladiste', 'type' => 'string'),
        array('label' => 'Valuta', 'type' => 'string'),
        array('label' => 'Placeno', 'type' => 'string'),
        array('label' => 'Nacin placanja', 'type' => 'string'),
        array('label' => 'Placeni iznos', 'type' => 'number'),
        array('label' => 'Opis', 'type' => 'string')
    );
        /* Extract the information from $result */
        foreach($result as $r) {
          $temp = array();
          // the following line will be used to slice the Pie chart
          $temp[] = array('v' => (int) $r['ID']); 
          $temp[] = array('v' => (string) $r['vrsta']);
          $temp[] = array('v' => (string) $r['artikl']); 
          $temp[] = array('v' => (int) $r['neto_kol']);
          $temp[] = array('v' => (int) $r['bruto_kol']);
          $temp[] = array('v' => (string) $r['jmere']); 
          $temp[] = array('v' => (int) $r['neto_iznos']);
          $temp[] = array('v' => (int) $r['bruto_iznos']);
          $temp[] = array('v' => (int) $r['porez']);
          $temp[] = array('v' => (string) $r['dobavljac']); 
          $temp[] = array('v' => 'Date('.str_replace('-',', ',($r['datum'])).')');
          $temp[] = array('v' => (string) $r['skladiste']); 
          $temp[] = array('v' => (string) $r['valuta']); 
          $temp[] = array('v' => (string) $r['placeno']); 
          $temp[] = array('v' => (string) $r['nacin_placanja']); 
          $temp[] = array('v' => (int) $r['placeni_iznos']);
          $temp[] = array('v' => (string) $r['opis']); 


          // Values of each slice

          $rows[] = array('c' => $temp);
        }

    $table['rows'] = $rows;

    // convert data into JSON format
    $jsonTable = json_encode($table);
    //echo $jsonTable;
    } catch(PDOException $e) {
        echo 'ERROR: ' . $e->getMessage();
    }
    echo $jsonTable;

现在我还有一张表，我还需要从中获取数据并将其放入同一个 json 文件中:

这张表是:

CREATE TABLE IF NOT EXISTS `skladista` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `naziv` text NOT NULL,
  `kapacitet` int(11) NOT NULL,
  `jmere` text NOT NULL,
  `vlasnistvo` text NOT NULL,
  `beleske` text NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

现在我如何将第二个表“skladista”中的数据添加到php文件并使用两个mysql表创建一个php-JSON文件...

我将轻松添加“cols”，因为它只是“字符串名称”，但随后我需要从第二个表中获取数据...

那么如何:mysql 表“zalihe”+“skladista”=一个 json 文件？

请帮忙!

Answer 1

也许这个小例子会对你有所帮助..

// Get the max key of your already existed $temp array
$max_key_val = max(array_keys($temp));

// Fetch data from table-skladista
$data = mysql_query("Your-SQL-query");
while($row = mysql_fetch_array($data))
{
    $temp[$max_key_val++] = array('v' =>  $row['tbl_col_name']); 
}

// Then follow your same procedure
$rows[] = array('c' => $temp);
$table['rows'] = $rows;

$jsonTable = json_encode($table);