php pdo query not working with array

Question

I have been doing this for quite a few queries but suddenly found one where it is not working. Here is my code:

$q = 'SELECT title FROM blog LIMIT :paging,:perpage';
$v = array(
    ':paging'=>(($page-1)*$perpage),
    ':perpage'=>$perpage
);
$sql = $conn->prepare($q);
$sql->execute($v);
if ($sql){
    foreach($sql as $rs){
        $title = $rs['title'];
        echo '<article>'.$title.'</article>';
    };
};

I am not getting any errors however It is also not displaying anything. When I take out $v and just put the values into the query like so:

$q = 'SELECT title FROM blog LIMIT '.(($page-1)*$perpage).','.$perpage.' ';

This works perfectly and outputs fields.

I have does this same array trick on many other queries in the past and never had any problems. Not sure what I did wrong here, need a fresh pair of eyes.

also $page = 1 and $perpage = 2

Kylie · Accepted Answer · 2013-06-26 22:46:44Z

4

Its because when you use an array for execute, it defaults to PDO::PARAM_STR, so it thinks its a string...which obviously doesn't play well with LIMIT

Instead use bindValue, that way you can explicitly state PDO::PARAM_INT

 $q = 'SELECT title FROM blog LIMIT :paging,:perpage';
 $sql = $conn->prepare($q);

 $sql->bindValue(':paging',(($page-1)*$perpage), PDO::PARAM_INT);
 $sql->bindValue(':perpage',$perpage, PDO::PARAM_INT);
 $sql->execute();

edited Jun 26, 2013 at 22:46

answered Jun 26, 2013 at 22:26

Kylie

11.8k11 gold badges51 silver badges81 bronze badges

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

7 Comments

Lawrence Cherone Over a year ago

@MihaiStancu Your wrong, this answer is correct, execute() 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.

Mihai Stancu Over a year ago

They are not treated as string (nor would it matter if they are). They type is inspected and they are treated according to type.

kqlambert Over a year ago

Thanks bindParam almost worked, changed it to bindValue and that came through for me. Have to wait for time limit to accept this as the answer

Sean Over a year ago

@MihaiStancu from your link -

public bool PDOStatement::execute ([ array $input_parameters ] ) :: 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.

All values are treated as strings

Kylie Over a year ago

Its not that yours worked or mine worked or whatever...its that I provided solid reasoning behind why his wasnt working, and some code...you didn't :) Effort pays off sometimes

|

Liam Sorsby · Accepted Answer · 2013-06-26 22:26:10Z

3

You need to use bindValues to bund the values to the query the use execute with no parameters!

http://php.net/manual/en/pdostatement.bindvalue.php

answered Jun 26, 2013 at 22:26

Liam Sorsby

3,0023 gold badges30 silver badges52 bronze badges

7 Comments

Mihai Stancu Over a year ago

Execute is meant to allow you to specify all bindvalues in one array; php.net/manual/ro/pdostatement.execute.php; OP's code looks correct at first glance.

Nir Alfasi Over a year ago

@MihaiStancu he didn't say you can't use an array - he just said that the OP forgot to bind the params

Mihai Stancu Over a year ago

OP didn't forget... he used ->execute($v)

Nir Alfasi Over a year ago

@MihaiStancu he neither used bind nor sent the array to execute

Lawrence Cherone Over a year ago

+1 as technicality correct, though a simple example will encourage people to upvote.

|

Mihai Stancu · Accepted Answer · 2013-06-26 22:26:57Z

-1

You're not getting any PHP errors but you're not checking for SQL errors at all. You need to explicitly check for SQL errors.

try {
    $dbh = new PDO($dsn, $user, $password);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

answered Jun 26, 2013 at 22:26

Mihai Stancu

16.2k2 gold badges35 silver badges51 bronze badges

2 Comments

Nir Alfasi Over a year ago

downvoting without commenting (or as a childish "revenge") is lame!

Mihai Stancu Over a year ago

My answer directs OP to investigate his SQL error and see for himself what the problem is. It's not an answer per se so I have no problem with their downvotes because it doesn't provide a solution.

Collectives™ on Stack Overflow

php pdo query not working with array

3 Answers 3

7 Comments

7 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related