johram pong johram pong - 3 months ago 20
MySQL Question

if no value in database i recieve an error

for some reason this works just fine on wamp but if put it on shared host i get the following error message, to some extent i guess its because there is no records in db so negative value is being passed and this is happening only if database is empty.

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1210 Incorrect arguments to mysqld_stmt_execute


i tried var_dump and this is the output

var_dump($offset);
var_dump($per_page);
var_dump($uid);

float(-10)
int(10)
int(4)


here is my coding

$query = "SELECT COUNT(*) FROM applied WHERE memberID = :memberID";
$stmt = $db->prepare($query);
$stmt->bindParam(':memberID', $uid, PDO::PARAM_INT);
$stmt->execute();
$total = $stmt->fetchColumn();

$pages = ceil($total / $per_page);

$page = min($pages, filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT, array(
'options' => array(
'default' => 1,
'min_range' => 1,
),
)));

$offset = ($page - 1) * $per_page;

$query = "SELECT * FROM applied WHERE memberID = :memberID ORDER BY id DESC LIMIT :per_page OFFSET :offset";

$stmt = $db->prepare($query);
$stmt->bindParam(':per_page', $per_page, PDO::PARAM_INT);
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$stmt->bindParam(':memberID', $uid, PDO::PARAM_INT);
$stmt->execute();

$result = $stmt->fetchAll();


thanks in advance, appreciate your time

Answer

Please replace php code with these

$query = "SELECT COUNT(*) FROM applied WHERE memberID = :memberID";
        $stmt = $db->prepare($query);
        $stmt->bindParam(':memberID', $uid, PDO::PARAM_INT);
        $stmt->execute();
        $total = $stmt->fetchColumn();    

$pages = ceil($total / $per_page);

$page = min($pages, filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT, array(
'options' => array(
'default'   => 1,
'min_range' => 1,
),
)));

$offset = ($page - 1)  * $per_page; 
if($offset < 0){
$offset = 10;
}
$query = "SELECT * FROM applied WHERE memberID = :memberID ORDER BY id DESC LIMIT :per_page OFFSET :offset";

         $stmt = $db->prepare($query);
         $stmt->bindParam(':per_page', $per_page, PDO::PARAM_INT);
         $stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
         $stmt->bindParam(':memberID', $uid, PDO::PARAM_INT);
         $stmt->execute();

$result = $stmt->fetchAll();
Comments