Extra padding in stored field 1

[rouse01]

This insert works ok, but code2 varchar(20) seems to get stored with an extra couple of spaces in front of it.

$tbl_query="INSERT INTO mytable (code1, date1, time, code2 )
values
('".$_POST['code1'] ."','
".$Date1 ."','
".$_POST['time'] ."','
".trim($_SESSION['code2'])."')" ;
echo "<br>CODE2: ".$_SESSION['code2']."<br>" ;
echo disp_stored($Date1) ;

The echo of code2 and the echo of the $tbl_query looks ok on screen. My disp_stored()function does not return results since it's asking for code2='abc' but code2 just got stored as ' abc'. I can see this when looking at the data through mysql at the console.

Thanks - Keith

 

[jpadie]

the trim should get rid of the spaces. is it possible that the spaces are not spaces but some other non-printing character?

if you hard code the input as 'abc' in the sql statement do you still get the extra spaces in the db?

is the field default set correctly in the database?

 

[rouse01]

Sorry I took so long to respond. The hard coding didn't work, so I ran a MySQL dump on the table and saw that the data dump has:
insert into 'mytable' values ('\r\n A','2006-02-01','1.00','\r\n ')

So I suspect the '\r\n' string, but don't know where it comes from.

 

[rouse01]

Added note - I've had the magic_quotes_gpc & magic_quote_runtime both on in my php.ini. I'm turning these off and will work with the addslashes/stripslashes inside my scripts. If I haven't reported a success in the next couple of days, would appreciate any ideas.

 

[jpadie]

the "\r\n" are windows line terminators. Carriage return and new line to be precise.

I find that they creep in to databases mainly through if you are accepting user input from textboxes. or it could be that users are cutting and pasting across new lines.

trim should remove these characters however (although there were reported problems in earlier versions of php4 - which version do you run?). can you run a test to see the output of

echo trim("\r\n    A");

note that the true output will be in the source code and not the screen.

 

[rouse01]

Many thanks! I suspected an apache/php config problem. But it seems to be the way I code the tbl_query with my editor (HTML-kit, which I really like) and misuse of quoting.? I thought white space was ignored. Apparently the way I structured the query - trying to format the source to be more readable - encapsulated the linebreaks.
Anyway, the fix is:
$tbl_query="INSERT INTO mytable (code1, date1, time, code2 ) values ('".$_POST['code1']."','".$Date1."','".$_POST['time']."','".$_SESSION['code2']."')";

Note that I removed the spaces, etc in the values string.

Your comment on the 'line terminators' got me thinking in the right direction.

Keith

 

[jpadie]

I thought white space was ignored

not inside quotation marks!