[SOLVED] Having problem with my join and echoing correct id

[Eiolon]

The information is displayed correctly.  The problem comes into play with the record id's. 

 

The link I would click on for the employees name is echoing the job id and not the employee id so I get sent to the job page and not the employee profile.  The link I would click on for the job is echoing fine.

 

Thanks!

 

I have tried various joins and I have tried employee.php?id=<?php echo $r_emp['e.id']; ?> instead of employee.php?id=<?php echo $r_emp['id']; ?>

 

Any suggestions?

 

<?php # department.php

require_once('mysql_connect.php');

$q_emp = "SELECT e.id, e.first_name, e.last_name, e.job_id, j.id, j.title FROM employees e INNER JOIN jobs j ON (e.job_id = j.id) WHERE department_id = 4 ORDER BY last_name ASC";
$emp = mysql_query($q_emp) OR die ('Could not query employee information.');
$r_emp = mysql_fetch_array($emp);

?>

 

			<table width="100%" border="0" cellspacing="1" cellpadding="6" bgcolor="#CCCCCC">
   	  			<tr bgcolor="#E5E5E5">
             		<td><strong>Employee</strong> </td>
             		<td><strong>Job Title</strong></td>
              	</tr>
            	<?php do { ?>
             	<tr bgcolor="#FFFFFF">
                	<td><a href="employee.php?id=<?php echo $r_emp['id']; ?>"><?php echo $r_emp['last_name']; ?>, <?php echo $r_emp['first_name']; ?></a></td>
                	<td><a href="job.php?id=<?php echo $r_emp['id']; ?>"><?php echo $r_emp['title']; ?></a></td>
                </tr>
                <?php } while ($r_emp = mysql_fetch_array($emp)); ?>
		</table>

[esukf]

Isn't job id j.id?

 

employee.php?id=<?php echo $r_emp['j.id']; ?>

 

 

[pocobueno1388]

In your query, you are selecting two field names named "id". You need to alias those fields so the query will know which one to use. So do something like this

 

SELECT e.id AS employeeID, e.first_name, e.last_name, e.job_id, j.id AS jobID, j.title FROM employees e INNER JOIN jobs j ON (e.job_id = j.id) WHERE department_id = 4 ORDER BY last_name ASC

 

Now when you want to use e.id, do this

$r_emp['employeeID']

 

If you want to use j.id, do this

$r_emp['jobID']

[Eiolon]

Thanks, I can't believe I overlooked aliasing.  Works like a charm 

 

In your query, you are selecting two field names named "id". You need to alias those fields so the query will know which one to use. So do something like this

 

SELECT e.id AS employeeID, e.first_name, e.last_name, e.job_id, j.id AS jobID, j.title FROM employees e INNER JOIN jobs j ON (e.job_id = j.id) WHERE department_id = 4 ORDER BY last_name ASC

 

Now when you want to use e.id, do this

$r_emp['employeeID']

 

If you want to use j.id, do this

$r_emp['jobID']