CODEWITHSUNDEEP
phpstringintegerpdoprepared-statement
Run
Run

Reputation: 57176

PHP PDO: how to return integer

I think it might be a flaw in my pdo fetching data method,

public function fetch_all($query, $params = array())
    {
        try
        {
            # prepare the query
            $stmt = $this->connection->prepare($query);

            # if $params is not an array, let's make it array with one value of former $params
            if (!is_array($params)) $params = array($params);

            # execute the query
            $stmt->execute($params);

            # return the result
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        }
        catch (PDOException $e) 
        {
            # call the get_error function
            $this->get_error($e);
        }
    }

All the parameters that have been passed into this method will become strings, but I need integers for sql LIMIT query, such as below

$sql = "
    SELECT *

    FROM root_pages
    ORDER BY root_pages.pg_created DESC

    LIMIT ?,?";

items = $connection->fetch_all($sql,array('0','6'));

It returns this error,

2SQLSTATE[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 ''0','6'' at line 32

How can I fix it?

EDIT:

As suggested, I changed the code in my method to this below,

# fetch a multiple rows of result as a nested array ( = multi-dimensional array)
    public function fetch_all($query, $params = array())
    {
        try
        {
            # prepare the query
            $stmt = $this->connection->prepare($query);

            # if $params is not an array, let's make it array with one value of former $params
            //if (!is_array($params)) $params = array($params);


            foreach($params as $k=>$p){
              if(is_numeric($p)){
                $stmt->bindParam($k+1, $p, PDO::PARAM_INT);
              }
              else{
                $stmt->bindParam($k+1, $p, PDO::PARAM_STR);
              }
            }
            $stmt->execute();


            # execute the query
            //$stmt->execute($params);

            # return the result
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        }
        catch (PDOException $e) 
        {
            # call the get_error function
            $this->get_error($e);
        }
    }

$items = $connection->fetch_all($sql,array(0,6));

then I get a different error,

2SQLSTATE[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 ''6'' at line 32

EDIT:

I just changed it to,

if(is_int($p)){..}

but still getting the same error... sigh...

Upvotes: 1

Views: 7343

Answers (4)

Charles
Charles

Reputation: 51411

You can't do this via placeholders.

PDO always quotes params that aren't null, even when they're integers. Normally this isn't such a bad thing, but LIMIT clauses can not handle quoted integers.

You're going to need to fall back to good old fashioned concatenation. Because you know that these are going to be integers, you can treat them safely in your code by calling intval or casting them prior to concatenation. 

$limit = intval($thing_that_provides_limit);
$offset = intval($thing_that_provides_offset);
$sql = "
    SELECT *

    FROM root_pages
    ORDER BY root_pages.pg_created DESC

    LIMIT {$offset}, {$limit}";

Upvotes: 3

Marc B
Marc B

Reputation: 360602

Try:

$items = $connection->fetch_all($sql,array(0,6));

Note the lack of quotes around the 0 and 6 - this makes PHP treat them as the integers they are, and not the strings you had.

Upvotes: -2

gen_Eric
gen_Eric

Reputation: 227220

Try removing the quotes around the 0 and the 6:

$connection->fetch_all($sql,array(0,6));

This should make the query:

LIMIT 0,6

With quotes on the 0 and the 6, the query is:

LIMIT '0','6'

EDIT: Call bindParam before you call execute.

foreach($params as $k=>$p){
  if(is_int($p)){
    $stmt->bindParam($k+1, $p, PDO::PARAM_INT);
  }
  else{
    $stmt->bindParam($k+1, $p, PDO::PARAM_STR);
  }
}
$stmt->execute();

And then call fetch_all like so:

$connection->fetch_all($sql,array(0,6));

Upvotes: 2

diegocstn
diegocstn

Reputation: 4667

you should pass your parameter with type PDO::PARAM_INT, like:

$sth->bindParam(':limit', $limit, PDO::PARAM_INT);

Upvotes: 7

Related Questions