Pulling data from a database according to field value

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
shauns2007
Forum Commoner
Posts: 27
Joined: Wed Feb 02, 2011 11:34 am

Pulling data from a database according to field value

Post 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.
shauns2007
Forum Commoner
Posts: 27
Joined: Wed Feb 02, 2011 11:34 am

Re: Pulling data from a database according to field value

Post 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";
shauns2007
Forum Commoner
Posts: 27
Joined: Wed Feb 02, 2011 11:34 am

Re: Pulling data from a database according to field value

Post by shauns2007 »

Got it working! :D
danwguy
Forum Contributor
Posts: 256
Joined: Wed Nov 17, 2010 1:09 pm
Location: San Diego, CA

Re: Pulling data from a database according to field value

Post 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
shauns2007
Forum Commoner
Posts: 27
Joined: Wed Feb 02, 2011 11:34 am

Re: Pulling data from a database according to field value

Post 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>";
			 		}
			 		}
Post Reply