1

I am wondering if someone might be able to spot where I'm going wrong here? I want to return the number of unique values for a column. When I run this query on Phpmyadmin the result is correct, but when I try to do it through PHP I keep getting the result 1.

//Find out number of unique slotids and assign to variable
    $q2= "SELECT COUNT( DISTINCT(`slotid`) ) FROM `individualavailability`";
    $result2 = mysqli_query ($dbcon, $q2);
    $count = mysqli_num_rows ($result2);
    echo $count. " slot ids";
Ray
  • 51
  • 6
  • 6
    1 is the count of records returned/rows affected. you want the VALUE of the record returned, not the number of records returned. Consider aliasing the column and referencing it by name. [see example](http://stackoverflow.com/questions/6907751/select-count-from-table-of-mysql-in-php) perhaps using `mysql_fetch_assoc($result);` I'll forgo the standard PDO comment – xQbert Aug 18 '15 at 20:27

2 Answers2

2

The mysqli_num_rows function returns the number of rows you read from the server, not the number of slotid counted by your query. Something like this should work better:

$q2= "SELECT COUNT( DISTINCT(`slotid`) ) as cnt FROM `individualavailability`";
$r2 = mysqli_query ($dbcon, $q2);
$row = mysqli_fetch_assoc($r2);
echo $row["cnt"];
Didier Spezia
  • 70,911
  • 12
  • 189
  • 154
  • I'm not much of a PHP programmer.. but `echo $row["count"];` seems wrong to me shoudn't it be tics and doesn't the SQL statement need to alias to `count(distinct('slotid')) as count` for it to work? Perhaps `"`'s work and I'll learn something new... just seemed odd to me. – xQbert Aug 18 '15 at 20:34
  • 1
    I guess printing the whole associative array will give the name of the key. Using an alias is probably cleaner and safer. – Didier Spezia Aug 18 '15 at 20:36
  • You should not use reserved word `count` as alias - at least not without enclosing in backticks. – Mike Brant Aug 18 '15 at 20:38
  • @xQbert: yes, you need the alias, or else the name of the column would be "COUNT( DISTINCT(`solid`) )" since MySQL assigns column aliases to be the expression if no alias is given. Also, the ticks are for MySQL only. PHP uses single or double quotes for strings. – siride Aug 18 '15 at 20:38
  • Thanks for the ideas xQbert and Didier!......I'm still working on it here.....Didier - With the above code I'm getting an undefined index error for "count". – Ray Aug 18 '15 at 20:41
0
$query=("SELECT count(DISTINCT (`slotid`)) as total from `individualavailability`");
$result=mysqli_query ($dbcon, $query);
$row=mysqli_fetch_assoc($result);
echo $row['total'];
Ray
  • 51
  • 6