[-Prodigy-]

Let's say I have the following table:

ID:
1
2
3
4
5
7
8
9
10
--
How would I set up a function to detect if a row in that column is missing? (In this case it would be 6, the missing #)

[JamesC]

Why exactly do you want to do this?

[s0cket]

If you only need to detect one missing value and you know what it is, put this in your sql query string:

Код:

SELECT `id` FROM `<your table name>` WHERE `id` = '<missing number>'

then check the amount of rows it returns with mysql_num_rows() function. If it's 0, then the value was not found.

[MP2]

But he doesn't know which one - read his post.

Can't the INSERT ID thing be used? Not sure.

[[HiC]TheKiller]

I know why people generally want to do this, and it's because they do:

pawn Код:

for(new i; i<LOOP; i++)
{
    format(query, sizeof(query), "SELECT * FROM table WHERE id = %d", i);
    mysql_query(query);
    mysql_store_result();
    //......
    mysql_free_result();
}

This is pretty inefficient as you can just do the following:

pawn Код:

mysql_query("SELECT * FROM table");
mysql_store_result();
while(mysql_retrieve_row())
{
//.....
}
mysql_free_result();

There is no real efficient way to detect the missing AI ID. There is no real need to anyway. If you wanted to detect the ID for something else, you could always do the following:

pawn Код:

new id, xid[10];
mysql_query("SELECT id FROM table");
mysql_store_result();
while(mysql_retrieve_row())
{
    id++;
    mysql_fetch_field_row(xid, "id"); //Not sure about mysql_fetch_int (May be able to use it in this case)
    if(strval(xid) != id) return id;
}
mysql_free_result();
/*
The code above will return the ID that 
is missing from the sequence. This is
not really a good idea though, and 
there is not many reasons for 
doing something like this.

[-Prodigy-]

Quote:

Originally Posted by JamesC Why exactly do you want to do this?

Let's say a house system in which the IDs are IMPORTANTLY stored by sequence. And let's say that any missing ID would screw the whole system up. So when we create a new house, it finds that "Missing" # and inserts it there. ( This is not my system, just an example )

Quote:

Originally Posted by MP2 But he doesn't know which one - read his post. Can't the INSERT ID thing be used? Not sure.

Nope.

Quote:

Originally Posted by [HiC]TheKiller pawn Код: new id, xid[10]; mysql_query("SELECT id FROM table"); mysql_store_result(); while(mysql_retrieve_row()) {     id++;     mysql_fetch_field_row(xid, "id"); //Not sure about mysql_fetch_int (May be able to use it in this case)     if(strval(xid) != id) return id; } mysql_free_result(); /* The code above will return the ID that is missing from the sequence. This is not really a good idea though, and there is not many reasons for doing something like this.

Edit: This worked perfectly. Thanks so much!!