back to PDO and get strange error on online host not on wamp

[lovephp]

so the following codes working just fine on wamp  and now that i try it on web host i get this error

 

 

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

 

i get this error if there is no value in db

 

here is the codes

 

$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(); //error states this line 
    
$result = $stmt->fetchAll();

[lovephp]

host provided current PHP version 5.4 i tried changing it to 5.6 5.4 5.5 but still no affect in those other versions the page becomes blank instead.

[Jacques1]

Is the MySQL version up to date?

[lovephp]

Is the MySQL version up to date?

these are all the versions the host is providing my i tried changing but nothing is happening and i get no option to change the MYSQL version

[benanamen]

GoDaddy shared hosting is the worst. I double my rate when I have to work on those accounts.

[lovephp]

i feel sad i yesterday bought it its taking forever to send emails too

[lovephp]

changing version gives me blank page. is it my code or their hosting issue? they tell me its my code which has problem

[ginerjm]

Cut your losses now. Switch and the saving in headaches will be worth it.

[lovephp]

they are telling me that i have used a mysqld statement but i cannot seem to find where i have used it, executive asked me to remove that mysqld statement as asked by their technical team

[ginerjm]

Do a simple search of your program files.

[Jacques1]

mysqld_stmt_execute() is an internal MySQL function. You cannot “remove” it.

[lovephp]

mysqld_stmt_execute() is an internal MySQL function. You cannot “remove” it.

guess their technical team also are good for nothing. now what other option i have to run those codes? only for the above code i face this error if there is no value in db

Edited August 4, 2016 by lovephp

[Jacques1]

What is the exact content of the variables you pass to the parameters? Post a var_dump() of each.

 

Are you using emulated prepared statements?

[lovephp]

those questions are going above my head ok my config is like this

 

try {

    //create PDO connection
    $db = new PDO("mysql:host=".DBHOST.";dbname=".DBNAME, DBUSER, DBPASS);
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);    
    $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC);
    
} catch(PDOException $e) {
    //show error
    echo '<p class="bg-danger">'.$e->getMessage().'</p>';
    exit;
}

 

and all integers are being called from db if that was what you asked? when 0 then there is error else no error. sorry my English understanding is little limited

[lovephp]

and then i removed //$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); then the error disappeared but this sure is no solution is not it?

Edited August 4, 2016 by lovephp

[Jacques1]

The error doesn't disappear, only the message. That's like burying your head in the sand.

 

You're passing 0 to what parameter? The limit? The offset? The user ID?

[lovephp]

hehe you are correct and it would be dumb to not use PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION

 

 

well im not passing 0 but im sure if there is no value in database applied table then 0 gets passed?

 

CREATE TABLE IF NOT EXISTS `applied` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vacancyID` int(11) NOT NULL,
  `memberID` int(11) NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

 

it has to be total or offset somewhere the 0 is coming and i m not understanding

Edited August 4, 2016 by lovephp

[Jacques1]

We don't need your speculations. We need you to var_dump() the three variables and post the output. Why is this so hard for you?

 

If you cannot do this, then we cannot help you.

[lovephp]

We don't need your speculations. We need you to var_dump() the three variables and post the output. Why is this so hard for you?

 

If you cannot do this, then we cannot help you.

sorry late, well i have no issue and i did thid

 

 

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

 

and this is what i see on the browser along with the error

 

 

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

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

[Jacques1]

You cannot have a negative offset, so you need to fix your calculation or add a special case to prevent the offset from becoming negative.

 

The page is currently calculated as

$page = min($pages, filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT, array(

'options' => array(

'default'   => 1,

'min_range' => 1,

),

)));

If there are 0 pages, the page is calculated as 0, and the offset

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

becomes negative.

 

I'd say the $page needs a minimum value of 1.

[Barand]

You might want to consider altering the logic of the program so that if there are no results to display then you do not even attempt to paginate nothing.

[lovephp]

You cannot have a negative offset, so you need to fix your calculation or add a special case to prevent the offset from becoming negative.

 

The page is currently calculated as

$page = min($pages, filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT, array(

'options' => array(

'default'   => 1,

'min_range' => 1,

),

)));

If there are 0 pages, the page is calculated as 0, and the offset

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

becomes negative.

 

I'd say the $page needs a minimum value of 1.

so if i make

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

to

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

would this make sense?

 

or something like

 

if($offset < 0){
$offset = 10;
}

Edited August 5, 2016 by lovephp

[Jacques1]

No, because this calculation is simply wrong. When you're on page 2 with 100 items per page, you'd get an offset of 300 (when it's actually 100).

 

There are two options:

• You explicitly check if there are no records (i. e. 0 pages). If that's the case, you display a single empty page, possible with a message like “No items found”,
• You make sure the page(!) is always at least 1, e. g. with the max() function.

[lovephp]

You might want to consider altering the logic of the program so that if there are no results to display then you do not even attempt to paginate nothing.

so i tried something like this but is this the only solution

 

if($offset < 0){
$offset = 10;
}

[lovephp]

 

No, because this calculation is simply wrong. When you're on page 2 with 100 items per page, you'd get an offset of 300 (when it's actually 100).

 

There are two options:

• You explicitly check if there are no records (i. e. 0 pages). If that's the case, you display a single empty page, possible with a message like “No items found”,
• You make sure the page(!) is always at least 1, e. g. with the max() function.

 

doing this then

 

if($offset < 0){
$offset = 10;
}

?