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 7 of 7
  1. #1
    Regular Coder
    Join Date
    Jan 2008
    Location
    Geneva, Switzerland
    Posts
    413
    Thanks
    12
    Thanked 29 Times in 29 Posts

    Question Export (backup) Mysql table

    Hi there !

    I searched many different places but did not find my answer. Btw, the search engine on CF.com returned an error when I tried today... so let's post my question.

    I have a mysql table (not the whole db) called PLAYERS that I want to backup into a .csv or .sql or anything else (I wish I can add this to my webcron page). This file should be exported to my /web folder using a filename like 2007-01-13.csv so it will be easier to recover and should not replace the previous one (naming the file is not my problem). I should be able then to reload the content of this file into my PLAYERS table at anytime. Of course I want to do all that using a web PHP interface and not use phpmyadmin anymore.

    I have tried the SELECT INTO OUTFILE method with no luck. There's obviously something I didn't understand or did wrong.

    Can anybody give me a working example or a link to a good and easy tut?

    And could someone tell me what would be the best or correct file format I should use (csv / sql / txt ...) to achieve what I want to?

    Important, there should be no 'human' input in this operation... no 'save as' popup or anything! Automated.

    Thanks in advance!

  • #2
    Regular Coder
    Join Date
    Jan 2008
    Location
    Geneva, Switzerland
    Posts
    413
    Thanks
    12
    Thanked 29 Times in 29 Posts
    Ok... I finally managed to save my .sql file where I want.
    I end up with a well formatted file that I can import with phpMyAdmin.

    Now how can I import this file in my table using PHP? What do u recommend?

    Thanks

  • #3
    Regular Coder
    Join Date
    Jan 2008
    Location
    Geneva, Switzerland
    Posts
    413
    Thanks
    12
    Thanked 29 Times in 29 Posts
    I am trying to use this:

    PHP Code:
    <?
    include("../_conn/config.php");
    mysql_connect($db_host,$db_user,$db_pass);
    mysql_select_db($db_name);

    $path $_SERVER['DOCUMENT_ROOT']."/admin/backup/sql/2008-01-13.txt";
    echo 
    $path;
    $file fopen($path,"w");
    load_backup_sql($file);
    fclose($file);

    function 
    load_backup_sql($file) {

        while (!
    feof($file)) {
            
    $query NULL;
            while (!
    feof($file)) {
                
    $query .= fgets($file);
            }
            if (
    NULL != $query) {
                
    mysql_query($query) or die("sql not successful: ".mysql_error()." query: ".$query);
            }
        }
    }
    ?>
    I get no error but my table is not filled by the records of the txt file...
    Any idea ?

  • #4
    Regular Coder
    Join Date
    Jan 2008
    Location
    Geneva, Switzerland
    Posts
    413
    Thanks
    12
    Thanked 29 Times in 29 Posts
    I still didn't find the good way for this...

    Anybody has a working example on how to:

    Import a mysql dump file (.sql or .txt) in a mysql table?

    My .sql files contain everything needed (INSERT INTO TBL_NAME ... VALUES ...) etc.

    Anybody?
    Thanks in advance!

  • #5
    Senior Coder shyam's Avatar
    Join Date
    Jul 2005
    Posts
    1,563
    Thanks
    2
    Thanked 163 Times in 160 Posts
    Quote Originally Posted by Arnaud View Post
    Code:
    <?
    include("../_conn/config.php");
    mysql_connect($db_host,$db_user,$db_pass);
    mysql_select_db($db_name);
    
    $path = $_SERVER['DOCUMENT_ROOT']."/admin/backup/sql/2008-01-13.txt";
    echo $path;
    $file = fopen($path,"w");
    load_backup_sql($file);
    fclose($file);
    
    function load_backup_sql($file) {
    
    	while (!feof($file)) {
    		$query = NULL;
    		while (!feof($file)) {
    			$query .= fgets($file);
    		}
    		if (NULL != $query) {
    			mysql_query($query) or die("sql not successful: ".mysql_error()." query: ".$query);
    		}
    	}
    }
    ?>
    I get no error but my table is not filled by the records of the txt file...
    Any idea ?
    opening a file in w mode truncates the file...so, essentially what ur script has been doing is truncating the backup file that u created...since ur not executing any invalid query (or any query for that matter $query is always NULL) you don't get an error either :|
    You never have to change anything you got up in the middle of the night to write. -- Saul Bellow

  • #6
    Regular Coder
    Join Date
    Jan 2008
    Location
    Geneva, Switzerland
    Posts
    413
    Thanks
    12
    Thanked 29 Times in 29 Posts
    Quote Originally Posted by shyam View Post
    opening a file in w mode truncates the file...so, essentially what ur script has been doing is truncating the backup file that u created...since ur not executing any invalid query (or any query for that matter $query is always NULL) you don't get an error either :|
    Aha.. That's what I thought too... do you mean I shall open the file using "r" parameter? I will try that, but I think I tried and it did not anything better. I will report here...

    Thanks

  • #7
    Regular Coder
    Join Date
    Jan 2008
    Location
    Geneva, Switzerland
    Posts
    413
    Thanks
    12
    Thanked 29 Times in 29 Posts
    OK... looks much better using the "r" parameter...

    Now I end up with another error.

    When exporting a large table with phpMyAdmin, I noticed that the following line:

    INSERT INTO `TABLE_NAME` (`ID_TABLE_NAME`, `USERID`, `TEAMID`, `PLAYERID`, `LINE`) VALUES (...), etc


    is repeated every 1800 lines. Knowing that, I built my script so that it would also repeat this line every 1800 lines but it looks like it is generating an error when I import.

    sql not successful: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; INSERT INTO `TABLE_NAME` (`ID_TABLE_NAME`, `USERID`, `TEAMID`,' at line 1800


    Any idea, why these mutliple INSERT INTO are generated by phpMyAdmin every 1800 lines? (MySql 4.1.20)
    Is there a real need to repeat this line ?
    Note that if I import that same file using phpMyAdmin, it works fine:

    Import has been successfully finished, 3 queries executed.


    It says 3 queries, because this INSERT INTO line appears 3 times in my file...

    Any advice on the above questions or help on how to correctly create a large mysql dump file would be much appreciated !

    Kindly note that removing these 2 additional INSERT INTO lines in my .txt file fixes the problems and the file gets imported correctly!
    Thanks!
    Last edited by Arnaud; 01-14-2008 at 06:32 PM. Reason: More info...


  •  

    Posting Permissions

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