database info retrieving and text format

[trismegisto]

I populated a db with an html form, when i typed in the <textarea>, i used enters for line breaks, it’s just a list, with bullets and everything. So i have several paragraphs of information; my db column is a blob, i decided to use a blob as jatar_k (comment msg #13) [webmasterworld.com] about using blob when you don’t know if you will have little or much text.

So i have a list in a blob. The problem comes when i want to display the list, in the browser the list looks as if i had typed it in a single line!, i don’t know if this happens if the db column is text, but i don’t know how to display the list as intended.

I tried using a <pre> tag, and it makes possible to display every list item in a line but in a monospace font (no problem since it can be overriden by a stylesheet), the problem is that the list must fit in a 200px width div, and with the pre tag, the list is just too long and overrides the image i have next to the div.

I can not fix it with stylesheets, so i suppose the answer is with the record stored in the db. Is there a way i can display the list correctly? Do i have to change the table from blob to text? And how do i do it? I have searched in the MySQL manual but i can’t find a command to change the column type…

Or (this would imply more job) would it be better to create another db table and copy the list as an html <ul> list with tags and everything so it can be correctly interpreted by the browser?

Maybe this should be post in google news, but since the answer might be in column choice, i’ll ask it here: —i don’t know if i’m saying this correctly— i’m using the primary key (named ID) in the link to “pass” the value to a more detailed page (.php?ID=4). I read in google news that it was not good to use the id because it may be interpreted by a spider as a cookie session id, or another kind of id, would it be better o ”pass” the value with another column?

thanks in advance for the advice.

[Birdman]

<ul>
<?php
$sql = "SELECT * FROM db";
$result = mysql_query($sql);
while ($i = mysql_fetch_array($result)){
echo "<li>$i[your_blob]</li>";
}
?>
</ul>

<added>Sorry, I read that wrong. I think you can use ENUM for a field with multiple entries or you could keep it a blob and separate your entries with commas.

Then use explode() to make it into an array and loop through it.

[trismegisto]

hi birdman.

you mean using commas instead of enters as line breaks?

[andreasfriedrich]

These threads might help:

Parsing double newlines into <p> tags using PHP - How to run a full Perl-style regular expression in PHP? [webmasterworld.com]

Newby help with MySQL / CGI / HTML - Newlines missing when parsing database data [webmasterworld.com]

Andreas

[Birdman]

Actually, you can use line breaks the same way.

$list = explode("\r", $blob);

That will turn the field into an array:

$list[0]
$list[1]
$list[2]
$list[3]

Count how many in the array:

$tot = count($list);

Then loop the array:

for ($i=0;$i<$tot;$i++){
echo "<li>$list[$i]</li>";
}

[andreasfriedrich]

explode() [php.net]ing on newline will work, but what happens when you ever want to store more than those list items in the BLOB [mysql.com]? I´d use some way to markup the items and then process them after you retrieve them from the DB.

Here´s another thread on how you might do that:

formatting p's and br's - encode and decode characters [webmasterworld.com]

Andreas

[crypto]

trismegisto,

You can use the nl2br($str) function to display
the text having linebreaks in it.

[andreasfriedrich]

However, simply using nl2br() [php.net] will not get you any structural markup. That´s why I would try to avoid it.

Andreas

[trismegisto]

Thanks everyone for your replies.

I’ve read the threads you posted Andreas, they have been very useful. I think the best solution is to use regex in php. Something like what you used here [webmasterworld.com] andreas. I’ve never used regex, so i’ll check the php manual and try to suit that script for my needs. I’ll let you know if i succeed or if i fail…