PDO/PHP : Fetch Array and Prepared Statements

Question

As you can see I'm trying to fetch rows from table while using prepared statements.

$stmt = $conn->prepare("SELECT * FROM table ORDER BY date DESC LIMIT ?, 10");
$stmt->bindParam(1, $row_start, PDO::PARAM_INT);
$stmt->execute();

while($row = $stmt->fetch()) {
echo $row['title'];
echo $row['name'];
}

It doesn't echo anything.

Edit: var_dump is showing this log :

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0', 10' at line 1" }

Edit 2: Here is how I declare my $row_start

$row_start = "0";
if (isset($_GET['page']) && is_numeric($_GET['page']) && $_GET['page'] != "0" )
{
$row_start = $_GET['page'];
if ($row_start == "1") {
$row_start = "0";
}else{
$row_start = ($row_start-1) * 10;
}                       
}

Does your SQL work when you run it straight in the database? — andrewsi
– andrewsi, Commented Aug 8, 2012 at 16:42
@andrewsi Yes! if I replace ? with a number and run it, it does work. — Hassan
– Hassan, Commented Aug 8, 2012 at 16:44
Try binding your parameter explicitly as an integer $stmt->bindParam(1, $row_start, PDO::PARAM_INT); — andrewsi
– andrewsi, Commented Aug 8, 2012 at 16:46
Is there anything returned by the database? Does var_dump($stmt->errorInfo()) print anything? — andrewsi
– andrewsi, Commented Aug 8, 2012 at 16:57
@xperator - oh, and I made a mistake with the casting code, too - it should be (int) $row_start with a space. — andrewsi
– andrewsi, Commented Aug 8, 2012 at 17:16

Mike Mackintosh · Accepted Answer · 2012-08-08 17:17:26Z

3

By default, the value type will be string.

You can set it to an integer like this:

$stmt->bindParam(1, $row_start, PDO::PARAM_INT);

Update:

Even though you are forcing it to INT, it's still passing it as a string.

Look at your error message:

near ''0', 10' at line 1" }

And specifically the ending ' after 0, indicating it is a string.

0'

Update

Manually typecasting the variable is needed, even if you pass the expected PDO type:

$limit = (int) 1;
$limit2 = (int) 1;


$stmt = $pdo->prepare("SELECT * FROM Table LIMIT :limit, :offset");
$stmt->bindParam(":limit", $limit, PDO::PARAM_INT);
$stmt->bindParam(":offset", $limit2, PDO::PARAM_INT);
$stmt->execute();

edited Aug 8, 2012 at 17:17

answered Aug 8, 2012 at 16:47

Mike Mackintosh

14.3k7 gold badges64 silver badges87 bronze badges

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

1 Comment

Hassan Over a year ago

Yes I think it's because I am giving that variable string value.

wanovak · Accepted Answer · 2012-08-08 16:45:41Z

0

$conn->prepare("SELECT * FROM table ORDER BY date DESC LIMIT ?");
$stmt->execute(array(10));

answered Aug 8, 2012 at 16:45

wanovak

6,12727 silver badges32 bronze badges

1 Comment

Mike Mackintosh Over a year ago

@wanovak is passing the value directly to the statement via execute, not through binding. See php.net/manual/en/pdostatement.execute.php

Collectives™ on Stack Overflow

PDO/PHP : Fetch Array and Prepared Statements

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related