Mysql PDO execute always return TRUE

Question

Mysql PDO execute always return true, even if i do a wrong query:

try {
    $sql = $this->db->prepare("INSERT INTO orders (total_price) VALUES ('not_int');");
    $result = $sql->execute();  // WHY TRUE??
} catch (Exception $e) {
    die("Oh noes! There's an error in the query!");
}

In table, type of column 'total_price' is INT. Exception also not catch (PDO::ATTR_ERRMODE is PDO::ERRMODE_EXCEPTION). In 'total_price' inserts '0'. I'm try to use:

$total_price = "not_int";
$is_int = $sql->bindParam(:total_price, $total_price, PDO::PARAM_INT);

but also returns TRUE ($is_int is true)

I think that might be some sort of automatic type conversion going on. Whether or not such a “wrong” value is taken silently might depend on server configuration/mode. You don’t get a PDO exception here, because you are only preparing a “static” statement. Had you made use of actual placeholders in your query, and then tried to bind a string value to an integer type, that might look different. — CBroe, Feb 17 '15 at 21:02
SHOW CREATE TABLE orders is : Table: orders Create Table: CREATE TABLE `orders` ( `id` int(11) NOT NULL AUTO_INCREMENT, `products` text COLLATE utf8_bin NOT NULL, `total_price` int(11) NOT NULL.... — Vin T, Feb 17 '15 at 21:05
http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sql-mode-strict <--- this — zerkms, Feb 17 '15 at 22:14

score 1 · Answer 1 · answered Feb 17 '15 at 22:01

actually, "INSERT INTO orders (total_price) VALUES ('not_int');" does work.

that's because 'not_int' was converted to 0 ,So If you check your table , You must find a new entry with 0 for 'total_price'.

for '$result = $sql->execute();' Of cause it would return true;

score 1 · Answer 2 · answered Mar 28 '16 at 15:54

Function PDOStatement::excute -> return value is only boolean

Wrong case :

$stmt = $pdo->prepare("Your Query");
...
$result = $stmt->execute();
$result->fetch(); // is only boolean

Try it :

$stmt = $pdo->prepare("Your Query");
...
$result = $stmt->execute();
$stmt->fetch(); // return (PDOStatement) or false on failure

score 1 · Answer 3 · answered Mar 28 '16 at 16:13

A PDO query that triggers errors should throw an exception. A query that triggers warnings should not.

The root behaviour with invalid data is a MySQL peculiarity that has nothing to do with PHP or PDO—you can reproduce it in plain SQL:

mysql> CREATE TABLE orders (
    ->  orders_id INT(10) UNSIGNED AUTO_INCREMENT,
    ->  total_price INT,
    ->  PRIMARY KEY (orders_id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> SET @@SESSION.sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO orders (total_price) VALUES ('not_int');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------------------------------+
| Level   | Code | Message                                                              |
+---------+------+----------------------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'not_int' for column 'total_price' at row 1 |
+---------+------+----------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM orders;
+-----------+-------------+
| orders_id | total_price |
+-----------+-------------+
|         1 |           0 |
+-----------+-------------+
1 row in set (0.00 sec)

mysql> SET @@SESSION.sql_mode='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO orders (total_price) VALUES ('not_int');
ERROR 1366 (HY000): Incorrect integer value: 'not_int' for column 'total_price' at row 1

score 0 · Answer 4 · answered Feb 17 '15 at 20:51

0

Try This:

try {
    $sql = $this->db->prepare("INSERT INTO orders (total_price) VALUES ('not_int');");
    $result = $sql->execute();  // WHY TRUE??
catch( PDOException $e ) {
    die("Oh noes! There's an error in the query!");
}

answered Feb 17 '15 at 20:51

Leandro Papasidero

3,728
1
18
33

... the difference being `Exception` vs `PDOException`. – showdev Feb 17 '15 at 20:54
set this attributes after PDO construction, `$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);` – Leandro Papasidero Feb 17 '15 at 20:56
Yeah, PDO::ERRMODE_EXCEPTION is set. – Vin T Feb 17 '15 at 21:08
1

Exception class catches all exceptions that extend from it. PDOException only catches PDOExceptions and exceptions classes that extend from it. There should be no difference here with this code except that you're specifically only catching PDOException and allowing all other exceptions to go uncaught. – danronmoon Feb 17 '15 at 21:21

Mysql PDO execute always return TRUE

4 Answers4