I am trying to read text from a file and storing it into the database. I have a text file with a list of of movie titles. One title per line in the text file. I am reading the file using PHP and storing the titles into a mysql text field within the databse. I need to use a text field because movie titles are not the only thing that could be stored in this field. The data reads in fine, and inserts into the database fine, but when I look at the data in the database in a program like Access I see a square at the end of the text. I use access to generate nice looking reports quickly and those squares show up in the reports. Can anyone help me?

If I echo the titles to the screen while inserting I get no sqaures, but I also get multiple movies on one line. The text file is a unix file.

I hope you're comfortable, this is a long answer

Ok ... that said ... 1st off, I'm not a mysql expert, but I assume it properly stores/retrieves newline characters. The problem you're having (I think(I hope!!)) is that unix systems and windows systems store their special characters for "make a new line of text" differently. The reason why goes way way back, but I won't bore you with all of that ....


Basically, when you want a new line of text in a unix text file, you want what's known as the linefeed character. Now, you can't -display- a linefeed character (what does it look like?), so every programming language has a special series of characters that represent things like tabs and new lines. Linefeed characters are represented in PHP and C as "\n". So to make 2 lines of text you'd echo "line1 \n line2";

However on windows, it takes _two_ characters. It takes not only the linefeed character ("\n") but also a carriage-return character, represented as "\r". Not only that, but they have to be in the right order! -- it has to be "\r\n". (known as the CarriageReturn-Linefeed sequence)

Now, when a unix system sees that extra character, "\r" (remember, unix uses "\n" for new lines of text), it doesn't really know what to do with it, so it'll show some form of that character (either a box, or a letter with a tilde, but not just a new line like you want). Likewise, when windows sees just a \n without an accompanying \r, it doesn't register it as a new line of text, so it shows you a box representing the \n by itself.


SO ... long story short ... You have to make sure that the data you're about to display is formatted for the correct OS you're on. If you're -always- going to view your data on windows, you could replace all of the \n's in your database with \r\n's and then Windows would always understand it.

However, then you have a problem looking at your data on unix ... so what I do is store all my data in the unix format (just the \n's, which btw is what an HTML form will submit it's newline characters with), and then after I have my data selected into my VB/Access/whatever app, I do a string replace. Replace all of the \n's with \r\n's.

Now, in VB, the linefeed and carriage-return characters are represented with vbCr and vbLf, not \n or \r, and the Windows Magic Newline Sequence (heh ...) is represented by vbCrLf. If you just replace all of the vbLf's in your strings with vbCrLf's then it'll display properly


Oh, one last thing, If you just dump you data to the screen in your web browser, your web browser will interpret the text as html. Now, in html, neither linefeed OR carriage-return characters mean anything! As you may know, it has it's own special "make a new line" sequence, which is "<br>". Now, these are the actual letters, not just a representation of some special character. So if you're going to dump your data to the screen, you would want to do:
echo str_replace("\n", "<br>", $mydata);



Well .... Now that that's all said & done, I hope that I guessed your problem right, given the long answer

That information was very helpful from a theoretical stance. I think I may have figured out my own problem and I think that before I store the data in the database I need to strip the end of line character. Do you know of a way to do this in PHP. Thanks for your help.

To strip linefeed characters:
$data = str_replace("\n", '', $data);

I have solved my problem thank you for your help. I decided to use $data=trim($data). This seems to have solved my problem. Thanks for the help.