Problem regarding writing a query for mysql table

[mahendrakalkura]

hi guys

this is my sample code

$class_average_total=0;
for($i=1;$i<9;$i++)
{
$query_subject_average = "select round(avg(sub_\"$i\"),2) from ist_5_sem";
$result_subject_average = mysql_query($query_subject_average,$connectparam);
$row_subject_average = mysql_fetch_row($result_subject_average);
$class_average_total=$class_average_total+$row_subject_average;
}

i know it doesnt work

gives an error

the problem is in the first line

in this this line here

"select round(avg(sub_\"$i\"),2) from ist_5_sem"

i have 9 columns in the table named ist_5_sem

they are sub_1,sub_2 so on till sub_9

i want a total of all the columns

so in the for loop i want to substitute the value of $i with the suffix sub_

how to do it

please tell me

thanks

[Lars_Berg]

$q = "select round(avg(sub_" . $i . "),2) from ist_5_sem" ;

Your database design is somewhat strange.

You should have one subject column and one column which indicates what type of subject you have. You can also have an unlimited number of subjects whereas now you are stuck at nine and if you wish to add extra subjects you need to change the data model and your scripts.

With a proper design you can have the result with one query (regardless of the number of subjects)

$q = "select round(avg(subject),2),typeColumn from ist_5_sem group by typeColumn" ;