Is it possible to get a sql count result assign to a php variable?

Question

So I have looked online about finding a way to convert a MySQL query count result into an integer for me to assign as a php variable, I had found several different links, and have read multiple documentation. None of them seem to cover exactly what I want. For example:

//code for connecting to database
$query = "SELECT COUNT(*) FROM `user_table` WHERE `username` = 'John12'";
$result = mysql_query($query);
if(!$result) die($db->error);

Now the code above after running the query should return 1, in the case that the username field is unique and there will only be one instance of that query result. Another example:

//code for connecting to database
$query = "SELECT COUNT(*) FROM `user_table` WHERE `username` = 'John12' AND `username` = 'Paula25'";
$result = mysql_query($query);
if(!$result) die($db->error);

Now the code above after running the query should return 2. What I been having trouble with is assigning the result to a php variable. It always says something about unable to convert mysql_result into int. I have seen plenty of forums on this as well, and none have successfully helped me. I have found online that the MariaDB return a BIGINT type for the count query. I would like to be able to do something like this:

$count = $result->result;
if($count < 2) { 
  // do this if true
} else {
  // do this
}

... WHERE `username` = 'John12' AND `username` = 'Paula25' retuns 2, that would be a neat magic trick ? — , Apr 10 '18 at 02:18
@smith For instance rather than using 'AND' use 'Or'. Instead of mysql_* , use mysqli — Chuck, Apr 10 '18 at 04:12

Jun Rikson · Accepted Answer · 2018-04-10T02:22:17.430

You can use mysql_fetch_row :

//code for connecting to database
$query = "SELECT COUNT(*) FROM `user_table` WHERE `username` = 'John12' OR `username` = 'Paula25'";
$result = mysql_query($query);
if(!$result) die($db->error);    

$row = mysql_fetch_row($result);

$count = $row[0];

if($count < 2) { 
  // do this if true
} else {
  // do this
}

As @mith mentioned in comment, if you just starting your project, better using mysqli than mysql. Read this : MySQL vs MySQLi when using PHP

score -1 · Answer 2 · answered Apr 10 '18 at 02:21

you have to get the returned value with $result[count()] because you are selecting column name as count().

or you can use alias like

$query = "SELECT COUNT(*) AS count ...

then

$result = mysql_query($query); $result['count'] = your value // 2

if you don't want to use alias

$result['COUNT(*)'] = your value // 2

so you have to do something like this in you case

if($result['COUNT(*)'] < 2) { 
  // do this if true
} else {
  // do this
}

or

$count = $result['COUNT(*)'];

if($count < 2) { 
  // do this if true
} else {
  // do this
}

Is it possible to get a sql count result assign to a php variable?

2 Answers2