PHP PDO fetchAll() returns empty array

Question

This is my code

$PageNumber = $_GET["p"];
if (!isset($PageNumber) || $PageNumber < 1 || !is_int($PageNumber))
    $PageNumber = 1;
$PageSize = 3;
$strSQL = $dbCon->prepare("SELECT * FROM News WHERE isActive = 1 ORDER BY NewsID DESC LIMIT (:PageNumber - 1)*:PageSize, :PageSize;");
$strSQL->bindParam(":PageNumber", $PageNumber, PDO::PARAM_INT);
$strSQL->bindParam(":PageSize", $PageSize, PDO::PARAM_INT);
$strSQL->execute();
$Result = $strSQL->fetchAll();
if (count($Result) != 0) {
    print_r($Result);
} else {
    echo "no record";
}

im sure rows exist but output is no record, When i replace parameters with default value like LIMIT 0,3 it works well.

user1432124 · Accepted Answer · 2012-07-06 10:32:58Z

2

Since you are not catching exceptions but i guess that what you are doing wrong .In PDO your bindValue or bindParam variable should be equal to used in query. I mean

$strSQL = $dbCon->prepare("SELECT * FROM News WHERE isActive = 1 ORDER BY NewsID DESC LIMIT :Start, :PageSize;");
$strSQL->bindParam(":Start",($PageNumber - 1)*$PageSize, PDO::PARAM_INT);
$strSQL->bindParam(":PageSize", $PageSize, PDO::PARAM_INT);

answered Jul 6, 2012 at 10:32

user1432124

Sign up to request clarification or add additional context in comments.

Comments

GoSmash · Accepted Answer · 2012-07-06 10:32:24Z

1

Try this, since you tried using mathematical operators inside prepared statement i just replaced them and did it at the place of assigning.

$PageNumber = $_GET["p"];
if (!isset($PageNumber) || $PageNumber < 1 || !is_int($PageNumber))
    $PageNumber = 1;
$PageSize = 3;
$strSQL = $dbCon->prepare("SELECT * FROM News WHERE isActive = 1 ORDER BY NewsID DESC LIMIT :PageNumber, :PageSize;");
$strSQL->bindParam(":PageNumber", ($PageNumber - 1) * $PageSize, PDO::PARAM_INT);
$strSQL->bindParam(":PageSize", $PageSize, PDO::PARAM_INT);
$strSQL->execute();
$Result = $strSQL->fetchAll();
if (count($Result) != 0) {
    print_r($Result);
} else {
    echo "no record";
}

answered Jul 6, 2012 at 10:32

GoSmash

1,1081 gold badge12 silver badges38 bronze badges

Comments

PhoneixS · Accepted Answer · 2012-08-09 11:08:46Z

I think that the problem is that you have used the same token two times (":PageSize").

As said in php documentation of PDO::prepare():

You cannot use a named parameter marker of the same name twice in a prepared statement.

So you can only use once each token, so you must use two different tokens, for example ":PageSizeA" and ":PageSizeB".

$strSQL = $dbCon->prepare("SELECT * FROM News WHERE isActive = 1 ORDER BY NewsID DESC LIMIT (:PageNumber - 1)*:PageSizeA, :PageSizeB;");
$strSQL->bindParam(":PageNumber", $PageNumber, PDO::PARAM_INT);
$strSQL->bindParam(":PageSizeA", $PageSize, PDO::PARAM_INT);
$strSQL->bindParam(":PageSizeB", $PageSize, PDO::PARAM_INT);

Anyway, @user1432124 or @Ramesh's solution is better because it process data in php and not in mysql, witch is where it must be done.

Collectives™ on Stack Overflow

PHP PDO fetchAll() returns empty array

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related