SA-MP Forums

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

 
 
Thread Tools Display Modes
Old 05/10/2009, 10:47 AM   #1
Dabombber
Big Clucker
 
Join Date: May 2006
Posts: 81
Reputation: 1
Default SQL queries using GROUP BY

I'm not sure if this is a bug or some SQL thing, but using GROUP BY in SQL queries seems to break db_get_field_assoc.

pawn Code:
#include <a_samp>

public OnFilterScriptInit()
{
    new DB:db = db_open("test.sqlite");
    if(db) {
        db_free_result(db_query(db, "CREATE TABLE IF NOT EXISTS `Test` ( `string` TEXT NOT NULL DEFAULT '' , `value` INTEGER NOT NULL DEFAULT 0 ); DELETE FROM `Test`"));

        db_free_result(db_query(db, "INSERT INTO `Test` ( `string` , `value` ) VALUES ( 'abc' , 123 )"));
        db_free_result(db_query(db, "INSERT INTO `Test` ( `string` , `value` ) VALUES ( 'def' , 456 )"));
        db_free_result(db_query(db, "INSERT INTO `Test` ( `string` , `value` ) VALUES ( 'hij' , 789 )"));

        new DBResult:result = db_query(db, "SELECT `string` , `value` FROM `Test`"),
            rows = db_num_rows(result);
        for(new i; i < rows; i++) {
            new string[20],
                value;
            db_get_field_assoc(result, "value", string, sizeof(string));
            value = strval(string);
            db_get_field_assoc(result, "string", string, sizeof(string));
            printf("String: \"%s\", Value: %i", string, value);
            db_next_row(result);
        }
        db_free_result(result);
        db_close(db);
    }
    return 1;
}

Using this gives
Quote:
String: "abc", Value: 123
String: "def", Value: 456
String: "hij", Value: 789
as expected, but when the SELECT query is changed to
pawn Code:
"SELECT `string` , `value` FROM `Test` GROUP BY `string`"

(or grouping by value), it gives
Quote:
String: "", Value: 0
String: "", Value: 0
String: "", Value: 0
It does work properly when using the query
pawn Code:
"SELECT `string` AS `string` , `value` AS `value` FROM `Test` GROUP BY `string`"

but shouldn't that be the same?

Using db_field_name without the AS gives "`string`" and "`value`" instead of "string" and "value", maybe this is the problem.
Dabombber is offline  
Old 05/10/2009, 02:20 PM   #2
Y_Less
Beta Tester
 
Y_Less's Avatar
 
Join Date: Jun 2008
Location: 629 - git.io/Y
Posts: 15,129
Reputation: 3171
Default Re: SQL queries using GROUP BY

Try doing:

Code:
"SELECT string , value FROM `Test` GROUP BY `string`"
Y_Less is online now  
Old 05/10/2009, 07:47 PM   #3
Dabombber
Big Clucker
 
Join Date: May 2006
Posts: 81
Reputation: 1
Default Re: SQL queries using GROUP BY

Not using quotes works but that's not really the point.

Quote:
Originally Posted by http://www.sqlite.org/lang_keywords.html
SQLite adds new keywords from time to time when it takes on new features. So to prevent your code from being broken by future enhancements, you should normally quote any identifier that is an English language word, even if you do not have to.
Code:
#include <a_samp>

public OnFilterScriptInit()
{
	new DB:db = db_open("test.sqlite");
	if(db) {
		db_free_result(db_query(db, "DROP TABLE IF EXISTS `Test`; CREATE TABLE `Test` ( `a` TEXT NOT NULL DEFAULT '', `b` TEXT NOT NULL DEFAULT '' , `c` TEXT NOT NULL DEFAULT '' , `d` TEXT NOT NULL DEFAULT '' , `e` TEXT NOT NULL DEFAULT '' )"));

		db_free_result(db_query(db, "INSERT INTO `Test` ( `a` , `b` ,`c` , `d` ,`e` ) VALUES ( 'abc' , 'def', 'ghi' , 'jkl' , 'mno' )"));

		new DBResult:result = db_query(db, "SELECT a , `b` , [c] , \"d\" , `e` AS `e` FROM Test GROUP BY `a`"),
			rows = db_num_rows(result),
			string[128];
		for(new i; i < rows; i++) {
			new fields = db_num_fields(result);
			format(string, sizeof(string), "%sRow %i\n", string, i);
			for(new j; j < fields; j++) {
				new field_name[20],
					field_value[20];
				db_field_name(result, j, field_name, sizeof(field_name));
				db_get_field(result, j, field_value, sizeof(field_value));
				format(string, sizeof(string), "%s %s: %s\n", string, field_name, field_value);
			}
			db_next_row(result);
		}
		print(string);
		db_free_result(result);
		db_close(db);
	}
	return 1;
}
Quote:
Row 0
a: abc
`b`: def
[c]: ghi
"d": jkl
e: mno
For some reason sa-mp isn't striping the quotes from the field name unless AS is used.
Dabombber is offline  
 

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
Little coding questions - For general minor queries 5 !damo!spiderman Scripting Help 4575 20/09/2018 09:51 AM
car for a group andrewp Help Archive 0 17/03/2010 12:32 AM
Group andrewp Help Archive 2 14/03/2010 06:12 AM
Counting with mysql queries actiwe Help Archive 2 26/02/2010 02:36 PM


All times are GMT. The time now is 02:59 PM.


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