I want to fetch invoice data from two tables

table a is in single row and table b data should be multiple rows

single data is fetching correctly but facing problem with table 2 multiple data fetch.

php file

<?php   

$connection= mysqli_connect("localhost","root","","invoice");

  $invID = $_POST['ID'];

     $Invo = "SELECT a.in_no, a.client, a.add, a.Date, a.subTotal a.gTotal, 
      b.id, b.item, b.quantity, b.price from invoice a, invoiceItems b where a.in_no = b.in_id AND  in_no = '$invID' ";

 $runQuery = $connection->query( $Invo);

if($runQuery->num_rows > 0) { 
$result = $runQuery->fetch_array();
} // if num_rows

$connection->close();
echo json_encode($result);
?>

js file

function Invoice (ID = null) {

if(ID) {
    $.ajax({
        url: 'fetch.php',
        type: 'post',
        data: {ID: ID},
        dataType: 'json',
        success:function(response) {

            // invoice id 
            $(".footerClass").append('<input type="hidden" name="invoId" id="iD" value="'+response.in_no+'" />');

            // invoice no
            $("#No").val(response.in_no);
            // Name
            $("#Name").val(response.client);
            // pat.Address
            $("#Add").val(response.add);
            // invoice date
            $("#Date").val(response.Date);

            $('#item_'+id[1]).val(response.item);               
            $('#quantity').val(response.quantity);
            $('#price').val(response.price);
            $('#total').val(response.quantity*response.price);
            calculateTotal();
            }
         });
        }else {
            alert('error loading data');
        }
     }

Hi @nishto,

Have you tried this query in a mysql CLI and confirmed the select fetches data properly?
To me, the SQL statement is not right:
"SELECT a.in_no, a.client, a.add, a.Date, a.subTotal a.gTotal, b.id, b.item, b.quantity, b.price from invoice a, invoiceItems b where a.in_no = b.in_id AND in_no = '$invID' ";
You have in_no = $invID: I believe you should have a.in_no, but not sure if it is a requirement.

Also, if you are expecting to have multiple results, I would suggest to declare $result as array and store the information through a while loop, otherwise, your fist match of the query is your only fetched data. Also, when showing the data you will need to loop through the resulted array to display data.
For testing purpouses, you can console.log the object to see how many entryes are there.

Hopefully this helps.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.