SA-MP Forums

Go Back   SA-MP Forums > SA-MP Scripting and Plugins > Scripting Help

Reply
 
Thread Tools Display Modes
Old 22/07/2020, 11:00 PM   #1
MarioKamani
Big Clucker
 
Join Date: Jan 2017
Location: Albania
Posts: 90
Reputation: 18
Default SQL 41-4 - Unknown column 'X' in 'field list'

Hey,

I am creating a faction system using dialogs and I'm having trouble.

This is my current code (excuse me if the code is bad, i just started sql a few days ago)

HTML Code:
case DIALOG_FACTION:
		{
			if(response)
			{
/*				new facid;
				cache_get_value_name_int(0, "id", facid);*/
				if(listitem == 0)
				{					
					new facid;
					cache_get_value_name_int(0, "id", facid);
					new query[400];
					mysql_format(g_SQL, query, sizeof(query),"INSERT INTO `factions` (`id`, `name`, `type`, `Rank1`, `Rank2`) VALUES ('%d', '%s', '%s', '%s', '%s')", facid, "NoName", "1" , "Rank1", "Rank2");
					mysql_tquery(g_SQL, query);
					ShowPlayerDialog(playerid, DIALOG_FACTIONNAME, DIALOG_STYLE_INPUT, "Faction Creation", "Enter the faction name below:", "Continue", "Abort");
				}
				if(listitem == 1)
				{
					new facid;
					cache_get_value_name_int(0, "id", facid);
					new query[400];
					mysql_format(g_SQL, query, sizeof(query),"INSERT INTO `factions` (`id`, `name`, `type`, `Rank1`, `Rank2`) VALUES ('%d', '%s', '%s', '%s', '%s')", facid, "NoName", "2" , "Rank1", "Rank2");
					mysql_tquery(g_SQL, query);
				}
				if(listitem == 2)
				{
					new facid;
					cache_get_value_name_int(0, "id", facid);
					new query[400];
					mysql_format(g_SQL, query, sizeof(query),"INSERT INTO `factions` (`id`, `name`, `type`, `Rank1`, `Rank2`) VALUES ('%d', '%s', '%s', '%s', '%s')", facid, "NoName", "3" , "Rank1", "Rank2");
					mysql_tquery(g_SQL, query);	
				}
			}

		}	
		case DIALOG_FACTIONNAME:
		{
			new facid;
			cache_get_value_name_int(0, "id", facid);
			new query[400];
			mysql_format(g_SQL, query, sizeof query, "UPDATE `factions` SET `name` = %s WHERE `id` = %d", inputtext, facid);
			mysql_tquery(g_SQL, query);
		}
		default: return 0; 
	}
	return 1;
}
So when I do /fcreate, a dialog with 3 types (DIALOG_TYPE_LIST) shows up and when I select one, it successfully appears in the database but when the factionname dialog shows up and after I enter the name I want, I get those errors in the .log file.
HTML Code:
01:01:12] [ERROR] error #1054 while executing query "UPDATE `factions` SET `name` = asd WHERE `id` = 0": Unknown column 'asd' in 'field list'
[01:01:13] [ERROR] cache_get_value_name_int: no active cache
DB after filling both dialogs:


Help appreciated.
MarioKamani is offline   Reply With Quote
Old 22/07/2020, 11:23 PM   #2
Markski
Big Clucker
 
Markski's Avatar
 
Join Date: Apr 2015
Location: La Plata, Argentina
Posts: 133
Reputation: 59
Default Re: SQL 41-4 - Unknown column 'X' in 'field list'

You must add ' symbols around strings in a SQL query.

Code:
mysql_format(g_SQL, query, sizeof query, "UPDATE `factions` SET `name` = '%s' WHERE `id` = %d", inputtext, facid);
` is used to indicate a table or column, ' is used to indicate a value.

You also might wanna make sure players don't inject malicious SQL instructions through faction names and any other text that goes into the database.
Markski is offline   Reply With Quote
Old 22/07/2020, 11:31 PM   #3
MarioKamani
Big Clucker
 
Join Date: Jan 2017
Location: Albania
Posts: 90
Reputation: 18
Default Re: SQL 41-4 - Unknown column 'X' in 'field list'

Quote:
Originally Posted by Markski View Post
You must add ' symbols around strings in a SQL query.

Code:
mysql_format(g_SQL, query, sizeof query, "UPDATE `factions` SET `name` = '%s' WHERE `id` = %d", inputtext, facid);
` is used to indicate a table or column, ' is used to indicate a value.

You also might wanna make sure players don't inject malicious SQL instructions through faction names and any other text that goes into the database.
Thanks for the reply. The error does not appear anymore, but I'm now getting the chache errors which I unfortunately do not know how to fix.

HTML Code:
[01:32:38] [ERROR] cache_get_value_name_int: no active cache
[01:32:39] [ERROR] cache_get_value_name_int: no active cache
MarioKamani is offline   Reply With Quote
Old 22/07/2020, 11:53 PM   #4
SlowARG
Little Clucker
 
Join Date: Feb 2014
Posts: 38
Reputation: 18
Default Re: SQL 41-4 - Unknown column 'X' in 'field list'

Quote:
Originally Posted by MarioKamani View Post
~snip~

HTML Code:
[01:32:38] [ERROR] cache_get_value_name_int: no active cache
[01:32:39] [ERROR] cache_get_value_name_int: no active cache
Maybe you are not using cache_set_active.

Example implementation: https://github.com/pBlueG/SA-MP-MySQ...stem-cache.pwn
__________________
My releases:

[GitLab] [Plugin] Nobody's fork of YSF
[GitLab] [FilterScript] SA-MP S.S.F.P.
SlowARG is offline   Reply With Quote
Old 23/07/2020, 12:01 AM   #5
MarioKamani
Big Clucker
 
Join Date: Jan 2017
Location: Albania
Posts: 90
Reputation: 18
Default Re: SQL 41-4 - Unknown column 'X' in 'field list'

I tried experimenting with it but still

HTML Code:
[02:03:44] [ERROR] cache_set_active: invalid cache id '0'
[02:03:44] [ERROR] cache_get_value_name_int: no active cache
Code;
HTML Code:
case DIALOG_FACTION:
		{
			if(response)
			{
				cache_set_active(Factions[factionid][Cache_ID]);
				if(listitem == 0)
				{					
					new facid;
					cache_get_value_name_int(0, "id", facid);
					new query[400];
					mysql_format(g_SQL, query, sizeof(query),"INSERT INTO `factions` (`id`, `name`, `type`, `Rank1`, `Rank2`) VALUES ('%d', '%s', '%s', '%s', '%s')", Factions[factionid][Cache_ID], "NoName", "1" , "Rank1", "Rank2");
					mysql_tquery(g_SQL, query);
					ShowPlayerDialog(playerid, DIALOG_FACTIONNAME, DIALOG_STYLE_INPUT, "Faction Creation", "Enter the faction name below:", "Continue", "Abort");
				}
				if(listitem == 1)
				{
					new facid;
					cache_get_value_name_int(0, "id", facid);
					new query[400];
					mysql_format(g_SQL, query, sizeof(query),"INSERT INTO `factions` (`id`, `name`, `type`, `Rank1`, `Rank2`) VALUES ('%d', '%s', '%s', '%s', '%s')", Factions[factionid][Cache_ID], "NoName", "2" , "Rank1", "Rank2");
					mysql_tquery(g_SQL, query);
				}
				if(listitem == 2)
				{
					new facid;
					cache_get_value_name_int(0, "id", facid);
					new query[400];
					mysql_format(g_SQL, query, sizeof(query),"INSERT INTO `factions` (`id`, `name`, `type`, `Rank1`, `Rank2`) VALUES ('%d', '%s', '%s', '%s', '%s')", Factions[factionid][Cache_ID], "NoName", "3" , "Rank1", "Rank2");
					mysql_tquery(g_SQL, query);	
				}
			}

		}	
		case DIALOG_FACTIONNAME:
		{
			new facid;
			cache_get_value_name_int(0, "id", facid);
			new query[400];
			mysql_format(g_SQL, query, sizeof query, "UPDATE `factions` SET `name` = '%s' WHERE `id` = %d", inputtext, Factions[factionid][Cache_ID]);
			mysql_tquery(g_SQL, query);
		}

		default: return 0; 
	}
	return 1;
}
MarioKamani is offline   Reply With Quote
Old 23/07/2020, 12:07 AM   #6
SlowARG
Little Clucker
 
Join Date: Feb 2014
Posts: 38
Reputation: 18
Default Re: SQL 41-4 - Unknown column 'X' in 'field list'

It's because the cache is automatically deleted when the query callback ends.
So if u want to implement a system like this, you will need to save the cache with cache_save().

Example: https://github.com/pBlueG/SA-MP-MySQ...cache.pwn#L255
__________________
My releases:

[GitLab] [Plugin] Nobody's fork of YSF
[GitLab] [FilterScript] SA-MP S.S.F.P.
SlowARG is offline   Reply With Quote
Old 23/07/2020, 12:22 AM   #7
MarioKamani
Big Clucker
 
Join Date: Jan 2017
Location: Albania
Posts: 90
Reputation: 18
Default Re: SQL 41-4 - Unknown column 'X' in 'field list'

still the no active cache errors idk where to put the functions tbh im confused
MarioKamani is offline   Reply With Quote
Old 23/07/2020, 12:26 AM   #8
Markski
Big Clucker
 
Markski's Avatar
 
Join Date: Apr 2015
Location: La Plata, Argentina
Posts: 133
Reputation: 59
Default Re: SQL 41-4 - Unknown column 'X' in 'field list'

"cache_get_value_name_int" and any other function that requires accesing a SQL cache will only inside the callback function specified for that query.

My suggestion is that you store that data in a variable you can read later instead of trying to access the mysql cache for it every time
Markski is offline   Reply With Quote
Old 23/07/2020, 12:50 AM   #9
MarioKamani
Big Clucker
 
Join Date: Jan 2017
Location: Albania
Posts: 90
Reputation: 18
Default Re: SQL 41-4 - Unknown column 'X' in 'field list'

No errors in the .log file but the name does not save.

HTML Code:
if(listitem == 0)
				{					
					new facid;
					new query[400];
					mysql_format(g_SQL, query, sizeof(query),"INSERT INTO `factions` (`id`, `name`, `type`, `Rank1`, `Rank2`) VALUES ('%d', '%s', '%s', '%s', '%s')", facid, "NoName", "1" , "Rank1", "Rank2");
					new Cache:result = mysql_query(g_SQL, query);

				    if(cache_num_rows())
				    {
				        cache_get_value_int(facid, "id", facid);
				    }
				    cache_delete(result);
					ShowPlayerDialog(playerid, DIALOG_FACTIONNAME, DIALOG_STYLE_INPUT, "Faction Creation", "Enter the faction name below:", "Continue", "Abort");

				}
				if(listitem == 1)
				{
					new facid;
					
					new query[400];
					mysql_format(g_SQL, query, sizeof(query),"INSERT INTO `factions` (`id`, `name`, `type`, `Rank1`, `Rank2`) VALUES ('%d', '%s', '%s', '%s', '%s')", facid, "NoName", "2" , "Rank1", "Rank2");
					new Cache:result = mysql_query(g_SQL, query);

				    if(cache_num_rows())
				    {
				        cache_get_value_int(facid, "id", facid);
				    }
				    cache_delete(result);
				    ShowPlayerDialog(playerid, DIALOG_FACTIONNAME, DIALOG_STYLE_INPUT, "Faction Creation", "Enter the faction name below:", "Continue", "Abort");
				}
				if(listitem == 2)
				{
					new facid;
					
					new query[400];
					mysql_format(g_SQL, query, sizeof(query),"INSERT INTO `factions` (`id`, `name`, `type`, `Rank1`, `Rank2`) VALUES ('%d', '%s', '%s', '%s', '%s')", facid, "NoName", "3" , "Rank1", "Rank2");
					new Cache:result = mysql_query(g_SQL, query);

				    if(cache_num_rows())
				    {
				        cache_get_value_int(facid, "id", facid);
				    }
				    cache_delete(result);
				    ShowPlayerDialog(playerid, DIALOG_FACTIONNAME, DIALOG_STYLE_INPUT, "Faction Creation", "Enter the faction name below:", "Continue", "Abort");
				}
			}

		}	
		case DIALOG_FACTIONNAME:
		{
			new facid;
			new query[400];
			mysql_format(g_SQL, query, sizeof query, "UPDATE `factions` SET `name` = '%s' WHERE `id` = %d", inputtext, facid);
			new Cache:result = mysql_query(g_SQL, query);

		    if(cache_num_rows())
		    {
		        cache_get_value_int(facid, "id", facid);
		    }
		    cache_delete(result);

		}

		default: return 0; 
	}
	return 1;
}
MarioKamani is offline   Reply With Quote
Old 23/07/2020, 01:25 AM   #10
Kursed
Huge Clucker
 
Kursed's Avatar
 
Join Date: Jan 2015
Posts: 278
Reputation: 35
Default Re: SQL 41-4 - Unknown column 'X' in 'field list'

make id column as auto_increment
Make the id column as auto increment
Code:
new factionID[MAX_PLAYERS];

        
         //dialog create
        if(listitem == 0)
        {                   
         
            new query[400];
            mysql_format(g_SQL, query, sizeof(query),"INSERT INTO `factions` (`name`, `type`, `Rank1`, `Rank2`) VALUES ('NoName', '1', 'Rank1', 'Rank2')");
            new Cache:result = mysql_query(g_SQL, query);

            factionID[playerid] = cache_insert_id();
            
            cache_delete(result);
            ShowPlayerDialog(playerid, DIALOG_FACTIONNAME, DIALOG_STYLE_INPUT, "Faction Creation", "Enter the faction name below:", "Continue", "Abort");

        }
        if(listitem == 1)
        {
            
            new query[400];
            mysql_format(g_SQL, query, sizeof(query),"INSERT INTO `factions` (`name`, `type`, `Rank1`, `Rank2`) VALUES ('NoName', '2' , 'Rank1', 'Rank2')");
            new Cache:result = mysql_query(g_SQL, query);

  
            factionID[playerid] = cache_insert_id();
            
            cache_delete(result);
            ShowPlayerDialog(playerid, DIALOG_FACTIONNAME, DIALOG_STYLE_INPUT, "Faction Creation", "Enter the faction name below:", "Continue", "Abort");
        }
        if(listitem == 2)
        {
          
            
            new query[400];
            mysql_format(g_SQL, query, sizeof(query),"INSERT INTO `factions` (`name`, `type`, `Rank1`, `Rank2`) VALUES ('NoName', '3' , 'Rank1', 'Rank2')");
            new Cache:result = mysql_query(g_SQL, query);

            factionID[playerid] = cache_insert_id();

            cache_delete(result);
            ShowPlayerDialog(playerid, DIALOG_FACTIONNAME, DIALOG_STYLE_INPUT, "Faction Creation", "Enter the faction name below:", "Continue", "Abort");
        }


        // dialog name
        case DIALOG_FACTIONNAME:
        {
            new query[250];
            mysql_format(g_SQL, query, sizeof query, "UPDATE `factions` SET `name` = '%e' WHERE `id` = %d", inputtext, factionID[playerid]);
            mysql_query(g_SQL, query);
        }
__________________
Kursed is offline   Reply With Quote
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


Similar Threads
Thread Thread Starter Forum Replies Last Post
(error #1054) Unknown column 'None' in 'field list' Jumberi Scripting Help 11 25/04/2020 09:14 PM
Unknown column 'ID' in 'field list' Libbyphay Server Support 2 26/11/2017 07:03 PM
Unknown column in field list penaut Scripting Help 0 05/08/2016 08:51 PM
** [MySQL]: Unknown column 'Gold' in 'field list' lulo356 Scripting Help 6 07/02/2015 06:34 PM
(error #1054) Unknown column '0' in 'field list' Jack_Leslie Scripting Help 8 16/09/2014 04:57 AM


All times are GMT. The time now is 09:11 PM.


Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.