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;
}
}
}
source to share
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!
source to share
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
source to share