Page 1 of 1
Loss of newline characters in database
Posted: Fri Jul 05, 2002 10:58 am
by RandomEngy
My database is doing great so far, but a couple of times, the info in the database suddenly lost its newline characters somehow. The field in which it happened is varchar(150), and it is often re-updated via form. The form input is a textarea, with its default value given as the information in the database. What happens is users go to edit their information, and all of the forms get their default values from the database, and the user can edit those values, and they all get updated when the user clicks the submit button.
I'm running mySQL 3.23.51, Apache 1.3.24, and PHP 4.2.1 .
Now where do you think the newline characters were lost? In the database? On the form submittal? When I altered the table?
If you could share any ideas or experience with this, it would be greatly appreciated, because it would be quite embarrasing for my database to randomly kill all of the user's newlines.
newline character
Posted: Tue Jul 23, 2002 6:46 pm
by musashi
It would help a lot if you could submit an example of your code... but I'm guessing it looks something like the following:
Code: Select all
<form method="POST" action="blah.php">
<textarea name="data">$data</textarea>
<input type="submit">
</form>
where $data is the data from the database for the textfield. A couple of questions about the error:
1. Does it occur every time, or only once and a while?
2. Does the database display the data correctly (if you view it from the db)?
If it happens all the time, and the the data doesn't look correct in the db, then the data is not being either inserted, or processed correctly. (Try echoing the $data variable after the user submits and view the HTML source file. newline characters will properly act as an EOL in the source file). If it happens all the time and the data does look correct in the db, then it has something to do with your reading from the db. Again try and echo each step along the way to see if you change anything.
If it doesn't happen all the time, then you need to determine the bug trigger (what input causes the error to appear). Then look at the code that branches down the path of no newlines and compare it to the path where newlines are retained.
Bottom line, I don't think this has anything to do with your db. I've done this before, and never had a problem (even when I altered the table. The reason is, the db doesn't look at \n characters any differently than other characters). If you want to eliminate even the thought that it is the DB change (just for a test) the field type to "text".
Posted: Wed Jul 24, 2002 8:00 am
by llimllib
If you're doing stuff with a lot of text, varchar() isn't very efficient, because mySQL has to do a lot of processing (rtrim, strlen) to figure out how big to make the column. If people have a textarea, it's better to use a field of type tinytext, plus that won't mess with your text, and if you ever needed it, you could search it easily.
At least, that's how I understand it...Grain of salt rule applies...
Posted: Wed Jul 24, 2002 8:35 am
by jason
If your refering to new lines that are displayed on a webpage that are lost, look into nl2br().
Posted: Wed Jul 24, 2002 1:14 pm
by RandomEngy
Ahh, I found out what was going on. llimllib was right on the money with his advice.
It had happened twice that the database lost the newline characters, but it was only in varchar fields and not text. The times the newline characters got lost was when I used phpmyadmin to edit the database. When I brought up the "edit row" screen, phpmyadmin loaded all the current database values into HTML fields. varchar happened to have an HTML type of "text" instead of textarea. So, "text" fields don't know what to do with newline characters, since there is only one line to write on, and the whole string without the newlines is displayed. When I went to hit submit, it updated the varchar field without the newlines!
Sometimes I feel like a detective while looking for bugs :D .
Posted: Wed Jul 24, 2002 2:09 pm
by llimllib
That's a beautiful bug solution though, Random. I love that moment when the bug unravels itself to you...it makes the whole effort worthwhile, no matter how frustrated you were searching for it.
Posted: Thu Jul 25, 2002 2:53 am
by twigletmac
This'll probably save other people a lot of headaches...
Mac