mysql_query(SELECT COUNT(*) Syntax for MyBB?
#1
I am trying to use the MySQL Select Count command, but I don't know how to execute a normal mysql_query because MyBB uses the $db-> object to run MySQL queries.

Does anyone know what the MyBB syntax for the MySQL query below is?


mysql_query(SELECT COUNT(*) FROM `mybb_users` WHERE uid = '1')
Reply
#2
$query = $db->query('SELECT COUNT(*) FROM ' . TABLE_PREFIX . 'users WHERE id = 1');

or

$query = $db->simple_select('users', 'COUNT(*)', 'id = 1');
Reply
#3
When I execute this on PHPMyAdmin, I get a value of 4.

When I execute it on MyBB localhost, I get a value of 1.

Why are they different?

What would be the correct PHP syntax to get the same result as PHPMyAdmin?
Reply
#4
PHPMyAdmin returns a field that has the value. Localhost I believe returns the number of rows, which is always one when you use the COUNT function. You could do this on localhost though:
$query = $db->query("SELECT COUNT(uid) as count FROM " . TABLE_PREFIX . "users WHERE uid=1");
$count = $db->fetch_field($query, "count");

Explanation of code:
When using a SELECT COUNT query, it is faster to use the primary key of the table, in this case uid, than to use * because its less data it has to remember. In this example I use the keyword "as" to create an alias. This is what the name of the field will be when I retrieve the data. $db->fetch_field has two parameters: The first is the resource query, the second is the name of the field you wish to retrieve. In this case the name of the field is "count" because I aliased it to that.
Reply
#5
Stupid question: Shouldn't the number always 1? There's always only one user with uid 1, otherwise you'd have a mysql error (primary key with same value).

Btw: there's also a function called "num_rows":
$query = $db->simple_select("users", "uid", "uid=1");
$count = $db->num_rows($query);
Support PMs will be ignored!
Reply
#6
While there is the $db->num_rows, it is going to be slower than using the count function.
Reply
#7
Yep, but sometimes (especially for larger queries with eg joins or queries you'll run later) it's better to use it Wink
Support PMs will be ignored!
Reply
#8
(01-14-2015, 01:50 PM)JonesĀ H Wrote: Stupid question: Shouldn't the number always 1? There's always only one user with uid 1, otherwise you'd have a mysql error (primary key with same value).

Btw: there's also a function called "num_rows":

$query = $db->simple_select("users", "uid", "uid=1");
$count = $db->num_rows($query);

Yes. Otherwise you'd have PK conflicts.
Reply
#9
To all who replied, I didn't clarify myself:

The result from PHPMyAdmin was different than the result I received from the MySQL query with PHP.

It turns out I needed to use "$db->fetch_array($myqueryexample)" to get the same SELECT COUNT(*) result.
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)