Unable to display username in Table

[EarthDay]

Hi there,

I am trying to display the username from another table and links by the ID in a table list but is giving me multiple error messages.

Quote

 

Error message at top of page;

Warning: Undefined array key "assignedto" in /var/www/vhosts/reflectionscic.com/remus.reflectionscic.com/company/viewall.php on line 10

Table error message;

Warning: Trying to access array offset on value of type bool in /var/www/vhosts/reflectionscic.com/remus.reflectionscic.com/company/viewall.php on line 154

 

Code

$stmt = $pdo->prepare('SELECT * FROM company');
$stmt->execute([]);
$fullContactInfo = $stmt->fetchAll(PDO::FETCH_ASSOC);

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

}

Display code in Table

<td><?= $memberInfo['username']; ?></td>

Database example

Accounts (Example)
ID - 1
Username - jbloggs

Company
assignedto - 1

I have got the code working on another page where it only displays the company information on one page by ?id=1

Any advice please? :)

Cheers,

ED.

[mac_gyver]

just use a single JOIN query to do this.

next, you should always list out the columns you are SELECTing in a query so that your code is self-documenting. you should build your sql query statement in a php variable, to make debugging easier and help prevent syntax mistakes, and you should set the default fetch mode to assoc when you make the database connection so that you don't need to specify it in each fetch statement.

the reason why your current code doesn't work, is because fetchAll(), the way you are using it, returns an array of the rows of data. if there are three rows in the company table, you will have an array with three rows in it, with each row having an assignedto element. you can use print_r() on the fetched data to see what it is.

 

Edited February 8, 2023 by mac_gyver

[EarthDay]

Firstly, sorry for the late reply.

Thank you for the advise mac_gyver

I've built my query as;

SELECT 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

But it's still showing as a number and not the username.

If change the accounts.id to .username - it shows nothing.

Any advise please?

[Barand]

In your original code you were using contacts.assignedto to locate the accounts record. Why, then, are you joining contacts to accounts using contacts.mentor column?

[EarthDay]

Firstly, sorry for the late reply.

Thank you for the advise mac_gyver

I've built my query as;

SELECT 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

But it's still showing as a number and not the username.

If change the accounts.id to .username - it shows nothing.

Any advise please?

 

UPDATE - My fail - Changed the table view from mentor to username and works fine.

Thanks again for your help

[EarthDay]

1 minute ago, Barand said:

In your original code you were using contacts.assignedto to locate the accounts record. Why, then, are you joining contacts to accounts using contacts.mentor column?

Sorry, I was working on another page that had the same issue.

[EarthDay]

The working code for the original code I posted;

SELECT company.compname, company.compcontact, company.assignedto, accounts.username, accounts.id
FROM company
INNER JOIN accounts ON company.assignedto=accounts.id
where assignedto = ?

<td><?= $info['username']; ?></td>