使用联接对两个表的 json 数据进行编码-解网

问：

有谁知道我的代码问题？我有两张桌子，分别是sales_details和sales_payment。销售详细信息，您可以在其中查看所有详细信息，另一个表格用于付款信息/交易。Sales 详细信息具有主键，sales_payment表具有 FK。为了合并所有数据，我使用了内部连接语句。

这是我的数据示例。我使用 join 运行一个示例查询。所有图片都是查询的结果。我裁剪了它，因为我无法在横向模式下拍摄长截图。

销售明细表：

销售明细表（续）：

销售付款表：

这是我尝试过的：

public function get_payment_info_by_id($payment_info_id) {
    $query = $this->db->query("SELECT * FROM tbl_sales_details AS tsd INNER JOIN tbl_sales_payments AS tsp ON tsp.sales_id = tsd.sales_id WHERE tsd.sales_id = $payment_info_id");
    
    echo json_encode($query->fetch_object());
}

返回的json数据：

正如你所看到的，JSON数据。它没有返回tbl_sales_payment中的所有数据。

我想像这样返回我的 json 数据：

{
    sales_balance: "4601.60"
    sales_company: ""
    sales_cp: ""
    sales_date: "2021-01-12 01:26:33"
    sales_discount: "0.00"
    sales_dr: "5768"
    sales_height: "8.00"
    sales_id: "3"
    sales_media: "Sticker on Sintra"
    sales_net_amount: "8601.60"
    sales_particulars: "Authorized Personnel Only"
    sales_po: "100549"
    sales_price_unit: "4.00"
    sales_qty: "15.00"
    sales_si: "1794"
    sales_so: "1234"
    sales_total: "7680.00"
    sales_total_area: "128.00"
    sales_unit: "in"
    sales_vat: "921.60"
    sales_width: "16.00"
    "payments": [
      {
        payment_id: "3"
        payment_amount: "1000.00"
        payment_date: "2021-01-15"
        payment_remarks: ""
      },
      {
        payment_id: "4"
        payment_amount: "1000.00"
        payment_date: "2021-01-18"
        payment_remarks: ""
      },
      {
        payment_id: "5"
        payment_amount: "2000.00"
        payment_date: "2021-01-29"
        payment_remarks: ""
      }
    ]
}

php json mysqli

public function get_payment_info_by_id($payment_info_id) {
    $return_data = [];
    $query = $this->db->query("SELECT * FROM tbl_sales_details AS tsd INNER JOIN tbl_sales_payments AS tsp ON tsp.sales_id = tsd.sales_id WHERE tsd.sales_id = $payment_info_id");
    
    while ($obj = $query->fetch_object()) {
        $return_data[] = $obj;
    }

    echo json_encode($return_data);
}

$tsd_query = $this->db->query("SELECT * FROM tbl_sales_details WHERE sales_id=$payment_info_id");  $tsd_result = $tsd_query ->fetch_object();  $tsp_query = $this->db->query("SELECT * FROM tbl_sales_payments WHERE sales_id=$payment_info_id");  while ($obj = $tsp_query->fetch_object()) {         $paymetns[] = $obj; } $tsd_result->payments = $payments; echo json_encode($tsd_result);

上一个：如何在php中将多行作为json传递

下一个：带有 bind_result（） / fetch（）的 JSON 类型字段 mysqli 准备好的语句不起作用

使用联接对两个表的 json 数据进行编码

Encode json data with two tables using join

评论

评论