John Davidson

php - Select ID from one table and insert in another table with while loop

0 comments
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.

Rate this post

3 of 5 based on 1912 votes

Comments




© 2024 Hayatsk.info - Personal Blogs Platform. All Rights Reserved.
Create blog  |  Privacy Policy  |  Terms & Conditions  |  Contact Us