SA-MP Forums

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

Reply
 
Thread Tools Display Modes
Old 23/11/2017, 09:43 PM   #1
Quis
Little Clucker
 
Join Date: May 2012
Location: Poland
Posts: 13
Reputation: 0
Default SQLite db_num_rows VS count

Hello!

What is better to use, this:
Code:
new DBResult:result = db_query(db, "SELECT COUNT(0) FROM players");
new count = db_get_field_int(result);
db_free_result(result);
or this:
Code:
new DBResult:result = db_query(db, "SELECT NULL FROM players");
new count = db_num_rows(result);
db_free_result(result);
to get count of results?


And another question: what, if I only want to know if a record just exists?

Thanks for your reply
Quis is offline   Reply With Quote
Old 24/11/2017, 12:02 AM   #2
BlackBank
High-roller
 
BlackBank's Avatar
 
Join Date: Dec 2010
Location: The Netherlands
Posts: 1,164
Reputation: 433
Default Re: SQLite db_num_rows VS count

The first one is faster then the second one, because the first on just returns one row with the total players in the database. The second one returns more rows with data (in your query nothing, since you select NULL).
BlackBank is offline   Reply With Quote
Old 24/11/2017, 12:11 AM   #3
L97
Little Clucker
 
Join Date: Nov 2017
Posts: 10
Reputation: 0
Default Re: SQLite db_num_rows VS count

Using SELECT COUNT(*) will count itself the number of rows that the table "players" contains and return it:
Code:
new DBResult:result = db_query(db, "SELECT COUNT(*) FROM players");
new count = db_get_field_int(result); //I think you don't really need this since SELECT COUNT(*) already returns an int
db_free_result(result);
Also replace SELECT NULL with SELECT *
Code:
new DBResult:result = db_query(db, "SELECT * FROM players"); //this gets every record of the table players
new count = db_num_rows(result); //counts the number of rows as int
db_free_result(result);
Between this two I'd personally use SELECT COUNT(*), instead of selecting everything from the table and then counting each row.
Quote:
Originally Posted by Quis View Post
And another question: what, if I only want to know if a record just exists?
Yes, you can check if a record exists without actually displaying it. For example, if you want to know if a player called John_Cena exists in the database:
Code:
SELECT 1 FROM players WHERE name = 'John_Cena'
This will return 1 if the record exists.
L97 is offline   Reply With Quote
Old 24/11/2017, 04:10 PM   #4
Quis
Little Clucker
 
Join Date: May 2012
Location: Poland
Posts: 13
Reputation: 0
Default Re: SQLite db_num_rows VS count

Thanks for your answers

COUNT uses field name as parameter, so if you COUNT(*) you counting by all fields - I think it's a bad habit
COUNT(NULL), or COUNT(0) return just empty rows
Quis is offline   Reply With Quote
Old 24/11/2017, 06:55 PM   #5
BlackBank
High-roller
 
BlackBank's Avatar
 
Join Date: Dec 2010
Location: The Netherlands
Posts: 1,164
Reputation: 433
Default Re: SQLite db_num_rows VS count

Quote:
Originally Posted by Quis View Post
Thanks for your answers

COUNT uses field name as parameter, so if you COUNT(*) you counting by all fields - I think it's a bad habit
COUNT(NULL), or COUNT(0) return just empty rows
It doesn't matter which on you use, but i prefer the COUNT(*) one, since NULL looks weird in SELECT.
BlackBank is offline   Reply With Quote
Old 24/11/2017, 11:59 PM   #6
L97
Little Clucker
 
Join Date: Nov 2017
Posts: 10
Reputation: 0
Default Re: SQLite db_num_rows VS count

Quote:
Originally Posted by Quis View Post
Thanks for your answers

COUNT uses field name as parameter, so if you COUNT(*) you counting by all fields - I think it's a bad habit
COUNT(NULL), or COUNT(0) return just empty rows
No, not really..
COUNT(0) will return the same number as COUNT(*) or COUNT(1) or COUNT(100) or even COUNT(10000000)
They all do the same thing: COUNT(0) will be counting rows assigning 0 to each one of them, COUNT(1) will do the same assigning 1 and COUNT(*) will just count them as they are.
So they are pretty much the same, same stuff, same performance. Also COUNT(*) is not a bad habit, it's proper SQL syntax.
NB: there's a huge difference between NULL and 0 in SQL..
L97 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
[SQLite] Deleted rows count Jefff Scripting Help 4 27/10/2017 02:24 AM
sqlite count() GoldenLion Scripting Help 2 15/04/2017 09:02 PM
Registered User Count [SQLite] SsHady Scripting Help 11 03/11/2013 08:30 PM
SQLite COUNT() Danijel. Scripting Help 13 07/07/2013 10:10 PM
db_num_rows Danijel. Scripting Help 7 24/06/2012 10:09 AM


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


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