Loading multiple rows with mysql question

Loading multiple rows with mysql question - 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: Loading multiple rows with mysql question (/showthread.php?tid=642872)

Loading multiple rows with mysql question - ax1 - 08.10.2017

I'm trying to create group system, where a player is able to create his own group.
When player tries to create group it should check if group name is not already taken so I figured that I should load data of all groups there are on OnGameModeInit and once player is trying to create his own group I would just run a for cycle to check if the name is taken

So let's say I want to load all these groups into "GroupInfo" on OnGameModeInit

Код:

enum Data
{
ID,
Name
};
new GroupInfo[Amount_of_groups][Data];

How should I do that?
Im using mysql R39-6

Re: Loading multiple rows with mysql question - Jstylezzz - 09.10.2017

You could either take the approach you described above, or query the database once a user tries to create a group. I wrote a small example with comments for the approach you described above.
It's just a quick and basic example, so copy/pasting may not work (nor was it intended to be a copy pasting example). Hope this gives you a basic picture on a way to do what you want. If anything's still unclear, I'll be happy to help and explain.

PHP код:



public OnGameModeInit()

{

    //Start the group loading. It's in another function to keep it clean.

    LoadGroups();

}


forward LoadGroups();

public LoadGroups()

{

    //Execute a SQL query to get all records from the groups table

    //We tell the function to pass data onto OnGroupsRetrieved.

    mysql_tquery(sqlHandle, "SELECT * FROM `groups_table`;", "OnGroupsRetrieved"); 

}


//Called on completion of LoadGroups query

forward OnGroupsRetrieved();

public OnGroupsRetrieved()

{

    //First check if there are any entries. If not, print that to console.

    if(cache_get_row_count() == 0) print("[INFO]: No groups found in database.");

    else

    {

        //Now that we know we have data, execute a for loop for the amount of entries in the database.

        //Same as above, we use cache_get_row_count to get the amount of result rows we have from the query

        for(new i = 0; i < cache_get_row_count(); i++)

        {

            //cache_get_field_content_int wants a result number and the name of the field to get the value from.

            //We use 'i' as result number because we loop through the results

            GroupInfo[i][ID] = cache_get_field_content_int(i, "ID");

            

            //cache_get_field_content is a little different. It too wants the result number and field name, but I always

            //compare it with the 'format' function. This because it also wants the variable to put the value into in the function

            //and it wants the length of the variable. In addition to that, it also wants the sqlHandle variable.

            //cache_get_field_content(resultnumber, fieldname, variable to store into, sqlhandle, length of variable to store into);

            cache_get_field_content(i, "Name", GroupInfo[i][Name], sqlHandle, GROUP_NAME_LENGTH);

        }

        //Once we loaded everything we print the amount of rows of our result, just for information.

        printf("[INFO]: Loaded %d groups from the database.", cache_get_row_count()); 

        

    }

    return true; //I believe you should return true to 'free result'; empty the cache.

}

Re: Loading multiple rows with mysql question - ax1 - 09.10.2017

Quote:

Originally Posted by Jstylezzz

You could either take the approach you described above, or query the database once a user tries to create a group. I wrote a small example with comments for the approach you described above.
It's just a quick and basic example, so copy/pasting may not work (nor was it intended to be a copy pasting example). Hope this gives you a basic picture on a way to do what you want. If anything's still unclear, I'll be happy to help and explain.

PHP код:



public OnGameModeInit()

{

    //Start the group loading. It's in another function to keep it clean.

    LoadGroups();

}


forward LoadGroups();

public LoadGroups()

{

    //Execute a SQL query to get all records from the groups table

    //We tell the function to pass data onto OnGroupsRetrieved.

    mysql_tquery(sqlHandle, "SELECT * FROM `groups_table`;", "OnGroupsRetrieved"); 

}


//Called on completion of LoadGroups query

forward OnGroupsRetrieved();

public OnGroupsRetrieved()

{

    //First check if there are any entries. If not, print that to console.

    if(cache_get_row_count() == 0) print("[INFO]: No groups found in database.");

    else

    {

        //Now that we know we have data, execute a for loop for the amount of entries in the database.

        //Same as above, we use cache_get_row_count to get the amount of result rows we have from the query

        for(new i = 0; i < cache_get_row_count(); i++)

        {

            //cache_get_field_content_int wants a result number and the name of the field to get the value from.

            //We use 'i' as result number because we loop through the results

            GroupInfo[i][ID] = cache_get_field_content_int(i, "ID");

            

            //cache_get_field_content is a little different. It too wants the result number and field name, but I always

            //compare it with the 'format' function. This because it also wants the variable to put the value into in the function

            //and it wants the length of the variable. In addition to that, it also wants the sqlHandle variable.

            //cache_get_field_content(resultnumber, fieldname, variable to store into, sqlhandle, length of variable to store into);

            cache_get_field_content(i, "Name", GroupInfo[i][Name], sqlHandle, GROUP_NAME_LENGTH);

        }

        //Once we loaded everything we print the amount of rows of our result, just for information.

        printf("[INFO]: Loaded %d groups from the database.", cache_get_row_count()); 

        

    }

    return true; //I believe you should return true to 'free result'; empty the cache.

}

Код:

new groups; 

public OnGameModeInit()
{
	 mysql_tquery(mysql, "SELECT * FROM `groups`", "GetLines");
	return 1;
}

forward GetLines();
public GetLines()
{
groups=cache_get_row_count();
}

enum Data
{
ID,
Name
};
new GroupInfo[groups][Data];

but I get an error

Код:

error 008: must be a constant expression; assumed zero

it isn't much of a problem as I can just give that array a big value like new GroupInfo[1000][Data], I'm just curious if it's possible to do it

Re: Loading multiple rows with mysql question - Konstantinos - 09.10.2017

Why not just use INSERT IGNORE INTO syntax and set Name as UNIQUE index? This way, it will check if rows were affected (a successful row was inserted) or otherwise (nothing affected) returned an error for duplicate and ignored (skipped) the query.

Re: Loading multiple rows with mysql question - ax1 - 10.10.2017

new problem

Код:

groups=cache_get_row_count();
    if(groups == 0) print("[INFO]: No groups found in database.");
    else
    {
    print("----------------------------------------------------------------");
            print(" ");
            print("\tLOADING GROUPS");
            print(" ");
        for(new i = 0; i < groups; i++)
        {
            GroupInfo[i][ID] = cache_get_field_content_int(i, "ID");
            cache_get_field_content(i, "Name", GroupInfo[i][Name], mysql, 12);
            printf("NAME : %s",GroupInfo[i][Name]);
        }
        printf("[LOADING GROUPS] : Loaded %d groups from the database.", groups);
        print(" ");
        print("----------------------------------------------------------------");
    }
    for(new i=0; i<groups; i++) printf("NAME : %s",GroupInfo[i][Name]);

this is what gets printed into console

[18:46:31] ----------------------------------------------------------------
[18:46:31]
[18:46:31] LOADING GROUPS
[18:46:31]
[18:46:31] NAME : dadac
[18:46:31] NAME : cvcvvvsvsa
[18:46:31] NAME : dqdaaacxzc
[18:46:31] NAME : xccsa
[18:46:31] NAME : yyyya
[18:46:31] [LOADING GROUPS] : Loaded 5 groups from the database.
[18:46:31]
[18:46:31] ----------------------------------------------------------------
[18:46:31] NAME : dcd xyyyya
[18:46:31] NAME : cd xyyyya
[18:46:31] NAME : d xyyyya
[18:46:31] NAME : xyyyya
[18:46:31] NAME : yyyya

I dont do anything to GroupInfo[i][Name] after storing group name into it, but it gets all messed up outside the for cycle.

Re: Loading multiple rows with mysql question - ax1 - 11.10.2017

Quote:

Originally Posted by ax1

new problem

Код:

groups=cache_get_row_count();
    if(groups == 0) print("[INFO]: No groups found in database.");
    else
    {
    print("----------------------------------------------------------------");
            print(" ");
            print("\tLOADING GROUPS");
            print(" ");
        for(new i = 0; i < groups; i++)
        {
            GroupInfo[i][ID] = cache_get_field_content_int(i, "ID");
            cache_get_field_content(i, "Name", GroupInfo[i][Name], mysql, 12);
            printf("NAME : %s",GroupInfo[i][Name]);
        }
        printf("[LOADING GROUPS] : Loaded %d groups from the database.", groups);
        print(" ");
        print("----------------------------------------------------------------");
    }
    for(new i=0; i<groups; i++) printf("NAME : %s",GroupInfo[i][Name]);

bump

Re: Loading multiple rows with mysql question - whadez - 11.10.2017

Quote:

Originally Posted by ax1

bump

Name is a string variable, which contains characters, you have to set it's lenght.

Код:

enum Data
{
ID,
Name[32]
};
new GroupInfo[Amount_of_groups][Data];

Make sure that you change the "Name" lenght (12) in the loading query.

Код:

cache_get_field_content(i, "Name", GroupInfo[i][Name], mysql, 32);

Re: Loading multiple rows with mysql question - Jstylezzz - 11.10.2017

Apologies for the late reply, I didn't notice the question.

Quote:

Originally Posted by ax1

Thank you for you detailed answer, it was very helpful. Perhaps you also know how to get the amount of rows for enum? There can be different amount of groups everytime OnGameModeInit gets called so I tried doing this
-- code --
it isn't much of a problem as I can just give that array a big value like new GroupInfo[1000][Data], I'm just curious if it's possible to do it

The size of the variable has to be constant. This means you have to define in advance how big this array can be at most, so you can't set it to the amount of groups that are in the database at the moment of loading. Similar to the MAX_PLAYERS definition we all know, you can make one for your group so that you can easily change it later on, and improve your readability throughout the script. Let's say you want a maximum amount of 20 groups to be created, you could just say:

PHP код:



#define MAX_GROUPS 20

Then, just declare your variable as

PHP код:



new GroupInfo[MAX_GROUPS][Data];

Alternatively, if you really want to have unlimited groups without using massive array sizes, you could go for an approach that doesn't store group info locally. This would mean that each time you want to get info/create/delete a group, you'd need to query the database. I use this approach for a few things, but it completely depends on the situation for me. If you would do it that way, you could use the way Konstantinos described a few replies earlier.
Also:

Quote:

Originally Posted by whadez

Name is a string variable, which contains characters, you have to set it's lenght.

Код:

enum Data
{
ID,
Name[32]
};
new GroupInfo[Amount_of_groups][Data];

Make sure that you change the "Name" lenght (12) in the loading query.

Код:

cache_get_field_content(i, "Name", GroupInfo[i][Name], mysql, 32);

This is correct, I didn't notice the 'Name' missing the length when I gave my answer. It's something easy to overlook, and it produces weird results :')