在运行json_encode时如何在同一个变量中引入多行（mysql）结果？

Question

I am running a POST + json code to collect data from database, all results come with only one value (this is correct), however there is only one column which should show more than one value but shows only the first one. What I need to change in my code to get this list instead of the first row result?

I've run one MYSQL query linking three databases those share the same id PCRNo, the first two databases tPCR and tcomplement should only have one result and the third one should receive more results due to we can have more lines with the same id.

This is my JavaScript

<script> 

  $(document).ready(function(){
        $('#table').on('click', '.fetch_data', function(){
          var pcr_number = $(this).attr('id');

          $.ajax({
            url:'fetch.php',
            method:'post',
            data:{pcr_number:pcr_number},
            dataType:"json",
            success:function(data){

              $('#PCR').val(data.PCRNo);
              $('#PCC').val(data.PCC);
              $('#PCR_Creation').val(data.Creation_Date);
              $('#PCR_Status').val(data.Stage);
              $('#Required_Completion').val(data.Required_Completion);
              $('#description').val(data.Name);
              $('#Comments').val(data.Comments);
              $('#originator').val(data.Creator);
              $('#change_type').val(data.Category);
              $('#product_type').val(data.Product);
              $('#req_dept').val(data.Department);
              $('#flow').val(data.Flow_Start_Date);
              $('#own').val(data.Owning_Site);
              $('#impacted').val(data.Impacted_Site);
              $('#approval').val(data.Meeting_Status);
              $('#review').val(data.Review_Date);
              $('#cat').val(data.Cat);
              $('#cost').val(data.Cost);
              $('#labor').val(data.Labour);
              $('#volume').val(data.Volume);
              $('#request').val(data.Request);
              $('#justification').val(data.Justification);
              $('#PCNlist').val(data.PCNNo);
              $('#monitor').val(data.Monitor);
              $('#env').val(data.Environment);
              $('#trial').val(data.Trial);
              $('#resp').val(data.Responsible);
              $('#deadline').val(data.Deadline);      
              $('#dataModal').modal('show');

            }
          });
        });

        $(document).on('click', '#update', function(){  
        var pcr_number = document.getElementById("PCR").value;  
        var Comments= document.getElementById("Comments").value; 
        var approval= document.getElementById("approval").value;
        var review= document.getElementById("review").value;
        var cat= document.getElementById("cat").value;
        var monitor= document.getElementById("monitor").value;
        var env= document.getElementById("env").value;
        var trial= document.getElementById("trial").value;
        var resp= document.getElementById("resp").value; 
        var deadline= document.getElementById("deadline").value;
        var PCC = document.getElementById("PCC").value;


        $.ajax({  
            url:"edit.php",  
            method:"POST",  
            data:{pcr_number:pcr_number, Comments:Comments, PCC:PCC, approval:approval, review:review, cat:cat, monitor:monitor, env:env, trial:trial, resp:resp, deadline:deadline},  
            dataType:"text",
            success:function(data)  
            {  
                alert('PCR Information Updated');  

            }

        });  
        });
  }); 
 </script> 

this is my fetch.php

<?php
$SelectedPCRNo = $_POST['pcr_number'];
if(isset($_POST['pcr_number']))
{
  $output = '';
  $hostname = "localhost";
  $username = "root";
  $password = "";

  $databaseName = "change_management";

  $dbConnected = @mysqli_connect($hostname, $username, $password);
  $dbSelected = @mysqli_select_db($databaseName,$dbConnected);

  $dbSuccess = true;
  if ($dbConnected) {
    if ($dbSelected) {
      echo "DB connection FAILED<br /><br />";
      $dbSuccess = false;
    }       
  } else {
    echo "MySQL connection FAILED<br /><br />";
    $dbSuccess = false;
  }

  $sql = mysqli_query($dbConnected, "SELECT *   FROM change_management.tPCR INNER JOIN change_management.tcomplement ON change_management.tPCR.PCRNo = change_management.tcomplement.PCRNo INNER JOIN change_management.tPCN ON change_management.tPCR.PCRNo = change_management.tPCN.PCRNo WHERE tPCR.PCRNo = '".$_POST['pcr_number']."'");  

  $row = mysqli_fetch_array($sql);     
  echo json_encode($row); 
}  
 ?>

I have no problems with the results and the table is filled OK, only the #PCNlist should be filled with the values of all rows it is related and now just is just coming one value, the first row only. Is there any way to bring the whole PCNlist only changing some code at the fetch.php?

Answer 1

If I understood you correctly, the table tPCN can contain multiple rows associated with each PCR number. And you want to fetch all these rows and return them in your JSON.

If you want to achieve that, but also make sure the other two tables only return one row, then I think simply you should remove the JOIN to tPCN in your first query, and then create a second query to fetch the tPCN rows specifically.

$output = [];
$stmt = $dbConnected->prepare("SELECT * FROM change_management.tPCR INNER JOIN change_management.tcomplement ON change_management.tPCR.PCRNo = change_management.tcomplement.PCRNo WHERE tPCR.PCRNo = ?");
$stmt->bind_param('s', $_POST['pcr_number']);
$stmt->execute();
$result = $stmt->get_result();

//select a single row from the result and assign it as the output variable
if ($row = $result->fetch_assoc()) {
    $output = $row;
}

$stmt2 = $dbConnected->prepare("SELECT * FROM change_management.tPCN WHERE PCRNo = ?");
$stmt2->bind_param('s', $_POST['pcr_number']);
$stmt2->execute();
$result2 = $stmt2->get_result();
$output["tPCN"] = array(); //create a new property to put the tPCN rows in

//loop through all the tPCN rows and append them to the output
while ($row2 = $result2->fetch_assoc()) {
    $output["tPCN"][] = $row2;
}

echo json_encode($output);

This will produce some JSON with this kind of structure:

{
  "PCRNo": "ABC",
  "CreationDate": "2019-08-07",
  "Name": "A N Other",
  //...and all your other properties, until the new one:
  "tPCN": [
    {
      "SomeProperty": "SomeValue",
      "SomeOtherProperty": "SomeOtherValue",
    },
    {
      "SomeProperty": "SomeSecondValue",
      "SomeOtherProperty": "SomeOtherSecondValue",
    }
  ]
}

You will then need to amend your JavaScript code to be able to deal with the new structure. Since I don't know exactly which fields come from the tPCN table, I can't give you an example for that, but hopefully it's clear that you will need to loop through the array and output the same HTML for each entry you find.

---

N.B. As you can see I re-wrote the query code to use prepared statements and parameterised queries, so you can see how to write your code in a secure way in future.

---

P.S. You have a lot of code there in the "success" function just to set the values of individual fields. You might want to consider using a simple JS templating engine to make this less verbose and cumbersome, and generate the HTML you need with the values automatically added into it in the right place. But that's a separate issue, just for the maintainability of your code