Pdo bind limit and offset pagination variable not bringing any result

MySQL database
#1

I am having difficulties setting attributes. Can someone help rewrite this code so that it can echo correct rows when $limi variable and $offset variable is passed.

Someone recommended $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE) . I couldn’t implement it with this my code I gave here. Therefore, I needed someone to put it in the right place and the code work.

connection class

protected function connect(){
		$dsn='mysql:host='.$this->host.';dbname='.$this->dbName;
		$pdo = new PDO($dsn, $this->user, $this->pwd);
		$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
		return $pdo;
	}

Another class that uses the connect method($this) of the connection class.

protected function getAlllimitedprivilege($privilege,$status,$company, $limit, $offset){
	
		$sql="SELECT * FROM `privilege` WHERE `privilege`=? AND `status`=? AND `company`=? ORDER BY `id` DESC LIMIT ? OFFSET ?";
		$stmt= $this->connect()->prepare($sql);
		
		$stmt->execute([$privilege,$status,$company, $limit, $offset]);
		$user=$stmt->fetchAll();
		return $user;

	}

Code where the variables are passed


 $list=$usersview->showAllprivilege('Friends','APPROVED',$_SESSION['company_id'], $limit, $offset);
#2

Well, it is $offset, $limit Not the other way around! You can use $limit by itself, but, if you add in an $offset, you place that first…

#3

I have did like you said. No result is been echo still. I thank you for you efforts. I am still expecting another solution to try. Thanks if values are passed directly like limt 3, offset 4, it works. I passed CAST(? AS UNSIGNED), it doesn’t query anything as well

#4

Well, you showed us a connection and a getAlllimitedprevilege() function.
Then, you call a showAllprivilege() function. So, we can not see this function.

Have you tested these calls in a test file that just opens the connect and makes the query?
What errors are you getting in your servers log files? More info please…

#5

Maybe this will help ->

<?php


namespace Miniature;

use PDO;
class Database {

    private $_connection;
    // Store the single instance.
    private static $_instance;

    // Get an instance of the Database.
    // @return Database:
    protected static function getInstance(): Database
    {
        if (!self::$_instance) {
            self::$_instance = new self();
        }
        return self::$_instance;
    }

    public static function pdo(): PDO
    {
        $db = static::getInstance();
        return $db->getConnection();
    }

    // Constructor - Build the PDO Connection:
    public function __construct() {
        $db_options = array(
            /* important! use actual prepared statements (default: emulate prepared statements) */
            PDO::ATTR_EMULATE_PREPARES => false
            /* throw exceptions on errors (default: stay silent) */
        , PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
            /* fetch associative arrays (default: mixed arrays)    */
        , PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        );
        $this->_connection = new PDO('mysql:host=' . DATABASE_HOST . ';dbname=' . DATABASE_NAME . ';charset=utf8', DATABASE_USERNAME, DATABASE_PASSWORD, $db_options);
    }

    // Empty clone magic method to prevent duplication:
    private function __clone() {

    }

    // Get the PDO connection:
    protected function getConnection(): PDO
    {
        return $this->_connection;
    }

}

and to use it simply do this ->

/*
 * Pagination static function/method to limit
 * the number of records per page. This is
 * useful for tables that contain a lot of
 * records (data).
 */
public static function page($perPage, $offset, $loc = 'index'): array
{
    $sql = 'SELECT * FROM ' . static::$table . ' WHERE page=:page ORDER BY date_updated DESC LIMIT :perPage OFFSET :blogOffset';
    $stmt = Database::pdo()->prepare($sql); // Prepare the query:
    $stmt->execute(['perPage' => $perPage, 'blogOffset' => $offset, 'page' => $loc]); // Execute the query with the supplied data:
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

for a more in depth look here’s my Github repository - https://github.com/Strider64/Miniature01282021

I personally just restrict the pages themselves if I only want certain people to have access to them:

Login::is_login($_SESSION['last_login']);

#6

I have gone through the error log. That’s no error there. Thanks

#7

Well, try adding this at the top of the page that fails. These turn on all of the error reporting. Might help!

error_reporting(E_ALL);
ini_set(“display_errors”, 1);

#8

There is no error in the code as far as I know. It is a bug issue with limit clause on php oop

#9

Just debug it. Add this line after $sql=… die($sql);
This will end the program and display the query. Then, you can see what it is really getting and fix it.

#10

That’s because you don’t have any error handling for the database statements (other than the connection, which always uses an exception for an error.) You need to set the PDO error mode to use exceptions, so that all the rest of the database statements use exceptions for errors.

An emulated prepared query, by default, treats all values as strings, which the LIMIT clause doesn’t accept, and produces a query error.

When you make the connection, you ARE setting the default fetch mode to assoc. This is good, since you won’t have to specify the fetch mode in each fetch statement. You then need to set the error mode to exceptions and set emulated prepared queries to false.

The reason no one did this for you is because there is no learning involved in copying code that someone else writes. Without learning the meaning of the statements (words) making up the code, you cannot find and fix problems in existing code, nor can you write original code that does something new.

You already have one working $pdo->setAttribute(…) statement in your code, for the default fetch mode setting. How about look at that line of code and use deduction and inference to determine what a line of code would look like for the emulated prepared query setting that someone recommend. Then do the same for the error mode setting. Note: you can find the setAttribute() setting names and values in the documentation - PHP: PDO::setAttribute - Manual Also, you can set these attributes using an array when you make the connection. @Strider64, included an example, with these same exact settings, in the code he posted, in case all you want to do is copy something.

Sponsor our Newsletter | Privacy Policy | Terms of Service