#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    37
    Rep Power
    1

    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.

    In PHP...

    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.
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,921
    Rep Power
    1045
    sql Code:
    UPDATE
    	client_invoice_items
    SET
    	invoiceid2 = (
    		SELECT
    			invoiceid
    		FROM
    			client_invoices
    		WHERE
    			ownerid = client_invoice_items.ownerid
    			AND invoice_date = client_invoice_items.invoice_date	
    	)
    ;
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    37
    Rep Power
    1
    Wow that's great I'll try that - thanks!
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    37
    Rep Power
    1
    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.
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,921
    Rep Power
    1045
    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
    sql Code:
    SELECT
    	*
    FROM
    	client_invoices
    WHERE
    	(ownerid, invoice_date) IN (
    		SELECT
    			ownerid
    			, invoice_date
    		FROM
    			client_invoices
    		GROUP BY
    			ownerid
    			, invoice_date
    		HAVING
    			COUNT(*) > 1	
    	)
    ORDER BY
    	ownerid DESC
    	, 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.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    37
    Rep Power
    1
    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.
  12. #7
  13. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,921
    Rep Power
    1045
    Originally Posted by chris74
    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.
    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.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    37
    Rep Power
    1
    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 :-(
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    37
    Rep Power
    1
    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?
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    37
    Rep Power
    1
    Originally Posted by Jacques1
    sql Code:
    UPDATE
    	client_invoice_items
    SET
    	invoiceid2 = (
    		SELECT
    			invoiceid
    		FROM
    			client_invoices
    		WHERE
    			ownerid = client_invoice_items.ownerid
    			AND invoice_date = client_invoice_items.invoice_date	
    	)
    ;

    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?

IMN logo majestic logo threadwatch logo seochat tools logo