April 20th, 2013, 08:59 AM
PHP MySQL Question...
Hi, I have a dilemma that I hope someone can help with...
I've got a database that contains invoices which are contained in two tables...
client_invoices and client_invoices_items
client_invoices_items contains all the line items for each invoice.
Both tables contain the following fields (among others)...
"ownerid" (this is a username)
"invoice_date" (this is a timestamp)
The client_invoices table contains a field "invoiceid" but this does not exist in the other table.
What I want to do is this...
Create a field in the client_invoices_items table called "invoiecid2". I can do this in phpmyadmin.
For each record in the client_invoices_items table where the "ownerid" and "invoice_date" match the same values in the client_invoices table - read the "invoiceid" field from the client_invoices table and update "invoiceid2" in the client_invoices_items table with that value.
The goal of this is to add the invoice ID to every line item so it matches with its parent invoice.
I hope I explained that correctly - is this possible? Ideally I'm looking for someone to write this for me and I'm aware that this isn't a freelancer forum, plus - I don't want to break any forum rules - but I'm willing to pay for this to be written for me.
Thanks for looking.
April 20th, 2013, 10:01 AM
invoiceid2 = (
ownerid = client_invoice_items.ownerid
AND invoice_date = client_invoice_items.invoice_date
April 20th, 2013, 12:09 PM
Wow that's great I'll try that - thanks!
April 20th, 2013, 12:18 PM
I got the following error...
#1242 - Subquery returns more than 1 row
I think that is because there can be multiple items for each invoice? So there are rows in client_invoices_items that neeed associating with the same invoiceid - but only one invoiceid for each row in the client_invoices table.
April 20th, 2013, 03:19 PM
No, it means your data is broken -- which was to be expected due to the inappropriate data model. A customer and an invoice date do not uniquely identify an invoice. In other words, some customers have gotten multiple invoices at the exact same time. So it's not possible to automatically find the invoice for a given invoice item, since there's no way of telling which one it is. You need to repair your data first.
You can get the "ambiguous" invoices with
(ownerid, invoice_date) IN (
COUNT(*) > 1
, invoice_date DESC
You need to go through all of these and try to make them unique with regard to the customer ID and the date. That is, for every group of invoices with the same customer and date, you choose a single invoice and delete the others.
If you could repair your data, run the query from the last post again, and you're done.
If you still have multiple invoices in some case, this is gonna be ugly. But first, see if you can repair your data.
I hope you learn from this and make sure you have a robust data model before you start collecting data. It might be a good idea to post the current structure of your tables in the database forum so that they can check it and prevent you from running into the same problems again.
Last edited by Jacques1; April 20th, 2013 at 03:23 PM.
April 20th, 2013, 04:19 PM
Thank you for your reply, I think you are saying in some cases, a customer may have had several different invoices generated at once - so this will cause the query not to work as it will find a matching timestamp for more than one invoice ID. I hadn't thought of that.
The problem that caused this is not the data model unfortunately - it is because the developer of the billing software has encrypted the "invoiceid" field in the items table - so they cannot be matched up conventionally. This was done purely to prevent us from moving to a different system :-( Unfortunately - the developer no longer maintains the software and so we have no choice but to try and find a way to export our data.
I'm running that query now so I will see how much editing we will need to do. Thanks for helping me with this, it is greatly appreciated.
April 20th, 2013, 04:34 PM
I see. Well, if the encryption is crap and the same IDs have the same cyphertext, you'll at least be able to group the invoice items by invoices.
Originally Posted by chris74
April 20th, 2013, 04:49 PM
That's a little beyond my expertise. I know it uses some kind of "seed". The developer provided a PHP script that should allow access to the data in that column - but the "usage" description they provided made no sense and he would not offer any instructions - the script itself is ioncube encryped.
That query is still running. There are 75,000 invoices so I'll presume it will just take some time to complete. If there are thousands of duplicated ones, I think I will be stuck :-(
April 21st, 2013, 01:19 AM
The query didn't complete, I left it overnight and phpmyadmin still shows "Loading" but the query isn't running any longer. Is there anything else I can do to identify these duplicates?
April 24th, 2013, 02:02 PM
Originally Posted by Jacques1
Is there an easy way of running this query with a small addition?
I need to update the invoiceid2 field exactly as above - but only where invoice_date (which is a timestamp) in the two tables is within a certain tolerance - i.e within 3 minutes or 5 minutes. Is it possible to do this in a query without needing PHP?