Full HTML pages in MySQL page?

How can I put complete pages in a database?

[dbzfyam]

I'm working on a small script to put full html pages in a database and then retreive the data to show on the page. The problem is that VARCHAR can only go up to 255 characters I believe. I also tried TEXT, but then I get a few columns like 'Field','Type','Function','Null' and 'Value'. I don't know what to do with the other columns. I also can't seem to be able to put data in the table with a script. Can anyone tell me what the best way is to do this and how to insert data into it with PHP? And are there any problems when I try to display it on the page (I read somewhere that special characters are being left out sometimes).

Thanks,
Stefan.

[ergophobe]

It looks like you're doing this via some GUI or something (phpMyadmin). Just ignore the Null, Function, and so forth "columns" in your case.

As for getting the page into the DB,

$page = file_get_contents("my_html_page.html"); // #1
$escaped_page = mysql_real_escape_string($page); // #2
$query = "INSERT INTO pages (page_id, page_html) VALUES(NULL, '$escaped_page');
$result = mysql_query($query);

#1 - I don't know where you're getting the pages - this assumes read from file

#2 - Must add slashes to escape the data, especially quotes and # and so forth in your text;

When you get your data back out of the DB, you will need to strip slashes from the data as in

$page = StripSlashes($db_data['page_html']);

Of course, the question in my mind is why you would want to save entire HTML pages in a DB unless you are creating a wayback machine or some other sort of archive.

[topr8]

>>unless you are creating a wayback machine or some other sort of archive.

or maybe copying other websites for some other reason

[ergophobe]

Yeah, I typed that and deleted it, but yes, if these are not your pages and you are doing anything with them other than storing them in the DB for later reading, you are likely violating copyright law, but that's a whole other can of worms.

[dbzfyam]

Thanks for the help, ergophobe. I'll try it out.

I didn't explain it correctly. What I meant was typing HTML code in a textfield which would then add it to the database (I want a easy way to update my site quickly since I don't have much time lately). I made a script which would insert the page name and content (by using a form) into the database. My layoutpage/main page then automatically updates the menu (It retreives the data from the database to create the links). On the content part of the layout, I've made a small script which would retreive the right page from the database based on the URL (like main.php?page=about which would retreive the html code stored in the row 'about').

This way, I don't have to write the pages, upload the files and update the menu manually like I did before. I don't know if there is a better way to do this and if this is searchengine-friendly though.

Can you tell me which characters I have to escape. Only with quotes and # or are there more characters which need to be escaped? I only knew about the quotes.

Thank you very much,
Stefan.

PS: I used PHPMyAdmin as the GUI indeed.

[Birdman]

You don't need to worry about escaping them by hand. The function, mysql_real_escape_string() [us2.php.net] does it automatically.

You may want to run this code first though:

if (get_magic_quotes_gpc() [us2.php.net]) {
$value = stripslashes($value);
}

Just in case magic_quotes_gpc() [us2.php.net] is enabled. If not, then your characters will be escaped twice (ie. Birdman\\'s).

Here is Erg's code with the slight mod:

$page = file_get_contents("my_html_page.html"); // #1
if (get_magic_quotes_gpc()) {
$escaped_page = stripslashes($page);
}
$escaped_page = mysql_real_escape_string($escaped_page); // #2
$query = "INSERT INTO pages (page_id, page_html) VALUES(NULL, '$escaped_page');
$result = mysql_query($query);

[ergophobe]

Got it! You want to save basically the central content of the page. That makes sense. When you said whole page I thought you meant from DOCTYPE to </html> at which point you've pretty much defeated the purpose of a dynamic site.

That changes things a bit, as magic_quotes_gpc might come into play, but Birdman has explained that sufficiently I think. If not, there are some good threads here on the subject that we can help you find if need be.

[dbzfyam]

Thank you very much for the help, Birdman. I'll try it out when my server is back online (My host is placing the servers in a other rack for some reason).

Got it! You want to save basically the central content of the page. That makes sense. When you said whole page I thought you meant from DOCTYPE to </html> at which point you've pretty much defeated the purpose of a dynamic site.

Indeed. When I read my first post again, I noticed I didn't really explain correctly what I wanted to do. Sorry for that.

Thanks all for the help. I'll let you know if I need some more help.
Stefan.

[yowza]

Didn't see anyone else answer the origninal part of your question about the character limit on text and varchar. What you want to use is mediumtext or longtext.

[dbzfyam]

Didn't see anyone else answer the origninal part of your question about the character limit on text and varchar. What you want to use is mediumtext or longtext.

Thanks for that, yowza. Do you know what the differences are between 'text', 'mediumtext' and 'longtext'? Do mediumtext and largetext have a character limit (I only tried 'text' so far)?

[ergophobe]

[dev.mysql.com...]

[dev.mysql.com...]

[dbzfyam]

Thanks for the links ergophobe! My server is back online again and I tried to add the new code to my script. The problem is, I somehow can't retreive the data when I add StripSlashes (I'm probably doing something wrong with the new code).

My code on the main page:

<?php
$dbhost = "localhost";
$dbuser = "user";
$dbpass = "pass";
$connect = mysql_connect($dbhost,$dbuser,$dbpass) or die (mysql_errno().": ".mysql_error()."<BR>");
mysql_select_db("test", $connect);
if (isset($_REQUEST['page'])) {
$sql = "SELECT * FROM content WHERE Link='".$_REQUEST['page']."'";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
StripSlashes($row['Content']);
PRINT "{$row['Page']}<BR><BR>{$row['Content']}";
PRINT "<BR><BR><BR>Author: {$row['Author']}";
}
else {include ("news.php");}
?>

I added the other part to the admin page aswell. Is it normal that the quotes display like normal quotes (Don't they have to be backslashed? Or I'm doing it wrong here again)
I added a test page with this code:
<font color="orange">
But it adds that exact line to the database without escaping the characters. The code for the update script:

$category = $_POST[Category];
$author = $_POST[Author];
$page = $_POST[Page];
$link = $_POST[Link];
$content = $_POST[Content];
if (get_magic_quotes_gpc()) { 
$escaped = stripslashes($content);
} 
$escaped = mysql_real_escape_string($escaped);
mysql_connect($dbhost,$dbuser,$dbpass);
mysql_select_db($dbname) or die(mysql_error());
$add = "INSERT INTO content (Category, Author, Page, Link, Content) VALUES('$category','$author','$page','$link','$escaped')";
mysql_query($add) or die(mysql_error());

Can someone help me with this?
Thanks.

[Sarah Atkinson]

this page might help explain colum types more
[htmlite.com ]

[Birdman]

Stripslashes() shouldn't prevent you from retrieving the data. I suggest you print the mysql query to make sure the proper "Page" value is there. Most likely, you are just getting an empty result set.

if (isset($_REQUEST['page'])) {
$sql = "SELECT * FROM content WHERE Link='".$_REQUEST['page']."'";
print $sql;
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
StripSlashes($row['Content']);
PRINT "{$row['Page']}<BR><BR>{$row['Content']}";
PRINT "<BR><BR><BR>Author: {$row['Author']}";
}

Then double check the db to make sure the values match.

[dbzfyam]

this page might help explain colum types more
[htmlite.com...]

Thank you very much for the link, Sarah! I think I'll change 'text' to 'longtext' (quite a difference..)

I suggest you print the mysql query to make sure the proper "Page" value is there.

Thanks for that Birdman (Why don't I think of something simple like that?). I printed a few of them and found out that it keeps looking for the text 'about', while it should be looking for main.php?page=about (In case I need to include something else). I fixed that now.

One last question though, when my script escapes the characters in the content field, is it normal that PHPMyAdmin shows the text without the slashes? My old host did show them. Just to make sure everything is as it should be.

Thanks

[Birdman]

I believe it should show the slashes. That could be a configurable option though, so you can double check by removing the stripslashes() function temporarily.

Cheers

[dbzfyam]

I've tried my script on my old server and that one also doesn't display them. Is my code correct (see message 12; last part)? When the text is displayed with or without StripSlashes, it doesn't change (the page is being displayed the way it should be). Is it neccesary to use StripSlashes when I use LongText for the column type or can it be left out?

[dbzfyam]

Ah, nevermind. It looks like it's working now. When I print the page without StripSlashes, it now displays \" instead of ". PHPMyAdmin doesn't show them. I think they did that so you can edit the content. Thanks to everyone for the help!

[intrepidbunny]

A related question: Is it ever preferable to preassemble PHP pages and store the code in a mysql table rather than parsing them on the fly?

I run a business directory, and each profile on the directory draws data from about 13 tables. In the past few weeks our traffic has shot up to 35,000 page views/day, and it caused our dedicated server to lag a bit. To speed things up, I started storing many of the dynamic pages in a table. It seems to be faster, but I'm not sure what the consequences will be as our traffic continues to rise.

Any advice/opinions would be appreciated.