duanqing2209 2017-09-12 08:42
浏览 101
已采纳

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

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

  • 写回答

2条回答 默认 最新

  • douqian1835 2017-09-12 08:54
    关注

    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);
    
    ?>
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?