SQLite COUNT() - Printable Version

SQLite COUNT() - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+--- Thread: SQLite COUNT() (/showthread.php?tid=449200)

SQLite COUNT() - Danijel. - 07.07.2013

My server crashes when this code executes.

Код:

stock CreatePlayerHouse(playerid, sellprice, HouseLvl)
{
		new Float:X, Float:Y, Float:Z; GetPlayerPos(playerid, X, Y, Z);
		new DBResult: ID, Query[256];

       	format(Query, sizeof(Query), "SELECT COUNT * FROM `HOUSES`");
 		ID = 1 + db_query(Database, Query);
 		new string[128];
		format(string, sizeof (string), "ID = %d", ID);
		printf(string);
      	
      	format(Query, sizeof(Query), "INSERT INTO `HOUSES` ('NAME') VALUES('Grad')");
 		db_query(Database, Query);

		format(Query, sizeof(Query), "UPDATE `HOUSES` SET FOR_SELL = '1', SELL_PRICE = '%d', RENTPRICE = '1000', HOUSE_LEVEL = '%d', X = '%f', Y = '%f', Z = '%f' WHERE `ID` = '%d' ", sellprice, HouseLvl, X, Y, Z, ID);
 		db_query(Database, Query);
      	
      	LoadPlayerHouse(ID);
       	SendClientMessage(playerid, COLOR_WHITE, ".:: [HOUSE]: House has been Successfully created.");
		return true;
}

Код:

SA-MP Dedicated Server
----------------------
v0.3x, ©2005-2013 SA-MP Team

[21:34:52] filterscripts = ""  (string)
[21:34:52] 
[21:34:52] Server Plugins
[21:34:52] --------------
[21:34:52]  Loading plugin: streamer
[21:34:52] 

*** Streamer Plugin v2.6.1 by Incognito loaded ***

[21:34:52]   Loaded.
[21:34:52]  Loading plugin: sscanf
[21:34:52] 

[21:34:52]  ===============================

[21:34:52]       sscanf plugin loaded.     

[21:34:52]          Version:  2.8.1        

[21:34:52]    © 2012 Alex "******" Cole  

[21:34:52]  ===============================

[21:34:52]   Loaded.
[21:34:52]  Loaded 2 plugins.

[21:34:52] 
[21:34:52] Filterscripts
[21:34:52] ---------------
[21:34:52]   Loaded 0 filterscripts.

[21:34:52] 
----------------------------------
[21:34:52] -----Balkan Life Roleplay v0.2.2-----
[21:34:52] ----------------------------------

[21:34:52] Number of vehicle models: 49
[21:35:11] Incoming connection: 192.168.1.2:54665
[21:35:11] [join] Dado_Marcana has joined the server (0:192.168.1.2)
[21:35:31] RCON (In-Game): Player #0 (Dado_Marcana) has logged in.
[21:35:34] ID = 1

Re: SQLite COUNT() - Jefff - 07.07.2013

pawn Код:

new DBResult:result, Query[256];

    format(Query, sizeof(Query), "SELECT COUNT(*) FROM `HOUSES`");
    result = db_query(Database, Query);
    new ID = db_num_rows(result) + 1;
    db_free_result(result);

Re: SQLite COUNT() - ReVo_ - 07.07.2013

What is this

Код:

 		ID = 1 + db_query(Database, Query);

You should run the query, fetch results, do +1.

You should see this:

https://sampwiki.blast.hk/wiki/Db_query

Re: SQLite COUNT() - Danijel. - 07.07.2013

Ok,i kind of sorted it out but now:

Код:

stock CreatePlayerHouse(playerid, sellprice, HouseLvl)
{
		new Float:X, Float:Y, Float:Z; GetPlayerPos(playerid, X, Y, Z);
		new ID, Query[256];

       	format(Query, sizeof(Query), "SELECT COUNT * FROM `HOUSES`");
 		ID = 1 + db_query(Database, Query);
 		new string[128];
		format(string, sizeof (string), "ID = %i", ID);
		printf(string);
      	
      	format(Query, sizeof(Query), "INSERT INTO `HOUSES` ('ID') VALUES('%d')", ID);
 		db_query(Database, Query);

		------> format(Query, sizeof(Query), "UPDATE `HOUSES` SET NAME = 'Grad', FOR_SELL = '1', SELL_PRICE = '%d', RENTPRICE = '1000', HOUSE_LEVEL = '%d', X = '%f', Y = '%f', Z = '%f' WHERE `ID` = '%d' ", sellprice, HouseLvl, X, Y, Z, ID);
 		db_query(Database, Query);
      	
      	
       	SendClientMessage(playerid, COLOR_WHITE, ".:: [HOUSE]: House has been Successfully created.");
       	//LoadPlayerHouse(ID);
		return true;
}

The problem is in the marked line,the ID code from the line above saves fine but the code under does not save the values.

Re: SQLite COUNT() - Konstantinos - 07.07.2013

Few tips for you my friend.

1) Selecting everything can be done without count too, just '*'.
2) Like Jefff showed you, your way to retrieve the next ID is incorrect.
3) printf is used to insert parameters and format the text which is going to be printed. No need to use format before.
4) Whever you use "SELECT" statement is SQL, free the result. Trust me, you don't want to see what will happen!
5) You can use '' to strings; however, it's not necessary to do that on integers and floats. If you have 20 variables (integers) to insert * 2 characters (''), it's waste of the Query.
6) Make sure the string is big enough to be executed, else it will not.

Re: SQLite COUNT() - Danijel. - 07.07.2013

Jeff-s code is not working for me,all ID-s are 2 and the other stuff from the query below is still not working.

Код:

stock CreatePlayerHouse(playerid, sellprice, HouseLvl)
{
		new Float:X, Float:Y, Float:Z; GetPlayerPos(playerid, X, Y, Z);
		new ID, Query[256], DBResult: result;

       	format(Query, sizeof(Query), "SELECT COUNT(*) FROM `HOUSES`");
 		result = db_query(Database, Query);
    	ID = db_num_rows(result) + 1;
    	db_free_result(result);
      	
      	format(Query, sizeof(Query), "INSERT INTO `HOUSES` ('ID') VALUES('%d')", ID);
 		db_query(Database, Query);

		format(Query, sizeof(Query), "UPDATE `HOUSES` SET NAME = 'Grad', FOR_SELL = '1', SELL_PRICE = '%d', RENTPRICE = '1000', HOUSE_LEVEL = '%d', X = '%f', Y = '%f', Z = '%f' WHERE `ID` = '%d' ", sellprice, HouseLvl, X, Y, Z, ID);
 		db_query(Database, Query);
      	
      	
       	SendClientMessage(playerid, COLOR_WHITE, ".:: [HOUSE]: House has been Successfully created.");
       	//LoadPlayerHouse(ID);
		return true;
}

Re: SQLite COUNT() - Konstantinos - 07.07.2013

Well, it should work like it does to everyone else! It stores the number of rows from everything to the table houses and adds +1, so that would be basically the next ID.

I'd use that like that and it'd work fine.

pawn Код:

stock CreatePlayerHouse(playerid, sellprice, HouseLvl)
{
    new Float:X, Float:Y, Float:Z; GetPlayerPos(playerid, X, Y, Z);
    new ID, Query[256], DBResult: result;

    format(Query, sizeof(Query), "SELECT * FROM HOUSES");
    result = db_query(Database, Query);
    ID = db_num_rows(result) + 1;
    db_free_result(result);

    format(Query, sizeof(Query), "INSERT INTO HOUSES (ID) VALUES(%d)", ID);
    db_query(Database, Query);

    format(Query, sizeof(Query), "UPDATE HOUSES SET NAME = 'Grad', FOR_SELL = 1, SELL_PRICE = %d, RENTPRICE = 1000, HOUSE_LEVEL = %d, X = %f, Y = %f, Z = %f WHERE ID = %d", sellprice, HouseLvl, X, Y, Z, ID);
    db_query(Database, Query);


    SendClientMessage(playerid, COLOR_WHITE, ".:: [HOUSE]: House has been Successfully created.");
    //LoadPlayerHouse(ID);
    return true;
}

Re: SQLite COUNT() - Danijel. - 07.07.2013

Ok,ID-s are working now,thanks for that but the query update stuff is not working and i have no clue why,i tried increasing query size to 512 but it does not help.

Код:

format(Query, sizeof(Query), "UPDATE HOUSES SET NAME = 'Grad', FOR_SELL = 1, SELL_PRICE = %d, RENTPRICE = 1000, HOUSE_LEVEL = %d, X = %f, Y = %f, Z = %f, VIRTUALWORLD = &d, STATUS = 0 WHERE ID = %d", sellprice, HouseLvl, X, Y, Z, ID, ID);
    db_query(Database, Query);

Re: SQLite COUNT() - Konstantinos - 07.07.2013

Change to:

pawn Код:

format(Query, sizeof(Query), "UPDATE HOUSES SET NAME = 'Grad', FOR_SELL = 1, SELL_PRICE = %d, RENTPRICE = 1000, HOUSE_LEVEL = %d, X = %f, Y = %f, Z = %f, VIRTUALWORLD = &d, STATUS = 0 WHERE ID = %d", sellprice, HouseLvl, X, Y, Z, ID, ID);
db_query(Database, Query);
print(Query); // DEBUG!

Also make sure the names of the fields are correct. If it's "SELLPRICE" instead of "SELL_PRICE" (en example) will fail to update.

Re: SQLite COUNT() - Danijel. - 07.07.2013

Код:

CREATE TABLE houses (ID NUMERIC, NAME , FOR_SELL , SELL_PRICE , RENT_PRICE , HOUSE_LEVEL , X , Y , Z , VIRTUALWORLD , STATUS );

This is the database.

EDIT: i just noticed the rentprice typing error,gonna check now if it works.

EDIT2: just saw ur post,debuging...