User Only rows from MySQL table in PHP

Question

I'm creating a client area kind of website in PHP or web host management which will have the details of the client and also the details of their web account.

This is my table

ID----USER----NAME----ACCOUNT----PASSWORD
1----user1----name1----accont1----password2
2----user1----name2----accont2----password2
3----user3----name3----accont3----password3

Now what I want to know is how can I only get the account details of user1 and display it in PHP. Like this,

You are user1
Your Accounts are accont1 & accont2 and 
their passwords are password1 & password2 and 
their name is name1 & name2

BTW! the name1 and name2 are the name of the hosting account and I use PDO for connecting to mySQL
Thanks in advance!!!

Edit: This is how I manually create the variables and insert it in my HTML

$id2 = "2";
$stmt2 = $conn->query("SELECT * FROM accounts WHERE ID = $id2");
$row2 = $stmt2->fetch();
$uname2 = $row2['account'];
$label2 = $row2['name'];

Now what I need is I want to show id 1 & 2 automatically after login and if you please help me on how to do that too!!! I'm a beginner so please consider helping!!!

Welcome to SO. Please see: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — Strawberry, Jun 11 '20 at 10:30
I suggest you read the accepted answer at the link provided, and follow the instructions therein. But obviously I can't insist. — Strawberry, Jun 11 '20 at 11:25

score 0 · Answer 1 · answered Jun 11 '20 at 14:27

Using PDO we don't need to insert variables inline with a query, instead we parameterize them. This helps prevent SQL injection attacks and follows best practice. We use question marks to denote where our variables would be.

SELECT NAME, ACCOUNT FROM accounts WHERE ID = ?

To have variables assigned to the query as parameters we use an array - in this example we only have the 1 variable to deal with.

    $stmt->execute([$id]);

PDO is traversible so we can iterate through the results using foreach. By using .= we can concatenate new data to the right of existing data inside a variable. We can also use a counter to know when we need to add the ampersand and spacing.

$id = "1";
$stmt = $pdo->prepare('SELECT NAME, ACCOUNT FROM accounts WHERE ID = ?');
$stmt->execute([$id]);
$i = 0;
foreach ($stmt as $row)
{
    if ($i > 0) {
        $accounts .= ' & ';
        $names .= ' & ';
    }
    $accounts .= $row['NAME'];
    $names .= $row['ACCOUNT'];
    $i++;
}
echo "Your Accounts are $accounts and <br>";
echo "Their names are $names";

I have skipped the password field on purpose. A password field should not store data in plaintext format. If the database were to be breached all those passwords would be leaked. It's considered best practice to hash a password, and store the hash value.

There are a great number of articles on the topic. I would recommend looking at Argon for anyone running PHP 7.2+.

User Only rows from MySQL table in PHP

1 Answers1