0

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');
        }
     }

Edited by Reverend Jim: mistake click on attacMoved to programming forumhed file

Attachments Capture.PNG 40.07 KB
2
Contributors
1
Reply
18
Views
4 Months
Discussion Span
Last Post by Dascal_1
1

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.

Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.