DWL with INNER JOIN doesn't work when LIMIT applied

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

did you turn emulation off ? also you need to set the proper param type — Masivuye Cokile, Sep 12 '17 at 08:46
Possible duplicate of [How to apply bindValue method in LIMIT clause?](https://stackoverflow.com/questions/2269840/how-to-apply-bindvalue-method-in-limit-clause) — Blank, Sep 12 '17 at 08:51

score 2 · Answer 1 · answered Sep 12 '17 at 08:52

When you are using limit / offset you need to type caste and pass as integer. Otherwise normally it take it as string

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

score 2 · Accepted Answer · answered Sep 12 '17 at 08:54

2

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

?>

answered Sep 12 '17 at 08:54

Masivuye Cokile

4,754
3
19
34

Your answer solved my problem but I've to delete this 'cos it got marked as a duplicate. I have been searching using `bindparam` not as `bindValue` so didn't see that but some one had the time to search it for me. I didn't know about the emulations part another thing to add. – S4NDM4N Sep 12 '17 at 09:02
its ok, man you may accept the answer, the emulation is turned off just after your connection has been established, – Masivuye Cokile Sep 12 '17 at 09:05
oh well I can delete another blunder any way thank you very much for your time. – S4NDM4N Sep 12 '17 at 09:08

DWL with INNER JOIN doesn't work when LIMIT applied

2 Answers2