Monthly Target: $400 Donations: $38
 9% 

How to get date from database ?


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
BFBombi
Junior Member
Join Date: Jul 2011
Old 07-20-2011 , 05:21   How to get date from database ?
Reply With Quote #1

Hey, i have a question... I need to get date from database from field date (yyyy-mm-dd or yyyy-dd-mm) then take away todays date, and get number what i need to show on server when user type one command.

Example: In database 2011-15-08, server get it and take away 2011-20-07 and when user type command he get something like this "You have 25 more days".
BFBombi is offline
berni
SourceMod Plugin Approver
Join Date: May 2007
Location: Austria
Old 07-20-2011 , 08:21   Re: How to get date from database ?
Reply With Quote #2

It would be easier to work with unix timestamps. You can save a unix timestamp
as int in the database. then you just subtract the current time with the saved time and you have the seconds which you can calculate into days.

MySQL Date should be possible too but then you better do the day calculation with MySQL functions.
__________________
Why reinvent the wheel ? Download smlib with over 350 useful functions.

When people ask me "Plz" just because it's shorter than "Please" I feel perfectly justified to answer "No" because it's shorter than "Yes"
powered by Core i7 3770k | 32GB DDR3 1886Mhz | 2x Vertex4 SSD Raid0
berni is offline
BFBombi
Junior Member
Join Date: Jul 2011
Old 07-20-2011 , 12:03   Re: How to get date from database ?
Reply With Quote #3

Okey, but how i can do it ? I tryed many things, but i can't do it.
BFBombi is offline
Thrawn2
Veteran Member
Join Date: Apr 2009
Old 07-20-2011 , 12:36   Re: How to get date from database ?
Reply With Quote #4

you can't get unix_timestamps or you can't get the mysql date functions to work?

if you want to use unix timestamps (take a look at FROM_UNIXTIME) just subtract the ints and you get the seconds between both dates.
if you want to utilize mysqls builtin stuff take a look at DATEDIFF and the examples.

also: what did you try and why did it fail?
__________________
einmal mit profis arbeiten. einmal.
Thrawn2 is offline
BFBombi
Junior Member
Join Date: Jul 2011
Old 07-20-2011 , 15:25   Re: How to get date from database ?
Reply With Quote #5

I'am a beginner in this and I don't now how to do it I tryed it with SQL_TQuerry.
Please show me this in code and I understand it.

Last edited by BFBombi; 07-20-2011 at 15:34.
BFBombi is offline
Thrawn2
Veteran Member
Join Date: Apr 2009
Old 07-20-2011 , 15:44   Re: How to get date from database ?
Reply With Quote #6

Quote:
Originally Posted by BFBombi View Post
I'am a beginner in this and I don't now how to do it I tryed it with SQL_TQuerry.
Please show me this in code and I understand it.
no. i'd rather help in the long term... if you really want to learn it, read about it and play with it.
with the links i gave earlier and this you can figure everything out yourself. also there are enough examples/plugins in the forums which could help you a lot.
its btw much more rewarding to do this yourself, than just get code to copy&paste...
__________________
einmal mit profis arbeiten. einmal.
Thrawn2 is offline
BFBombi
Junior Member
Join Date: Jul 2011
Old 07-21-2011 , 10:43   Re: How to get date from database ?
Reply With Quote #7

Okey, i tryed it but where is my error ?

PHP Code:
public Action:CmdTest(clientargs)
{
    if (
g_bIsTestclient ] == 1)
    {
        new 
BaseDate;
        
CheckDate(clientBaseDate);
        
        
PrintToChat(client"\x05%t \x01> \x05%t Days left (In unixtime): %i""Mod""activated"BaseDate);
    }
    else 
    {
        
PrintToChat(client"\x05%t \x01> \x05%t""Mod""not activated");
        
PrintToChat(client"\x05%t \x01> \x05%t""Mod""buy link");
    }
    
    return 
Plugin_Handled;
}

CheckDate(clientBaseDate)
{
    
decl String:query[255];
    
    
Format(querysizeof(query), "SELECT last_date FROM users WHERE last_date = '%i'"BaseDate);
    
SQL_TQuery(DatabaseT_CheckDatequeryclient);
}  

public 
T_CheckDate(Handle:ownerHandle:hndl, const String:error[], any:client)
{    
    if (
hndl == INVALID_HANDLE)
    {
        
LogToFile(logFile"%s."error);
    }

BFBombi is offline
Thrawn2
Veteran Member
Join Date: Apr 2009
Old 07-21-2011 , 11:04   Re: How to get date from database ?
Reply With Quote #8

Quote:
Originally Posted by BFBombi View Post
Okey, i tryed it but where is my error ?
your logic is borked in several places:

PHP Code:
new BaseDate;
CheckDate(clientBaseDate); 
You never set BaseDate, so it is always 0, therefore your print output in CheckDate() is always '...(in unixtime): 0'.
But you don't want to print in there to begin with, because you are using a threaded query, which means you won't have the results ready after calling CheckDate, but only after T_CheckDate has been called. Only then you could use your results e.g. to print them.

PHP Code:
Format(querysizeof(query), "SELECT last_date FROM users WHERE last_date = '%i'"BaseDate);
SQL_TQuery(DatabaseT_CheckDatequeryclient); 
Again, BaseDate is not set and you select the attribute you are querying against in the first place, so your sql query could be read as: "Select 0". Well almost. You will get a number of rows all with the result 0, if there are any rows in your table with last_date = 0.
I also thought you wanted to check how many days a certain user has left, so your query should rather be sth like this:
"select last_date FROM users WHERE steamid = 'STEAM_0:1:12314'".
Furthermore you'll never do anything with the result you get from the database (which you should do in T_CheckDate()).

As a principal hint: Don't try to do everything at once, but start slow and grow your code. So you first want to have the correct SQL query, use phpMyAdmin or whatever you are comfortable with to create and test your query. If you are getting the results you want (steamid + last_date), proceed to your plugin and use non-threaded queries. If those work, rewrite your code to use threaded queries.
You are currently trying to do all at the same time, which is bound to fail if you are new to all of them.
__________________
einmal mit profis arbeiten. einmal.

Last edited by Thrawn2; 07-21-2011 at 11:08.
Thrawn2 is offline
BFBombi
Junior Member
Join Date: Jul 2011
Old 07-21-2011 , 16:25   Re: How to get date from database ?
Reply With Quote #9

Sorry, but i can't understand it I want to give last date of user premium
I do this, but server show me SQL_FetchInt reported: Invalid query Handle 7e0077 (error: 2)

PHP Code:
public Action:CmdPremium(clientargs)
{
    if (
g_bIsPremiumclient ] == 1)
    {
        new 
last_date;
        
CheckTest(clientlast_date);
    }
    else 
    {
        
PrintToChat(client"\x05%t \x01> \x05%t""premium""not activated");
        
PrintToChat(client"\x05%t \x01> \x05%t""premium""buy link");
    }
    
    return 
Plugin_Handled;
}

CheckTest(clientlast_date)
{
    
decl String:query[255];
    
    
decl String:auth[32];
    
GetClientAuthString(clientauthsizeof(auth));
    
    
Format(querysizeof(query), "SELECT last_date FROM premium_users_dust2only WHERE steam_id = '%s'"auth);
    
    
last_date SQL_FetchInt(Database5);
    
PrintToChat(client"Your last day in Unixtime: %i"last_date);
    

Please help me with this, i need that. I also locked in another plugins, but it don't work.
BFBombi is offline
Thrawn2
Veteran Member
Join Date: Apr 2009
Old 07-21-2011 , 17:02   Re: How to get date from database ?
Reply With Quote #10

ok, you obviously tried to help yourself.
i like that, so here's some more help:
i quickly wrote this to push you in the right direction. i guess almost everything you need is in there.
this is completely untested and you also need to adapt the db name and stuff. this is just meant as an example which you can heavily copy+paste.
but please, still try to understand what's happening. if you have any further questions, feel free to ask.

PHP Code:
#pragma semicolon 1
#include <sourcemod>

new Handle:g_hDatabase INVALID_HANDLE;
new 
Handle:g_hCvarDBName;

new 
String:g_sDatabaseName[64];

public 
OnPluginStart() {
    
g_hCvarDBName CreateConVar("sm_yourpluginname_dbname""admintools""Use this db for your plugin."FCVAR_PLUGIN);

    
HookConVarChange(g_hCvarDBNameCvar_Changed);

    
RegConsoleCmd("sm_querydb"Command_QueryDB);
    
RegConsoleCmd("sm_querydb_unixtimestamp"Command_QueryDBUnix);
}


public 
Cvar_Changed(Handle:convar, const String:oldValue[], const String:newValue[]) {
    
OnConfigsExecuted();
}

public 
OnConfigsExecuted() {
    
GetConVarString(g_hCvarDBName,g_sDatabaseName,sizeof(g_sDatabaseName));

    
SQL_TConnect(SQL_Connectedg_sDatabaseName);
}

public 
SQL_Connected(Handle:ownerHandle:hndl, const String:error[], any:data)
{
    if(
g_hDatabase != INVALID_HANDLE) {
        
CloseHandle(g_hDatabase);
        
g_hDatabase INVALID_HANDLE;
    }

    if (
hndl == INVALID_HANDLE)
    {
        
LogError("Database error: %s"error);
        return;
    }

    
g_hDatabase hndl;
    
LogMessage("Connected successfully to database");
}

public 
Action:Command_QueryDB(client,args) {
    if(
g_hDatabase == INVALID_HANDLE)
        return;

    
decl String:sSteamID[32];
    
GetClientAuthString(clientsSteamIDsizeof(sSteamID));

    
decl String:sQuery[1024];
    
Format(sQuerysizeof(sQuery), "SELECT last_date FROM premium_users_dust2only WHERE steam_id = '%s'"sSteamID);
    
SQL_TQuery(g_hDatabaseSQL_ProcessResultsQueryclientDBPrio_High);
}

public 
SQL_ProcessResult(Handle:dbHandle:hQuery, const String:error[], any:client)
{
    if(
hQuery == INVALID_HANDLE) {
        
LogError("Query failed: %s"error);
        return;
    }

    if(
SQL_FetchRow(hQuery))
    {
        new 
String:sDate[255];
        
SQL_FetchString(hQuery0sDatesizeof(sDate));

        
PrintToChat(client"Expiry date: %s"sDate);
    }

    
CloseHandle(hQuery);
}

public 
Action:Command_QueryDBUnix(client,args) {
    if(
g_hDatabase == INVALID_HANDLE)
        return;

    
decl String:sSteamID[32];
    
GetClientAuthString(clientsSteamIDsizeof(sSteamID));

    
decl String:sQuery[1024];
    
Format(sQuerysizeof(sQuery), "SELECT UNIX_TIMESTAMP(TIMESTAMP(last_date))-UNIX_TIMESTAMP(now()) AS seconds_left FROM premium_users_dust2only WHERE steam_id = '%s'"sSteamID);
    
SQL_TQuery(g_hDatabaseSQL_ProcessResultUnixsQueryclientDBPrio_High);
}

public 
SQL_ProcessResultUnix(Handle:dbHandle:hQuery, const String:error[], any:client)
{
    if(
hQuery == INVALID_HANDLE) {
        
LogError("Query failed: %s"error);
        return;
    }

    if(
SQL_FetchRow(hQuery))
    {
        new 
iSecondsLeft SQL_FetchInt(hQuery0);

        if(
iSecondsLeft 0) {
            
PrintToChat(client"Seconds left: %i"iSecondsLeft);
        }
    }

    
CloseHandle(hQuery);

__________________
einmal mit profis arbeiten. einmal.
Thrawn2 is offline
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -4. The time now is 12:32.


Powered by vBulletin®
Copyright ©2000 - 2015, vBulletin Solutions, Inc.
Theme made by Freecode