MySQL help - Printable Version

MySQL help - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+--- Thread: MySQL help (/showthread.php?tid=319947)

MySQL help - -Prodigy- - 21.02.2012

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 #)

Re: MySQL help - JamesC - 21.02.2012

Why exactly do you want to do this?

Re: MySQL help - s0cket - 21.02.2012

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.

Re: MySQL help - MP2 - 21.02.2012

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

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

Re: MySQL help - [HiC]TheKiller - 21.02.2012

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.

Re: MySQL help - -Prodigy- - 21.02.2012

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!!