[ReshiramZekrom]

Hi guys, I've got a problem with MySQL: i want to add two new variables into an already existent mysql database but these one aren't saved if a player is already registered. I don't know exactly how Mysql creates variables and so I don't know where i should edit the script.. I post the mysql query on the /register command:

pawn Код:

format(Query, sizeof(Query), "INSERT INTO `users` VALUES('%s', '%s', '%s', '%d', '%d','%d','%d','%d','%d','%d','%d','%d','%d','%d','%d',%d, '%d', '%d')", escnome, escpass, IPm, cookies[playerid], lvl[playerid],ak[playerid], GetPlayerScore(playerid), GetPlayerMoney(playerid), GetPlayerSkin(playerid),anno,mese,giorno,dpm[playerid],kills[playerid],morti[playerid],vip[playerid],exp[playerid],level[playerid]);
    mysql_query(Query);

And this one is /login:

pawn Код:

mysql_fetch_field_row(datastr,"Cookies"); cookies[playerid] = strval(datastr);
        mysql_fetch_field_row(datastr,"Livello"); lvl[playerid] = strval(datastr);
        mysql_fetch_field_row(datastr,"AK"); ak[playerid] = strval(datastr);
        mysql_fetch_field_row(datastr,"Score"); SetPlayerScore(playerid,strval(datastr));
        mysql_fetch_field_row(datastr,"Soldi"); GivePlayerMoney(playerid,strval(datastr));
        mysql_fetch_field_row(datastr,"Skin"); SetPlayerSkin(playerid,strval(datastr));
        mysql_fetch_field_row(datastr,"PMs"); dpm[playerid] = strval(datastr);
        mysql_fetch_field_row(datastr,"Kills"); kills[playerid] = strval(datastr);
        mysql_fetch_field_row(datastr,"Morti"); morti[playerid] = strval(datastr);
        mysql_fetch_field_row(datastr,"Vip"); vip[playerid] = strval(datastr);
        mysql_fetch_field_row(datastr,"exp"); exp[playerid] = strval(datastr);
        mysql_fetch_field_row(datastr,"level"); level[playerid] = strval(datastr);

The new variables are exp[playerid] and level[playerid]. If a player is new everything going perfect but if a player is already registered i have this problem.. Can you help me? If you need more explanation about my problem just ask!

Thanks

[PowerPC603]

In case the table has the correct columns already (as you said it saves correctly for new players), do the existing players have any value in those columns?
If those columns hold NULL (nothing), that could explain why MySQL doesn't return anything.

If there are a few registered players, you might be able to add default values (I assume '0') into those columns.
If there are alot registered players, you might wanna create an UPDATE query to update all those NULL fields to a default value of 0.

Also, you should use an UPDATE query to save data, not INSERT.
INSERT is used to add new rows into your table, not to update an existing one.
Only use INSERT to create a new account when a player registers on your server.

After that, use UPDATE to edit saved values.

[ReshiramZekrom]

Ok thanks Can you give me an example of UPDATE if you can?

[PowerPC603]

I'll give you some examples from my own script when I get home (in about 2.5 hours).
I'm at work right now and don't have access to my script from here.

My script is using threaded queries, using BlueG's MySQL plugin R34, btw.
But it would show you the syntax for UPDATE queries.


EDIT:
Just found a topic that shows an update query:
http://forum.sa-mp.com/showpost.php?...51&postcount=3


EDIT:
I'm using this code to register new players on my server.
When registering a new player, he doesn't have any money, scorepoints, stats, ... anyway, so my tables have e default value of '0' for those columns.
Only the playername and password don't have default values and need to be given before INSERT can do it's work.
ID is an auto-increment column set as primary key as well.

pawn Код:

// Add the player's account to the MySQL database (escape the name only, as there is no need to escape a hashed password)
mysql_format(SQL_db, Query, sizeof(Query), "INSERT INTO playerdata (PlayerName, Password) VALUES ('%e', '%s')", APlayerData[playerid][PlayerName], APlayerData[playerid][PlayerPassword]);
mysql_tquery(SQL_db, Query, "OnAccountCreate", "i", playerid);



// This custom callback is used after creating a new account (playername and password are inserted, but the ID must be loaded now to reference the player later on using this ID)
forward OnAccountCreate(playerid);
public OnAccountCreate(playerid)
{
    // Get the ID from the auto-incremented column ("ID") and store it
    APlayerData[playerid][SQLID] = cache_insert_id();

    return 1;
}

And this code is used to update the money and score for the player after they are rewarded (completing a mission for example):

pawn Код:

// Update money and score for this player in the player's account in MySQL
mysql_format(SQL_db, Query, sizeof(Query), "UPDATE playerdata SET Money = '%i', Score = '%i' WHERE ID = '%i'", APlayerData[playerid][PlayerMoney], APlayerData[playerid][PlayerScore], APlayerData[playerid][SQLID]);
mysql_tquery(SQL_db, Query, "", "");

[ReshiramZekrom]

Ok fixed Thanks

[ColeMiner]

Instead of adding columns, which SQL isn't brilliant at, why not just create a new table and use JOIN?

[Tamer]

You can add new columns at phpmyadmin if you have it.