John Davidson

Message:

The code below works on three tables:

iStock, iInvoicebook, iInvoiceitems

iInvoicebook and iInvoiceitems are linked bt ID and dInvoicebookFKEY,

iStock - This table stores all products with clients name and quantity. To generate an invoice, the code gets DISTINCT clients and inserts to iInvoicebook.

After this, the code inserts all product details from the iStock table to iInvoiceitems.

The code below mostly works, except that when I try to get iInvoicebook ID and try to insert it (as foreign key) in iInvoiceitems, it inserts the old ID of iInvoicebook based on clientID.

I want the newly added ID of iInvoicebook to be inserted in iInvoiceitems.

if($CurrDate == $LastDayofMonth) 

{

    // Get Distinct clients and add it into Main table  "Invoicebook"

    $getClients = "SELECT  DISTINCT iClientName AS CLT,  Product from stock where quantity > '0' AND InvoiceDate ='$LastDayCM'";

    $stmt = ExecuteQuery($getClients); // execute the query

    if ($stmt->rowCount() > 0) { // check condition: if record count is greater than 0



        while ($row = $stmt->fetch()) { // begin of loop

            $NewNo = Executerow("Select max(ibInvoiceNo) AS NEXTNO from invoicebook");

            $Nor = $NewNo["NEXTNO"] + 1 ;

            $instInInvoice = ExecuteStatement("INSERT INTO invoicebook (ibClientName, Product, InvoiceNo)

            Values( '".$row["CLT"]."','".$row["Product"]."', '".$Nor."')");  

        }

    }



    // Now no Disticnt value. Get all stock values of related client and insert into     "invoiceitems"  table

    $getDetails = "select iClientID, product, quantity from stock where quantity > '0' AND InvoiceDate ='$LastDayCM' AND  iClientName = '".$getClients["iClientName"]."'";



    $dstmt = ExecuteQuery($getDetails); // execute the query



    if ($dstmt->rowCount() > 0) { // check condition: if record count is greater than 0

        while ($row = $dstmt->fetch()) { // begin of loop

            $InvoiceFKEY = Executerow("Select INVID from invoicebook where dClientID = '".$row["iClientID"]."'");

            $DetailsInsrt = ExecuteStatement("INSERT INTO invoiceitems (dproduct, dQuantity, dRate, dInvoicebookFKEY)

            Values( '".$row["product"]."','".$row["quantity"]."','".$rate."', '".$InvoiceFKEY["INVID"]."')");

        }

    }

}

---

My Answer:
$query = "SELECT DISTINCT clientID FROM iStock";
$result = mysqli_query($conn, $query);

while($row = mysqli_fetch_assoc($result)) {
$clientID = $row['clientID'];

// Insert clientID into iInvoicebook
$insert_query = "INSERT INTO iInvoicebook (clientID) VALUES ('$clientID')";
mysqli_query($conn, $insert_query);

// Get the newly added ID of iInvoicebook
$invoicebookID = mysqli_insert_id($conn);

// Insert product details into iInvoiceitems
$insert_items_query = "INSERT INTO iInvoiceitems (dInvoicebookFKEY, productID, quantity) SELECT '$invoicebookID', productID, quantity FROM iStock WHERE clientID = '$clientID'";
mysqli_query($conn, $insert_items_query);
}
}

This code should now insert the newly added ID of iInvoicebook into iInvoiceitems as the foreign key. Make sure to replace $conn with your database connection variable.