Raised This Month: $67 Target: $400
 16% 

SQL Problem


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
Abhinash
Senior Member
Join Date: Jan 2015
Location: India,kolkata
Old 03-06-2021 , 14:28   SQL Problem
Reply With Quote #1

I am using SQLite.
But when I am table data with 2 conditions the columns are getting added every time I start server.
Means everytime I am creating server the columns are getting repeated.

Codes ---

Load Section ---

PHP Code:
public Load_MySql(id)
{
    new 
szSteamId[32], szTemp[512]
    
get_user_authid(idszSteamIdcharsmax(szSteamId))

    new 
Data[1]
    
Data[0] = id

    
//we will now select from the table `tutorial` where the steamid match
    
format(szTemp,charsmax(szTemp),"SELECT * FROM `perfectzm` WHERE `steamid` = '%s' AND `name` = '%s'"szSteamIdg_playername[id])
    
SQL_ThreadQuery(g_SqlTuple"register_client"szTempData1)

Register client section ---

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

    new 
id
    id 
Data[0]

    if(
SQL_NumResults(Query) < 1
    {
        
//.if there are no results found
        
        
new szSteamId[32]
        
get_user_authid(idszSteamIdcharsmax(szSteamId)) // get user's steamid
        
        //  if its still pending we can't do anything with it
        
if (equal(szSteamId,"ID_PENDING"))
            return 
PLUGIN_HANDLED
            
        
new szTemp[512]
        
        
// now we will insturt the values into our table.
        
format(szTemp,charsmax(szTemp),"INSERT INTO `perfectzm` ( `steamid`, `name`, `points`, `kills`, `deaths`, `score`)VALUES ('%s', '%s', '0', '0', '0', '0');"szSteamIdg_playername[id])
        
SQL_ThreadQuery(g_SqlTuple,"IgnoreHandle"szTemp)
        
g_totalplayers++
    } 
    else 
    {
        
// if there are results found
        
g_points[id] = SQL_ReadResult(Query2)
        
g_kills[id] = SQL_ReadResult(Query3)
        
g_deaths[id] = SQL_ReadResult(Query4)
        
g_score[id] = SQL_ReadResult(Query5)

        
set_dhudmessage(02552550.030.526.010.0)
        
show_dhudmessage(id"You are now ranked!")
    }
    
    return 
PLUGIN_HANDLED

MySQL Init section ---

PHP Code:
public MySql_Init()
{
    
// Set Affinity to use SQLite instead of SQL
    
SQL_SetAffinity("sqlite")

    
// We tell the API that this is the information we want to connect to,
    // just not yet. basically it's like storing it in global variables
    
g_SqlTuple SQL_MakeDbTuple("""""""ZombieQueen")
   
    
// Ok, we're ready to connect
    
new ErrorCode,Handle:SqlConnection SQL_Connect(g_SqlTuple,ErrorCode,g_Error,charsmax(g_Error))
    if(
SqlConnection == Empty_Handle)
    {
        
// stop the plugin with an error message
        
set_fail_state(g_Error)
    }

    new 
Handle:Queries
    
// we must now prepare some random queries
    
Queries SQL_PrepareQuery(SqlConnection,"CREATE TABLE IF NOT EXISTS perfectzm (steamid varchar(32), name varchar(32), points INT(11), kills INT(11), deaths INT(11), score INT(11))")

    if(!
SQL_Execute(Queries))
    {
        
// if there were any problems the plugin will set itself to bad load.
        
SQL_QueryError(Queries,g_Error,charsmax(g_Error))
        
set_fail_state(g_Error)
    }
    
    
// Free the querie
    
SQL_FreeHandle(Queries)

    
// you free everything with SQL_FreeHandle
    
SQL_FreeHandle(SqlConnection)   

Does anyone knows any solution ?
Abhinash is offline
Bugsy
AMX Mod X Moderator
Join Date: Feb 2005
Location: NJ, USA
Old 03-07-2021 , 02:10   Re: SQL Problem
Reply With Quote #2

Are you saying a new row is being created on each connect, even when the player already has a row in the db?

Check that your query is correctly getting formatted:
"SELECT * FROM `perfectzm` WHERE `steamid` = '%s' AND `name` = '%s'", szSteamId, g_playername[id])"

I would avoid using a column name of 'name'.
__________________
Bugsy is offline
Abhinash
Senior Member
Join Date: Jan 2015
Location: India,kolkata
Old 03-07-2021 , 03:12   Re: SQL Problem
Reply With Quote #3

Quote:
Originally Posted by Bugsy View Post
Are you saying a new row is being created on each connect, even when the player already has a row in the db?

Check that your query is correctly getting formatted:
"SELECT * FROM `perfectzm` WHERE `steamid` = '%s' AND `name` = '%s'", szSteamId, g_playername[id])"

I would avoid using a column name of 'name'.
Exactly.
I want to select data only if the steamid and name of the player matches, else don't.
But when doing this a new row is getting added on every connect and the old row is left behind and not updated.
Abhinash is offline
Natsheh
Veteran Member
Join Date: Sep 2012
Old 03-07-2021 , 06:55   Re: SQL Problem
Reply With Quote #4

I don't see a return when the query fails instead it will proceed to add more rows.

When creating the table make sure the steamid column is unique.
__________________
@Jailbreak Main Mod v2.6.5 100%
@User Tag Prefix 100% done !
@Mystery Box 100% done !
@VIP System 100% done !
Natsheh is offline
Send a message via MSN to Natsheh Send a message via Skype™ to Natsheh
Bugsy
AMX Mod X Moderator
Join Date: Feb 2005
Location: NJ, USA
Old 03-07-2021 , 10:38   Re: SQL Problem
Reply With Quote #5

Good catch. I assume he's checked his logs. @Abhinash, do you see the below in logs?

"Load - Could not connect to SQL database. [%d] %s"
"Load Query failed. [%d] %s"

I recommend changing your CREATE to:
Code:
CREATE TABLE IF NOT EXISTS perfectzm (ID INT AUTO_INCREMENT PRIMARY KEY, steamid varchar(32) UNIQUE, name varchar(32), points INT(11), kills INT(11), deaths INT(11), score INT(11))
and then to INSERT or UPDATE, use this. You will never get a duplicate steam ID and it will always over-write the players existing data.
Code:
REPLACE INTO perfectzm (steamid, name, points, kills, deaths, score) VALUES ("STEAM:0:12345","bugsy",5,6,7,8)
__________________
Bugsy is offline
LondoN
Senior Member
Join Date: Dec 2015
Location: Roman, Romania.
Old 03-08-2021 , 14:16   Re: SQL Problem
Reply With Quote #6

use UPDATE for updating client data

and for restoring best option for my is:

g_value[id] = SQL_ReadResult(Handle:Query, SQL_FieldNameToNum(Handle:Query, "name,points,steamid etc"));
__________________
LondoN is offline
Natsheh
Veteran Member
Join Date: Sep 2012
Old 03-08-2021 , 14:36   Re: SQL Problem
Reply With Quote #7

I don't think that update is better than replace.
__________________
@Jailbreak Main Mod v2.6.5 100%
@User Tag Prefix 100% done !
@Mystery Box 100% done !
@VIP System 100% done !
Natsheh is offline
Send a message via MSN to Natsheh Send a message via Skype™ to Natsheh
LondoN
Senior Member
Join Date: Dec 2015
Location: Roman, Romania.
Old 03-08-2021 , 14:54   Re: SQL Problem
Reply With Quote #8

Quote:
Originally Posted by Natsheh View Post
I don't think that update is better than replace.
yeah, probably you're right, i know basic skills of sql.

They do the same thing in diffferent ways but the speed intex of sql statements differs with 0,00001 seconds probably.

It's a 22 year old game, i don't think the cpu/ram will increase with 20-30 percent if using replace instread of update.

Buy everyone use what it's like.
__________________
LondoN is offline
Natsheh
Veteran Member
Join Date: Sep 2012
Old 03-08-2021 , 15:18   Re: SQL Problem
Reply With Quote #9

its not that, but its that UPDATE won't add a new row if the current data wasn't found it will just ignore in the other hand REPLACE will add a new row if the current data wasn't found, thats why i recommend using REPLACE.
__________________
@Jailbreak Main Mod v2.6.5 100%
@User Tag Prefix 100% done !
@Mystery Box 100% done !
@VIP System 100% done !

Last edited by Natsheh; 03-08-2021 at 15:18.
Natsheh is offline
Send a message via MSN to Natsheh Send a message via Skype™ to Natsheh
JocAnis
Veteran Member
Join Date: Jun 2010
Old 03-08-2021 , 15:27   Re: SQL Problem
Reply With Quote #10

I would suggest as Bugsy said first, rename column 'name' to smth else like 'nickname', and ofc put ';' at the end of every query to avoid any possible error/problem
__________________
KZ Public Autocup - PrimeKZ

My blog: http://primekz.xyz (in progress...)
JocAnis 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 19:36.


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