Re[2]: [PHP] Inserting rows with missing IDs

[Andre Polykanine]

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[2]: [PHP] Inserting rows with missing IDs

[Andre Polykanine]

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