Join Query Issue

[EarthDay]

Hi there,

I have setup a join query to show the username from the accounts table when displaying information from my contacts table because it was only showing the user

For example, say the contacts ID is 125, the link below will show ID 3 from the accounts table.

Should be update.php?id=125

Actually showing update.php?id=3

(There are actually only ID's 2,3 & 4 in the accounts table)

$stmt = $pdo->prepare('SELECT contacts.id, contacts.name, contacts.last_name, contacts.dob, contacts.mobile_number, contacts.status, contacts.mentor, contacts.image, accounts.username, accounts.id FROM contacts INNER JOIN accounts ON contacts.mentor=accounts.id');
$stmt->execute([]);
$contact = $stmt->fetchAll(PDO::FETCH_ASSOC);

The issue that I am having is when I click the Update button to update the client, the page is trying to look for the ID of the account from the accounts table and not the contacts table.

=						<?php if($contact == null){
							echo "<tr><td>No Record Found!</td></tr>";
						}else{ foreach($contact as $info){ ?>
						<tr>
							<td><?php echo $info['name']; ?> <?php echo $info['last_name']; ?></td>
							<td><?php echo $info['mobile_number']; ?></td>
							<td><?php echo $info['status']; ?></td>
							<td><?php echo $info['dob']; ?></td>
							<td><?php echo $info['username']; ?></td>
							<td><img src="<?php echo $info['image']; ?>" alt="" style="height: 30px; width:30px;"></td>
							<td>
-->								<a href="display.php?id=<?=$info['id']?>"><i class="fa fa-eye" aria-hidden="true"></i></a>
-->								<a href="update.php?id=<?=$info['id']?>"><i class="fas fa-pencil-alt"></i></a>
-->								<?php if ($_SESSION['role'] == 'Admin'): ?><a href="del.php?id=<?=$info['id']?>"><i class="fas fa-trash fa-xs"></i></a><?php endif; ?>
							</td>
						</tr>

Any help would be greatly appreciated.

Cheers,

ED.

Edited February 17, 2023 by EarthDay
Added more info

[EarthDay]

Solution

SELECT contacts.id as cid,

[mac_gyver]

php builds associative indexed arrays from left to right and top to bottom. when you have the same index name more than once in the data, the last value overwrites any previous value. since you are SELECTing the accounts.id last, that is the value you are getting for the id index.

are you even using the accounts.id in the fetched data? if not, don't include it in the SELECT list. likewise for any other data values you are not actually using in the feteched data.