Show WARNINGS in MySQL + PDO

Question

I'd like to know how to get warning count in MySQL + PDO.

I was having warnings when making a query in console, looking for varchar and not adding aphostrophes (' ').

mysql> describe keywords;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| document_id | int(11)      | NO   | MUL | NULL    |                |
| keyword     | char(50)     | NO   |     | NULL    |                |
| value       | varchar(250) | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> select * from keywords where value = 1234567890;
+-----+-------------+--------------------+------------+
| id  | document_id | keyword            | value      |
+-----+-------------+--------------------+------------+
| 311 |          71 | Nº de Operacion   | 1234567890 |
+-----+-------------+--------------------+------------+
1 row in set, 12 warnings (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '1234-0'              |

I have a parametrized query, and I'd like to know if that query is generating warnings too, or if it doesn't matter if the value is string or integer when you parametrize it.

Example:

'SELECT * FROM keywords WHERE value = :value'

Would work for searching integers and strings, or should I add aphostrophes:

'SELECT * FROM keywords WHERE value = \':value\''

for searching a varchar. Which by the way, doesn't give results.

Answer 1

Well, as far as I know, there is no way except explicitly run SHOW WARNINGS query.

But honestly, I see not much point in having mysql warnings in PHP. One need them in a development phase only, but on a live server there should be no query that raise a warning at all.

Concerning your doubts on truncated values - they're groundless. Prepared statements works not the way you are thinking of. PDO will never produce a query that may raise a warning like this when prepared statements are used.

Just get rid of these wrong quotes, run the proper query and see:

$stm = $pdo->prepare('SELECT * FROM keywords WHERE value = :value');
$stm->execute(array(':value'=>'whatever'));
$stm = $pdo->query('SHOW WARNINGS');
var_dump($stm->fetchAll());

Answer 2

When constructing your PDO instance, pass an array of options, containing this key-value pair:

array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)

To force PDO to throw exceptions (PDOException instances) when an error occurs. Also, when using prepepared statements, you don't add quotes of your own... if the value is a string,

WHERE value = :value

Is just fine, PDO/MySQL will quote the value correctly for you.

PS: You can always change the error mode later on, by calling setAttribute:

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);//raise warnings, don't throw errors

Here's a list of attributes
Here, you'll find examples where options are set via the constructor

example from the doc pages, only extended a bit:

$pdo = new PDO('mysql:host=myhost;dbname=mydb', 'login', 'password',
    array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\'',
          PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
          PDO::ATTR_ORACLE_NULLS       => PDO::NULL_NATURAL)
);

Edit:
After a second glance at your question, I noticed the actual warning you got. That warning will evidently not throw an Exception. Prepared statements check the datatypes upon statement preparation, after that job is done, the statement gets executed. In your case, the double is cast to a varchar, and then the query is executed, comparing the varchars in value with the varchar in the query. No warnings, no fuss, it just does the job.
Here's a related question
And here's a more detailed explanation on how prepares work