Retrieve data from sql

N3v3rM1nd · **Author**

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

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

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

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"

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

N3v3rM1nd

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

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

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

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

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

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