Project : Storing file details in a MySQL table

In Part 1 of the project we have seen how to display the file details of a directory. The same list we will try to store in a table. Our table will have four columns. All are available here.
First the connecting file z_db1.php

<?Php
$dbhost_name = "localhost";
$database = "sql_tutorial";
$username = "root";
$password = "test";

//////// Do not Edit below /////////
try {
$dbo = new PDO('mysql:host=localhost;dbname='.$database, $username, $password);
} catch (PDOException $e) {
//print "Error!: " . $e->getMessage() . "<br/>";
die();
}
?>

After connecting to MySQL we will insert the data. The part which does the storing is here.

/////Inserting to MySQL database //////
$sql=$dbo->prepare("insert into file_dtl(url,title,dt) values(:url,:title,:dt)");
$sql->bindParam(':url',$url,PDO::PARAM_STR, 200);
$sql->bindParam(':title',$title,PDO::PARAM_STR, 150);
$sql->bindParam(':dt',$dt,PDO::PARAM_STR,10);
if($sql->execute()){
}
else{
print_r($sql->errorInfo()); 
echo "<br>";
//echo " Not able to add data please contact Admin ";
}
////// Enf of Inserting to MySQL datbase //////

We have used PDO to connect and insert data to MySQL table
The complete script is here

<!doctype html public "-//w3c//dtd html 3.2//en">
<html>
<head>
<title>Storing the file data in a MySQL table</title>
</head>
<body>
<?Php
include "z_db1.php";
///////////////function my_strip///////////
function my_strip($start,$end,$total){
$total = stristr($total,$start);
$f2 = stristr($total,$end);
return substr($total,strlen($start),-strlen($f2));
}
/////////////////////End of function my_strip ///
///////////// Reading of file content////
$i=0; // Number of file counter
echo "<table><tr><th>URL ( Path + file name)</th><th>Title</th><th>Date</th></tr>";

$path="dir_name/path/";// Change this to your directory or path

$handle=opendir($path);
while (($file_name = readdir($handle))!==false) {
$str="";
if(stristr($file_name,".php")){ // We are checking for a perticular file extension, change this to match yours
$url="$path".$file_name;
$contents="";
$fd = fopen ($url, "r"); // opening the file in read mode
while($buffer = fread ($fd,1024)){
$contents .=$buffer;
}
/////// End of reading file content ////////

//////// We will start with collecting title part ///////
$title = my_strip("<title>","</title>",$contents);
$dt=date("Y-m-d",filemtime($url)); // Last modification date of the file

/////Inserting to MySQL database //////
$sql=$dbo->prepare("insert into file_dtl(url,title,dt) values(:url,:title,:dt)");
$sql->bindParam(':url',$url,PDO::PARAM_STR, 200);
$sql->bindParam(':title',$title,PDO::PARAM_STR, 150);
$sql->bindParam(':dt',$dt,PDO::PARAM_STR,10);
if($sql->execute()){
}
else{
print_r($sql->errorInfo()); 
echo "<br>";
//echo " Not able to add data please contact Admin ";
}
////// End of Inserting to MySQL datbase //////

//echo "<tr><td>$url</td><td>$title</td><td>$dt</td></tr>";

$i=$i+1;
}// end of if checking file extension
}// end of while reading directory
closedir($handle);
echo "</table>";

echo "Total Number of files checked = $i ";
?>
</body>
</html>

The SQL Dump of the table is here

CREATE TABLE IF NOT EXISTS `file_dtl` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `url` varchar(200) NOT NULL,
  `title` varchar(150) NOT NULL,
  `dt` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=136 ;

File Checking if file exists before deleting
PHP

Subscribe to our YouTube Channel here

Storing file Data in MySQL table

Subscribe