PDO PHP & MYSQL SELECT FOUND_ROWS () always returns 0

Good afternoon, I was trying to figure out the problem in my code. I am using SQL_CALC_FOUND_ROWS and FOUND_ROWS () to get the total number of records in my database (with PDO). My problem is that FOUND_ROWS () always returns 0.

Thing is, I've modified this before, but I've done some tweaks here and there and now it stops working. I can't love me, remember that I could delete what was important and can't find any good documentation using these functions with PDO.

So far I've tried to put it in different loops, tried fetch () with different modes, tried to place the commands in a different order (i.e. before and after the while loop, etc.). I think I'm just missing something very light here, but I've been watching this thing for about 1 or 2 hours and it was driving me crazy.

So here's my code:

public function findBerichten($args)
{       
    //$offset zorg ervoor dat pagina 1 als record 0 in de database zoekt
    $offset = ($args['huidigePagina'] - 1) * $args['itemsPerPagina'];

    $sth = $this->db->DBH()->prepare("SELECT    SQL_CALC_FOUND_ROWS
                                                berichten.berichtID, 
                                                berichten.bericht, 
                                                berichten.naam, 
                                                berichten.mail
                                        FROM    `berichten` 
                                        ORDER BY berichten.datumToegevoegd DESC 
                                        LIMIT   ?, ?");
    $sth->bindParam(1, $offset, PDO::PARAM_INT);
    $sth->bindParam(2, $args['itemsPerPagina'], PDO::PARAM_INT);
    $sth->execute();
    $sth->setFetchMode(PDO::FETCH_ASSOC);

    $berichten = array();

    while($row = $sth->fetch())
    {
        $bericht = new Bericht();
        $bericht->setBerichtID(htmlentities(strip_tags($row['berichtID'])));
        $bericht->setBericht(htmlentities(strip_tags($row['bericht'])));
        $bericht->setNaam(htmlentities(strip_tags($row['naam'])));
        $bericht->setMail(htmlentities(strip_tags($row['mail'])));  
        $berichten[] = $bericht; 
    }

    $sth = $this->db->DBH()->prepare("SELECT FOUND_ROWS() as aantalBerichten");
    $sth->execute();
    $sth->setFetchMode(PDO::FETCH_ASSOC);
    $this->aantalBerichten = $sth->fetch();

    var_dump($this->aantalBerichten);

    return $berichten;
}

      

index.php

if($huidigePagina < 1)
{
    //$huidigePagina is 1
    $huidigePagina = 1;
}

//Als de huidige pagina groter is als het totaal aantal pagina's
if($huidigePagina > $totaalPaginas)
{
    //$huidigePagina is gelijk aan het totaal aantal pagina's
    $huidigePagina = $totaalPaginas;
}

$berichtDAO->findBerichten(array('huidigePagina'=>$huidigePagina, 'itemsPerPagina'=>10))

      

var_dump output: array(1) { ["aantalBerichten"]=> string(1) "0" }

If you have an idea, let me know because I'm ready to try something right now :)
Hope here this question isn't too noob! As I said, I think I am missing something very lightweight here.

change
execute () function is correct, etc. when used in an if () statement it still continues to execute code

Also; im 90% sure this has to do with the FOUND_ROWS () part of the code.

change 2, include db class and Bericht class ()
Bericht class:

<?php 

Class Bericht
{
    private $db;
    private $berichtID;
    private $bericht;
    private $naam;
    private $mail;

    public function __construct(Db $db)
    {
        $this->db = $db;
    }

    public function setBerichtID($berichtID)
    {
        $this->berichtID = $berichtID;
    }

    public function getBerichtID()
    {
        return $this->berichtID;
    }

    public function setBericht($bericht)
    {
        $this->bericht = $bericht;
    }

    public function getBericht()
    {
        return $this->bericht;
    }

    public function setNaam($naam)
    {
        $this->naam = $naam;
    }

    public function getNaam()
    {
        return $this->naam;
    }

    public function setMail($mail)
    {
        $this->mail = $mail;
    }

    public function getMail()
    {
        return $this->mail;
    }
}

      

Db class:

<?php
class Db
{
    //bij het laden van Db
    public function __construct()
    {
        //voer functie connect() uit
        $this->connect();
    }

    //functie voor het verbinding maken met en het selecteren van een database
    private function connect()
    {
        //$connection is connectie naar mysql database (met de opgegeven inlog waardes)
        $connection = mysql_connect('localhost', 'user', 'pw');

        //als er geen connectie gemaakt kan worden
        if(!$connection)
        {
            //die (voer overige code niet meer uit) en echo string + de mysql error
            die("Kan geen verbinding maken: " . mysql_error());
        }

        //selecteert de opgegeven database met de connectie ($connection)
        mysql_select_db("db", $connection);
    }

    public function DBH()
    {
        try 
        {
            $DBH = new PDO('mysql:host=localhost;dbname=db', 'user', 'pw');
            $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            return $DBH;
        }

        catch (PDOException $except)
        {
            echo $except->getMessage();
        }
    }

    //function to retrieve records -- not important
    public function getRecords($sth)
    {
        $rows = array();

        if($sth->execute() == true)
        {       
            $sth->setFetchMode(PDO::FETCH_OBJ);

            while($row = $sth->fetch())
            {
                $rows[] = $row;
            }

            return $rows;
        }

        else
        {
            return false;
        }
    }
}

      

+3


source to share


3 answers


Not really sure what is going on inside your DB class, so a few assumptions regarding this answer.

I'm guessing this worked in one step on your machine similar code, so trace_mode is not the answer.

I guess

$this->db->DBH() 

      

is the function that goes away to get a new database handler to use in the database query.

The second time you call the prepare function for DBH, a new database handler is created that has not previously saved information from another call to DBH.



The fix would be something like

$dbh = $this->db->DBH(); 

      

at the beginning of the function, then replace the preparation calls with

$dbh->prepare("(SQL)");

      

Edit: It looks like my assumption DB-> DBH () was correct!

+3


source


Have you tried setting MySQL trace mode in PHP?

In PHP 5.2.6 this value is enabled by default and may cause some errors with FOUND_ROWS()

You can do this using one of three ways:

php.ini:

mysql.trace_mode = 0

      

PHP:



ini_set("mysql.trace_mode", "0");

      

.htaccess:

php_value mysql.trace_mode "0"

      

As an alternative:

I'm not sure if the SQL_CALC_FOUND_ROWS

PDO is being passed into the second session. Have you tried to combine them into one query and get multiple recordsets? It's very easy to do

+2


source


You are missing a comma behind SQL_CALC_FOUND_ROWS in your SQL

-1


source







All Articles