PHP SQL get SELECT COUNT(user) to variable

Question

How do I get the outcome of

SELECT COUNT(user) FROM game_paths WHERE user = '$user'

to a PHP variable

I tried

mysql_num_rows

but it returns nothing.

Answer 1

You should use mysql_result and get the first column of the result. Like this:

$result = mysql_query("SELECT COUNT(user) FROM game_paths WHERE user='$user'");
$count = mysql_result($result, 0);

You can also alias the column like this:

$result = mysql_query("SELECT COUNT(user) AS total FROM game_paths WHERE user='$user'");
$data = mysql_fetch_assoc($result);
$count = $data['total'];

Which might be better if you're going to select several columns at the same time, and also for readability.

Answer 2

Try like this. you need to use mysql_fetch_assoc or mysql_fetch_array functions

  $result = mysql_query(" SELECT COUNT(user) as total FROM game_paths WHERE user='$user' ");
  $row = mysql_fetch_assoc($result);  
  echo $row['total'];

Or

  $result = mysql_query(" SELECT COUNT(user) FROM game_paths WHERE user='$user' ");
  $row = mysql_fetch_array($result);  
  echo $row[0];

Docs Link: https://www.php.net/mysql_fetch_array http://www.w3schools.com/php/func_mysql_fetch_array.asp

Note: mysql_* function are deprecated try to use mysqli or PDO

Answer 3

You can use the following code:

  $result = mysql_query(" SELECT COUNT(user) FROM game_paths WHERE user='$user' ");
  $row = mysql_fetch_array($result);  
  $count= $row[0];

or

$result = mysql_query("SELECT * FROM game_paths WHERE user='$user'");
$count=mysql_num_rows($result);

This will return the number of rows satisying the condition.

Answer 4

Hey friend Try this code, ithink this will solve your problem

<?php
 $con=mysql_connect('hostname','DBusername','paassword');
  mysql_select_db('Db_name',$conn);
  $query="SELECT COUNT(user) as total FROM game_paths WHERE user='$user'"; 
   $result=mysql_query($query,$con);
   $row=mysql_fetch_array($result);
   echo $row['total'];
  ?>

Answer 5

$result = mysqli_query("SELECT COUNT(user) AS user_count FROM game_paths WHERE user='$user'");
$result_array = mysql_fetch_assoc($result);
$user_count=$result_array['user_count'];

Please use mysqli_ instead of mysql_ as its deprecated in the new version