AlliedModders

AlliedModders (https://forums.alliedmods.net/index.php)
-   Scripting Help (https://forums.alliedmods.net/forumdisplay.php?f=11)
-   -   Solved Need help with SQL Queries (https://forums.alliedmods.net/showthread.php?t=316762)

trgthe2nd 06-08-2019 19:53

Need help with SQL Queries
 
I snagged some code from the admin.sma trying to make a Temporary Admins plugin for my personal server. Everything is working except for the deletion code. I can make an admin and insert startdate and days into the table, no problem, but this code isn't deleting properly, no matter what I do.

startdate is a unix timestamp stored as an int in the database.
Code:

public check_date()
{
       
        new table[32], error[128], type[12], errno
       
        new Handle:info = SQL_MakeStdTuple()
        new Handle:sql = SQL_Connect(info, errno, error, 127)
       
        get_cvar_string("amx_sql_table", table, 31)
       
        SQL_GetAffinity(type, 11)
       
        if (sql == Empty_Handle)
        {
                server_print("[AMXX] %L", LANG_SERVER, "SQL_CANT_CON", error)
                return PLUGIN_HANDLED
        }

        new Handle:query
        query = SQL_PrepareQuery(sql,"SELECT `auth`,`password`,`access`,`flags`,`startdate`,`days` FROM `%s`", table)

        if (!SQL_Execute(query))
        {
                SQL_QueryError(query, error, 127)
                server_print("[AMXX] %L", LANG_SERVER, "SQL_CANT_LOAD_ADMINS", error)
        } else if (!SQL_NumResults(query)) {
                server_print("[AMXX] %L", LANG_SERVER, "NO_ADMINS")
        } else {
               
                /** do this incase people change the query order and forget to modify below */
                /*new qcolAuth = SQL_FieldNameToNum(query, "auth")
                new qcolPass = SQL_FieldNameToNum(query, "password")
                new qcolAccess = SQL_FieldNameToNum(query, "access")
                new qcolFlags = SQL_FieldNameToNum(query, "flags")*/
                new qcolStartdate = SQL_FieldNameToNum(query, "startdate")
                new qcolDays = SQL_FieldNameToNum(query, "days")
               
                /*new AuthData[44];
                new Password[44];
                new Access[32];
                new Flags[32];*/
                new Startdate;
                new Days;
                new CurrDate = get_systime()
               
                while (SQL_MoreResults(query))
                {
                        /*SQL_ReadResult(query, qcolAuth, AuthData, sizeof(AuthData)-1);
                        SQL_ReadResult(query, qcolPass, Password, sizeof(Password)-1);
                        SQL_ReadResult(query, qcolAccess, Access, sizeof(Access)-1);
                        SQL_ReadResult(query, qcolFlags, Flags, sizeof(Flags)-1);*/
                        SQL_ReadResult(query, qcolStartdate, Startdate);
                        SQL_ReadResult(query, qcolDays, Days);
                       
                        if (Startdate + (Days * 86400) <= CurrDate)
                        {
                                SQL_QueryAndIgnore(sql, "DELETE FROM `%s` WHERE startdate = %i", table, Startdate)
                                //server_print("[AMXX] %s Admin has expired.", AuthData)
                        }
                        SQL_NextRow(query)
                }
               
                SQL_FreeHandle(query)
                SQL_FreeHandle(sql)
                SQL_FreeHandle(info)
        }
       
        return PLUGIN_HANDLED
}


Natsheh 06-09-2019 03:00

Re: Need help with SQL Queries
 
Few questions when are you calling check_date, are you connected to the database, do you have enough privileges to delete from the tables

Also do some debugging to know where the code is failing

Would be helpful if you provided some error logs

Bugsy 06-09-2019 11:07

Re: Need help with SQL Queries
 
StartDate & Days are integers so you should use the following format:
PHP Code:

Startdate SQL_ReadResult(queryqcolStartdate ); 

You should use an <= condition in your query:
PHP Code:

SQL_QueryAndIgnore(sql"DELETE FROM `%s` WHERE startdate <= %i"tableStartdate

What I want to know is why you are reading all of the records in the table and then deleting 1 by one. See if this works:

PHP Code:

SQL_QueryAndIgnore(sql"DELETE FROM `%s` WHERE (Startdate + (Days * 86400)) <= %d"tableCurrDate 


trgthe2nd 06-09-2019 16:16

Re: Need help with SQL Queries
 
Quote:

Originally Posted by Bugsy (Post 2654993)
What I want to know is why you are reading all of the records in the table and then deleting 1 by one. See if this works:

PHP Code:

SQL_QueryAndIgnore(sql"DELETE FROM `%s` WHERE (Startdate + (Days * 86400)) <= %d"tableCurrDate 


Inexperience is the answer to that question. This works ALMOST flawlessly.
What is the syntax if I wanted to check that days is not equal to 0?
I'm using 0 as permanent rather than a whole lotta 9s.

Edit: that was a simple google :P
PHP Code:

SQL_QueryAndIgnore(sql"DELETE FROM `%s` WHERE (Startdate + (Days * 2)) <= %d AND Days != 0"tableCurrDate 

Thanks for the help


All times are GMT -4. The time now is 17:19.

Powered by vBulletin®
Copyright ©2000 - 2024, vBulletin Solutions, Inc.