Jump to content

joining tables


debz89uk

Recommended Posts

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';
	}

 

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

Link to comment
https://forums.phpfreaks.com/topic/196530-joining-tables/
Share on other sites

What happends if you try a LEFT JOIN?

 

$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))
{
while($row = mysql_fetch_array($result))
{
echo ' lecture attendance : '. $row['attendance'];
echo ' lab attendance : '. $row['lab_attendance'];
}
}
else
{
echo 'not successful';
}

Link to comment
https://forums.phpfreaks.com/topic/196530-joining-tables/#findComment-1031840
Share on other sites

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';
	}

 

doh'.

Link to comment
https://forums.phpfreaks.com/topic/196530-joining-tables/#findComment-1031848
Share on other sites

Every time you call mysql_fetch_array it removes one row from resultset, co when doing something like

if($row = mysql_fetch_array($result))  {
  while($row = mysql_fetch_array($result)) {
    echo ' lecture attendance : ', $row['attendance'];
    echo ' lab attendance : ', $row['lab_attendance'];  
  }
}

 

you lose one row when calling mysql_fetch_array witin if condition

Link to comment
https://forums.phpfreaks.com/topic/196530-joining-tables/#findComment-1031864
Share on other sites

Every time you call mysql_fetch_array it removes one row from resultset, co when doing something like

if($row = mysql_fetch_array($result))  {
  while($row = mysql_fetch_array($result)) {
    echo ' lecture attendance : ', $row['attendance'];
    echo ' lab attendance : ', $row['lab_attendance'];  
  }
}

 

you lose one row when calling mysql_fetch_array witin if condition

 

^^ Exactly!

 

I think you meant the IF condition to test if the query ran successfully, but that is not what it is doing.

 

Give this logic a try:

 

<?php

$sql = "Select pf.student_id, pf.attendance,
               pfl.lab_attendance
        FOM ProgrammingFoundations pf
        INNER JOIN ProgrammingFoundationsLab pfl
          ON pf.student_id = pfl.student_id";
$result = mysql_query($sql) or die(mysql_error());;

if (!$result)
{
    //Query failed
    echo 'not successful';
}
else if (mysql_num_rows($result)===0)
{
    //Query ran, but no results returned
    echo 'no results returned';
}
else
{
    ($row = mysql_fetch_array($result))
    {
        echo " lecture attendance : {$row['attendance']}";
        echo " lab attendance : {$row['lab_attendance']}";
    }
}

?>

 

Link to comment
https://forums.phpfreaks.com/topic/196530-joining-tables/#findComment-1031870
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.