Insane Script

[miffe]

Here I have an odd script to do if anyone is up to the task for helping me a little bit... it's nothing too hard but it involves a bit of PHP and SQL, I considered it appropriate in posting it here because the script itself can be a PHP script.Basically I have 3 related tables in an mySQL db: Invoices, Sale, Lend.Invoices:

id  | name    | date--------------------29  | bla2    | 2003/07/051   | bla     | 2004/02/0150  | ok      | 2004/04/20134 | new1    | 2005/03/024   | test    | 2007/03/29

Sale:

idSale | idPart | idInvoice1 - 03 - 12 - 09 - 13 - 20 - 294 - 01 - 295 - 94 - 1346 - 34 - 1347 - 15 - 134

Lend:

idLend | idPart | idInvoice | time1 - 23 - 502 - 19 - 503 - 40 - 504 - 81 - 505 - 33 - 46 - 99 - 47 - 00 - 4

So basically you have an invoices box, which might have been for sales or for parts being lent for a period of time... my problem is that in the invoices section the id's are not ordered... I dont want an ORDER BY nonsence, that's not my problem... I mean in relation to the date... lets suppose I had 200 invoices and many of them where deleted or skipped or not introduced into the system and only those where remaining with different dates... now I want to REarrange them according to the existing numbers.In this case: I only have 5 invoices, so I need to renumber them 1,2,3,4,5... but I also have to change all relationships in 'sale' and in 'lend' first... so I guess I have to create a buffer of some sort, an array? or something where I display the old value and the new ordered value, then change all in sale and in lend, and afterwards change the invoice ID's...Does anyone have an idea on where to start? some code? or something ^^Thanks a lot,Miffe

[pulpfiction]

Using a stored procedure, create a temporary table and get all the data from Invoices [auto-increment in temp table] and then update invoice table ID and the other 2 tables....

[miffe]

Using a stored procedure, create a temporary table and get all the data from Invoices [auto-increment in temp table] and then update invoice table ID and the other 2 tables....

Let me see if I understand...1. Create temporary table 'invoiceTemp'2. Copy all data from invoices to invoiceTemp (auto-increment the id) and a row to specify the old id number3. Loop through new invoiceTemp resultset and change all id's on 'invoices' to new values where old id # = invoice actual id #4. Loop through 'lend' and 'sale' resultsets and change all id's on them the same was as the latter step.Is this all? or did I do something incorrectly?(edit: BTW I've never used stored procedures, thats why I thought PHP would be an easier script to make, but changing large numbers of records might make it unstable right? or wont it affect?)

[justsomeguy]

Just out of curiosity, why do you want to do this? There's no real benefit in having a consecutive list of IDs in MySQL. You also might run into some consistency problems. For example, if you rename invoice #29 to invoice #1, and then change all references to invoice 29 in the other two tables to reference invoice 1, that's fine. But then if you go to renumber invoice 1 to invoice 2, and try to change all the references in the tables from invoice 1 to invoice 2, you will be changing all of the references that you previously changed to invoice 1, so the old invoice 29 will lose all of the linked info.

[miffe]

Just out of curiosity, why do you want to do this? There's no real benefit in having a consecutive list of IDs in MySQL. You also might run into some consistency problems. For example, if you rename invoice #29 to invoice #1, and then change all references to invoice 29 in the other two tables to reference invoice 1, that's fine. But then if you go to renumber invoice 1 to invoice 2, and try to change all the references in the tables from invoice 1 to invoice 2, you will be changing all of the references that you previously changed to invoice 1, so the old invoice 29 will lose all of the linked info.

Ok well, let's say, I was inserting all values from older invoices into the system but then i saw they were mixed up, so I decided to put them all in as they were, the problem is that the ID is the principal ordering value, I can order by Date but I prefer the ID to be the main one as each new invoice uses the 'next' id number, and the dates are a bit mixed up consecuently, for example:invoice 1 has a 2005 dateinvoice 2 has a 2004 dateif I order by date thats fine, but what if I want to order by ID which is the default...so I decided to make this script.MY solution to what you said is do something like this, I know consistency problems would occur but what if I put all id's to 1,000,000 + idnumber, as well as in all other related tables 1,000,000 + idnumber... then rename all to the consecutive order..or something like that,im not sure if i explain myself but any help would be appreciated,thx... miffe

[justsomeguy]

You could do it, I guess I just don't really see the point. The ID is for internal use by the database, it doesn't matter if the IDs are not in chronological order. If you want to sort the records by chronological order, that's what you have the date column for, right?. You can do something like what you mentioned though if you really want to. You would need to get all of the records from the main table, create a counter for the ID, and just start inserting. After each insert, you would update the other two tables to update the old ID to the new ID. You wouldn't need to delete and insert from the other two tables.