Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,446
    Thanks
    275
    Thanked 32 Times in 31 Posts

    Restore a demo database php mysql cron

    Hi this is nothing fancy and still needs a bit of tweeking with some TODO stuff left but it does function and it does do the job for me as is for this particular db and sql structure. So i wanted to share it as a starter for someone who was trying to do the same thing.

    Mission: I have a software demo that is still uses the mysql library and i want to restore the data in the database every 5 hours (4x a day) to keep the data fresh.

    There are two files:

    One is the php file that builds an array from the sql file and uses ; as the end of the query and the processes the query. This means that you have to make sure you dont have   or other stuff in your text data that ends in ; other than the end of the query itself. This is on the TODO list. I actually had a routine that worked but i didnt like it so i want to redo it.

    The second file is the sql file. I went through the file and removed all comments because a of now i dont filter those. Again i had a routine but it was a hack at best and i wanted to do a better job. So i just cleaned up my sql file so that this would work as is for now.

    Then in cron you just execute the php file when you want.

    Here is my php file

    PHP Code:

    <?php

    /*
     * dbrestore.php 1.0 Nov 2013
     *
     * Description: Restore demo db using this file executed by cron
     */



    $rdbhost 'localhost';
    $rdbuser 'prefix_username';
    $rdbpwd  'password';
    $rdbname 'prefix_dbname';

                 
    // Open db connection

            
    $rlink mysql_connect($rdbhost$rdbuser$rdbpwd);
            if (!
    $rlink
            {        
                die(
    'Could not connect: ' mysql_error());
            }

            if (!
    mysql_select_db($rdbname)) 
            {            
                die (
    "Can't use database : " mysql_error());
            }    

              
    // read the sql file and write to database 

               
    $filename 'restore.sql';
                if (!(
    $f fopen($filename"r" )))   
                {
                
    $msg 'Could not open file with sql instructions: '.$filename;
                      echo 
    "<pre>";
                      echo 
    $msg;
                      exit;        
               }else{
                
                      
    //file is open so read it
                              
                      
    $sqlf fread($f,filesize($filename));
                            
    $sqlarray explode(';',$sqlf);

                           while (
    $s fgets ($f10240)) 
                  {

                            
    //now run the loop and process statements
                            
    foreach($sqlarray as $key => $sqlstatement)
                            {    
                    
    //check if there is a statement or not so you dont have an empty query
                    
    if($sqlstatement)
                    {
                                    
    //readd the ; back to the end of the statement
                                    
    $sqlstatement $sqlstatement.';';

                                    
    //execute query
                                  
    $results mysql_query($sqlstatement);         
     
                                    
    //todo need to work on protecting from possible sql injection on this kind of project via cron
                                    //todo  need to filter out things like &nbsp; and text lines that end in ; but are not the end of the query
                           //todo  need to filter out comments in the sql file

                          
    if (!$results
                       {
                                   
    $mserror mysql_error($rlink);
                                 echo 
    "Error: ";
                                   echo 
    $sqlstatement;
                                   echo 
    " message: ";
                                   echo 
    $mserror;
                             exit;
                             }
    //close if     
                        
                    
    //close if sqlstatement

                               // at the end of each sql statement show done  -- for testing only
                               // echo $sqlstatement."  DONE!";
                               // echo "<br /><br />";

                    
    //close foreach

                          
    }//close while
                      
                 
    fclose($f);
                 }
    // close else

    // close up shop and exit
    mysql_close($rlink);

    //todo test to see if this echo shows up in the cron report or not, i actually dont know.
                
    echo "db has been restored";
    exit;
    ?>
    sql file not included but just make sure its clean and all lines end with ; and that nothing between the first and last of each query has a semicolon ;


    Any improvments or suggestions welcome... especially when it comes to protecting again sql injection on this type of project.

    thanks
    Last edited by durangod; 11-13-2013 at 06:46 AM.

  • #2
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,446
    Thanks
    275
    Thanked 32 Times in 31 Posts
    One thing i forgot to share last night was the fact that in my sql i added some truncates to the first part of the file to clear the tables before reload. You are not deleting the DB your are just clearing it and restoring the data.

    so in my case i have 17 tables, i could have put them into an array but since so few i just put 17 truncates

    TRUNCATE TABLE prefix_name;
    TRUNCATE TABLE prefix_name;
    TRUNCATE TABLE prefix_name; ... and so on

    then i started my create and insert querys.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •