php - 在 php 中运行两个选择查询并将其编码为 json 格式？

Question

我有两个表 billing_all 和 payment_details。我正在尝试编写 php 代码，以便我可以在相同的 php 代码中运行 2 个查询，并将它们的数据编码为 json 格式。我目前正在使用以下代码在没有 json 编码的情况下实现这一点:-

<?php
        include "config.php";
        $dbname ="webappdb";
        $con = mysqli_connect($server_name,$mysql_user,$mysql_pass,$dbname);
         if(!$con)
         {
           echo "Connection Error".mysqli_connect_error();
         }
         else{
        //echo "";
         }
$sql = "SELECT SUM(total_wt) FROM `billing_all` WHERE date ='15-Apr-2016';";
$sql .= "SELECT pymt_amount, mode_of_pymt FROM  `payment_details` WHERE DATE ='15-Apr-2016';";
// Execute multi query
if (mysqli_multi_query($con,$sql))
{
  do
    {
    // Store first result set
    if ($result=mysqli_store_result($con)) {
      // Fetch one and one row
      while ($row=mysqli_fetch_row($result))
        {
        printf("%s%s\n",$row[0],$row[1]);
        }
      // Free result set
      mysqli_free_result($result);
      }
    }
  while (mysqli_next_result($con));
}
mysqli_close($con);
?>

如何将接收到的数据编码为json格式？我试过类似的东西:-

 // Store first result set
    if ($result=mysqli_store_result($con)) 
    {
    $r = array();
    while($row = mysqli_fetch_array($result))
    {
        array_push($r,
        array('total_wt'=>$row[0],
        'pymt_amount'=>$row[0],$row[1],
        'mode_of_pymt'=>$row[0],$row[1]));
    }

    echo json_encode(array("result"=>$r));

Which does not give me the expected result.How can I encode the data in the right way? The following is the structure for the 2 tables

I am new to programming any help is appreciated.Thank you?

Answer 1

<?php

//Joining both tables on the basis of doc_no column

 $sql = "SELECT billing_all.SUM(total_wt) AS total
        ,payment_details.pymt_amount
        ,payment_details.mode_of_pymt
    FROM billing_all
        ,payment_details
    WHERE billing_all.doc_no = payment_details.doc_no
        AND billing_all.DATE = '15-Apr-2016'";


// Executing query
$res = mysqli_query($con,$sql); 

//initializing array to store result
$rows = array(); 

//Iterating result and storing each row in $r then array $rows to covert result set into array because json accept array as parameter.

while($r = mysqli_fetch_assoc($res)) 
{
    $rows[] = $r; 
}

//print whole array in json format
echo  json_encode($rows); ?>