return one value from database with mysql php pdo

Question

Im not trying to use a loop. I just one one value from one column from one row. I got what I want with the following code but there has to be an easier way using PDO.

try {
        $conn = new PDO('mysql:host=localhost;dbname=advlou_test', 'advlou_wh', 'advlou_wh');
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch(PDOException $e) {
        echo 'ERROR: ' . $e->getMessage();
    }

$userid = 1;

$username = $conn->query("SELECT name FROM `login_users` WHERE username='$userid'");
$username2 = $username->fetch();
$username3 = $username2['name'];

echo $username3;

This just looks like too many lines to get one value from the database. :\

Your Common Sense · Accepted Answer · 2020-06-16 16:49:57Z

69

You can use fetchColumn():

$q= $conn->prepare("SELECT name FROM `login_users` WHERE username=?");
$q->execute([$userid]);
$username = $q->fetchColumn();

edited Jun 16, 2020 at 16:49

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

answered Dec 1, 2014 at 0:41

Ouadie

13.2k4 gold badges54 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Your Common Sense · Accepted Answer · 2020-06-17 07:03:57Z

22

You could create a function for this and call that function each time you need a single value. For security reasons, avoid concatenating strings to form an SQL query. Instead, use prepared statements for the values and hardcode everything else in the SQL string. In order to get a certain column, just explicitly list it in your query. a fetchColumn() method also comes in handy for fetching a single value from the query

function getSingleValue($conn, $sql, $parameters)
{
    $q = $conn->prepare($sql);
    $q->execute($parameters);
    return $q->fetchColumn();
}

Then you can simply do:

$name = getSingleValue($conn, "SELECT name FROM login_users WHERE id=?", [$userid]);

and it will get you the desired value.

So you need to create that function just once, but can reuse it for different queries.

This answer has been community edited addressing security concerns

edited Jun 17, 2020 at 7:03

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

answered Aug 13, 2012 at 5:04

asprin

9,85312 gold badges72 silver badges127 bronze badges

5 Comments

bdsl Over a year ago

It's dangerous to use concatenation to build a database query. Much better to use a PDO prepared statement.

Benni Over a year ago

Dude. Just use prepared queries, even if it's only an example. Beginners might copy this!

miken32 Over a year ago

Even with the edits this is still a poor answer. Where does $conn come from?

Your Common Sense Over a year ago

Mind if I edit your answer to make it less dangerous and more to the point?

jirig Over a year ago

@YourCommonSense you don't have to ask other users for permission to edit their answers. You are free to improve it in case it would really help and you feel like it.

Marc B · Accepted Answer · 2012-08-13 04:29:18Z

0

Just like it's far too much work to have to get into your car, drive to the store, fight your way through the crowds, grab that jug of milk you need, then fight your way back home, just so you can have a milkshake.

All of those stages are necessary, and each subsequent step depends on the previous ones having been performed.

If you do this repeatedly, then by all means wrap a function around it so you can reuse it and reduce it down to a single getMyValue() call - but in the background all that code still must be present.

answered Aug 13, 2012 at 4:29

Marc B

362k44 gold badges433 silver badges508 bronze badges

2 Comments

liamvictor Over a year ago

"All of those stages are necessary" No, they're not. And you should walk to the shops. ;-)

miken32 Over a year ago

I realize it was a different time, but this is really just a comment.

Collectives™ on Stack Overflow

return one value from database with mysql php pdo

3 Answers 3

Comments

5 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related