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 9 of 9
  1. #1
    New to the CF scene
    Join Date
    Jan 2013
    Location
    mbeya - Tanzania
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    restore db table

    Any one who can help with php scripts (step by step) for restoring a table in mysql database

  • #2
    Master Coder mlseim's Avatar
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,395
    Thanks
    8
    Thanked 1,078 Times in 1,069 Posts
    Has the previous table been saved somewhere ... like you did a backup at one time?
    Or are you not concerned about the table values ... just the table column names, etc?

  • #3
    New to the CF scene
    Join Date
    Jan 2013
    Location
    mbeya - Tanzania
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by mlseim View Post
    Has the previous table been saved somewhere ... like you did a backup at one time?
    Or are you not concerned about the table values ... just the table column names, etc?
    Yes, I have created a php script for backing up and saving a table somewhere in disks.

    Any Idea please.

  • #4
    Master Coder mlseim's Avatar
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,395
    Thanks
    8
    Thanked 1,078 Times in 1,069 Posts
    So you know where that backup is located? You can see the file?

    If so, how did you back it up? What did your PHP script do to make the backup?
    Is it a text file, an XML file, a query file?

  • #5
    New to the CF scene
    Join Date
    Jan 2013
    Location
    mbeya - Tanzania
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by mlseim View Post
    So you know where that backup is located? You can see the file?

    If so, how did you back it up? What did your PHP script do to make the backup?
    Is it a text file, an XML file, a query file?
    Thank you br. The following is the scripts for backing up a table in mysql db. it saves file in .sql format:

    <?php


    backup_tables('localhost','root','','dsm_db');



    /* backup the db OR just a table */
    function backup_tables($host,$user,$pass,$name,$tables = 'student_info')
    {

    $link = mysql_connect($host,$user,$pass);
    mysql_select_db($name,$link);

    //get all of the tables
    if($tables == '*')
    {
    $tables = array();
    $result = mysql_query('SHOW TABLES');
    while($row = mysql_fetch_row($result))
    {
    $tables[] = $row[0];
    }
    }
    else
    {
    $tables = is_array($tables) ? $tables : explode(',',$tables);
    }

    //cycle through
    foreach($tables as $table)
    {
    $result = mysql_query('SELECT * FROM '.$table);
    $num_fields = mysql_num_fields($result);

    $return.= 'DROP TABLE '.$table.';';
    $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
    $return.= "\n\n".$row2[1].";\n\n";

    for ($i = 0; $i < $num_fields; $i++)
    {
    while($row = mysql_fetch_row($result))
    {
    $return.= 'INSERT INTO '.$table.' VALUES(';
    for($j=0; $j<$num_fields; $j++)
    {
    $row[$j] = addslashes($row[$j]);
    $row[$j] = ereg_replace("\n","\\n",$row[$j]);
    if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
    if ($j<($num_fields-1)) { $return.= ','; }
    }
    $return.= ");\n";
    }
    }
    $return.="\n\n\n";
    }

    //save file

    $handle = tais.date("_d.m.Y").'.sql';


    ob_clean();
    ob_start();
    Header("Content-type: application/octet-stream");
    Header("Content-Disposition: attachment; filename=$handle");

    //echo $result;
    echo $return;
    ob_end_flush();

    exit;


    }

    ?>

  • #6
    Master Coder mlseim's Avatar
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,395
    Thanks
    8
    Thanked 1,078 Times in 1,069 Posts
    So you have a file on your PC with the .sql file extension.
    That file was created by your PHP script, it spit-out the .sql content,
    and you saved it on your PC. Now you want to go in reverse ... you
    want to take that .sql file and restore the database ... is that correct?

    I'm just trying to follow what you mean by "restore db table".
    We might both be thinking of two different things.

    If what I'm thinking is correct, you need to have access to "phpMyAdmin".
    This is usually done using your webhost (website) control panel. They have
    a thing in the control panel called "phpMyAdmin" where you can access
    all of your mysql databases, view them, edit them, add/delete. You can
    also present it with an .sql file ... like the one you saved as a backup.
    It will execute that .sql file and perform the query instructions to restore
    your table(s).


    .
    Last edited by mlseim; 01-06-2013 at 05:11 PM.

  • #7
    New to the CF scene
    Join Date
    Jan 2013
    Location
    mbeya - Tanzania
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by mlseim View Post
    So you have a file on your PC with the .sql file extension.
    That file was created by your PHP script, it spit-out the .sql content,
    and you saved it on your PC. Now you want to go in reverse ... you
    want to take that .sql file and restore the database ... is that correct?

    I'm just trying to follow what you mean by "restore db table".
    We might both be thinking of two different things.

    If what I'm thinking is correct, you need to have access to "phpMyAdmin".
    This is usually done using your webhost (website) control panel. They have
    a thing in the control panel called "phpMyAdmin" where you can access
    all of your mysql databases, view them, edit them, add/delete. You can
    also present it with an .sql file ... like the one you saved as a backup.
    It will execute that .sql file and perform the query instructions to restore
    your table(s).


    .
    Yes, that is right, the thing is I have developed a huge php application in my institution and huge amount of data is expected to be uploaded (restoring/import to remote server) and downloaded (backup/export from remote server).

    I am very familiar with phpMyadmin and that my web host company provides that platform. The problem I found with that is, I have a given a limitation of importing only 10.2Mb of data from phpMyadmin.
    At this moment I have only 55Mb of data for the whole database and I frequently upload and download one table of size 9Mb though I zip it and get 1-2 Mb, but am sure it come a time where I will be required to import say 100Mb.

    Therefore If I have the script in the application it will be easier to do so. And that is why I asking for script of restore table back to the database, cause I have the script for taking backup from database. This is a link to the login page of that application: http://tais.teku.ac.tz
    Last edited by amb.godwin; 01-07-2013 at 09:37 PM.

  • #8
    Regular Coder
    Join Date
    Nov 2012
    Posts
    109
    Thanks
    6
    Thanked 12 Times in 12 Posts
    Quote Originally Posted by amb.godwin View Post
    The problem I found with that is, I have a given a limitation of importing only 10.2Mb of data from phpMyadmin.
    At this moment I have only 55Mb of data for the whole database and I frequently upload and download one table of size 9Mb though I zip it and get 1-2 Mb, but am sure it come a time where I will be required to import say 100Mb.
    By that time you'll run into PHP's memory and upload limit, especially if you're on a (budget) shared hosting environment. Only way around it then is to use SSH to get MySQL to do it for you.

  • #9
    New to the CF scene
    Join Date
    Jan 2013
    Location
    mbeya - Tanzania
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Thyrosis View Post
    By that time you'll run into PHP's memory and upload limit, especially if you're on a (budget) shared hosting environment. Only way around it then is to use SSH to get MySQL to do it for you.
    Thank you for your idea, the problem with your suggestion is that, I have been disabled to connect remotely via SSH by my hosting company.

    Would you please provide me with php scripts to such task (restoring table to the mysql db)!


  •  

    Posting Permissions

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