inner join question

[futrose]

I have three tables I am using to loop through and get results, ELDERS, ELDERCATEGORY, ELDERCAT.

Elders has all the profile of the staff.

Eldercat is just a list of staff positions.

Eldercategory link the two together by their id's.

 

Here is the whole code, I'll post the snippet in question below.

<?php
ini_set("display_errors", "1");
error_reporting(-1);
$result = mysqli_query($link, 'SELECT * From eldercat');
if (!$result)
{
$error = 'Error getting elder categories: ' . mysqli_error($link);
include  'error.php';
exit();
}

while ($row = mysqli_fetch_array($result)){

$eldercat[] = array('id' => $row['id'], 'category' => $row['category']);
}

foreach ($eldercat as $cat):
echo "<div style='font-weight:bold; clear:both'>";
echo $cat['category'];
echo "</div>";

$result2 = mysqli_query($link, "Select * from elders inner join eldercategory on elders.id = elderid inner join eldercat on eldercatid = eldercat.id where
eldercatid = " . $cat['id'] ." order by orderby");

if (!$result2)
{
$error = 'Error getting elders in the category: ' . mysqli_error($link);
include  'error.php';
exit();
}

while ($row2 = mysqli_fetch_array($result2))
{

if (($row2['avail']) =='Y')
{
echo '<div class="smallelderblock">';
echo '<img src = "http://www.bbcpa.org/images/' . ($row2['image']) . '" alt="' . ($row2['LastName']) . ' elders photo" class="elderimg" />';
echo $row2['position'] . '<br />';
echo "<a href='javascript: window.document.LastName" . $row2['id'] . ".submit();'>" . $row2['HFirst'];
	if (($row2['HFirst'] !="") && ($row2['WFirst'] !=""))
	{
	echo ' &amp ';
	}	
    echo $row2['WFirst'] . " " . $row2['LastName'] . "</a>";
echo "<form name='LastName" . $row2['id'] . "'" . " " . "action='staff-details.php' method='post'>";
echo "<input value='" . $row2['id'] . "'" . "type='hidden' name='ElderDetails' />";
echo "</form>";
echo '</div>';
}

}
endforeach;


?>

 

$result2 = mysqli_query($link, "Select * from elders inner join eldercategory on elders.id = elderid inner join eldercat on eldercatid = eldercat.id where
eldercatid = " . $cat['id'] ." order by orderby");

 

I am getting the output that I want except for the

$row2['id']

lines are displaying the id from the wrong table.  I need the id from the elders table but I am getting the id from the eldercat table instead. 

Any ideas?

 

 

[jcbones]

In your column selection add

elders.id AS eldersid

 

Then instead of getting the variable via:

$row2['id']

 

You would get it using:

$row2['eldersid']

 

This is happening because you have 2 columns that are named `id` in the result set, and php is overwriting the first one `elders`.`id` with the second.

[futrose]

thanks jcbones.  Worked perfectly.  I thought i might be something like that but wasn't sure how to implement it.