How to looop through mysql result using php

Question

0.00/5 (No votes)

See more:

I have a table called vw_results which holds the result profile for a particular student:

idNum      |courseUnit | marks | Gpp | grade| id | semseterID | sessionName

06/021     |   2       | 47    | 8   |  B+  | 1  |   1        |  2010/11
06/021     |   3       | 56    | 7   |  C   | 1  |   1        |  2010/11
06/021     |   4       | 34    | 5   |  C   | 1  |   1        |  2010/11
06/021     |   5       | 34    | 0   |  F   | 1  |   1        |  2010/11
06/021     |   2       | 89    | 10  |  A   | 1  |   2        |  2010/11
06/021     |   3       | 45    | 4   |  D   | 1  |   2        |  2010/11
06/021     |   4       | 56    | 10  |  C   | 1  |   2        |  2010/11
06/021     |   2       | 67    | 12  |  B+  | 2  |   1        |  2011/12
06/021     |   1       | 70    | 15  |  A   | 2  |   1        |  2011/12
06/021     |   2       | 80    | 10  |  A   | 2  |   2        |  2011/12
06/021     |   3       | 90    | 5   |  A   | 2  |   2        |  2011/12

I have a form that uses GET method having two select boxes named sessionID and semesterID, part of the php code is

PHP

 <?php
    ...

#To calculate the GPA

if(isset('chkresult')){

$query="SELECT SUM( vwr.courseUnit ) cummUnit, SUM( vwr.GPP ) cummGPP, (
SUM( vwr.GPP ) / SUM( vwr.courseUnit ))cummGPA
FROM vw_result vwr
WHERE vwr.Grade NOT IN ('F') AND vwr.sessionID=".$_GET['sessionID']." AND vwr.semesterID=".$_GET['semesterID'].";"
$result=mysql_query($query);
$row_query=mysql_fetch_assoc($result);
}
...
?>;

supposing I want to calculate the CGPA cummmulatively such that when $_GET['sessionID']=1 and $_GET['semesterID']=2 it includes the result of GPA computation for sessionID=1 and semesterID=2, such that:
<?php echo $row_query['cummGPA']; ???>
the result will be:

HTML

|cummGPA|
|2.444  |

also if $_GET['sessionID']=2 and $_GET['semesterID']=1
then it includes the result of GPA computation for sessionID=1 and semesterID=1; sessionID=1 and semesterID=2,

HTML

|cummGPA|
|3.381  |

if $_GET['sessionID']=2 and $_GET['semesterID']=2
then

HTML

|cummGPA|
|3.301  |

any suggestions on MySQl and PHP statements to go about this using the vw_result table above or a new idea is highly appreciated.
THANKS!

Posted 2-May-13 16:33pm

Engr Jyd

Updated 3-May-13 2:52am

Kenneth Haugland

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

W∴ Balboos, *GHB* · Answer 1 · 2013-05-03T02:45:00

My personal preference for using the database result sets is to copy all rows into an array (pay attention to scope!). I can then close the connection and do whatever I wish to the array.

roughly, after executing the SQL query:

$rows = NULL;
$i = 0;
while($r = mysql_fetch_array(stmt))
$rows[$i++] = $r;

You should close statement now and connection if done with it.

I use the ...fetch_array() version and can then work with associative or indexed arrays as I choose.

You can do anything you want with the data in the arrays.

Engr Jyd · Answer 2 · 2013-05-15T14:10:00

PHP

		while($row=mysql_fetch_array($rsChkarray,MYSQL_BOTH))
{

#calculate the results for each academic semester
mysql_select_db($database_connChePortal, $connChePortal);
$cal_Cumm= "SELECT SUM(vwr.courseUnit) cummUnit, SUM(vwr.GPP) cummGPP
			FROM vw_result vwr
			WHERE vwr.Grade NOT IN ('F')
			AND vwr.id=".$row[0]."
			AND vwr.semesterID=".$row[1].";";
			
$results=mysql_query($cal_Cumm, $connChePortal) or die(mysql_error());
$row_calCumm=mysql_fetch_assoc($results);


#declare the results into variables
$units= $row_calCumm['cummUnit'];
$gpp=$row_calCumm['cummGPP'];

$semesterGPA=($gpp/$units);

#cummulative results
#$cUnit=0;
$cUnit +=$units;
#$cGpp=0;
$cGpp +=$gpp;
$cGpa= ($cGpp/$cUnit);

#echo "";
#print results
if ($row[1]==$_GET['semesterID']){

echo "<p><label for='cumUnit'>
        Cummulative Unit:</label>".$cUnit."</p>";
echo "<p><label for='cumPrdt'>
        Cummulative GPP:</label>".$cGpp."</p>";
echo "<p><label for='cumGpa'>
        Cummulative GPA:</label>".$cGpa."</p>";

}
#check URL variable for the result request	
if(($row[0]==$_GET['sessionID']) &&($row[1]==$_GET['semesterID']))  break;

}

How to looop through mysql result using php

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0