how to assign a value of a query to a variable in zend

Question

I need to calculate the average from a mysql table. So I am taking the sum of a col and dividing it by the number of rows. But the values are not being assigned properly :-

$total_sum_query = $db->query("SELECT SUM(marks) FROM markstable WHERE sid = '$sid'");
$num_rows_query = $db->query("SELECT * FROM markstable WHERE sid = '$sid'");

$avg_marks = $total_sum_query/$num_rows_query;

I know i am doing something wrong but can not figure it out. Is there a way to derive the average in mysql?

Try the [MySQL `AVG()` command](http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_avg) — Jon, Mar 15 '13 at 07:26

score 1 · Answer 1 · answered Mar 15 '13 at 09:26

1

What you actually want is:

$avg_marks = $db->fetchOne("SELECT AVG(marks) FROM markstable WHERE sid = ?", $sid);

fetchOne gives you a single value instead of a result set or array, and use parameterised queries to protect against SQL injection.

answered Mar 15 '13 at 09:26

Tim Fountain

33,093
5
41
69

score 0 · Answer 2 · answered Mar 15 '13 at 07:27

0

Try like

$num_rows_query = $db->query("SELECT count(*) as cnt FROM markstable WHERE sid = '$sid'");

and use it as $num_rows_query['cnt'];

or you can directly try like

$avg_query =  $db->query("SELECT AVG(marks) FROM markstable WHERE sid = '$sid'");

answered Mar 15 '13 at 07:27

GautamD31

28,552
10
64
85

score 0 · Answer 3 · answered Mar 15 '13 at 07:28

0

Try to use AVG in MYSL

$average_query =  $db->query("SELECT AVG(marks) FROM markstable WHERE sid = '$sid'");

answered Mar 15 '13 at 07:28

Vimalnath

6,373
2
26
47

how to assign a value of a query to a variable in zend

3 Answers3