PHP PDO bindValue in LIMIT

Question

Someone please help me, why do I get always this error, and what is the solution:

Failed: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''5'' at line 1

class.database.inc.php

I wrote this class to somewhat automate my sql query. Works well, except when I want to bind value to the SQL LIMIT operation:

class Database extends PDO
{
    private $engine;
    private $host;
    private $database;
    private $user;
    private $pass;

    private $tablename;
    private $valueArray;
    private $optionArray;

    public function __construct($engine,$charset,$host,$database,$user,$pass)
    {
        $this->engine   = $engine;
        $this->charset  = $charset;
        $this->host     = $host;
        $this->database = $database;
        $this->user     = $user;
        $this->pass     = $pass;
        $dns = $this->engine.':dbname='.$this->database.';charset='.$this->charset.';host='.$this->host;
        parent::__construct($dns,$this->user,$this->pass);
        $this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }

    private function runSQL($sql=NULL,$valueArray=NULL)
    {
        $sth = $this->prepare($sql);
        if(isset($valueArray))
        {
            foreach($valueArray as $values)
            {
                foreach($values as $key => $value)
                {
                    $paramDataType = $this->getPdoParamDataType($value);
                    $sth->bindValue($key,$value,$paramDataType);
                }
                $sth->execute($values);
            }
        }
        else
        {
            $sth->execute();
        }
        return $sth;
    }

    private function getPdoParamDataType($variable)
    {
        $variableType = gettype($variable);
        switch ($variableType)
        {
            case 'integer':
                return PDO::PARAM_INT;
                break;
            case 'string':
                return PDO::PARAM_STR;
                break;
            case 'double':
                return PDO::PARAM_STR;
                break;
            case 'boolean':
                return PDO::PARAM_BOOL;
                break;
            case 'NULL':
                return PDO::PARAM_NULL;
                break;
        }
    }

    private function fetchRows($sth=NULL)
    {
        $result = $sth->fetchAll(PDO::FETCH_ASSOC);
        return $result;
    }

    private function getTableStructure($tableName=NULL)
    {
        $this->tableName  = $tableName;
        $sql = 'DESCRIBE '.$this->tableName;
        $tableStructureArray = $this->fetchRows($this->runSQL($sql));
            return $tableStructureArray;
    }

    public function getRecords($tableName=NULL,$options=NULL,$valueArray=NULL)
    {
        $this->tableName  = $tableName;
        $this->valueArray = $valueArray;
        $options=' '.$options;
        $columnFields = $this->getTableStructure($this->tableName);
        $sql = 'SELECT '.implode(', ',$columnFields).' FROM '.$tableName.$options;
        return $this->fetchRows($this->runSQL($sql,$this->valueArray));
    }
}

test.php

And that's how I use it:

function __autoload($class_name)  
{  
    include_once 'classes/class.' . $class_name . '.inc.php';  
} 
$pdo = new Database('mysql','utf8','localhost','database','password','');
try
{
    $valueArray = array(
                        array(':limit'=>5)
                        );
    $pdo->beginTransaction();
    $result = $pdo->getRecords('my table name','LIMIT :limit',$valueArray);
    $pdo->commit();
}
catch (Exception $e)
{
  $pdo->rollBack();
  echo "Failed: " . $e->getMessage();
}

If I change this line:

$result = $pdo->getRecords('my table name','LIMIT :limit',$valueArray);

To this:

$result = $pdo->getRecords('my table name','LIMIT 5');

it works well.

Any idea what could be wrong?

Note that the error message says `''5''` with two single quotes, indicating that your value was bound as string still. So ỳour `$this->getPdoParamDataType` didn't succeed. — mario, Mar 12 '13 at 23:10
it must be something like this : http://stackoverflow.com/questions/2269840/php-pdo-bindvalue-in-limit — psadac, Mar 12 '13 at 23:36

Matt · Accepted Answer · 2013-03-12T23:37:58.767

1

The entire foreach here is a NOOP since passing the variables on execute overwrites this.

foreach($values as $key => $value) {
    $paramDataType = $this->getPdoParamDataType($value);
    $sth->bindValue($key,$value,$paramDataType);
}
$sth->execute($values); // this makes the previous loop a NOOP.

I would recommend you delete it and also do not try to explicitly set the data type. PDO will infer the correct type.

Also, I bet that if you echo $sql right before preparing it, the syntax error will be obvious.

edited Mar 12 '13 at 23:37

answered Mar 12 '13 at 23:22

Matt

1,287
2
11
25

Thanks for the reply. I was blind. The reasons mentioned by Mario I have to explicitly specify the data type. – Viktor Mar 13 '13 at 08:51

PHP PDO bindValue in LIMIT

class.database.inc.php

test.php

1 Answers1