1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Need MySql help

Discussion in 'PHP & Perl' started by mylikes786, Apr 11, 2014.

  1. mylikes786

    mylikes786 Registered Member

    Joined:
    Oct 31, 2013
    Messages:
    81
    Likes Received:
    6
    I have lot of folder in cpanel. i want add all folder in a database is there any comand by running that all folder name will add in database?

    and same thing i want do for files. i want add all files in database it should scan file inside folder also
     
  2. theuman

    theuman Newbie

    Joined:
    Feb 28, 2011
    Messages:
    8
    Likes Received:
    1
    Occupation:
    Med Student
    Location:
    Sunny South Cost of England
    Home Page:
    Yeah you can do that. It would be very tedious and I am not too sure why you would want to do that.

    You can scan a folder and list all the files and add them individually and open sub-folders and do the same. But how many levels are you going at?
     
  3. bartosimpsonio

    bartosimpsonio Jr. VIP Jr. VIP Premium Member

    Joined:
    Mar 21, 2013
    Messages:
    12,269
    Likes Received:
    11,003
    Occupation:
    CHEAP
    Location:
    LINK LISTS
    Home Page:
    You need to clarify your question. What do you mean by adding folders to mysql?
     
  4. bpcl2

    bpcl2 Newbie

    Joined:
    Feb 7, 2013
    Messages:
    19
    Likes Received:
    1
    Are you saying databases?
     
  5. track89

    track89 Newbie

    Joined:
    Apr 16, 2014
    Messages:
    3
    Likes Received:
    0
    Occupation:
    M&A
    Location:
    Melbourne
    If you have a little PHP experience, you could try something like the "recursive directory iterator" (sorry, I can't post links yet) to get an array of all files and directories. Then use a foreach() loop to iterate through the output and put a MySQL INSERT query within it.
     
  6. Gogol

    Gogol Jr. VIP Jr. VIP

    Joined:
    Sep 10, 2010
    Messages:
    3,476
    Likes Received:
    3,102
    Gender:
    Male
    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 :)
     
    • Thanks Thanks x 1
  7. member8200

    member8200 Regular Member

    Joined:
    Aug 9, 2014
    Messages:
    473
    Likes Received:
    33

    i gotta thank you for this man. :) this could be very useful for me too. Cheers
     
    • Thanks Thanks x 1
  8. Gogol

    Gogol Jr. VIP Jr. VIP

    Joined:
    Sep 10, 2010
    Messages:
    3,476
    Likes Received:
    3,102
    Gender:
    Male
    Glad I could help :)