PDO bindParam not allowing statement to return results

Question

I have a pdo statement to select rows and return them in an array. I am using pagination to display 12 results per page. If I directly input LIMIT 12, 12 , instead of LIMIT ?, ? the statement works correctly.

What am I doing wrong with the bindParam for both variables?

Both variables contain the correct data.

Heres the function I'm using:

// Retrieve active posts
function retrieve_active_posts(){
    //test the connection
    try{
        //connect to the database
        $dbh = new PDO("mysql:host=db2940.oneandone.co.uk;dbname=db348699391","xxx", "xxx");
    //if there is an error catch it here
    } catch( PDOException $e ) {
        //display the error
        echo $e->getMessage();

    }

    // Get all the posts
    $stmt = $dbh->prepare(" SELECT  p.post_id, post_year, post_desc, post_title, post_date, img_file_name, p.cat_id
                            FROM    mjbox_posts p
                            JOIN    mjbox_images i
                            ON      i.post_id = p.post_id
                                    AND i.cat_id = p.cat_id
                                    AND i.img_is_thumb = 1
                                    AND post_active = 1
                            ORDER BY post_date
                            DESC
                            LIMIT ?,?");
    $stmt->bindParam(1, $limit);
    $stmt->bindParam(2, $offset);                       
    $stmt->execute();


    while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

            $resultarray[] = $row;
    }

    return $resultarray;
}

Many thanks

Incidentally, why loop over the resultset with `fetch()` to build an array when PDO provides [`fetchAll()`](http://php.net/manual/en/pdostatement.fetchall.php) for you? — eggyal, Apr 06 '13 at 16:40
You can / should use `fetchAll()` if you just need to return the result array. Sometimes there are situation when it is nessesary to *touch* each record before returning it (for whatever reason). In such caes `fetch()` would fit. — hek2mgl, Apr 06 '13 at 16:44
Also, I think you have the parameters ordered the wrong way around. The offset comes first, followed by the number of records to be returned (unless one includes the `OFFSET` keyword in which case the order is reversed). — eggyal, Apr 06 '13 at 16:46

hek2mgl · Accepted Answer · 2018-12-04T12:12:51.263

4

Make sure that the type of $limit and $offset is set to PDO::PARAM_INT:

$limit = 20;
$offset = 0;

$stmt->bindParam(1, $limit,  PDO::PARAM_INT);
$stmt->bindParam(2, $offset, PDO::PARAM_INT);

edited Dec 04 '18 at 12:12

answered Apr 06 '13 at 16:36

hek2mgl

152,036
28
249
266

I tried your example exactly as you wrote it and i get this error `Fatal error: Cannot pass parameter 2 by reference` – crmepham Apr 06 '13 at 16:47
Really? (cannot believe that). Try my updated example using variables – hek2mgl Apr 06 '13 at 16:49
1

I guess the error is triggered by other parts of your code. The above example should work – hek2mgl Apr 06 '13 at 16:53
1

Your right, i forgot to pass the variable to the function #facepalm thanks – crmepham Apr 06 '13 at 16:57

score 0 · Answer 2 · answered Dec 02 '21 at 22:31

0

Right after connect to the database, insert the following line of code:

$conn->setAttribute( PDO::ATTR_EMULATE_PREPARES, false);

Source: https://www.php.net/manual/en/pdo.setattribute.php

answered Dec 02 '21 at 22:31

MMJ

555
4
6

PDO bindParam not allowing statement to return results

2 Answers2

Linked