joining tables

[debz89uk]

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?

[irkevin]

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

[shlumph]

How many matching student id's in both ProgrammingFoundations and ProgrammingFoundationsLab?

[debz89uk]

Left join shows the same result as inner join.

 

ProgrammingFoundations and ProgrammingFoundationsLab have the same number(all matching) of student_ids, the amount varies depending on how many the user enters.

[debz89uk]

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'.

[shlumph]

This seems a bit odd to me 

[Mchl]

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

[Psycho]

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

?>

 

[Mchl]

Since we have die here:

$result = mysql_query($sql) or die(mysql_error());

it means that if query fails, then the script will end before it will get to

 

if (!$result)

[Psycho]

Since we have die here:

$result = mysql_query($sql) or die(mysql_error());

it means that if query fails, then the script will end before it will get to

 

if (!$result)

 

Yeah, I missed that.