PDO datetime where datefield = 0

Hello!

My PHP code looks like this

$sqlMax = '
SELECT COUNT(`primKey`) numb
FROM `mytable`
WHERE `idadmin` = :p1 AND `deleted` = :p2
';
$params = array(
    ':p1' => 4,
    ':p2' => 0,
);
// this is just a piece of code so you can understand the context
// (all things are defined, $this->PDOobj and the MySql connection)
// I have some db class and use PDO like this:
$this->PDOStatement = $this->PDOobj->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$this->PDOStatement->execute($params);

My problem is that PHP will not return any results but MySql works just fine.
So, I do have results here:

SELECT COUNT(`primKey`) numb FROM `mytable` WHERE `idadmin` = 4 AND `deleted` = 0

Now, if I replace:

$params = array( ':p1' => 4, ':p2' => 0, );
with

$params = array( ':p1' => 4, ':p2' => '0000-00-00 00:00:00', );
PHP also works fine.

So, why doesn’t PDO know to pass the ‘0’ OR, does it convert it somehow?
Is there an option to make it accept 0 instead of 0000-00-00 00:00:00?

Thank you!

So have you checked PDOStatement->errorInfo()?

There is no error.
It’s a simple SELECT and in case of a failure an exception would be thrown.

I tried to echo the errorInfo just to be sure.

array (size=3)
  0 => string '00000' (length=5)
  1 => null
  2 => null

Here it is a code that someone can try and tell me if it’s something about version or other things.

<?php

define('HOST', 'localhost');
define('DBNAME', 's1');
define('USER', 'root');
define('PASS', '');

try {
    $conn = new PDO(
        'mysql:host=' . HOST . ';dbname=' . DBNAME . ';charset=utf8',
        USER,
        PASS,
        array( PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8' )
    );
} catch(PDOExceptions $e) {
    echo 'Connection failed: ' . $e->getmessage() . '<br>';
}

$sql = '
SELECT COUNT(`idsupport`) numb
FROM `tempTable`
WHERE `idadmin` = :p1 AND `deleted` = :p2
';

$params = array(
    ':p1' => 4,
    ':p2' => 0,
);

$pdoStatement = $conn->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$result = $pdoStatement->execute($params);
$rec = $pdoStatement->fetchAll(PDO::FETCH_NAMED);

var_dump($rec);

$params = array(
    ':p1' => 4,
    ':p2' => '0000-00-00 00:00:00',
);

$pdoStatement = $conn->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$result = $pdoStatement->execute($params);
$rec = $pdoStatement->fetchAll(PDO::FETCH_NAMED);

var_dump($rec);

Table structure:

CREATE TABLE `tempTable` (
  `idsupport` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `idadmin` bigint(20) unsigned NOT NULL,
  `idcategory` bigint(20) unsigned NOT NULL,
  `deleted` datetime NOT NULL,
  PRIMARY KEY (`idsupport`),
  KEY `idadmin` (`idadmin`),
  KEY `idcategory` (`idcategory`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `tempTable` VALUES ('1', '3', '1', '0000-00-00 00:00:00');
INSERT INTO `tempTable` VALUES ('2', '4', '0', '0000-00-00 00:00:00');
INSERT INTO `tempTable` VALUES ('3', '4', '0', '2015-03-01 02:03:04');
INSERT INTO `tempTable` VALUES ('4', '3', '0', '0000-00-00 00:00:00');
INSERT INTO `tempTable` VALUES ('5', '4', '0', '0000-00-00 00:00:00');

Thank you in advance!

For starters, you’d only catch an exception on the CONNECT attempt, because thats the only thing in the try-catch block. Second of all, a failed query does not throw an exception. You are doing no checks in this code about catching query errors.

Humor me, and var_dump($pdoStatement->errorInfo()) after your execute.

@vectorialpx,

Use bindValue in your second prepare satement.

1 Like

by default. if you tell PDO to handle errors via exceptions then it does convert mysql errors into exceptions.

This line works for me inside a try-catch
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Thanks! That’s it! @jemz
But works only with PDO::PARAM_INT
LE: Looks like MySql does not accept = "0" so, that’s why it must be = 0.

@StarLion I know what you mean and I do check my $result in my sql class but if the SQL didn’t work in the first time, I could not see any results at all. So, the SQL works fine, it’s just how the MySql server gets it to check that datetime field. The errorInfo() was posted after my execute and I did a copy-paste of the result here :smile:

Thank you, to all of you!

What SQL class? Your code uses a default PDO object, and your code shows no checking at all.

For the future: If you’re looking to get help, it will be much more helpful to have your ACTUAL code to look at, rather than what i presume to be a ‘prettied up’ situation, because you’re changing the scenario.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.