PHP - (PDO) Execute with binding variables

Question

I just started playing around with PDO and I am trying to create a function that will display all the data for a given table name. After reading a few posts here I found a solution that I can get working (shown below with a hard-coded select statement). However, I can't get my execute statements to work when I bind my field names (I get an exception similar to: Undefined index: person_id). I should mention my class extends PDO:

 /*********************************************************************
 *Function  showTable
 *Purpose   Display all information for a given table.
 *Params    $sTable -> Table name
 ********************************************************************/
public function showTable($sTable)
{
    $result;

    try
    {
        if(isset($sTable))
        {
            //create a result in a table format
            $result = "<table>";
            //$stmt = $this->prepare('DESCRIBE :sTable');
            $stmt = $this->prepare('DESCRIBE ' . $sTable);

            //$stmt->bindParam(':sTable', $sTable); 
            $stmt->execute();

            //array version of the column names
            $aCols = $stmt->fetchAll(PDO::FETCH_COLUMN);
            //string version of the column names
            $sCols = implode (", ", $aCols);

            //$stmt = $this->prepare('SELECT :fields FROM :sTable');
            //$stmt = $this->prepare('SELECT :fields FROM person');
            $stmt = $this->prepare('SELECT person_id, first_name, last_name FROM person');

            //$stmt->execute(array(':fields'=>$sCols, 'stable'=>$sTable));
            //$stmt->execute(array(':fields'=>$sCols));
            $stmt->execute();

            while($row = $stmt->fetch(PDO::FETCH_ASSOC))
            {
                var_dump($row);
                $result = $result . "<tr>";
                foreach($aCols as $col)
                {
                    //var_dump($row);
                    $result = $result . " <td>" . $row[$col]. "</td>";
                }
                $result = $result . "</tr>";
            }
            $result = $result . "</table>";
        }
        return $result;
    }
    catch(PDOException $e)
    {
        if($this->bDebug)
        {
            echo $e->getMessage();
        }
    }
}

Like I said the hard coded select string works but when i comment out the hard coded and uncomment the execute with a bind it throws exceptions.

bwoebi · Accepted Answer · 2013-08-28 01:24:54Z

2

You cannot insert identifiers or keywords this way.

PDOStatement::execute() will put the value in escaped form inside single quotes. Your query would look like:

SELECT 'col1, col2' FROM person

What is invalid MySQL syntax.

A valid example:

$stmt = $this->prepare('SELECT col FROM person WHERE name = :name');
$stmt->execute(array(':name' => $name));

It works, because it's a value you insert here; and not an keyword or identifier.

answered Aug 28, 2013 at 1:24

bwoebi

23.8k5 gold badges64 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Travis Over a year ago

Oh, I understand now. Thank you very much. I'll set as accepted answer as soon as I can.

Travis Over a year ago

With that information I got it working the way I wanted, thanks again

Collectives™ on Stack Overflow

PHP - (PDO) Execute with binding variables

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related