Need MySql help

g0g0l said:

For some special reason, I am feeling great today and I did have a free hour. So, let me help you out. There you go :


First, create a database and insert the following sql which creates the table called path

Code:

CREATE TABLE IF NOT EXISTS `path` (
  `id` int(15) NOT NULL AUTO_INCREMENT,
  `url` varchar(512) NOT NULL,
  `type` enum('folder','file') NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Now that we have the db ready, let's code the script. Save the following code as urlscan.php on top of the structure ( from which, you want to start insert). Directly call the file and it will do it's job

Code:

<?php
// unlimited execution time for long operations.
set_time_limit(0);
/**
  * Start db details. Modify according to your db setup
**/
define('DBHOST' , 'localhost');
define('DBNAME' , 'folders');
define('DBUSER' , 'root');
define('DBPASS' , '');


/**
  * end db details and start connection
 **/
try {
    $db = new PDO('mysql:host=' . DBHOST . ';dbname=' . DBNAME, DBUSER, DBPASS);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (Exception $e) {
    die($e->getMessage());
} 

/**
  * end db  connection and start iterator function
 **/
 
 function DirectoryIteratorToArray(DirectoryIterator $it) {
    foreach ($it as $key => $child) {
        // if dot files e g  . / .. don't add
        if ($child->isDot()) {
            continue;
        }
        // get the name of the file/folder
        $name = $child->getBasename();
        // if the file is this script, don't add
        if($name == basename(__FILE__)){
            continue;
        }    
        //is it a folder? 
        if ($child->isDir()) {
            // add to db as a folder
            insert_to_db($child->getPathname() , 'folder');
            //take the folder and make a sub object
            $subit = new DirectoryIterator($child->getPathname());
            // recursively call the function itself.
            DirectoryIteratorToArray($subit);
        } 
        // not a folder. we can add it to db as file
        else {
            insert_to_db($child->getPathname() , 'file');
        }
    }
    return;
}
 
 // function insert_to_db()
 
 function insert_to_db($path, $type){
    // we defined db outside this function, so let's take it as a global
    global $db;
    // see if the url already exists. if exists, don't insert.
    $st = $db->prepare('SELECT id from `path` WHERE url= :url AND type= :type');
    $st->bindParam(':url', $path, PDO::PARAM_STR);
    $st->bindParam(':type', $type, PDO::PARAM_STR);
    $st->execute();
    if($st->rowCount() < 1 ){
        // doesn't exist, insert!
        $st = $db->prepare('INSERT INTO `path` (`id`, `url`, `type`, `timestamp`) VALUES (NULL,:url,:type,null)');
        $st->bindParam(':url', $path, PDO::PARAM_STR);
        $st->bindParam(':type', $type, PDO::PARAM_STR);
        $st->execute();
    }
 }
 

$it = new DirectoryIterator(dirname(__FILE__));
DirectoryIteratorToArray($it);

N.B. : Instead of just copy pasting, try to understand what is being done and why. That would help you learn something

Click to expand...

member8200 said:

i gotta thank you for this man. this could be very useful for me too. Cheers

Click to expand...