MySql query with PDO, returned value issue

[romanromeo89]

Hello

I'm running a php function to get some user information from mysql using pdo. The problem is that i don't get the results i want. This is my php code:

<?php 
require_once('db_connect.php');

    function mila(){
        global $dbh;
        // Set dummy variables
        $required_id=1;
        // Select table with query
        $sth = $dbh->prepare("SELECT username,firstname FROM users WHERE :id");

        // Bind variables to your statement
        $sth->bindParam(':id', $required_id);

        // Flip the switch
        $sth->execute();

        foreach ($sth as $row){         
                    $username = $row['username'];
                    $firstname = $row['firstname'];
                    echo '<br/>' .$username.' '.$firstname;
        }
    }

    mila();
?>

Below is the database table i access:

And i get this on my browser:

user1 name1
user2 name2
user3 name3
user4 name4

I should get only one record, because of my sql query.
If i use "SELECT username,firstname FROM users WHERE id=1" as my query, i get only on record.

[AndreRet]

Try -

$sth = mysql_query("SELECT COUNT(username,firstname) FROM users WHERE id='".$required_id."'");

return (mysql_result($sth, 0) == 1) ? true : false;

This should return record 1 because you declared "$required_id=1".

@romanromeo89

I should get only one record, because of my sql query.

-

If I use "SELECT username,firstname FROM users WHERE id=1" as my query, i get only on record.

This is your query from your code above:

SELECT username,firstname FROM users WHERE :id

It should be:

SELECT username,firstname FROM users WHERE id=$id

Change this:

$sth->bindParam(':id', $required_id);

To this:

$sth->bindParam('$id', $required_id);

[pritaeas]

You can't use execute to get results. You need to call fetch after execute. See the examples here.

@LastMitch: :id is correct for parameter binding.

@LastMitch: :id is correct for parameter binding.

OK

[romanromeo89]

Thank you all for replies,
I changed my code to this:

<?php
    require_once('db_connect.php');
    function mila(){
    global $dbh;
    $required_id=3;
    $sth = $dbh->prepare("SELECT username,firstname FROM users WHERE id = ?");
    $sth->execute(array($required_id));
    $user_info = $sth->fetchAll();
    $user_info['username'];
    //print_r($user_info);
    }
    mila();
?>

The result is:
Notice: Undefined index: username in C:...\db_get_user_id.php on line 13
if i print_r($user_info) i get
Array ( [0] => Array ( [username] => user3 [0] => user3 [firstname] => name3 [1] => name3 ) )
and i cant't understand how to get the values i want from there.

$sth = $dbh->prepare("SELECT username,firstname FROM users WHERE id = :id");
$sth->execute(array(':id'=>$required_id));

If using execute. If using bindParam:

$sth = $dbh->prepare('SELECT username,firstname FROM users WHERE id = ?');
$sth->bindParam(1, $required_id, PDO::PARAM_INT);
$sth->execute();

However, AFAIK you can match like this too:

$sth = $dbh->prepare('SELECT username,firstname FROM users WHERE id = :id');
$sth->bindParam(':id', $required_id, PDO::PARAM_INT);
$sth->execute();

Or like this:

$sth = $dbh->prepare('SELECT username,firstname FROM users WHERE id = ?');
$sth->execute(array($required_id));

WHich is what you seem to have already.

//EDIT

I think I see - due to the fetchAll, you're returning an array, not a single record, so to get the first record:

echo $user_info[0]['username'];