For Each Loop Insert

[SH29489]

Hi Guys,

Looking for a bit of coding help with a query I am building. I have two tables, one contains all of the billable items that have been used by the specific user and the charges for said items and the other will contain the userID and a generated invoiceID and the BillableLine.

What I want to do now is bill them for the items, so the script will loop through all the charges in the billable items table and populate a second table with the grouped charges so everything can then be fpdf'd over from the cron. The problem I am facing is that I need to create an invoice ID and then use this ID for the invoice lines to th at I will be able to report using the invoiceID for each user.

 

include("../includes/config.php");

// Get's the last ID from the dsn_invoice_id field (Not an auto increment)
$sql2 = "SELECT dsn_invoice_id FROM DSN_invoices ORDER BY chargeID DESC LIMIT 1";
$result2 = mysqli_query($db, $sql2);while($row2 = mysqli_fetch_assoc($result2)) {  $dsn_invoice_id = $row2["dsn_invoice_id"];}

// Get the list of charges / billale items 
$sql1 = "SELECT userID, chargeID FROM posted_charges WHERE charge_processed = '0'";
$result1 = mysqli_query($db, $sql1); while($row1 = mysqli_fetch_assoc($result1)) { $userID = $row1["userID"];$chargeID = $row1["chargeID"];

// Insert the item, with an increment of + 1.
$sqlIns = "INSERT INTO dsn_invoice_id (dsn_invoice_id, userID, ChargeID) VALUES ('". ($currentnumber + 1) ."','$userID','$chargeID')";
        }
        
    

 

Thanks for taking the time to read this!

Kind regards,

[gw1500se]

This is really a MySQL question not PHP. You can let MySQL create the ID for you. Create a join table that associates the customer with the invoice ID and make that field auto increment. Each customer can then have multiple invoice IDs.

Edited June 19, 2019 by gw1500se

[ginerjm]

not following all of your discussion but suggesting this:

do not create unnecessary table entries for something already saved in the billable items table

do create the user record for the new invoice id along with its date and anything else necessary

add the new invoice id to EACH billable item in the billable items table.  I assume that you already have the userid in that same table.

no need for saving any kind of total since it can always be re-calculated using a query.

[gw1500se]

As I said, post your schema and we will be better able to help you. In the meantime this should be moved to the MySQL forum.

[SH29489]

OK, so I just realised I would be doubling up data so changed it a little and included the Schema below.

tbl_invoice_numbers
invoice_number (auto_increment)

tbl_billable_lines
billable_lined_id (auto_increment)
product_id (int)
user_id (int)
charge (int)
invoice_number (int)
date_processed (datetime)

What I am thinking now is that the tbl_billable_lines has all the information we need to I can add the invoice_number to this. The tbl_invoice_numbers table would purely be used to keep track of the invoice numbers so we can then insert a new ID, and get this ID and update this on the tbl_billable_lines.

The problem I am having is the running through each user and finding all of their tbl_billable_lines and then adding a new invoice ID to the billable lines. the idea is that I will be able to pull the invoice numbers where the date_processed is empty and then pull all off the billable lines into one pdf to email.

Kind regards,

[ginerjm]

I would begin with the invoice numbers table and perhaps add the user id to it.  Then insert a new record there with the user id , then get back the just-created invoice number.  You don't have to add the user id since you have it in the billable lines table already.  Next time though you should think about your db design.  BTW - where IS your user data stored, such as address, contact info, etc.?

Second - you would then select those billable lines that match that user id and do not yet have an invoice number.  The question that comes up tho is do you want to invoice ALL of these lines or do you give the user a screen to select those to be invoiced?

Once you have decided on which billable lines are to go on the new invoice it's a simple matter to do an update query to add the invoice number to the matching billable line ids for that user id which would be a simple query, not a loop process.   Read up on the "where in() " sql clause.

[SH29489]

Thanks for getting back to me. I now have this working!