[MySQL] Primary Key (Account ID) problems

[MySQL] Primary Key (Account ID) problems - 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] Primary Key (Account ID) problems (/showthread.php?tid=377496)

[MySQL] Primary Key (Account ID) problems - vIBIENNYx - 14.09.2012

Whenever I need to delete an account for whatever reason, it messes up the MySQL key information and causes the register system to crash, how can I use MySQL to fix the primary key (Account ID) to suit the deleted account?

For example, I have 15 accounts, and I delete the nineth.

I want ten to become nine, eleven to become ten, etc.

How can I do this?

Re: [MySQL] Primary Key (Account ID) problems - Vince - 14.09.2012

Why would you want to? If you enable AUTO_INCREMENT on the primary key then the MySQL server will automatically create a new id that follows the last one.

Re: [MySQL] Primary Key (Account ID) problems - mamorunl - 14.09.2012

The only real way is this one: http://sqlserverplanet.com/tsql/usin...e-after-delete

DBCC to reseed.

Or you want to repopulate your whole table, but that aint right!

Re: [MySQL] Primary Key (Account ID) problems - vIBIENNYx - 14.09.2012

How would I use, Auto Increment? here's the current register code:

pawn Код:

new Query[400], AccQuery[256];
                    new salt[32],saltstring[156], salt1[33];
                    randomString(salt,sizeof(salt));
                    format(salt1, sizeof(salt1), "%s", salt);
                    format(saltstring,sizeof(saltstring), "%s%s", salt, inputtext);
                    
                    new ip[128], nextaccid;
                    GetPlayerIp(playerid, ip, 128);
                    format(AccQuery, sizeof(AccQuery), "SELECT `accid` FROM `playerinfo` WHERE `accid` > '-1'");
                    mysql_query(AccQuery);
                    mysql_store_result();

                    nextaccid = mysql_num_rows();

                    format(Query, sizeof(Query), "INSERT INTO `playerinfo` (`accid`, `registeringip`, `dateregistered`, `lastloginip`, `lastlogindate`, `username`, `psalt`, `ppassword`) VALUES ('%d', '%s', UTC_TIMESTAMP(), '%s', UTC_TIMESTAMP(), '%s', '%s', md5('%s'))",nextaccid, ip, ip, pName(playerid), salt1, saltstring);
                    mysql_query(Query);
                    print(Query);
                    
                    // Player has registered

                    SendClientMessage(playerid, 0xFFFFFFFF, "You are now registered!");
                    StopAudioStreamForPlayer(playerid);
                    StartCutsceneForPlayer(playerid, 1, 1);
                    //SetPlayerPos(playerid, 0, 0, 100000);
                    GivePlayerCash(playerid, 500);
                    TogglePlayerControllable(playerid, 0);
                    SavePVar(playerid);
                    //loggedin[playerid] = 1;

Re: [MySQL] Primary Key (Account ID) problems - jameskmonger - 14.09.2012

You have to configure the field in phpMyAdmin

Re: [MySQL] Primary Key (Account ID) problems - Vince - 14.09.2012

In phpMyAdmin: Click your table, go to structure tab. Click the edit button for the accid field. Tick AUTO_INCREMENT box. Save. Now in order for it to work correctly you might have to set the AI value manually, so execute this query:

PHP код:



ALTER TABLE playerinfo AUTO_INCREMENT = 16

Obviously replacing 16 with what the next value would be.

Also alter your insert query and leave the accid field out.

Re: [MySQL] Primary Key (Account ID) problems - vIBIENNYx - 14.09.2012

Thank you very much guys, repped you both.

Re: [MySQL] Primary Key (Account ID) problems - jameskmonger - 14.09.2012

Quote:

Originally Posted by vIBIENNYx

Thank you very much guys, repped you both.

You're welcome, glad to have helped.

Re: [MySQL] Primary Key (Account ID) problems - Wesley221 - 14.09.2012

Its also possible to do it when you create the table in pawn. Ex:

pawn Код:

mysql_query( "CREATE TABLE IF NOT EXISTS PlayerData( Id INT( 11 ) NOT NULL AUTO_INCREMENT, OTHERSHIZZLEHERE, PRIMARY KEY( `Id` ) )" );