Getting rid of MySQL Warning: Truncated incorrect DOUBLE value

If you come across a cryptic warning like this:

| Warning | 1292 | Truncated incorrect DOUBLE VALUE: 'xxxxxxx'

on running a MySQL query, it could be caused by using a numeric value against a CHAR/VARCHAR column.

---assuming `name` is a CHAR/VARCHAR column
---the following query might cause warnings
SELECT * FROM `t1` WHERE `name` IN (1, 2, 3);
---replace the above with this
SELECT * FROM `t1` WHERE `name` IN ('1', '2', '3');
--and the warnings are quite likely to go away

PHP as a replacement for sed/awk

I had gotten sick of not being able to do what I wanted to do with sed/awk – partly because I am not too familiar with those – and, was investigating replacements for those. Many people seem to be using perl and since I wasn’t too familiar with Perl as well, I started thinking about using PHP – which I do know. And, that served my needs brilliantly.

This is a simplistic version of my problem & a solution:
I can do the following to get the values for specific params (cid and oid in this case) for requests made, from my access logs:

k7@local:echo "" | php -R 'preg_match("#.*cid=([^&]+)&oid=([^&]+)#",$argn, $matches);if(!empty($matches)) {echo $matches[1], " ", $matches[2], "n";}'
123 435

Regular Expression to check for comma separated list of Integers

//get rid of all kinds of spaces
$IDs = preg_replace('/s+/', '', $inputIDs);
if(!preg_match(''/^\d+$|^(\d+,)+\d+$/'', $IDs)) {
echo "Invalid Input";

You could use the validated IDs above in a SQL like the this:


Disclaimer: I am not going to be responsible for any kind of SQL injection resulting from using the snippet above.
updated 20-Mar-2013