Asked  7 Months ago    Answers:  5   Viewed   27 times

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

 Answers

32

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

Wednesday, March 31, 2021
 
francadaval
answered 7 Months ago
64

It seems that PDO::MYSQL_ATTR_FOUND_ROWS is a mysql connection option. Thus, it works only as PDO connection option as well. So, set it up this way

$opt  = array(
    PDO::MYSQL_ATTR_FOUND_ROWS   => TRUE,
    // you may wish to set other options as well
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
);
$this->_db = new PDO($dsn,DB_USER,DB_PASS,$opt);
Wednesday, March 31, 2021
 
shivam
answered 7 Months ago
87

Do you really want encodeURIComponent(pid) here?

Try without encodeURIComponent:

    window.open('search_project.php?id=pid','popuppage',
       'width=400,toolbar=1,resizable=1,scrollbars=yes,height=400,top=100,left=100');
Saturday, May 29, 2021
 
talkhabi
answered 5 Months ago
89

You can only return one value. But you can use an array that itself contains the other two values:

return array($uid, $sid);

Then you access the values like:

$ids = ids();
echo $ids[0];  // uid
echo $ids[1];  // sid

You could also use an associative array:

return array('uid' => $uid, 'sid' => $sid);

And accessing it:

$ids = ids();
echo $ids['uid'];
echo $ids['sid'];
Monday, June 28, 2021
 
Ula
answered 4 Months ago
Ula
29

Your PDO is configured to emulate prepared queries, whereas mysqli is using true prepared queries.

The prepared query binds the string ''1'' as an integer parameter value. PHP coerces it to an integer using something like intval(). Any string with non-numeric leading characters is interpreted as 0 by PHP, so the parameter value sent after prepare is the value 0.

The fake prepared query uses string interpolation (instead of binding) to add the string ''1'' into the SQL query before MySQL parses it. But the result is similar, because SQL also treats a string with non-numeric leading characters in an integer context as the value 0.

The only difference is what ends up in the general query log when the parameter is bound before prepare versus after prepare.

You can also make PDO use real prepared queries, so it should act just like mysqli in this case:

$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

PS: This may demonstrate a good reason why it's customary to start id values at 1 instead of 0.

Wednesday, August 18, 2021
 
derobert
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :