应用LIMIT时，带INNER JOIN的DWL不起作用

Question

I'm trying to create a simple pagination using Bootpag.js in order to fetch my data I've created a PDO script with an INNER JOIN because I need to get and display the user team names from another table also I need to apply LIMIT to set the page selection.

This is the troublesome code,

session_start();
include_once("../iConnect/handShake.php");
include_once ("../Functions/userCheck.php");

if (isset($_REQUEST["page"])){
    $page_number = filter_var($_POST["page"], FILTER_SANITIZE_NUMBER_INT, FILTER_FLAG_STRIP_HIGH);
    if(!is_numeric($page_number)){die('Invalid page number!');} //incase of invalid page number
}else{
    $page_number = 1;
}
$perpage = 3;
//get current starting point of records
$position = (($page_number-1) * $perpage);

//Data base join to get team names from teams data base
$getUsers = "SELECT userlogin.*, teams.TeamName FROM userlogin INNER JOIN teams ON teams.tId = userlogin.uTeam ORDER BY uId DESC LIMIT :place, :item_per_page";
$getUsersQuery = $dbConnect -> prepare($getUsers);
$getUsersQuery -> bindParam(':place', $position);
$getUsersQuery -> bindParam(':item_per_page', $perpage);
$getUsersQuery -> execute();

I tried the same SQL in phpMyAdmin it worked with out any errors I don't know why it throwing the below error when used with PHP + PDO.

Note: About the duplicate I've been searching the site using bindParam but I didn't see the duplicate and well it really didn't solve my issue the selected answer for this did any way if get marked what

Error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[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', '3'' at line 1' in ********\fetchUserList.php on line 27

Answer 1

When in emulation mode (which is on by default),PDO substitutes placeholders with actual data, instead of sending it separately.PDO treats every parameter as a string. As a result, the prepared LIMIT ?,? query becomes LIMIT '0', '3' which is invalid syntax that causes query to fail.

When you using bindParam each variables explicitly you need to set the proper param type.

you might need to turn off emulation by :

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

Then

<?php

$getUsersQuery -> bindParam(':place', $position,PDO::PARAM_INT);
$getUsersQuery -> bindParam(':item_per_page', $perpage,PDO::PARAM_INT);

?>