Missing index values

Germaris

Hi there!

my_table has 23043 records.
The next autoindex indicates 23047.
I have verified the largest index value allocated which is 23046 and which is correct.

How can I find the two index values which aren't used?
(so I can use them with new records and therefore have the index values and the number of records having the same value : 23046).

Many thanks in advance for your help

bradgrafelman

Hmm... the only way I could think to do it would involve PHP reading through each row, attempting to find a gap in the numbers.

A better question would be this: Why does it matter if the highest index number used is the same as the number of rows?

Germaris

Thanks for responding.
Good sense of humor !!! :-)
Jesuits, usually, answer to a question by asking another one...

I want to know the missing index values because there are images related to the index values which will not, therefore, be displayed...

By the way, how would you write this "exploring" PHP?

bradgrafelman

Germaris wrote:
By the way, how would you write this "exploring" PHP?

Well... as I said, this solution loops through the entire table, so I'm sure it's the least efficient of solutions, but here 'goes:

$query = 'SELECT `id_field` FROM `table` ORDER BY `id_field` ASC';
$exec = mysql_query($query);

for($i=1,$last=mysql_result($exec, 0),$missing=array(); $i<mysql_num_rows($exec); $i++) {
    if(($num = mysql_result($exec, $i)) && $num != $last)
        $missing[] = $num;
    $last = $num
}

Then, $missing would be an array of missing numbers. If it's an empty array, no numbers were missing obviously.

EDIT: Code is untested - written off the top of my head, so who knows if it works properly.