PHP PDO: how to return integer

Question

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...

You may still need if (!is_array($params)) $params = array($params); — gen_Eric
– gen_Eric, Commented Mar 16, 2011 at 16:37
@Rocket: you are right! it is now working! thanks so much. can I ask - why $k+1? Thanks! — Run
– Run, Commented Mar 16, 2011 at 16:40
It's $k+1 because arrays in PHP are zero-indexed, but bindParam wants the 1st parameter to be 1, not 0 (and so on). — gen_Eric
– gen_Eric, Commented Mar 16, 2011 at 16:45

diegocstn · Accepted Answer · 2011-03-16 15:58:23Z

7

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

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

answered Mar 16, 2011 at 15:58

diegocstn

4,7273 gold badges23 silver badges17 bronze badges

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

1 Comment

chris Over a year ago

Not working with the LIMIT clause as it still returns a string (see accepted answer)

Charles · Accepted Answer · 2011-03-16 16:38:25Z

3

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}";

answered Mar 16, 2011 at 16:38

Charles

51.5k13 gold badges107 silver badges146 bronze badges

1 Comment

Run Over a year ago

thank you, Charles. can I ask - what is a placeholder? thanks.

gen_Eric · Accepted Answer · 2011-03-16 16:04:46Z

2

~~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));

edited Mar 16, 2011 at 16:04

answered Mar 16, 2011 at 15:54

gen_Eric

228k42 gold badges304 silver badges343 bronze badges

3 Comments

Mark Baker Over a year ago

When passing an array of params to execute for a PDO statement, all values are treated as PDO::PARAM_STR.

gen_Eric Over a year ago

@lauthiamkok: Try using bindParam to tell PDO that you're using INTs. (See edited answer)

Run Over a year ago

thanks for the help. I still get an error though... please check my edit above. thanks.

jon_darkstar · Accepted Answer · 2011-03-16 16:15:38Z

-2

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.

edited Mar 16, 2011 at 16:15

jon_darkstar

16.9k7 gold badges35 silver badges38 bronze badges

answered Mar 16, 2011 at 15:54

Marc B

362k44 gold badges433 silver badges508 bronze badges

Collectives™ on Stack Overflow

PHP PDO: how to return integer

4 Answers 4

1 Comment

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related