Bug #17907 Updated: mysql_query seems to strip trailing spaces from variables

ID: 17907 Updated by: cynic@php.net Reported By: andrewt@weekendhosting.com -Status: Open +Status: Bogus Bug Type: MySQL related Operating System: Win2000 / Unix PHP Version: 4.2.1 New Comment: you were supposed to provide something like this: mysql> DESCRIBE test; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | test | char(5) | | | | | +-------+---------+------+-----+---------+-------+ <? echo PHP_VERSION , "\n"; echo uname -sr , "\n"; mysql_connect('localhost', 'test', ''); mysql_select_db('test'); mysql_query('TRUNCATE test'); mysql_query("INSERT test (test) VALUES ('x ')"); $rs = mysql_query('SELECT CONCAT(\'"\', test, \'"\') FROM test'); while ($row = mysql_fetch_assoc($rs)) { var_dump($row); } 4.3.0-dev FreeBSD 4.5-STABLE array(1) { ["CONCAT('"', test, '"')"]=> string(3) ""x"" } checking with mysql(1): mysql> TRUNCATE test; Query OK, 0 rows affected (0.00 sec) mysql> INSERT test (test) VALUES ('x '); Query OK, 1 row affected (0.00 sec) mysql> SELECT CONCAT('"', test, '"') FROM test; +------------------------+ | CONCAT('"', test, '"') | +------------------------+ | "x" | +------------------------+ checking with the MySQL manual (you didn't mention the column type, so I'm just guessing): "When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed." looks like it's not a PHP bug -> bogusifying. Previous Comments: ------------------------------------------------------------------------ [2002-06-21 14:00:49] andrewt@weekendhosting.com With respect, but please be gentle, i've read that page, I don't know what more I can tell you $myVar = '1234 ' // note the trailing space If I include this in a query $query = "INSERT INTO myTable (myValue) VALUES ('$myVar')"; I would expect it to store '1234 ' including the trailing space in to the db. However, what actually happens is somewhere between calling mysql_query() and the query taking place something strips the trailing space. This happens on 4.0.6, 4.1.2 and 4.2.1, it happens on my Win2000 box and my Unix host. The field is a varchar. If I make the same insert using a Win32 gui (such as mysqlFront) it includes the trailing space, if then copy the sql statement that the gui uses in to a mysql_query() the space goes. I really can't give any more information than this, please please don't just dismiss this as bogus, just run those two lines against a DB, if it doesn't happen to you then just ignore me. Just to re-itterate, I can not store values with trailing spaces in to mysql. ------------------------------------------------------------------------ [2002-06-21 13:53:30] cynic@php.net You haven't provided the required info -> bogus. ------------------------------------------------------------------------ [2002-06-21 13:45:00] andrewt@weekendhosting.com It's nothing more complex than I can't insert a variable in which the last character is whitespace into mysql using mysql_query. $myVar = '1234 '; will go in to the database as '1234' I can do this using mysqlfront which indicates there is something different in the php function mysql_query ------------------------------------------------------------------------ [2002-06-21 13:38:45] cynic@php.net Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.php.net/how-to-report.php If you can provide more information, feel free to add it to this bug and change the status back to "Open". Thank you for your interest in PHP. ------------------------------------------------------------------------ [2002-06-21 13:37:37] andrewt@weekendhosting.com Further to this mysqlfront (which is presumably using the c++ mysql API can handle this trailing space no problem) ------------------------------------------------------------------------ The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at http://bugs.php.net/17907 -- Edit this bug report at http://bugs.php.net/?id=17907&edit=1