Undefined index error when using Count()

[nashsaint]

Hi,

 

I have a simple query to count names of their occurence and then sort by count.

 

Here's the query.

 

SELECT acctm_name, COUNT( acctm_name ) AS totalCount FROM job_enquiry GROUP BY acctm_name ORDER BY totalCount DESC

 

The query works perfectly if i run it inside phpmyAdmin but produced this error when running the actual php page:

Notice: Undefined index: COUNT( acctm_name ) in C:\wamp\www\enquiries\report.php on line 80

 

Thanks in advance.

[Mchl]

This is not SQL error, but PHP notice. Post the relevant PHP code.

[nashsaint]

This is the query:

mysql_select_db($database_enquiry_connect, $enquiry_connect);

$query_countAcctm = "SELECT acctm_name, COUNT( acctm_name ) FROM job_enquiry GROUP BY acctm_name";

$query_limit_countAcctm = sprintf("%s LIMIT %d, %d", $query_countAcctm, $startRow_countAcctm, $maxRows_countAcctm);

$countAcctm = mysql_query($query_limit_countAcctm, $enquiry_connect) or die(mysql_error());

$row_countAcctm = mysql_fetch_assoc($countAcctm);

 

if (isset($_GET['totalRows_countAcctm'])) {

  $totalRows_countAcctm = $_GET['totalRows_countAcctm'];

} else {

  $all_countAcctm = mysql_query($query_countAcctm);

  $totalRows_countAcctm = mysql_num_rows($all_countAcctm);

}

$totalPages_countAcctm = ceil($totalRows_countAcctm/$maxRows_countAcctm)-1;

?>

 

This is the form that populates the query:

  <form id="form1" name="form1" method="post" action="">

    <table border="0" cellpadding="3" cellspacing="1" id="archiveTblSmall">

      <tr>

        <th>Names</th>

        <th>Totals</th>

      </tr>

      <?php do { ?>

        <tr>

          <td><?php echo $row_countAcctm['acctm_name']; ?></td>

          <td><?php echo $row_countAcctm['COUNT( acctm_name )']; ?></td>

        </tr>

        <?php } while ($row_countAcctm = mysql_fetch_assoc($countAcctm)); ?>

    </table>

  </form>

[Mchl]

Change your query to

SELECT acctm_name, COUNT( acctm_name ) AS count_acctm_name FROM job_enquiry GROUP BY acctm_name

 

And this piece of code:

<td><?php echo $row_countAcctm['COUNT( acctm_name )']; ?></td>

 

to

 

<td><?php echo $row_countAcctm['count_acctm_name']; ?></td>

[nashsaint]

That works well.  Have one more problem though, how can i sort the result by count?

 

I tried this code but didn't work, produced same problem.

SELECT acctm_name, COUNT( acctm_name ) AS count_acctm_name FROM job_enquiry GROUP BY acctm_name SORT BY count_acctm_name ASC

[Mchl]

It's ORDER BY not SORT BY

[nashsaint]

Thanks a lot Mchl... works great now.