MySQL

[Newbie]

How do you store information into a table that takes up two lines (has a carriage return in it)?

Example:

Line of text

Line of text

Be able to add that to a database and recall it so it looks the same.

[Graphmastur]

I am not sure, but you might be able to use the escape character "\n".

I haven't done SQL in forever, so...

[Newbie]

It appears it does it automatically. My problem was I was using VARCHAR which only allows for 255 characters. I tried using LONGTEXT, but it will not let me create a table with that data type for some reason.

[magicdanw]

What sort of data are you storing? If you're writing a web application that just needs to display the text with the line breaks, then using HTML format <br /> tags is the best way to go. Or, if the data on each line is a separate entity, they can be stored in two columns instead, and the newline added in whenever the text must be displayed. But that's your call how the data should be stored.

[Newbie]

I need to store like a blog like post. The only reason I need the line breaks is for paragraphs and that's it. Is there a way to get php to show the breaks when it calls the data from the database.

ie

<?php
// Make a MySQL Connection
mysql_connect("localhost", "user", "pass") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());

$query = "SELECT * FROM example";

$result = mysql_query($query) or die(mysql_error());


$row = mysql_fetch_array($result) or die(mysql_error());
echo $row['data'];
?>

That grabs all the text which appears to have the line breaks in the database, but it displays it as if there are no breaks.

[Newbie]

I tried the breakout \n. It worked in the database but not with the php.

[Newbie]

Thanks. I fixed the issue using a string replace.

Quick question though. I have the ID auto increment for each data entry. If I delete an entry how do I get it so it will shift everything down?

What I mean is If I have 5 items 1-5 and I delete 4 it will be like 1, 2, 3, 4, and not 1, 2, 3, 5?

[magicdanw]

Don't. Ever. The point of an autoincrementing ID is so that every item will have a unique ID FOREVER. Even if it's deleted. You use the IDs to reference records in other tables (cross-referencing), and so it can't change. IDs should only be used to identify a record, and if you want something to order the items, use another column, such as a date column, or a position column, or whatever you need.

[Newbie]

Oh OK. Thanks. I'll just timestamp my records then.

Edit: Not records, but items.

[benryves]

With regard to turning ASCII newlines into HTML breaks, see the PHP nl2br() function (I know you already solved this, but always nicer to use a built-in function than straight string replacement).

[Newbie]

Thanks benryves! I"m actually going to do that as it will get rid of a lot of extra lines of code for a much simpler way of doing it.

Just applied it. I'll take one line of code over three any day.