PDO insert to MySQL works on Windows running Xampp stack but fails on a LAMP stack
Firstly, bear with me as I'm a relative novice at PHP. I have a feeling this may be more a MySQL problem then PHP but any insights into understanding and resolving the issue would be greatly appreciated.
I have a PHP script that imports data from a text file and inserts it into a MySQL table. This script works fine on my Windows box running Xampp but when I move it over to my Linux production box, it fails at the insert part of the process with the following error:
PHP Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '(string value here)' for column 'FIELD_NAME_HERE' at row 1
The MySQL database has been imported from the box running the Xampp stack and all field definitions and privileges appear to be correct. A couple of my own observations:
-
There is a delete query that uses the same PDO connection to the database. This executes on both boxes without issue.
-
If I dump the array that's being passed for parameters for the binding of placeholders to values, I can see they're all strings. However, the script then chokes on the first instance of a string value being inserted into an integer field. The example running on Xampp seems to parse this ok, the Lamp stack doesn't.
Source for my code can be found here. I hope this information suffices, but let me know if anything is unclear.
SOLVED - for anybody finding this because of the same issue u/thenbardingreen gave me the clue I needed. Windows is fuzzy and will let bindings pass and insert the data without explicitly declaring it as some type other than a string. Linux doesn't - adding intval('value being bound') for each integer field was all that was needed to get the script to run.
Posted Aug 23, 2019, 11:51 AM UTC · edited
Sounds like a type error. I bet Windows lets you be fuzzy with passing say "2" rather than 2 to an INT field, but Linux doesn't. Your array should be something like:
[0=>"cow" 1=>"sheep" 2=>5] rather than [0=>"cow" 1=>"sheep" 2=>"5"]
Many thanks, that was enough of a clue for me to resolve it. Script runs happily on both now.
Type certainly seems to be an issue. The original iteration of this script was inserting across less fields and used positional placeholders (i.e. '????' and 's' or 'i' respectively for string and integer values respectively). As the field list grew longer it seemed a better idea to use named placeholders. That these worked happily in Windows without me having to make the types explicit lulled me into a false sense of security.