[Sime30]

Hi everyone

I haven't been scripting nor playing SA:MP for over a year now, just doing other stuff in my life.
Recently I found some old gamemodes and scripts on a CD which I thought I lost a long time ago. After some thinking, I updated all plugins, includes etc.

And now comes the important part...
I have a working Y_INI based Phonebook system made by Larzi - https://sampforum.blast.hk/showthread.php?tid=406221
Well, I edited it and removed everything but the phonebook. It works well.
BUT now I want that in MySQL database, not in file ini system.

What would be the best approach? I wanted to make fixed slots in a MySQL table but that's not how it works like now. I want it to insert new rows when a player adds a contact, similiar to Vince's weapon saving/loading - https://sampforum.blast.hk/showthread.php?tid=505081

I'll show you some code of course but most variable names are in my native language. Sorry for that.

pawn Код:

forward ImenikLoad(playerid);
public ImenikLoad(playerid) // on player connect
{
        if( !fexist( PutanjaImenika( playerid )))
        {
            new
                INI:ImennikFile = INI_Open( PutanjaImenika( playerid ));

            INI_SetTag( ImennikFile, "statistika" );
            INI_WriteInt( ImennikFile, "kontakti", 0 );
            INI_Close( ImennikFile );
        }
        else
        {
            INI_ParseFile( PutanjaImenika( playerid ), "Imenik", .bExtra = true, .extra = playerid, .bPassTag = true );
            for( new i = 0; i < MAX_KONTAKATA; i ++ )
            {
                if( isnull( g_Imenik[ playerid ][ i ][ ImenikPlayerName ] ))
                {
                    format( g_Imenik[ playerid ][ i ][ ImenikPlayerName ], MAX_PLAYER_NAME, "- empty -" );
                }
            }
        }
        return 1;
}
forward ImenikUnload(playerid);
public ImenikUnload(playerid) // on player disonnect
{

        fremove( PutanjaImenika( playerid ));
        new
            INI:File = INI_Open( PutanjaImenika( playerid ));

        INI_SetTag( File, "statistika" );
        INI_WriteInt( File, "kontakti", g_Kontakti[ playerid ] );

        for( new i = 0; i < g_Kontakti[ playerid ]; i ++ )
        {

            if( !strcmp( g_Imenik[ playerid ][ i ][ ImenikPlayerName ], "- empty -" ))
            {
                INI_DeleteTag( File, valstr2( i ));
            }
            else
            {
                INI_SetTag( File, valstr2( i ));
                INI_WriteString( File, "name", g_Imenik[ playerid ][ i ][ ImenikPlayerName ] );
                INI_WriteString( File, "number", g_Imenik[ playerid ][ i ][ ImenikPlayerNumber ] );
            }
        }
        INI_Close( File );

        g_Kontakti[ playerid ] = 0;

        for( new i = 0; i < MAX_KONTAKATA; i ++ )
        {
            format( g_Imenik[ playerid ][ i ][ ImenikPlayerName ], MAX_PLAYER_NAME, "" );
            format( g_Imenik[ playerid ][ i ][ ImenikPlayerNumber ], MAX_NUMBERS, "" );
        }

        if(KupnjaImenika[playerid] == 1)
        {
            SetTimerEx("ImenikLoad", 700, false , "i" , playerid);
            KupnjaImenika[playerid] = 0;
        }
        return 1;

}

I'll add more code when needed. For now, I just need an example of doing this dynamic adding & removing contacts in MySQL. Thanks!

[Virtual1ty]

Well to start with, any decent programmer will name variables according to the international language which is the de-facto standard, that is - English. But putting that aside I see why you wanted to translate everything to make it easier for yourself and to learn from it.

Onto the coding / logical part.

Of course loading and saving systems will have to be redesigned according to whatever MySQL version you plan to use (e.g. the threaded model). So the real question you're asking is just how to design the database. That is a little out of scope of this forum but some forum users who have the knowledge still want to help out (a shout out to Vince) so I hope you'll get a better response from various other people other than just mine.

You can insert whatever amount of data into a database that is only limited by the storage capacity you have, so the "limiting" part will be in the gamemode / script (e.g. arrays) and so then you'll have to limit how many contacts a player can have, messages, if you plan to store them and other stuff. You can achieve this limit in the database by querying if the table has more than N rows (cache_get_row_count or similiar) and if it has then don't insert anything new into the table.

As for the database design I recommend having a few tables, lets say a main `phonebook_users` table and a `phonebook_contacts` table, you can expand this and rename to how you see fit. The relation between them would obviously be an `id` field which links them back to the main table.

Hope it helps you some, have a good day.

P.S.
Sorry if I hadn't read clearly what you wrote, it's 2 AM here so my eyes are a little squinty. The code is generally very easy, it's just a basic get # of rows, loop, load/save stuff, there are plenty of MySQL loading tutorials out there.

[Vince]

Assuming that each player only has one phone, and therefore only one phone number. For this you need an association table. This is a tertiary table that links two entities together in a many-to-many relationship. In its simplest form it would look like this:



Underlined attributes are primary keys. Foreign key 1 (FK1) links Player.id to PhoneBook.playerId. Foreign key 2 (FK2) links Player.id to PhoneBook.contactId. Still with me here?

What the actual table would look like would be something like:

playerId	contactId
1	2
1	4
1	5
2	1
2	5

Always read this left to right. Player 1 has players 2, 4 and 5 as a contact. Player 2 has players 1 and 5 as a contact. So, according to this table only players 1 and 2 have each others numbers.

If you would then want to retrieve all the contacts of a player you could write a query like this:

PHP код:

SELECT name, phoneNumber FROM Player WHERE id IN (SELECT contactId FROM PhoneBook WHERE playerId = ?) 


Replacing the question mark with the id of the player you want to get the contacts for.

[Sime30]

Thanks for the replies!

Okay, let's see... Your example is kinda what I wanted to achieve, kinda. While it shows a nice way of doing this, it's still not what I have right now. Let me be more specific. Right now, a player is creating a custom contact in his notebook/phonebook. He has to write a number he wants and give a name for it. Everything is actually like writing custom strings in a file and then showing in a dialog either "empty" if it is empty and showing a contact name if there is something in that slot. It also shows "next page" if there are more than 6 contacts on a page. If I delete a contact lets say in a slot 4 and there are contacts in slots 5,6 and 7 then it will automatically move all contacts by one slot down so there are no holes.

So basically how would I load and save contacts if my table looks like (Phonebook - id, contactName, contactNumber). Id would link to users id if needed.

[Vince]

Hmm, that makes it more tricky. You can add a custom name column to the PhoneBook table without a problem. The real issue is whether you allow players to enter phone numbers that do not exist?

[Sime30]

I have no problem with that. As long as they type a valid number without any special character, it's okay with me. It's their problem if they typed a wrong number. After all, I am going for realism. A guy gives you his number and then you write it down.

Some checking code

pawn Код:

IsPhoneNumber(string[])
{
    if( strfind( string, "-", true ) == 3 && strlen( string ) == 7 )
    {
        new i;
        while( i < MAX_NUMBERS )
        {
            if(( string[ i ] < '0' || string[ i ] > '9' ) && string[ i ] != EOS )
            {
                return false;
            }
            if( i == 2 ) //skip '-'
            {
                i ++;
            }
            i ++;
        }
        return true;
    }
    return false;
}

[Sime30]

@Vince

I forgot about this one lol. Anyway, where were we... How would YOU make it load or save?
I translated everything else in this pastebin, take a look.

http://pastebin.com/XNCxnHUq

[Virtual1ty]

Since Vince hadn't seen this or replied yet...

Assuming a table structure like this one:

Код:

CREATE TABLE `Players` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `password` VARCHAR,
    `phoneNumber` VARCHAR,
    PRIMARY KEY (`id`)
);

CREATE TABLE `Phonebook` (
    `id` INT UNSIGNED NOT NULL,
    `contactId` INT,
    `contactName` VARCHAR,
    `contactNumber` VARCHAR,
    FOREIGN KEY (`id`) REFERENCES `Players` (`id`)
);

"g_MySQL_Handle" is the variable you assigned a mysql conn. handle to when using "mysql_connect":

Код:

g_MySQL_Handle = mysql_connect("127.0.0.1", "root", "mydatabase", "mypass");

"Player_SQLID[playerid]" is the variable where you save the Player's SQLID (`id` field in `Players` table).

Loading (somewhere like OnPlayerConnect):

PHP код:

new
    query[ 128 ];
mysql_format( g_MySQL_Handle, query, sizeof( query ),
    "SELECT `contactName`,`contactNumber` FROM `Phonebook` WHERE `id`=%d",
    Player_SQLID[playerid]
);
inline OnPhonebookDataLoad()
{
    new rows = cache_num_rows();
    for (new i = 0; i < rows; i++)
    {
        cache_get_field_content(i, "contactName", g_Phonebook[ playerid ][ g_Contacts[ playerid ] ][ PhonebookPlayerName], g_MySQL_Handle, MAX_PLAYER_NAME); 
        // You should really be saving phone numbers as integers but meh...
        cache_get_field_content(i, "contactNumber", g_Phonebook[ playerid ][ g_Contacts[ playerid ] ][ PhonebookPlayerNumber], g_MySQL_Handle, MAX_PLAYER_NAME); 
        // Associated "Player name - description" for the phone number should never be NULL
        //if( isnull( g_PhoneBook[ playerid ][ i ][ PhonebookPlayerName ] ))
        //    strcpy( g_Phonebook[ playerid ][ i ][ PhonebookPlayerName], MAX_PLAYER_NAME, "- empty -" );
        g_Contacts[ playerid ] ++;
        g_ExtraContacts[ playerid ] ++;
        if( g_ExtraContacts[ playerid ] > 6 )
        {
            g_ExtraContacts[ playerid ] -= 6;
            g_ExtraPages[ playerid ] ++;
        }
    }
}
mysql_tquery_inline( g_MySQL_Handle, query, using inline OnPhonebookDataLoad ); 


Saving (under AddThatContact or wherever you save stuff):

PHP код:

new
    query[ 256 ];
mysql_format( g_MySQL_Handle, query, sizeof( query ),
    "INSERT INTO `Phonebook` (`id`,`contactName`,`contactNumber`) VALUES (%d,'%e','%e')",
    Player_SQLID[playerid],
    g_PhoneBook[ playerid ][ g_Contacts[ playerid ] ][ PhonebookPlayerName ],
    g_Phonebook[ playerid ][ g_Contacts[ playerid ] ][ PhonebookPlayerNumber]
);
mysql_tquery( g_MySQL_Handle, query ); 


[Sime30]

Thanks, I'll try this one. Just gonna watch football first of course.
I will inform you here by an edit.

EDIT: Your code was very similar to what I tried to do and now when I fulfilled it with yours, it's working... Well, not 100%. I am still tweaking some things around but I think I can handle that.

Thank you and I am very glad you helped me, both of you!