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 11 of 11
  1. #1
    Regular Coder
    Join Date
    Sep 2007
    Posts
    809
    Thanks
    5
    Thanked 2 Times in 2 Posts

    Exporting database content with PHP

    Hi,

    Basically what I want to do is have an automated back up of each table within my database, on a daily basis.

    I'm pretty sure this is possible, but I am having trouble writing a script to do so... From what I can gather the script will need to do the following...

    1) Use a select statement to select all data from the table in question
    2) Take all the data and add it to a string
    3) Write the data to a text file

    I can do step one no problem (the easiest bit I know) but I have no idea how to do the other steps. Can anyone advise here?

    Thanks

  • #2
    Regular Coder
    Join Date
    Nov 2009
    Location
    Scotland / Glasgow
    Posts
    184
    Thanks
    1
    Thanked 19 Times in 19 Posts
    I cant help automating it but i can help with the script that pulls the table data and store it's in an external xls file.
    PHP Code:
    <?php
    $dbHost 
    "localhost";
    $dbUser "username";
    $dbPass "password";
    $dbName "db_1"

    $link mysql_connect($dbHost$dbUser$dbPass);
    if (!
    mysql_select_db($dbName)) {
        echo 
    "Couldnt find database";
        exit;
    }  
    $query "SELECT name, company, address, email, submitted, question, fsa FROM questions";
    $result mysql_query($query);
    if(!
    $result) {
         echo 
    "read error";
        exit;
    }
    $header="";
    $data="";
    $count mysql_num_fields($result);
    for (
    $i 0$i $count$i++){
        
    $header .= mysql_field_name($result$i)."\t";
    }
    while(
    $row mysql_fetch_row($result)){
          
    $line '';
          foreach(
    $row as $value){
            if(!isset(
    $value) || $value == ""){
                  
    $value "\t";
            }else{
    # important to escape any quotes to preserve them in the data.
                  
    $value str_replace('"''""'$value);
    # needed to encapsulate data in quotes because some data might be multi line.
    # the good news is that numbers remain numbers in Excel even though quoted.
                  
    $value '"' $value '"' "\t";
            }
            
    $line .= $value;
          }
        
    $data .= trim($line)."\n";
    }
    # this line is needed because returns embedded in the data have "\r"
    # and this looks like a "box character" in Excel
    $data str_replace("\r"""$data);


    # Nice to let someone know that the search came up empty.
    # Otherwise only the column name headers will be output to Excel.
    if ($data == "") {
        
    $data "\nno matching records found\n";
    }

    # This line will stream the file to the user rather than spray it across the screen
    header("Content-type: application/octet-stream");

    # replace excelfile.xls with whatever you want the filename to default to
    $fname "database_entries.xls";
    header("Content-Disposition: attachment; filename=$fname");
    header("Pragma: no-cache");
    header("Expires: 0");
    echo 
    $header."\n".$data
    mysql_close($link);
    ?>
    This is not my code but like yourself needed to pull and store info from the database in an external file.

    Hope it's of some help
    .:To me AS is like LEGO, Only for the big Kids :.
    - Site - Blog - Glasgow Flasher

  • #3
    Regular Coder
    Join Date
    Sep 2007
    Posts
    809
    Thanks
    5
    Thanked 2 Times in 2 Posts
    Hi, Many thanks for that...

    As for automating, our hosting provider provides a tool called CRON JOBS which apparently will take the PHP and run it when specified, should I should be OK there.

    Just one questions regarding this code...

    It's saving it as an excel file right? Also where does it save it to?

  • #4
    Regular Coder
    Join Date
    Nov 2009
    Location
    Scotland / Glasgow
    Posts
    184
    Thanks
    1
    Thanked 19 Times in 19 Posts
    ah this script prompts you to download the file.

    I will see if i can do a quick mode so it saves to a location on your server.
    .:To me AS is like LEGO, Only for the big Kids :.
    - Site - Blog - Glasgow Flasher

  • #5
    Regular Coder
    Join Date
    Nov 2009
    Location
    Scotland / Glasgow
    Posts
    184
    Thanks
    1
    Thanked 19 Times in 19 Posts
    Sorry time is getting away from me today.
    I did a quick search and found this
    Hopefully there is enough there to get you moving in the right direction a bit quicker.
    .:To me AS is like LEGO, Only for the big Kids :.
    - Site - Blog - Glasgow Flasher

  • #6
    Regular Coder
    Join Date
    Sep 2007
    Posts
    809
    Thanks
    5
    Thanked 2 Times in 2 Posts
    Thanks for that, I'll check it out!

    Came across a few scripts since then to be honest...

    I think the problem will be trying to run it as a CRON Job, since I have no knowledge of CRON commands!

    thanks once again

  • #7
    Regular Coder
    Join Date
    Nov 2009
    Location
    Scotland / Glasgow
    Posts
    184
    Thanks
    1
    Thanked 19 Times in 19 Posts
    Check this. I was up to speed in a couple of minutes
    .:To me AS is like LEGO, Only for the big Kids :.
    - Site - Blog - Glasgow Flasher

  • #8
    Regular Coder
    Join Date
    Sep 2007
    Posts
    809
    Thanks
    5
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by [Paul Ferrie ] View Post
    Check this. I was up to speed in a couple of minutes
    I've literally just finished reading that, lol...

    Giving it a go now

  • #9
    Regular Coder
    Join Date
    Nov 2009
    Location
    Scotland / Glasgow
    Posts
    184
    Thanks
    1
    Thanked 19 Times in 19 Posts
    Quote Originally Posted by greens85 View Post
    I've literally just finished reading that, lol...

    Giving it a go now
    Let me know how you get on. Not got time to test myself.
    .:To me AS is like LEGO, Only for the big Kids :.
    - Site - Blog - Glasgow Flasher

  • #10
    Regular Coder
    Join Date
    Sep 2007
    Posts
    809
    Thanks
    5
    Thanked 2 Times in 2 Posts
    I get one of two results:

    1) /bin/sh: /dev/null2: Permission denied
    2) /bin/sh: /: Is a directory

    but I think this has gone beyond the scope of PHP

  • #11
    Regular Coder
    Join Date
    Sep 2007
    Posts
    809
    Thanks
    5
    Thanked 2 Times in 2 Posts
    To update, this script seems to work fine with my Cron job but does anyone know how it can be altered so it saves as either a xls or csv and is sent to a specified email account, kinda like a form - mail script?

    PHP Code:
    <?php
        
    // THIS EXPORTS ONE TABLE AT A TIME
        
    $host 'localhost';
        
    $user 'myuser';
        
    $pass 'mypass';
        
    $db 'mydb';
        
    $table 'my tbl';
        
    $file '$table_export';

        
    $link mysql_connect($host$user$pass) or die("Can not connect." mysql_error());
        
    mysql_select_db($db) or die("Can not connect.");

        
    $result mysql_query("SHOW COLUMNS FROM ".$table."");
        
    $i 0;
            if (
    mysql_num_rows($result) > 0) {
                while (
    $row mysql_fetch_assoc($result)) {
        
    $csv_output .= $row['Field'].", ";
        
    $i++;
    }
    }
        
    $csv_output .= "\n";

        
    $values mysql_query("SELECT * FROM ".$table."");
            while (
    $rowr mysql_fetch_row($values)) {
                for (
    $j=0;$j<$i;$j++) {
        
    $csv_output .= $rowr[$j].", ";
    }
        
    $csv_output .= "\n";
    }

        
    $filename $file."_".date("Y-m-d_H-i",time());
        
    header("Content-type: application/vnd.ms-excel");
        
    header("Content-disposition: csv" date("Y-m-d") . ".csv");
        
    header"Content-disposition: filename=".$filename.".csv");
        print 
    $csv_output;
        exit;
    ?>


  •  

    Posting Permissions

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