Can't do pagination with php mysql pdo

[colap]

            if (isset($_REQUEST['page']) ) {
                $offset = $_REQUEST['page'];
            } else {
                $offset = 0;
            }
            $limit = 2;
            $dbh = mysql_connection();
            $sql = "select * from posts limit :limit offset :offset";
            $stmt = $dbh->prepare($sql);
            $stmt->bindParam(':limit', $limit);
            $stmt->bindParam(':offset', $offset);
            $stmt->execute();
            $result = $stmt->fetchAll();

It displays empty array(0) { } , what can be the reason?

Edited June 17, 2015 by php-coder

[requinix]

Invalid SQL.

 

SELECT Syntax

[colap]

Invalid SQL.

 

SELECT Syntax

Can't understand the syntax. What would be the correct syntax?

 

http://stackoverflow.com/questions/5508993/pdo-limit-and-offset

[requinix]

Ah, I'm wrong. LIMIT...OFFSET is perfectly legal. Someone should have RTFMed.

 

That thing you linked mentioned strings being bad. Try

$offset = (int)$_REQUEST['page'];

[Barand]

Isn't the pdo default for binding parameters "string" type and you therefore need to specify PDO::PARAM_INT?

[Barand]

Ah, I'm wrong. LIMIT...OFFSET is perfectly legal. Someone should have RTFMed.

 

 

 

I too had never noticed that OFFSET option in the LIMIT clause. Live 'n' learn.

[JenniferLawrence]

The mysql_connection() is bugging me. Are you mix matching the old deprecated mysql_* functions with PDO or is mysql_connection() going to be PDO's correct database connection?

[mac_gyver]

once you get past the query errors (see Barand's reply #5), you are using the page part of pagination incorrectly.

 

the logical page number from the link - 1, 2, 3.... needs to be converted to the offset value for the query statement by subtracting one and multiplying by the rows-per-page/limit value. since the offset value is now a calculated value, it doesn't need to be a bound parameter in the query statement. likewise, if the limit value is defined in your php code, it doesn't need to be a bound parameter.