Php pagination with PDO

What is the equivalent of fetch_row in PDO? I’m having some troubles with my pagination pager. The displaying the rows all work, just the pagination pager doesn’t work. Here’s what I have.

$result = $this->db->query("SELECT COUNT(id) FROM users");
$get_total_rows = $result->rowCount();
$total_pages = ceil($get_total_rows[0]/$item_per_page);
$page_position = (($page_number-1) * $item_per_page);

Could you just modify your SQL query to only select a certain number of rows? E.g. if your page is 1, then, in your SQL, just select rows 1-10? Instead of trying to filter it out via PHP?

Well in MySQLi, I used a some what similar method and it works just fine. The pagination and display pages work as I want them to. This time, I want to use PDO to do the job. It’s the same codes, just in PDO form, but I can’t seem to figure it out. I was thinking of fetchAll(PDO::FETCH_ASSOC) or maybe even fetch(). Here’s the errors for both of them.

Fatal error: Unsupported operand types in /var/www/html/members.php on line 161

Line 161 has the ceil. Here’s what I have for the ceil

$total_pages = ceil($get_total_rows[0]/$item_per_page);

If I use fetch(), this is what I get.

Fatal error: Cannot use object of type stdClass as array in /var/www/html/members.php on line 161

Then if I do something like this fetch(PDO::FETCH_ASSOC). I get this error.

Notice: Undefined offset: 0 in /var/www/html/members.php on line 161

Using the first code in my first post, I get this a number 1 as the returned value with print_r. I have 20 sample records made to test in case. None of them are being called. At one point earlier, I got it to print out 20 total records which I want, but it kept saying something about the off set.

Shouldn’t $get_total_rows be a number and not an array?

PDOStatement::rowCount

Return Values

Returns the number of rows.

Scott

Yes, I see what you’re saying. However, rowCount returns the number 1. That isn’t what I want. If I apply it with just fetch() and add in as total into the query string, I get the total number of rows I want. But, I can’t seem to turn an stdClass Object into an integer. I’m getting the total number of rows I’m looking for.

$results = $this->db->query("SELECT COUNT(id) as total FROM users");
$get_total_rows = $results->fetch();
print_r($get_total_rows);

Since I have 20 rows of records, it returns 20 rows for me in an array. Only problem is, when I try to print($get_total_rows), it gives me an error saying that the stdClass could not be converted into a string. This only means that I would have to work with arrays for now as it is my only choice. How does one turn an array into a string? I’m guessing by appending the row to the $get_total_rows. I’m not sure if I am correct or not.


EDIT: Never mind. It seems like I figured it out. I should of used foreach loops for arrays. This is what I did.

$results = $this->db->query("SELECT COUNT(id) as total FROM users");
$get_total_rows = $results->fetch();
foreach($get_total_rows as $array_rows) {
}
$get_total_rows = $array_rows;
$total_pages = ceil($get_total_rows/$item_per_page);

The code returns the number 20 which is the total number of rows I have for the users table. Then I just put the total I get as an array into a foreach loop and then I try to get that number to go outside of the foreach loop so I can convert it into a string.

I think you are making this far more difficult than it needs to be. SELECT COUNT(id) as total FROM users will return one row with the number of rows in the first slot.

$dsn = 'mysql:dbname=ng2014;host=127.0.0.1';
$db  = new PDO($dsn, 'USER', 'PASSWORD');

// Throw exceptions on errors
$db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);

// Use assoc arrays by default
$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC);

$sql = 'SELECT COUNT(id) as total FROM users;';
$rows = $db->query($sql)->fetchAll();

$get_total_rows = $rows[0]['total'];
echo "Number of users: $get_total_rows\n";

Thanks. It seems that yours works very well. I guess there’s two ways of doing it, but I’ll choose your way because it should be the right way. I think my head was in the right place, but I just didn’t know what to use PDO or how the syntax works.

that’s quite a complicated way to get a single value from PDO…

PDO has a convenience method for that:

$sql  = 'SELECT COUNT(id) FROM users;';
$rows = $db->query($sql)->fetchColumn();

echo "Number of users: $rows\n";

Just in case this isnt clear and I think I can see both. Depending on how you init PDO (can be changed later) depends on how you fetch rows from it.

So if you had the following:

$sql = "SELECT * FROM table";
$query = $this->db->prepare($sql);
$query->execute();

PDO::FETCH_ASSOC is like the MySQLi syntax where you use the array…

while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
    echo $row['columnname'];
}

PDO::FETCH_OBJ
You would fetch them by object like so:

$allrows = $query->fetchAll();

foreach ($allrows as $row){
echo $row->columnname;
}

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.