0

I have a method getRows that I use to extract multiple rows from the database with parameters $query and $params. Here it is:

public function getRows($query, $params = [])
{
    try {
        $stmt = $this->conn->prepare($query);
        $stmt->execute($params);
        return $stmt->fetchAll();
    } catch (PDOException $ex) {
        throw new Exception($ex->getMessage());
    }
}

When I use it like this: 

$this->db->getRows("SELECT * FROM news ORDER BY id DESC LIMIT $startFrom, $perPage")

Everything is ok and works fine. However, when I pass $startFrom and $perPage to the method's $params array, things get hairy. I start getting errors.

The problematic query: 

$this->db->getRows("SELECT * FROM news ORDER BY id DESC LIMIT ?, ?", [$startFrom, $perPage]);

returns the following error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''45', '5'' at line 1' in D:\xampp\htdocs\MVC\App\Core\Database.php:50 Stack trace: #0 D:\xampp\htdocs\MVC\App\Core\Database.php(50): PDOStatement->execute(Array) #1 D:\xampp\htdocs\MVC\App\Models\News\NewsRepository.php(28): App\Core\Database->getRows('SELECT * FROM n...', Array) #2 D:\xampp\htdocs\MVC\App\Controllers\news.php(24): App\Models\News\NewsRepository->index('10', 5) #3 [internal function]: News->index() #4 D:\xampp\htdocs\MVC\App\Core\App.php(35): call_user_func_array(Array, Array) #5 D:\xampp\htdocs\MVC\App\init.php(28): App\Core\App->__construct() #6 D:\xampp\htdocs\MVC\Public\index.php(3): require_once('D:\xampp\htdocs...') #7 {main} thrown in D:\xampp\htdocs\MVC\App\Core\Database.php on line 50

I tried googling for the problem and I found that it could be if the array is making the integers into strings, but I checked by var_dumping the $stmt befoce executing and the keys in the array were integers, so I don't think this is the reason. Anyone know why this is happening? Why are there single quotes added to my values e.g ''45', '5''

user3628807
  • 325
  • 3
  • 12
  • → [PDO prepared statement causes an error in LIMIT statement](https://stackoverflow.com/a/15991623/476) – deceze Sep 12 '17 at 14:18
  • from http://php.net/manual/en/pdostatement.execute.php: "input_parameters An array of values with as many elements as there are bound parameters in the SQL statement being executed. **All values are treated as PDO::PARAM_STR.**" (my bold). You can't use this shorthand method to send anything except strings by the looks of it. Use the bindParam or bindValue method (as appropriate for you) instead. – ADyson Sep 12 '17 at 14:20

0 Answers0