Member Avatar for whiteyoh

Hi All,

Im accessing a function with paticular parameters, but my response is not recognising the parameter of the count value.

If I change the variable in the COUNT part of the query to a static value it works fine, but if i use the variable name it returns "undefined index".

Any help would be greatly appreciated

<?php
class answers {


    function totals($questionName, $startdate, $enddate, $advisor){
      $SQL = "SELECT COUNT($questionName) FROM interview_answers i, accounts a WHERE a.client_id = i.client_id AND advisor = '$advisor' AND sesdate>= '$startdate' AND sesdate<='$enddate'";
      $result = mysql_query($SQL);
      $row = mysql_fetch_assoc($result);
      echo $totalresponses = $row['COUNT($questionName)'];

    }
}


?>
  • 2 Contributors
  • 2 Replies
  • 115 Views
  • 16 Hours Discussion Span
  • Latest Post Latest Post by whiteyoh

Dani AI

Generated

Short answer: the root cause is the way MySQL exposes the aggregate column name to PHP. As suggested, give the COUNT a simple alias so the associative-array key is predictable. That fixes the immediate "undefined index" symptom and makes the result easier to read.

A few practical improvements beyond the alias:

  • Never interpolate unvalidated identifiers. If $questionName is a column name, validate it against a whitelist of allowed column names before inserting it into SQL. Column names cannot be parameterized, so validation is required.
  • Decide whether you need COUNT(column) (counts non-NULL values) or COUNT(*) (counts rows). They behave differently.
  • Use prepared statements for user data ($advisor, $startdate, $enddate) and stop using the old mysql_* extension — migrate to PDO or mysqli for parameter binding and modern error handling.
  • Wrap validated column names in backticks to avoid syntax issues with unusual names.

Example pattern (modern, secure approach):

$allowed = ['q1','q2','q3']; // whitelist
if (!in_array($questionName, $allowed, true)) {
    throw new InvalidArgumentException('Invalid column');
}
$col = "`$questionName`";
$sql = "SELECT COUNT($col) AS cnt FROM interview_answers i JOIN accounts a ON a.client_id = i.client_id WHERE advisor = :advisor AND sesdate >= :start AND sesdate <= :end";
$stmt = $pdo->prepare($sql);
$stmt->execute(['advisor'=>$advisor,'start'=>$startdate,'end'=>$enddate]);
$total = (int) $stmt->fetchColumn();

Quick troubleshooting: if you still see "undefined index", inspect the raw result (var_dump/print_r), check for SQL errors, and run the query in the DB client to confirm the column label returned. This covers the immediate fix and prevents SQL-injection and logic pitfalls long-term.

Recommended Answers

All 2 Replies

Member Avatar for wilch

That's a simple one. Just add the clause " AS questioncount " immediately after the COUNT keyword.
ie. SELECT COUNT($questionName) AS questioncount FROM interview_answers ... Then in line 9 use the code: echo $totalresponses = $row['questioncount'];

Edited by wilch because: n/a
Member Avatar for whiteyoh

perfect, thank you very much. i should have known that

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.