Having trouble getting my prepared statement to work

Question

I have the following function :

function get_config($name = '')
{   
    $config = array();
    $row = array();
    $sql = "SELECT * FROM ".$GLOBALS['prefix']."config ";
    if ('' != $name) {$sql .= " WHERE name = '". $name ."'";}
    $result = $GLOBALS['conn']->query($sql);
    while ($row = $result->fetch()) {$config[$row['name']] = $row['value'];}
    if ('' != $name){return $config[$name];}
}

I use this to call all my site settings. I wanted to use prepared statements for this.

Here is what I did when I added the prepare statement:

function get_config($name = '')
{   
    $config = array();
    $row = array();
    $sql = "SELECT * FROM config";
    if ('' != $name) {
    $sql .= " WHERE name = 'home'";
    }
    $result = $GLOBALS['conn']->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
    $result->execute(array(':name' => $name));
    while ($row = $result->fetch()) {
    $config[$row['name']] = $row['value'];
    }
    if ('' != $name) {
    return $config[$name];
    }
}

But now the function is not working. why is not working ?

possible duplicate of [PHP PDO prepared statements](http://stackoverflow.com/questions/1457131/php-pdo-prepared-statements) — mario, Feb 24 '13 at 21:51
If you are using this for a flat key:value configuration store, it might make sense to fetch all entries at once, and keep them in an array instead. — mario, Feb 24 '13 at 21:51
@mario what else can I do other than that? because that is what I am really trying to do, if there is a better way, tell me about it. — syrkull, Feb 24 '13 at 21:58
Make a `$prepvars = array()` and only add `[":name"] = $name` in the `if` condition. Then use `$prepvars` for the `->execute()` call. — mario, Feb 24 '13 at 22:24

score 3 · Accepted Answer · edited May 23 '17 at 10:24

3

Migrating my answer from the (now removed) duplicate.

You're executing the statement with a :name placeholder, but it's not there in the query itself:

function get_config($name = 'home')
{   
    $config = array();
    $row = array();
    $sql = "SELECT value FROM config WHERE name = :name";

    $result = $GLOBALS['conn']->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
    $result->execute(array(
        ':name' => $name
    ));

    return array(
        $name => current($result->fetchALL(PDO::FETCH_COLUMN, 0))
    );
}

What changed

The two main changes:

function get_config($name = 'home')

When the function is called without parameters, $name defaults to 'home'.

return current($result->fetchAll(PDO::FETCH_COLUMN, 0));

The fetchAll() returns an array with only the first column in each array entry. Applying current() on that either returns the first (and only) array entry or false if the array is empty.

Caution

Using database handles from $GLOBALS is not recommended; it would be better to either pass the instance in get_config() or let get_config() be a method in a class that receives as the database handle in their constructor.

edited May 23 '17 at 10:24

Community

1
1

answered Feb 24 '13 at 22:36

Ja͢ck

170,779
38
263
309

it would be great if you can explain Those two lines : `':name' => $name ?: 'home'` and `current($result->fetchALL(PDO::FETCH_COLUMN, 0));` – syrkull Feb 24 '13 at 22:44
for some reason, I am only getting the first letter of the result.. what can it be the problem? – syrkull Feb 24 '13 at 23:00
@shnisaka What does `print_r($result->fetchALL(PDO::FETCH_COLUMN, 0))` give then? – Ja͢ck Feb 24 '13 at 23:01
whenever I change it with `current()` it gives me the first letter.. in this case "h" – syrkull Feb 24 '13 at 23:05
@shnisaka Well, `current()` on that should just give `http://www.example.com`. [Proof](http://codepad.viper-7.com/X5W4fm). If not, the problem is somewhere else. – Ja͢ck Feb 24 '13 at 23:05
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/25055/discussion-between-shnisaka-and-jack) – syrkull Feb 24 '13 at 23:06

Having trouble getting my prepared statement to work

1 Answers1