return one value from database with mysql php pdo-ThrowExceptions

Exception or error:

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. :\

How to solve:

You could create a function for this and call that function each time you need a single value

function getSingleValue($tableName, $prop, $value, $columnName)
{
    $q = $conn->query("SELECT `$columnName` FROM `$tableName` WHERE $prop='".$value."'");
    $f = $q->fetch();
    $result = $f[$columnName];
    return $result;
}

Then you can simply do:

$singleValue = getSingleValue('login_users', 'username', $userid, 'name'); // will get you the value

So you need to create that function just once, but can reuse it for different tables with different column names.

COMMUNITY EDIT:
For security reasons, avoid concatenating strings to form an SQL query, use prepared statements instead.

Thus, instead of the vulnerable

$q = $conn->query("SELECT `$columnName` FROM `$tableName` WHERE $prop='".$value."'");

Use the corresponding code with prepared statements

$q = $conn->prepare('SELECT :columnName FROM :tableName WHERE :property=:value', [
    'columName' => $columnName,
    'tableName' => $tableName,
    'property' => $prop,
    'value' => $value
]);

Answer:

You can use fetchColumn():

$q= $conn->query("SELECT name FROM `login_users` WHERE username='$userid'");
$username = $q->fetchColumn();

Answer:

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.

Leave a Reply

Your email address will not be published. Required fields are marked *