Re: [PHP] Inserting rows with missing IDs
Richard Quadling wrote: On 3 May 2010 14:34, Andre Polykanine wrote: Hello everyone, It's not a strictly PHP question, however since I use that with PHP, I'm asking it there. How can I accomplish the task of inserting rows into MySql database with missing IDs? Say, I have rows with IDs 1, 2, 3, 5, 9, 12, 17, and 195. How do I make the check that allows to insert firstly the missing IDs and only then apply the auto-increment? Thanks! You can also reseed the autoinc column back to 0 and that will start filling in the gaps. ALTER TABLE tablename AUTO_INCREMENT = 0 may work for you (http://arstechnica.com/civis/viewtopic.php?f=20&t=123689 via http://tinyurl.com/3amlo2u) quite sure that once mysql hit's the max int limit it starts filling in the gaps too - like ~96.4% sure have to agree though, one shouldn't have any dependency on sequential numbers (unless it's a number table!) best, nathan And if they really want just a sequential number ID that they DO NOT depend upon, they can always drop the id from the table and then recreated it -- that way it will be automatically renumbered. Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Inserting rows with missing IDs
Richard Quadling wrote: On 3 May 2010 14:34, Andre Polykanine wrote: Hello everyone, It's not a strictly PHP question, however since I use that with PHP, I'm asking it there. How can I accomplish the task of inserting rows into MySql database with missing IDs? Say, I have rows with IDs 1, 2, 3, 5, 9, 12, 17, and 195. How do I make the check that allows to insert firstly the missing IDs and only then apply the auto-increment? Thanks! You can also reseed the autoinc column back to 0 and that will start filling in the gaps. ALTER TABLE tablename AUTO_INCREMENT = 0 may work for you (http://arstechnica.com/civis/viewtopic.php?f=20&t=123689 via http://tinyurl.com/3amlo2u) quite sure that once mysql hit's the max int limit it starts filling in the gaps too - like ~96.4% sure have to agree though, one shouldn't have any dependency on sequential numbers (unless it's a number table!) best, nathan -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re[2]: [PHP] Inserting rows with missing IDs
Hello Richard, Thanks, will try this!) -- With best regards from Ukraine, Andre Skype: Francophile; Wlm&MSN: arthaelon @ yandex.ru; Jabber: arthaelon @ jabber.org Yahoo! messenger: andre.polykanine; ICQ: 191749952 Twitter: m_elensule - Original message - From: Richard Quadling To: Andre Polykanine Date: Tuesday, May 4, 2010, 6:08:28 PM Subject: [PHP] Inserting rows with missing IDs On 3 May 2010 14:34, Andre Polykanine wrote: > Hello everyone, > It's not a strictly PHP question, however since I use that with PHP, > I'm asking it there. > How can I accomplish the task of inserting rows into MySql database > with missing IDs? Say, I have rows with IDs 1, 2, 3, 5, 9, 12, 17, and > 195. How do I make the check that allows to insert firstly the missing > IDs and only then apply the auto-increment? > Thanks! Using PHP, you could find the missing IDs by using the following steps. 1 - Get the current maximum ID. SELECT max(ID) as Max FROM table If you aren't interested in the ones before the first valid ID then also retrieve the min(ID). So you end up with $MinID = 1, $MaxID = 195. 2 - Get all the current IDs. SELECT ID FROM table So you end up with $KnownIDs = array(1,2,3,5,9,12,17,195); 3 - Use the range() function in PHP to build an array from the lowest id (or 1) to the highest id. $RangeIDs = range($MinID, $MaxID); 4 - Use array_diff($RangeIDs, $KnownIDs); to find the missing IDs. So ... outputs ... Array ( [3] => 4 [5] => 6 [6] => 7 [7] => 8 [9] => 10 [10] => 11 [12] => 13 [13] => 14 [14] => 15 [15] => 16 [17] => 18 [18] => 19 [19] => 20 [20] => 21 [21] => 22 [22] => 23 [23] => 24 [24] => 25 [25] => 26 [26] => 27 [27] => 28 [28] => 29 ) -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html EE4Free : http://www.experts-exchange.com/becomeAnExpert.jsp Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Inserting rows with missing IDs
On 3 May 2010 14:34, Andre Polykanine wrote: > Hello everyone, > It's not a strictly PHP question, however since I use that with PHP, > I'm asking it there. > How can I accomplish the task of inserting rows into MySql database > with missing IDs? Say, I have rows with IDs 1, 2, 3, 5, 9, 12, 17, and > 195. How do I make the check that allows to insert firstly the missing > IDs and only then apply the auto-increment? > Thanks! BTW, I agree with the other posters here. Buggering around with the ID is normally pointless unless you have SO many missing IDs that you are approaching the limit of the value of the column containing the ID. -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html EE4Free : http://www.experts-exchange.com/becomeAnExpert.jsp Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Inserting rows with missing IDs
On 3 May 2010 14:34, Andre Polykanine wrote: > Hello everyone, > It's not a strictly PHP question, however since I use that with PHP, > I'm asking it there. > How can I accomplish the task of inserting rows into MySql database > with missing IDs? Say, I have rows with IDs 1, 2, 3, 5, 9, 12, 17, and > 195. How do I make the check that allows to insert firstly the missing > IDs and only then apply the auto-increment? > Thanks! You can also reseed the autoinc column back to 0 and that will start filling in the gaps. ALTER TABLE tablename AUTO_INCREMENT = 0 may work for you (http://arstechnica.com/civis/viewtopic.php?f=20&t=123689 via http://tinyurl.com/3amlo2u) -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html EE4Free : http://www.experts-exchange.com/becomeAnExpert.jsp Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Inserting rows with missing IDs
On 3 May 2010 14:34, Andre Polykanine wrote: > Hello everyone, > It's not a strictly PHP question, however since I use that with PHP, > I'm asking it there. > How can I accomplish the task of inserting rows into MySql database > with missing IDs? Say, I have rows with IDs 1, 2, 3, 5, 9, 12, 17, and > 195. How do I make the check that allows to insert firstly the missing > IDs and only then apply the auto-increment? > Thanks! Using PHP, you could find the missing IDs by using the following steps. 1 - Get the current maximum ID. SELECT max(ID) as Max FROM table If you aren't interested in the ones before the first valid ID then also retrieve the min(ID). So you end up with $MinID = 1, $MaxID = 195. 2 - Get all the current IDs. SELECT ID FROM table So you end up with $KnownIDs = array(1,2,3,5,9,12,17,195); 3 - Use the range() function in PHP to build an array from the lowest id (or 1) to the highest id. $RangeIDs = range($MinID, $MaxID); 4 - Use array_diff($RangeIDs, $KnownIDs); to find the missing IDs. So ... outputs ... Array ( [3] => 4 [5] => 6 [6] => 7 [7] => 8 [9] => 10 [10] => 11 [12] => 13 [13] => 14 [14] => 15 [15] => 16 [17] => 18 [18] => 19 [19] => 20 [20] => 21 [21] => 22 [22] => 23 [23] => 24 [24] => 25 [25] => 26 [26] => 27 [27] => 28 [28] => 29 ) -- - Richard Quadling "Standing on the shoulders of some very clever giants!" EE : http://www.experts-exchange.com/M_248814.html EE4Free : http://www.experts-exchange.com/becomeAnExpert.jsp Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731 ZOPA : http://uk.zopa.com/member/RQuadling -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Inserting rows with missing IDs
At 4:34 PM +0300 5/3/10, Andre Polykanine wrote: Hello everyone, It's not a strictly PHP question, however since I use that with PHP, I'm asking it there. How can I accomplish the task of inserting rows into MySql database with missing IDs? Say, I have rows with IDs 1, 2, 3, 5, 9, 12, 17, and 195. How do I make the check that allows to insert firstly the missing IDs and only then apply the auto-increment? Thanks! Andre: Why? Why worry about the ID's that are missing? Does your code depend upon the data being in sequential ID's? If so, it's probably not a good idea to do that and you should rethink how you pull your data. Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: [Bulk] [PHP] Inserting rows with missing IDs
Andre Polykanine wrote: Hello everyone, It's not a strictly PHP question, however since I use that with PHP, I'm asking it there. How can I accomplish the task of inserting rows into MySql database with missing IDs? Say, I have rows with IDs 1, 2, 3, 5, 9, 12, 17, and 195. How do I make the check that allows to insert firstly the missing IDs and only then apply the auto-increment? Thanks Almost certainly you should not want to. The ID should simply be unique, and have no inherent meaning. Why do you care if there are gaps? But if you must, just select the IDs, put them in an array, and query the elements. Stephen -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re[2]: [PHP] Inserting rows with missing IDs
Hello Bob, Nope; they're not. They are blog entries that were deleted along with their comments. I'm just thinking about doing this when the amount of entries exceeds a reasonable number. -- With best regards from Ukraine, Andre Skype: Francophile; Wlm&MSN: arthaelon @ yandex.ru; Jabber: arthaelon @ jabber.org Yahoo! messenger: andre.polykanine; ICQ: 191749952 Twitter: m_elensule - Original message - From: Bob McConnell To: Andre Polykanine Date: Monday, May 3, 2010, 4:40:18 PM Subject: [PHP] Inserting rows with missing IDs From: Andre Polykanine > It's not a strictly PHP question, however since I use that with PHP, > I'm asking it there. > How can I accomplish the task of inserting rows into MySql database > with missing IDs? Say, I have rows with IDs 1, 2, 3, 5, 9, 12, 17, and > 195. How do I make the check that allows to insert firstly the missing > IDs and only then apply the auto-increment? Why are they missing? Were they present at one time then deleted? If so, were they used as foreign keys from another table or referenced in queries for other data that may still reference those empty rows? Think about the ramifications of old data in other tables that may be inherited when new rows are assigned a deleted ID. Bob McConnell -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Inserting rows with missing IDs
From: Andre Polykanine > It's not a strictly PHP question, however since I use that with PHP, > I'm asking it there. > How can I accomplish the task of inserting rows into MySql database > with missing IDs? Say, I have rows with IDs 1, 2, 3, 5, 9, 12, 17, and > 195. How do I make the check that allows to insert firstly the missing > IDs and only then apply the auto-increment? Why are they missing? Were they present at one time then deleted? If so, were they used as foreign keys from another table or referenced in queries for other data that may still reference those empty rows? Think about the ramifications of old data in other tables that may be inherited when new rows are assigned a deleted ID. Bob McConnell -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Inserting rows with missing IDs
Hello everyone, It's not a strictly PHP question, however since I use that with PHP, I'm asking it there. How can I accomplish the task of inserting rows into MySql database with missing IDs? Say, I have rows with IDs 1, 2, 3, 5, 9, 12, 17, and 195. How do I make the check that allows to insert firstly the missing IDs and only then apply the auto-increment? Thanks! -- With best regards from Ukraine, Andre Http://oire.org/ - The Fantasy blogs of Oire Skype: Francophile; Wlm&MSN: arthaelon @ yandex.ru; Jabber: arthaelon @ jabber.org Yahoo! messenger: andre.polykanine; ICQ: 191749952 Twitter: http://twitter.com/m_elensule -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
