Maximum SQL Table Fields ? - Printable Version

Maximum SQL Table Fields ? - 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: Maximum SQL Table Fields ? (/showthread.php?tid=414145)

Maximum SQL Table Fields ? - Youice - 08.02.2013

hello,

so yeah as the title says..

let me explain:

In my table there is 5 fields, no problem right?, the problem starts when I add a new field to them; it doesn't save then,
why? (or it's from my code) ?

PHP код:





    new Query[128];

    new PlayerName[MAX_PLAYER_NAME];


    GetPlayerName(playerid, PlayerName, MAX_PLAYER_NAME);

    format(Query, 128, "INSERT INTO `USERS` (`NAME`, `PASSWORD`, `ADMINLEVEL`, `CASH`, `SCORE`) VALUES ('%s','%s','0','0','0')", DB_Escape(PlayerName), DB_Escape(inputtext));

    db_query(DataBase, Query);

This one above saves.

PHP код:





    new Query[128];

    new PlayerName[MAX_PLAYER_NAME];


    GetPlayerName(playerid, PlayerName, MAX_PLAYER_NAME);

    format(Query, 128, "INSERT INTO `USERS` (`NAME`, `PASSWORD`, `ADMINLEVEL`, `CASH`, `SCORE`, `SKIN`) VALUES ('%s','%s','0','0','0', '0')", DB_Escape(PlayerName), DB_Escape(inputtext));

    db_query(DataBase, Query);

This one ^^, doesn't but why?

any help will be appreciated!!
~Thanks~

Re: Maximum SQL Table Fields ? - iggy1 - 08.02.2013

You need to use the "ALTER TABLE `table_name` ADD COLUMN `column_name` type" statement to add new fields you cant just insert values and the fields be created.

pawn Код:

ALTER TABLE users ADD COLUMN admin_level INT

EDIT: To answer your question 2000 columns is the default max amount of columns for an SQLite DB. (for the newest version of SQLite not sure which one sa-mp uses).

Source: http://www.sqlite.org/draft/limits.html

Re: Maximum SQL Table Fields ? - Youice - 08.02.2013

Quote:

Originally Posted by iggy1

You need to use the "ALTER TABLE `table_name` ADD COLUMN `column_name` type" statement to add new fields you cant just insert values and the fields be created.

pawn Код:

ALTER TABLE users ADD COLUMN admin_level INT

okay thanks, but what if I need to add a new column? in the same way?

Quote:

Originally Posted by ******

Probably because you did something else wrong like not setting up the table correctly. Databases are designed to handle TERABYTES of data in vastly complex arrangements, if it can't hack your six fields you did something wrong.

but how it's wrong, if the 5 fields are working perfect?, the 6 field is only "duplicated" to the ones before it...

Re: Maximum SQL Table Fields ? - iggy1 - 08.02.2013

You said your database has 5 fields? I'm guessing NAME PASSWORD ADMINLEVEL CASH SCORE.

So if that's the case, before you run the second query you need to alter the database and add the new columns (SKIN).

pawn Код:

ALTER TABLE `USERS` ADD COLUMN `SKIN` INT

After running that query it should be fine to insert into the SKIN column because it now exists.

Re: Maximum SQL Table Fields ? - Youice - 08.02.2013

Quote:

Originally Posted by iggy1

You said your database has 5 fields? I'm guessing NAME PASSWORD ADMINLEVEL CASH SCORE.

So if that's the case, before you run the second query you need to alter the database and add the new columns (SKIN).

pawn Код:

ALTER TABLE `USERS` ADD COLUMN `SKIN` INT

After running that query it should be fine to insert into the SKIN column because it now exists.

alright thanks but I got one more question, do I use the same system of loading and saving in this additional columns?

Re: Maximum SQL Table Fields ? - iggy1 - 08.02.2013

As long as you select the data correctly you should be fine. Once you have inserted a new column just add it to your SELECT/UPDATE queries like any column.

You only have to add the column once then you can remove it from your code. (the ALTER TABLE statement that is)

Re: Maximum SQL Table Fields ? - Youice - 08.02.2013

Quote:

Originally Posted by iggy1

As long as you select the data correctly you should be fine. Once you have inserted a new column just add it to your SELECT/UPDATE queries like any column.

You only have to add the column once then you can remove it from your code. (the ALTER TABLE statement that is)

Oka-ay, Thanks, and thanks a lot for this beautiful article, yeah it made me understands a lot and again thanks!

Re: Maximum SQL Table Fields ? - SuperViper - 08.02.2013

You need to increase the size of the query string from 128 to something bigger so it can fit everything into the string.

Re: Maximum SQL Table Fields ? - AaronKillz - 08.02.2013

Quote:

Originally Posted by SuperViper

You need to increase the size of the query string from 128 to something bigger so it can fit everything into the string.

Lol, such a simple fix.