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')
$query = $db->query('SELECT COUNT(*) FROM ' . TABLE_PREFIX . 'users WHERE id = 1');
or
$query = $db->simple_select('users', 'COUNT(*)', 'id = 1');
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?
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.
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);
While there is the $db->num_rows, it is going to be slower than using the count function.
Yep, but sometimes (especially for larger queries with eg joins or queries you'll run later) it's better to use it
(2015-01-14, 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.
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.