Joining tables

I am trying to join two tables in mysql and then show the results in php. I have managed to join the two tables but it only shows the last entry/row for each.

Here is my php code :

$sql = "Select ProgrammingFoundations.student_id, ProgrammingFoundations.attendance, ProgrammingFoundationsLab.lab_attendance
		from ProgrammingFoundations
		inner join ProgrammingFoundationsLab
		on ProgrammingFoundations.student_id = ProgrammingFoundationsLab.student_id";
		$result = mysql_query($sql) or die(mysql_error());;
		if($row = mysql_fetch_array($result))
		{
			while($row = mysql_fetch_array($result))
			{
				echo ' lecture attendance : ', $row['attendance'];
				echo ' lab attendance : ', $row['lab_attendance'];
			}
		}
		else
		{
		echo 'not successful';
		}

PHP:

this shows all the rows except the first but i dont know why?

 

Check if the ID of the first row exists in both tables.

 

Yeah, it does.

 

Fixed, by changing code to :

		$sql = "Select ProgrammingFoundations.student_id, ProgrammingFoundations.attendance, ProgrammingFoundationsLab.lab_attendance
		from ProgrammingFoundations
		left join ProgrammingFoundationsLab
		on ProgrammingFoundations.student_id = ProgrammingFoundationsLab.student_id";
		$result = mysql_query($sql) or die(mysql_error());;
		if($row = mysql_fetch_array($result))
		{
			$sql = "Select ProgrammingFoundations.student_id, ProgrammingFoundations.attendance, ProgrammingFoundationsLab.lab_attendance
		from ProgrammingFoundations
		left join ProgrammingFoundationsLab
		on ProgrammingFoundations.student_id = ProgrammingFoundationsLab.student_id";
		$result = mysql_query($sql) or die(mysql_error());;
			while($row = mysql_fetch_array($result))
			{
			
				echo ' lecture attendance : '. $row['attendance'];
				echo ' lab attendance : '. $row['lab_attendance'];
			}
		}
		else
		{
		echo 'not successful';
		}

PHP:

doh'.

 

It's because you are using mysql_fetch_array($result)) twice on the same resultset.

Like this it should look fine.

<?
        $sql = "Select ProgrammingFoundations.student_id, ProgrammingFoundations.attendance, ProgrammingFoundationsLab.lab_attendance
        from ProgrammingFoundations
        left join ProgrammingFoundationsLab
        on ProgrammingFoundations.student_id = ProgrammingFoundationsLab.student_id";
        
$result = mysql_query($sql) or die(mysql_error());;
            while($row = mysql_fetch_array($result))
            { 
                echo ' lecture attendance : '. $row['attendance'];
                echo ' lab attendance : '. $row['lab_attendance'];
            }
?>

PHP:

 

Yes... you can make the query twice too. lol

 

<?
        $sql = "Select ProgrammingFoundations.student_id, ProgrammingFoundations.attendance, ProgrammingFoundationsLab.lab_attendance
        from ProgrammingFoundations
        left join ProgrammingFoundationsLab
        on ProgrammingFoundations.student_id = ProgrammingFoundationsLab.student_id";
       
		$result = mysql_query($sql) or die(mysql_error());
		if (!mysql_num_rows($result))
		{
			echo "No record found";
			exit ();
		}
			
		while($row = mysql_fetch_array($result))
		{
			echo ' lecture attendance : '. $row['attendance'];
			echo ' lab attendance : '. $row['lab_attendance'];
		}
?>

PHP: