Unable to display username from another table

[EarthDay]

Hi there - I currently am able to view a list of contacts with no issues and would like to be able to view the mentors / tutors for the clients.

The username is stored in another table to the contact information and the mentor ID is stored in the contacts table.

I have followed a tutorial on how to do this but can't get it to work.

The information for the contacts table appears fine but does not display the username from the accounts table.

Error reporting is on but not giving me any error messages.

Code;

$stmt = $pdo->prepare('SELECT id,name,last_name,mobile_number,status,dob,mentor,image FROM contacts');
$stmt->execute([$_GET['id']]);
$fullContactInfo = $stmt->fetchAll(PDO::FETCH_ASSOC);

if($fullContactInfo == true){
    $stmt = $pdo->prepare('SELECT username FROM accounts WHERE id = ?');
    $stmt->execute([ $fullContactInfo['mentor'] ]);
    $fullContactInfo1 = $stmt->fetch(PDO::FETCH_ASSOC);
    
}

Display code

                    <?php if($fullContactInfo == null){
                        echo "<tr><td>No Record Found!</td></tr>";
                    }else{ foreach($fullContactInfo 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><?= $fullContactInfo1['username']; ?></td>

Table examples

Contacts ID:1 
Name: Joe Blogs 
Mobile: 1889454 
Status: Current 
Mentor: 25 

Accounts 
ID: 25 
Username: jbloggs

Many thanks.

[Barand]

Don't use 2 queries when 1 will do

SELECT c.id
     , c.name
     , c.last_name
     , c.mobile_number
     , c.status
     , a.username as mentorname
FROM contacts c 
     LEFT JOIN
     accounts a ON c.mentor = a.id
WHERE c.id = 1;

+----+-------+-----------+---------------+---------+------------+
| id | name  | last_name | mobile_number | status  | mentorname |
+----+-------+-----------+---------------+---------+------------+
|  1 | Scott | Chegg     | 01012345678   | current | jbloggs    |
+----+-------+-----------+---------------+---------+------------+

 

[EarthDay]

1 hour ago, Barand said:

Don't use 2 queries when 1 will do

SELECT c.id
     , c.name
     , c.last_name
     , c.mobile_number
     , c.status
     , a.username as mentorname
FROM contacts c 
     LEFT JOIN
     accounts a ON c.mentor = a.id
WHERE c.id = 1;

+----+-------+-----------+---------------+---------+------------+
| id | name  | last_name | mobile_number | status  | mentorname |
+----+-------+-----------+---------------+---------+------------+
|  1 | Scott | Chegg     | 01012345678   | current | jbloggs    |
+----+-------+-----------+---------------+---------+------------+

 

Hi Barand,

Many thanks for this.

My code now reads

$stmt = $pdo->prepare('SELECT c.id
     , c.name
     , c.last_name
     , c.mobile_number
     , c.status
     , a.username as mentorname
FROM contacts c 
     LEFT JOIN
     accounts a ON c.mentor = a.id
WHERE c.id = 1;');
$stmt->execute([$_GET['c.id']]);
$fullContactInfo = $stmt->fetchAll(PDO::FETCH_ASSOC);

 

						<?php if($fullContactInfo == null){
							echo "<tr><td>No Record Found!</td></tr>";
						}else{ foreach($fullContactInfo 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['mentorname']; ?></td
							<td><img src="<?php echo $info['image']; ?>" alt="" style="height: 30px; width:30px;"></td>
							<td>

The info is not showing anything now, is there something I am missing please?

Cheers

[maxxd]

You've hard-coded a customer id of 1 into your query. Change that to be a prepared statement placeholder.

[mac_gyver]

3 hours ago, EarthDay said:

Error reporting is on but not giving me any error messages.

have you checked, using a phpinfo() statement, that it is, because the first posted code should be producing at least a php error. $fullContactInfo is an array of rows, so $fullContactInfo['mentor'] doesn't exist. also, in both pieces of code, the number of prepared query place-holders doesn't match the number of supplied input parameters, which should produce php/sql errors.

when you make the database connection are you setting the error mode to use exceptions (which is the default now in php8), setting emulated prepared queries to false, and you should be setting the default fetch mode to assoc so that you don't need to keep repeating it in each fetch statement.

 

[EarthDay]

Hi All,

Thank you for your replies.

I have enabled PHP 8 on my server and it giving me a lot better error messages now, thanks for that.

The error message that I am getting is "Warning: Undefined array key "id" or Warning: Undefined array key "c.id".

I have read a few tutorials about this now and can't see where I am going wrong 

assoc mode has also been set on the query.

$stmt = $pdo->prepare('SELECT c.id
     , c.name
     , c.last_name
     , c.mobile_number
     , c.status
     , a.username as mentorname
FROM contacts c 
     LEFT JOIN
     accounts a ON c.mentor = a.id
WHERE c.id = ?;');
$stmt->execute([$_GET['c.id']]);
$fullContactInfo = $stmt->fetchAll(PDO::FETCH_ASSOC);

Thanks, ED.

[Barand]

Is there an item in your $_GET array with the key "c.id"? I suspect it will be just $_GET["id"] as you were originally using.

[EarthDay]

15 hours ago, Barand said:

Is there an item in your $_GET array with the key "c.id"? I suspect it will be just $_GET["id"] as you were originally using.

Hi, thanks for this.

I was trying c.id to see if I could get it working.

I've set $stmt->execute([$_GET['id']]); to ID as requested and gives me the same error message. Warning: Undefined array key "id"

Thanks, ED.

[mac_gyver]

you must trim and validate all inputs to your code before using them. the $_GET['id'] is a required input. if it doesn't exist, and isn't an integer > 0, that's an error and you should setup a message for the user letting them know that a required input is not valid, and don't attempt to run any code that's dependent upon that input.

where is this $_GET['id'] input supposed to be coming from?

[Barand]

Does output of your $_GET array show an "id" key?

echo '<pre>' . print_r($_GET, true) . '</pre>';