How to Convert Web Pages to a Database

Create a database table. The table needs a "name" and "data" field. Use the "blob" type for the data, as the file contents should be stored in binary format.

CREATE TABLE `web2db` (

`name` varchar(255) NOT NULL,

`data` blob NOT NULL,

PRIMARY KEY (`name`)

);

Create a PHP file to store the pages in the database. This example uses "convert.php."

Create a function in your PHP file to connect to the MySQL database. Use the values for your server path, username, and password instead of the placeholders below.

function dbConnect(){

return mysql_connect(DB_SERV,DB_USER,DB_PASS);

}

Create a function that will store data from a webpage in the database. Use the name of your database instead of the placeholder for mysql_select_db.

function dbWrite($name,$data){

$dbs = dbConnect();

$name = mysql_real_escape_string($name);

$data = mysql_real_escape_string($data);

mysql_select_db(DB_NAME);

mysql_query("INSERT INTO web2db(name,data) VALUES('$name','$data')");

mysql_close($dbs);

}

Create a function that will search the current directory for files and add them to the database. The example only searches for HTML in the current directory, but it can easily be modified to search for other file types and to search subdirectories.

function findFiles(){

$dirStream = opendir(".");

while($nextFile = readdir($dirStream)) $dirArray[] = $nextFile;

closedir($dirStream);

foreach($dirArray as $thisFile){

if(substr($thisFile,-4)!==".htm" && substr($thisFile,-5)!==".html") continue;

$data = file_get_contents($thisFile);

dbWrite($thisFile,$data);

}

}

Call the findFiles function at the end of the PHP file. The full contents of the example code for the page are given below.

function dbConnect(){

return mysql_connect(DB_SERV,DB_USER,DB_PASS);

}

function dbWrite($name,$data){

$dbs = dbConnect();

$name = mysql_real_escape_string($name);

$data = mysql_real_escape_string($data);

mysql_select_db(DB_NAME);

mysql_query("INSERT INTO web2db(name,data) VALUES('$name','$data')");

mysql_close($dbs);

}

function findFiles(){

$dirStream = opendir(".");

while($nextFile = readdir($dirStream)) $dirArray[] = $nextFile;

closedir($dirStream);

foreach($dirArray as $thisFile){

if(substr($thisFile,-4)!==".htm" && substr($thisFile,-5)!==".html") continue;

$data = file_get_contents($thisFile);

dbWrite($thisFile,$data);

}

}

findFiles();

?>

Navigate to your PHP file in a Web browser to run the script. Check your database contents to make sure it worked.

Create another PHP file to display the pages stored in the database. You can simply call it "index.php".

Check to see if a "page" variable has been passed to the script, which is how you will tell the script which file to fetch from the database. If not, set it to a default value. The example uses "index.html".

$page = (isset($_GET['page']) ? $_GET['page'] : "index.html");

Use the same function from your PHP file to connect to the database.

function dbConnect(){

return mysql_connect(DB_SERV,DB_USER,DB_PASS);

}

Write a function that will read the data from the database and return it. The function returns false if the page cannot be found. Remember to change mysql_select_db to use the proper database name.

function dbRead($name){

$dbs = dbConnect();

$name = mysql_real_escape_string($name);

mysql_select_db(DB_NAME);

$result = mysql_query("SELECT data FROM web2db WHERE name = '$name'");

if(!$result){ mysql_close($dbs); return false; }

if(mysql_num_rows($result)==0){ mysql_close($dbs); return false; }

$result = mysql_result($result,0);

mysql_close($dbs);

return $result;

}

Use the dbRead function to fetch the page from the database.

$pageData = dbRead($page);

Check to see if the dbRead function returned page data. If so, display the page contents. If not, display an error message. The full contents of index.php are below.

$page = (isset($_GET['page']) ? $_GET['page'] : "index.html");

function dbConnect(){

return mysql_connect(DB_SERV,DB_USER,DB_PASS);

}

function dbRead($name){

$dbs = dbConnect();

$name = mysql_real_escape_string($name);

mysql_select_db(DB_NAME);

$result = mysql_query("SELECT data FROM web2db WHERE name = '$name'");

if(!$result){ mysql_close($dbs); return false; }

if(mysql_num_rows($result)==0){ mysql_close($dbs); return false; }

$result = mysql_result($result,0);

mysql_close($dbs);

return $result;

}

$pageData = dbRead($page);

echo ($pageData ? $pageData : "Page not found.");

?>

Navigate to index.php in a Web browser to make sure it loads correctly. If you want to fetch the contents of the example.php, you would navigate to: convert.php?page=example.php .