Page will not echo results with PDO statement

Question

My function does not want to load. I am trying to echo out id, name, and age. What am I doing wrong?

<?php

$db_host = "localhost";
$db_username = "root";
$db_pass = "";
$db_name = "testdb";

$dbh = new PDO('mysql:host='.$db_host.';dbname='.$db_name,$db_username,$db_pass);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);

function getManger () {

    global $con;

    $sql= $dbh->prepare("SELECT * FROM test_tbl");
    $stmt = $db->prepare($sql); 
    $stmt->execute();    

    while( $row = $stmt->fetch(PDO::FETCH_ASSOC) ) { 

        $id= $row['id'];
        $name = $row['name'];
        $age - $row['age'];

        echo "<li>$id</li>";
        echo "<li>$name</li>";
        echo "<li>$age</li>";

    }

}

?>

What is `$con`? What is `$age - $row['age'];`? Where do you call `getManger()`? — showdev, Nov 14 '14 at 00:33
I don't think that your function getManger() is executed, at least not in the code you have showed to us... — andreas, Nov 14 '14 at 00:33

score 3 · Answer 1 · edited Nov 14 '14 at 00:51

You use

global $con;

But do not reference it anymore inside the function, instead you reference $dbh which is out of function scope as it's nor passed not set to be a global reference, so options may be:

function getManger () {
global $dbh;

$sql= $dbh->prepare("SELECT * FROM test_tbl");

...
}

Or

function getManger ($DBCon) {

$sql= $DBCon->prepare("SELECT * FROM test_tbl");
...
}

Then call the function:

getManager($dbh);

This will pass the database connection variable directly into the functions parameters to be used internally. I'd more recommend this over using global

Further more. It doesn't seem like you're even calling the function in question based on the code. Are you sure you're doing that?

Other typos inside the function may be:

$age - $row['age'];

Which you're currently telling PHP to subtract $row['age']; from an integer stored in $age, perhaps you mean:

$age = $row['age'];

and as pointed by @fred-ii in the comments. You are calling prepare twice, which is unnecessary and could fire an error, depending on your error reporting setup.

$sql= $dbh->prepare("SELECT * FROM test_tbl");
$stmt = $db->prepare($sql);

Should be either 1)

$sql = "SELECT * FROM test_tbl";
$stmt = $dbh->prepare($sql);

OR

$stmt = $dbh->prepare("SELECT * FROM test_tbl");

but also a possible typo in the second prepare command?

$stmt = $db->prepare($sql);

Plus, is using `prepare` twice and not to mention using the wrong variable again `$stmt = $db->prepare($sql);` — Funk Forty Niner, Nov 14 '14 at 00:38
your right I need to call function on the page or on a separate page ty — Veronica, Nov 14 '14 at 00:39
@Fred-ii- Still going over the complications in OPS code, got the main problems nailed in the original post, now just time to edit to make it perfect — Daryl Gill, Nov 14 '14 at 00:39

score 2 · Accepted Answer · edited May 23 '17 at 10:33

To outline the errors in your code, and offer a tested and working solution:

You're calling the prepare() function twice.
Using the wrong variable for your SELECT.
...using $sql where it should be $stmt
Not possibly calling your getManger() function.
Using a hyphen instead of an equal sign $age - $row['age']; => $age = $row['age'];

Rewrite:

<?php 
$db_host = "localhost";
$db_username = "root";
$db_pass = "";
$db_name = "testdb";

$dbh = new PDO('mysql:host='.$db_host.';dbname='.$db_name,$db_username,$db_pass);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);

  function getManger($dbh) {

    $stmt= $dbh->prepare("SELECT * FROM test_tbl");
    $stmt->execute();    

    while( $row = $stmt->fetch(PDO::FETCH_ASSOC) ) { 

        $id   = $row['id'];
        $name = $row['name'];
        $age  = $row['age'];

        echo "<li>$id</li>";
        echo "<li>$name</li>";
        echo "<li>$age</li>";
    }
}

// call the function
getManger($dbh);

Footnotes:

It's usually best to pass your DB connection variable inside your function, rather than making it global.

Here are a few articles on Stack about global:

The choice is yours.

ty This worked only thing is I have to figure how to get it to another page to be able to select names in a dropdown. — Veronica, Nov 14 '14 at 21:11

Page will not echo results with PDO statement

2 Answers2