Automatically adding files from a directory to a database.

Residue

Ok, I downloaded a script that reads all the zip files in the directory it's in and displays them on a page, with the file size aswell.

I then changed it a bit, so it would add the files to a database instead of displaying them on a page. But I need help with two things. (I then call them with a more complex download script)

First off here is the script:

<?php

require("config.php");
require("functions.php");
$ffdb=new Ffdb;
$ffdb->StartTimer();
$tpl=new Templates;
$mysql=new Mysql;
$mysql->Connect();
set_magic_quotes_runtime(0);
error_reporting  (E_ERROR | E_WARNING | E_PARSE);
// Ok Time to do some HTML fun!
?>

<?php

$handle=opendir('.');
while (($file = readdir($handle))!==false){
rtrim($file);
if($file=="."){
$file=readdir($handle);
$file=readdir($handle);
}
if($file=="hits.txt"){
$file=readdir($handle);
}
if(eregi("[a-zA-Z0-p_-]*.zip",$file)!==false){
$spliter=explode(".",$file);
$filea="$spliter[0].zip";
$filei="TEXT INFO";
$filez="$spliter[0].zip";
$size=filesize($filez);
$size=($size/1000);
$size=round($size,1);
$info="  Text info files have not yet been created..";
echo "Adding $spliter[0] <br>";
$mysql->Query("INSERT INTO `downloads` ( `size` , `name` , `url` ) VALUES ('$size', '$spliter[0]', '$filea')");
echo "$spliter[0]. Added Successfully</br>";
$filer="$file";
$file=file($filer);
$sline=0;
$count=count($file);
$eline=$count;
$i = $count;
for($j=$sline;$j<$eline;$j++){

}


} else {

}

}




?>

Where it says

$mysql->Query("INSERT INTO `downloads` ( `size` , `name` , `url` ) VALUES ('$size', '$spliter[0]', '$filea')");

there was originally an echo command that would just show the files on that index.php

This works, and if I were to upload 5000 files in the directory, and go to this (which I called "add.php") all 5000 files would automatically be added, however there are two more things that I need to do.

I want to make this even more efficient, by setting this to run every hour or so using a cron job...the problem is, right now it would keep on adding all of the same files over and over again.

So request 1 is: Can you help me make it so it queries the table "downloads" to find out which files have already been added there, and for it to ignore the already added files and add the new files.

Secondly, my site orders all files alphabetically, but in order to do this easily in each file row there is a field called "letter", which goes from 1 - 27 (1 = A, 26 = Z, 27 = 0-9) Is there a way to use php to find out the first letter of the files in the directory, and then add the appropiate number (ie: if the file begins with an "b", then it would be able to add the number "2")

Thanks in advance.

superwormy

You can access the first letter of a string by doing:

$string{0}

Likewise, the second letter is:

$string{1}

Etc.

As far as checking files... before you do your insert, do a SELECT:

SELECT * FROM downloads WHERE name = '$splitter[0]' LIMIT 1

If it returns a record, don't insert it. If it does not, insert it.
You could make it even better if you checked if the file was new... maybe check the file mod time ( theres a PHP function for this under the Filesystem functions ) or compare filesizes... or maybe even better, store and MD5() of the file and compare that to the MD5() of the current file.

Then if the MD5() / mod time / filesizes are different, do an UPDATE:

UPDATE downloads SET filesize = '$new_filesize', md5 = '$new_md5'

Etc.

intenz

This works too, superwormy...

$string = 'foo';
echo $string[2];
# Results: o

Residue

Damn, now I feel stupid...it seems so easy now.

Thanks alot you two, i'll mark this resolved.

coreyp_1

RE: adding files into a database

If you want to use brute force in updating your file list, simply clear out the old table entirely and repopulate it from scratch. It would take less queries this way. For that matter, it would also deal with the issue of removing deleted files from the database.

-- OR --

Again, in attempting to keep down the number of queries, you could build a new (temporary) table containing all of the current files, then, in one command, merge the tables. This would lessen the possibility of someone attempting to access the table while you are modifying it.

Just some thoughts...

[EDIT: Oops... I guess I started typing about the time you posted the issue resolved.]

superwormy

You could also use string functions to get pieces of strings...
www.php.net/substr
www.php.net/stristr
www.php.net/strpos

Residue

Originally posted by coreyp_1
RE: adding files into a database

If you want to use brute force in updating your file list, simply clear out the old table entirely and repopulate it from scratch. It would take less queries this way. For that matter, it would also deal with the issue of removing deleted files from the database.

-- OR --

Again, in attempting to keep down the number of queries, you could build a new (temporary) table containing all of the current files, then, in one command, merge the tables. This would lessen the possibility of someone attempting to access the table while you are modifying it.

Just some thoughts...

[EDIT: Oops... I guess I started typing about the time you posted the issue resolved.]

Originally I was gonna truncate the downloads db, and re add everything each time...but then I would lose all the statistics (total downloads, comments, download_id) each time round.

Yeah I was thinking about that, I saw your function in another post, the url was http://www.durdenwebapplications.com/durdenwebapplications_PHP/#StrBetweenStr that would be pretty cool for grabbing other statistics that php can't really grab from normal file statistics, like I could include author name here in between two underscores and include it.

But how exactly would I go about using a function like yours with the existing php functions that you just mentioned.