AlliedModders - [Solved] Retrieve data from sql

Page 1 of 2

Show 40 post(s) from this thread on one page

AlliedModders (https://forums.alliedmods.net/index.php)

- Scripting Help (https://forums.alliedmods.net/forumdisplay.php?f=11)

- - Solved Retrieve data from sql (https://forums.alliedmods.net/showthread.php?t=347426)

N3v3rM1nd

04-24-2024 09:22

Retrieve data from sql

Hi, could anyone help me with a piece of code for a RANK function?

For example, I have this top function:

PHP Code:


		
			
formatex(szTemp, charsmax(szTemp), "SELECT * FROM `%s` ORDER BY `score` DESC LIMIT 0,15", TABEL);

SQL_ThreadQuery(g_SqlTuple, "ZP_TOP", szTemp, szData, 1)

PHP Code:


		
			
public ZP_TOP( FailState, Handle:Query, Error[], Errcode, Data[], DataSize ) 

{

    if(FailState == TQUERY_CONNECT_FAILED) 

        log_amx("Load - Could not connect to SQL database.  [%d] %s", Errcode, Error)

    else if(FailState == TQUERY_QUERY_FAILED) 

        log_amx("Load Query failed. [%d] %s", Errcode, Error)



    new id; id = Data[0]

    new to_get[15][64]

        

    new rows1 = SQL_NumResults(Query)

    new iScore[15]



    if(SQL_MoreResults(Query)) 

    {

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

        {

            SQL_ReadResult(Query, 1, to_get[i], 63)

            iScore[i] = SQL_ReadResult(Query, 6)

            

            SQL_NextRow(Query)

        }

    }



    if(rows1 > 0) 

    {

        new iLen=0;

        iLen = format( g_Buffer[iLen], 2047, g_sTopStyle )

        iLen += format( g_Buffer[iLen], 2047 - iLen, "<body bgcolor=#000000><table border=0 cellspacing=0 cellpadding=3px><tr><th class=p>#<td class=p><th>Player<th>Score" )     



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

        {

            new rowColor[16];

            if(i < 3)

            {

                if(i == 0) format(rowColor, sizeof(rowColor), "blue");

                else if(i == 1) format(rowColor, sizeof(rowColor), "yellow");

                else format(rowColor, sizeof(rowColor), "red");

            } 

            else format(rowColor, sizeof(rowColor), "");

            iLen += format(g_Buffer[iLen], 2047 - iLen, "<tr><td class=p>%d<td class=p><td class=%s>%s<td>%i<td>", i + 1, rowColor, to_get[i], iScore[i]);

        }

        show_motd(id, g_Buffer, "Top15 Zombie")

    }

    return 1

}

and I want to make one for RANK, and display:
"[AMXX] Your rank is m/n with a data1 b data2 c data3 d data4"
Where 'm' is the current rank of the player (or position in the DB, I'm not sure how it would come), 'n' is the total entries in the DB, and a, b, c, d are other integer values.

Rohanlogs

04-24-2024 13:14

Re: Retrieve data from sql

Hi. Do you have the code for "CREATE TABLE ..." query? Can you post it?

N3v3rM1nd

04-24-2024 16:07

Re: Retrieve data from sql

Quote:

Originally Posted by Rohanlogs (Post 2821556)

Hi. Do you have the code for "CREATE TABLE ..." query? Can you post it?

PHP Code:


		
			
public MySql_Init()
{
    g_SqlTuple = SQL_MakeDbTuple(Host,User,Pass,Db)

    new ErrorCode,Handle:SqlConnection = SQL_Connect(g_SqlTuple,ErrorCode,g_Error,charsmax(g_Error))
    if(SqlConnection == Empty_Handle)    set_fail_state(g_Error)

    new Handle:Queries
    Queries = SQL_PrepareQuery(SqlConnection,"CREATE TABLE IF NOT EXISTS %s (steamid varchar(35), name VARCHAR(32), points INT(11), infections INT(11), kills INT(11), deaths INT(11), score INT(11))", TABEL)

    if(!SQL_Execute(Queries))
    {
        SQL_QueryError(Queries,g_Error,charsmax(g_Error))
        set_fail_state(g_Error)
    }

    SQL_FreeHandle(Queries)
    SQL_FreeHandle(SqlConnection)   
}

I tried my best but im not that good in MySQL

Rohanlogs

04-24-2024 18:55

Re: Retrieve data from sql

1 Attachment(s)

Its fine, try this. Do you know how to re-compile .sma file?
You need to edit this part of the code:

#define TABLE "zm_top"

Replace "zm_top" with your actual table name. You should have a definition '#define TABEL' inside your code which has the name of your table, then recompile. After that it 'should' work.

Once running, write amx_rank in console, or say /rank
This will do a full table scan on each command, therefore I've added antispamming.

In non-technical terms, if your database has 100s of thousands of rows, this command will be rather slow and might even cause lag if the server is really bad. However I doubt your database has that many players so it should be fine. If you run into any performance issues, we can optimize it further. But for now this should do. You may edit the displayed chat message to your liking.

This plugin will use connection info specified in amxmodx/configs/sql.cfg:

amx_sql_host "127.0.0.1"
amx_sql_user "root"
amx_sql_pass ""
amx_sql_db "amx"
amx_sql_type "mysql"

N3v3rM1nd

04-26-2024 22:25

Re: Retrieve data from sql

Quote:

Originally Posted by Rohanlogs (Post 2821573)

Thanks, i will test the code these days and i'll let you know the result

N3v3rM1nd

05-03-2024 10:26

Re: Retrieve data from sql

So, I've looked over the code. I don't want to use "SQL_MakeStdTuple()" because I'm using SQLite and it doesn't help me that much.

Returning to the code, what you gave me doesn't work. I just want a simple function to retrieve the player's position from MySQL and its related information, that's all.

Rohanlogs

05-03-2024 20:45

Re: Retrieve data from sql

Have you tried going to amxmodx/configs/sql.cfg, and changing the driver type from "mysql" to -> "sqlite"?

Code:

amx_sql_host "<fill in your actual host>"

amx_sql_user "<fill in your actual username>"

amx_sql_pass "<fill in your actual password>"

amx_sql_db "<fill in your actual database>"

amx_sql_type "sqlite"

You need to make sure your credentials are correct. It doesn't work if there's no successful connection to the database.

Quote:

Originally Posted by N3v3rM1nd (Post 2822031)

I just want a simple function to retrieve the player's position from MySQL

This plugin actually does just that. There's no 'magic function' to just know player's rank "and its related information", without querying the database, lol. There's no extra shenanigans here, just that query. Also, you just said you're using SQLite and not MySQL, so which one is it? These are 2 different databse systems.

SQL_MakeStdTuple is not the issue, as the name of the library <sqlx> suggests, its supposed to work with both MySQL and SQLite. Saying "it doesn't work" is not very helpful. If you have issues, provide any error logs with debug turned on.

In your plugins.ini:

Code:

zm_rank.amxx debug

But first, just make sure your sql.cfg is actually setup correctly, then try again.

N3v3rM1nd

05-04-2024 08:02

Re: Retrieve data from sql

This is the error

PHP Code:


		
			
Load Query failed. [1] near "(": syntax error

I've searched for what might have caused the error and tried everything that could be tried, but in vain.

This is what I currently have in the script:

PHP Code:


		
			
new szData[1]; szData[0] = iPlayer
new szTemp[512]
...

else if(equali(cMessage, "/rank") || equali(cMessage, "rank"))
{
format(szTemp, charsmax(szTemp),
"
WITH rankedPlayers AS (
SELECT
steamid,
name,
score,
points,
infections,
kills,
deaths,
RANK() OVER (ORDER BY score DESC) AS rank,
(SELECT COUNT(*) FROM %s) AS total_players
FROM
%s
)
SELECT
steamid,
name,
score,
rank,
total_players,
points,
infections,
kills,
deaths
FROM
rankedPlayers
WHERE
steamid = '%s';
",
TABEL, TABEL, g_cSteam[iPlayer]
);
SQL_ThreadQuery(g_SqlTuple, "ZP_RANK", szTemp, szData, 1)
return 1

PHP Code:


		
			
public ZP_RANK(FailState, Handle:Query, Error[], Errcode, Data[], DataSize)
{
if(FailState == TQUERY_CONNECT_FAILED)
log_amx("Load - Could not connect to SQL database. [%d] %s", Errcode, Error)
else if(FailState == TQUERY_QUERY_FAILED)
log_amx("Load Query failed. [%d] %s", Errcode, Error)

new id; id = Data[0]
if(!is_user_connected(id))
    return

if(SQL_NumResults(Query))
{
    new score = SQL_ReadResult(Query, 2)
    new rank = SQL_ReadResult(Query, 3)
    new total = SQL_ReadResult(Query, 4)
    new points = SQL_ReadResult(Query, 5)
    new infections = SQL_ReadResult(Query, 6)
    new kills = SQL_ReadResult(Query, 7)
    new deaths = SQL_ReadResult(Query, 8)

    PrintToChat(id, "^x04[ZP] ^x01 Your rank is %i/%i with a score of %i >> %i kills %i deaths, %i infections and %i points.", \
        rank, total, score, kills, deaths, infections, points)
}
else PrintToChat(id, "^x04[ZP] You don't have any rank !")
}

Its saying "You don't have any rank" for everyone

Rohanlogs

05-04-2024 08:17

Re: Retrieve data from sql

You're trying to integrate the plugin I just gave you inside your own code, you don't need to do all that. You're better off just using the plugin that I posted, and fixing your sql.cfg

The error however is caused by a lack of line breaks. Look closer at the code I posted:

Spoiler

There's a line break character '\' which is used to continue a line of code on the next line without breaking the syntax. Since you've removed all of them, the syntax breaks, and throws this error. Its saying "You have no rank", because the query doesn't return any results due to the broken syntax.

N3v3rM1nd

05-04-2024 09:17

Re: Retrieve data from sql

I attached the backslash as well (https://prnt.sc/36U_6dAPr96T), but I don't know why it wasn't attached. I don't want another plugin; I want it to be integrated into what I already have

All times are GMT -4. The time now is 18:47.

Page 1 of 2

Show 40 post(s) from this thread on one page