PDA

View Full Version : displaying text from database field format problem


desertdirk
04-24-2006, 12:37 AM
A site has a page to display news information that the site owner types into a mysql database table as longtext. When the news is displayed on the page, it comes out as one long paragraph, even though it was typed in as paragraphs. How can I make it display properly.

http://www.jicarillamining.info/news_detail.php?recordID=14

Thanks
DD

dthomsen8
04-24-2006, 01:15 AM
A site has a page to display news information that the site owner types into a mysql database table as longtext. When the news is displayed on the page, it comes out as one long paragraph, even though it was typed in as paragraphs. How can I make it display properly.

http://www.jicarillamining.info/news_detail.php?recordID=14

Thanks
DD

What did this page look like before it was placed in the MySQL database?

How was it placed into the database? "Typed in" some software tool, or directly into MySQL?

More details are needed.

desertdirk
04-24-2006, 01:39 AM
Whether you type it into the form, copy and paste from note pad, or Word, it does not make paragraphs. Even if you type it directly into the database, it will not work.
DD

davidj
04-24-2006, 08:44 AM
what if you changed the field type in the db to a blob??

im not saying i know this is the case, just that i thought a blob would retain its input structure?

am i wrong ???

also you could try wrapping your string variable in a nl2br() php function

example ....
echo nl2br($paragraph_string);
this will replace any new line return chars with

dthomsen8
04-24-2006, 01:28 PM
Whether you type it into the form, copy and paste from note pad, or Word, it does not make paragraphs. Even if you type it directly into the database, it will not work.
DD

Did you try inserting HTML into the text?

or

tags, for example? I am not certain this will work, but try it.

desertdirk
04-24-2006, 03:29 PM
I have tried both longtext and blob with same results. I added

tags and that works. However I can't expect the client to do that for every article he wants to enter. I really thought that there was some way to set this up in the database. Do all long text and blob fields really display this way?
DD

davidj
04-24-2006, 03:34 PM
did you try ...


echo nl2br($paragraph_string);


if the text is getting keyed into the db from your front end and you are developing in PHP why not use a nl2br() function befor the text is added to the insert statement.

will be inserted for any \nl it finds

post your code

desertdirk
04-24-2006, 04:47 PM
I have not tried that function. Where does it go? here is the code:

<?php require_once('../Connections/conn_jicarilla.php'); ?>
<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "add_news_form")) {
$insertSQL = sprintf("INSERT INTO news (title, enter_date, `desc`) VALUES (%s, %s, %s)",
GetSQLValueString($_POST['title'], "text"),
GetSQLValueString($_POST['date_field'], "text"),
GetSQLValueString($_POST['news_field'], "text"));

mysql_select_db($database_conn_jicarilla, $conn_jicarilla);
$Result1 = mysql_query($insertSQL, $conn_jicarilla) or die(mysql_error());

$insertGoTo = "control.php";
if (isset($_SERVER['QUERY_STRING'])) {
$insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
$insertGoTo .= $_SERVER['QUERY_STRING'];
}
header(sprintf("Location: %s", $insertGoTo));
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>add news</title>
</head>

<body>


Add New News Article</p>
<form name="add_news_form" id="add_news_form" method="POST" action="<?php echo $editFormAction; ?>">
<table width="702" border="1">
<tr>
<td width="36">Date</td>
<td width="650"><input name="date_field" type="text" id="date_field" /></td>
</tr>
<tr>
<td>Title</td>
<td><input name="title" type="text" id="title" /></td>
</tr>
<tr>
<td>Article</td>
<td><textarea name="news_field" cols="70" rows="5" id="news_field"></textarea></td>
</tr>
</table>



<input type="submit" name="Submit" value="Submit" />
</p>
<input type="hidden" name="MM_insert" value="add_news_form">
</form>


return to control panel (control.php)</p>
</body>
</html>

davidj
04-24-2006, 04:53 PM
i need to change it abit coz its full of DreamW gubbins

i will streamine it in the morning with the added function included

(morning = UK morning which is in about 15 hrs)

desertdirk
04-24-2006, 05:06 PM
OK Dj
thanks. I wasn't sure if it went in the insert or on the display page.
Lovely Dreamweaver wants to add so much stuff--

DD

davidj
04-25-2006, 08:36 AM
ok

i have added the function and cleaned up the insert statement
This replaces all your PHP so paste this after you delete the old stuff.


<?php require_once('../Connections/conn_jicarilla.php'); ?>
<?php

$title = $_POST['title'];
$date_field = $_POST['date_field'];
$news_field = $_POST['news_field'];

$news_field = nl2br($news_field); /// *** <<<< the magic added

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "add_news_form")) {
$insertSQL = sprintf("INSERT INTO news (title, enter_date, `desc`) VALUES (%s, %s, %s)",$title, $date_field, $news_field );

mysql_select_db($database_conn_jicarilla, $conn_jicarilla);
$Result1 = mysql_query($insertSQL, $conn_jicarilla) or die(mysql_error());

$insertGoTo = "control.php";
if (isset($_SERVER['QUERY_STRING'])) {
$insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
$insertGoTo .= $_SERVER['QUERY_STRING'];
}
header(sprintf("Location: %s", $insertGoTo));
}
?>

desertdirk
04-26-2006, 02:15 AM
Hmmm
DJ I copied and pasted you code. Now I am
getting this SQL error:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'Test, 4/25/06, this is line one
this is line two

davidj
04-26-2006, 09:05 AM
you date is going in incorrectly

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'Test, 4/25/06 <<<<<<< error

desertdirk
04-26-2006, 08:40 PM
don't see how, it is a text field (rather a varchar field). the error is showing for all the entered fields: title, date, and content
DD

davidj
04-27-2006, 08:41 AM
where you have this line in your code...

$insertSQL = sprintf("INSERT INTO news (title, enter_date, `desc`) VALUES (%s, %s, %s)",$title, $date_field, $news_field );

add an echo and run the script again ...

echo $insertSQL = sprintf("INSERT INTO news (title, enter_date, `desc`) VALUES (%s, %s, %s)",$title, $date_field, $news_field );

copy the output and post the results to this thread

desertdirk
04-27-2006, 03:56 PM
here is the result:

INSERT INTO news (title, enter_date,`desc`) VALUES (test, 4/27/06, this is line one
this is line 2
this is line 3)You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'line one
this is line 2
this is line 3)' at line
DD

desertdirk
04-27-2006, 04:15 PM
DJ
I put the function in the display page and that seems to work ok. Maybe this function is not for inserting into the database?
DD

davidj
04-27-2006, 04:17 PM
this doesnt show all the the INSERT SQL

please post the error as its displayed

also i could be wrong but your field desc could be a reserved name in MYSQL as it represents an order to sort data

desertdirk
04-27-2006, 11:29 PM
DJ
that was the whole error that appeared on the page. I am going to change the field name. Knew it was in quotes for some reason, but wasn't sure why.
DD

Chris b
06-12-2006, 04:20 PM
I use this extension to add newlines as BR. (etc.,etc..)

http://www.tecnorama.org/document.php?id_doc=51

I think it's still a free extension.

Hope it helps,

Chris