Page 1 of 1

Pulling data from a database according to field value

Posted: Mon Apr 11, 2011 11:44 pm
by shauns2007
I have 3 tables of which 1 is a relations table between the other 2. What I'm trying to do is make a table that shows courses using table A (this works) and the students registered for that specific course from table B. My problem is that not all my students are showing (it seems to only show 1 student per course) but the few that do show are in the correct places.

Code: Select all

function list_students_per_course() {
			 		global $connection;
			 		$qry = "SELECT cid FROM course_student";
			 		$res = mysql_query($qry);
			 		$ary = mysql_fetch_array($res);
					$cid = $ary['cid'];
			 		$query = "SELECT sno FROM course_student WHERE cid=".$_GET['cid'];
			 		$result = mysql_query($query);
			 		while ($array = mysql_fetch_array($result)) {
			 		$sno = $array['sno'];
			 		}
			 		$query2 = "SELECT sno, fname, sname FROM student WHERE sno = $sno";
			 		$result2 = mysql_query($query2);
			 		while ($row = mysql_fetch_array($result2)) {
			 		$sno2 = $row['sno'];
			 		$fname = $row['fname'];
			 		$sname = $row['sname'];
			 		
			 		echo "<tr>";
			 		echo "<td width = '33%' align = 'center'>".$row['sno']."</td>";
			 		echo "<td width = '33%' align = 'center'>".$row['fname']." ".$row['sname']."</td>";

			 		}
			 		}
Any advice would be appreciated.

Re: Pulling data from a database according to field value

Posted: Tue Apr 12, 2011 12:15 am
by shauns2007
Changed it to this

Code: Select all

function list_students_per_course() {
			 		global $connection;
			 		$query = "SELECT sno FROM course_student WHERE cid=".$_GET['cid'];
			 		$result = mysql_query($query);
			 		while ($array = mysql_fetch_array($result)) {
			 		$sno = $array['sno'];
			 		print_r($array);
			 		}
			 		$query2 = "SELECT sno, fname, sname FROM student WHERE sno = $sno"; //put a loop in like when showing courses
			 		$result2 = mysql_query($query2);
			 		while ($row = mysql_fetch_array($result2)) {
			 		$sno2 = $row['sno'];
			 		$fname = $row['fname'];
			 		$sname = $row['sname'];
			 		//print_r($row);
			 		echo "<tr>";
			 		echo "<td width = '33%' align = 'center'>".$row['sno']."</td>";
			 		echo "<td width = '33%' align = 'center'>".$row['fname']." ".$row['sname']."</td>";
					echo "</tr>";
			 		}
			 		}
This part works perfectly as it prints out all the student id's (sno) values for the correct course id (cid)

Code: Select all

$query = "SELECT sno FROM course_student WHERE cid=".$_GET['cid'];
			 		$result = mysql_query($query);
			 		while ($array = mysql_fetch_array($result)) {
			 		$sno = $array['sno'];
			 		print_r($array);
			 		}
My issue I have is in this line I think as it doesn't give all the students or their details from the above query. I only get 1 student.

Code: Select all

$query2 = "SELECT sno, fname, sname FROM student WHERE sno = $sno";

Re: Pulling data from a database according to field value

Posted: Tue Apr 12, 2011 12:31 am
by shauns2007
Got it working! :D

Re: Pulling data from a database according to field value

Posted: Tue Apr 12, 2011 7:12 pm
by danwguy
care to share solutions so if someone else has a similar problem they can refer to this and see what the fix was please

Re: Pulling data from a database according to field value

Posted: Tue Apr 12, 2011 11:40 pm
by shauns2007
I put the closing } to the loop in the wrong place

Code: Select all

function list_students_per_course() {
			 		global $connection;
			 		$query = "SELECT sno FROM course_student WHERE cid=".$_GET['cid']." ORDER BY sno";
			 		$result = mysql_query($query);
			 		while ($array = mysql_fetch_array($result)) {
			 		$sno = $array['sno'];
			 		
			 		$query2 = "SELECT sno, fname, sname FROM student WHERE sno = $sno";
			 		$result2 = mysql_query($query2);
			 		while ($row = mysql_fetch_array($result2)) {
			 		$sno2 = $row['sno'];
			 		$fname = $row['fname'];
			 		$sname = $row['sname'];
			 		}
			 		echo "<tr>";
			 		echo "<td width = '33%' align = 'center'>$sno2</td>";
			 		echo "<td width = '33%' align = 'center'>$fname $sname</td>";

					echo "</tr>";
			 		}
			 		}