-1

Let's say I have a table with a specific ID (quiz id) and another data cell called diff which is the time in seconds it took to complete that quiz and put it in the DB.

I have this:

$doCommonQueryDiff = $mysqli->query("SELECT AVG(diff) FROM submissions WHERE quiz_id=$id")->fetch_array() or die($mysqli->error);

Later, when I do this to get the most common (or average?) of the data set, you will get the average of the all of the quizes it took in seconds.

I echo it using: $doCommonQueryDiff['diff'] but I get: Notice: Undefined index: diff even though diff is in the database as a column.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Zeng Cheng
  • 755
  • 2
  • 7
  • 16
  • `The AVG() function returns the average value of a numeric column.`. Is diff a numeric column? – Roy M J Nov 24 '14 at 05:58
  • try `$doCommonQueryDiff['AVG(diff)']` or change your query as `SELECT AVG(diff) AS diff ...` – Tejesh Nov 24 '14 at 05:58
  • @RoyMJ Yes, it is a numerical data. In seconds. – Zeng Cheng Nov 24 '14 at 06:13
  • **Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) – Dharman Feb 22 '20 at 12:20
  • It is a very bad idea to use `die($mysqli->error);` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) – Dharman Feb 22 '20 at 12:20
  • Does this answer your question? [SELECT COUNT(\*) AS count - How to use this count](https://stackoverflow.com/questions/28561154/select-count-as-count-how-to-use-this-count) – Dharman Jan 19 '21 at 22:24

1 Answers1

1

In Mysql when you use AVG(diff) column name will become AVG(diff) . then rename column name with diff

Try like this 

$doCommonQueryDiff = $mysqli->query("SELECT AVG(diff) as diff FROM submissions WHERE quiz_id=$id")->fetch_array() or die($mysqli->error);
Anik Islam Abhi
  • 25,137
  • 8
  • 58
  • 80