SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Evangelist GeekSupport's Avatar
    Join Date
    May 2002
    Location
    Southern California
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    restore mysql without using exec(mysql...)? Answered!

    is there a mysql_* function that will execute a whole list of mysql querys? I want to make a script that will allow users to update/restore a database by uploading the .sql file. what the script will do is first read in the uploaded file, test to make sure the sql works by trying the .sql in a dummy database, and if successful, load *.sql in the live database (with some more error checking in the middle).

    the main reason for avoiding mysql --user --pass etc is because, afaik, the user/pass will be in `ps aux` and i also want to avoid using phpmyadmin -- that's just another tool i would have to teach them. simple point-click for these guys (it'll be hard enough training them to convert access->mysql)
    Last edited by GeekSupport; Sep 16, 2003 at 16:15.

  2. #2
    La la la la la bronze trophy lieut_data's Avatar
    Join Date
    Jun 2003
    Location
    Waterloo, ON
    Posts
    1,517
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Solution?

    After grabbing the uploaded file, using a form and $_FILES, copy the SQL commands to a text file, and execute :

    PHP Code:
    mysql_query('source file.sql'); 
    making sure the file.sql is in the current directory (or specifying the full path).

    AFAIK, this should work (according to the MySQL docs)
    My name is Steve, and I'm a super-villian.

  3. #3
    SitePoint Evangelist GeekSupport's Avatar
    Join Date
    May 2002
    Location
    Southern California
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh cool. thanks. i tried reading in file.sql then doing mysql_query($read_file); made errors. i'll try 'source /file/location/file.sql'

  4. #4
    SitePoint Evangelist GeekSupport's Avatar
    Join Date
    May 2002
    Location
    Southern California
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    1.
    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 'source /home/user1/projects/directory_search/upload/search.sql' at l

    PHP Code:
    mysql_query('source '.$sql_location)
        or die(
    mysql_error()); 
    yes, the file exists

    2.
    also tried to hard-code it
    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 'source /home/user1/projects/directory_search/upload/search.sql' at
    PHP Code:
    mysql_query('source  /home/user1/projects/directory_search/upload/search.sql')
        or die(
    mysql_error()); 
    3.
    using backticks than single quotes: Query was empty

    4.
    Table 'search_test.Unit' doesn't exist
    PHP Code:
    mysql_query(exec('cat /home/user1/projects/directory_search/upload/search.sql'))
        or die(
    mysql_error()); 
    That seemed to do something more interesting, but the Unit call isn't until half way down the sql file and it didn't make the other tables.
    Last edited by GeekSupport; Sep 16, 2003 at 14:06.

  5. #5
    La la la la la bronze trophy lieut_data's Avatar
    Join Date
    Jun 2003
    Location
    Waterloo, ON
    Posts
    1,517
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm -- yeah, according to the docs, 'source' works from the console, but I guess not from a regular query...

    You've a good idea going with the exec / cat -- though -- I think it too will fail, as the whole file will still be parsed into the return value of exec...

    Perhaps a better solution is in order -- first, look into the source code for PHPMyAdmin.. I was browsing through it a few minutes ago, looking for answers. Specifically, look at read_dump.php

    However, it might be easier to create a function that reads, from a text file, SQL queries, one at a time...

    From a PHPMyAdmin dump, I see that every complete query ends with a ';', and any lines beginning with # are ignored (comments).

    Thus a function as the following (untested) should suffice:

    PHP Code:
    function mysql_queries($sql_file) {
       if (
    $handle fopen($sql_file'r')) {
          
    $query '';
          while (!
    feof($handle)) {
             
    $line fgets($handle);
             if (
    substr($line01) != '#'$query .= $line;
             
             if (
    substr(strrev(chop($query)), 01) == ';') {
                 
    mysql_query($query);
                 
    $query '';
             }
          } 
       }

    ?
    Last edited by lieut_data; Sep 16, 2003 at 15:59.
    My name is Steve, and I'm a super-villian.

  6. #6
    SitePoint Evangelist GeekSupport's Avatar
    Join Date
    May 2002
    Location
    Southern California
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks a lot lieut_data! i'll add this to my sql functions

    a little typo?
    PHP Code:
    while (!feof($sql_file)) 
    to
    PHP Code:
    while (!feof($handle)) 

  7. #7
    La la la la la bronze trophy lieut_data's Avatar
    Join Date
    Jun 2003
    Location
    Waterloo, ON
    Posts
    1,517
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by GeekSupport
    thanks a lot lieut_data! i'll add this to my sql functions

    a little typo?
    PHP Code:
    while (!feof($sql_file)) 
    to
    PHP Code:
    while (!feof($handle)) 
    Yes, that's correct -- as well as the addition of the chop() function to remove the new line at the end of $line...
    My name is Steve, and I'm a super-villian.

  8. #8
    SitePoint Evangelist GeekSupport's Avatar
    Join Date
    May 2002
    Location
    Southern California
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ah, beauitful! it works as advertised. thanks again lieut_data.

    for anyone wondering, this is how i'm implementing it
    PHP Code:
        function restore_db($sql_file$dbname)
        {
            if (
    $handle fopen($sql_file'r')) 
            { 
                  
    $query ''
                  while (!
    feof($handle)) 
                  { 
                     
    $line fgets($handle); 
                     if (
    substr($line01) != '#'$query .= $line
                      
                     if (
    substr(strrev(chop($query)), 01) == ';'
                     { 
                         
    mysql_db_query($dbname$query); 
                         
    $query ''
                     } 
                  } 
                  return 
    1
            }           
               else 
                   return 
    0;
            
        } 
    i'll go though it twice. once to store it on a dummy database. do some code to make sure the required fields are in it then run restore_db() again on the live db.



    i'm not using mysql_db_select() and mysql_query() because, if the script crashes, the user will be stuck in the dummy database and will have to logout/login to correct any strange errors that might creep up (since all queries are now on the dummy db). mysql_db_query() only hits the db i want and any errors/crashes wont affect the rest of the site.


Bookmarks

Posting Permissions

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