How to update remote server's database by a .sql file using PHP script?

Question

I want to create a utility in PHP like phpMyAdmin's import option, which should allow database updates to the remote server via a .sql file without creating a new database.

Since it's a client side utility, access to cpanel is not allowed.

The app has two kinds of working environments, offline & online.

If the client works offline, they need to take the backup of database and should update the database with remote server similar for online.

Then they have to update the database of remote server.

@Dagon: Please don't mistake me..I'm newbie to all this stuff.Please give me a idea to get start off. — Vijin Paulraj
– Vijin Paulraj, Commented Jan 7, 2012 at 7:56
@VijinPaulraj, why don't use PHPMyAdmin itself? This is just for a suggestion because I don't fully understand the situation — Abhay
– Abhay, Commented Jan 7, 2012 at 8:03
@Abhay,Well of course as a developer i can use the PHPMyAdmin but my client won't.First of all,my application works in both online and offline.If my client use the app offline they'll take the backup by using my another script then they go to online and needs to update the database which is in the remote host.For that purpose, i need a script that'll update the remote server's database..Thanks! — Vijin Paulraj
– Vijin Paulraj, Commented Jan 7, 2012 at 11:25
@VijinPaulraj, in the original question you said that "The .sql file would be exported from localhost phpMyAdmin" - what does that mean? And in your comment above, you say that "they'll take the backup by using my another script". So which one of the above does the client use to generate the SQL and if is the latter, what method are you using to take the backup? I might have misunderstood though — Abhay
– Abhay, Commented Jan 7, 2012 at 14:41

Abhay · Accepted Answer · 2012-01-07 17:55:26Z

2

Solution 1

If you are running your PHP on a Linux system, you can try using the 'mysql' command itself. However please note that your PHP installation has the permission to run "system" commands, like system(), exec() etc.

So here is what I mean to say:

system("mysql -U{db_user_name} -h{db_host} -P{db_password} < {full_path_to_your_sql_file}");

Please replace,

{db_user_name} with the DB username,

{db_host} with the DB host,

{db_password} with the DB password,

{full_path_to_your_sql_file} with the path to your SQL file.

And this of course requires the SQL file to be uploaded.

Solution 2:

Read the SQL file line by line and while reading execute each statement using PHP's standard MySQL library. Something like:

$arrFile = file("full_path_to_sql_file.sql", FILE_IGNORE_NEW_LINES);
foreach ($arrFile as $q) {
    mysql_query($q);
}

However, this might not be as simple as it seems. If your SQL file has comments and other .sql specific statements, you might need to put checks to ignore them. Or better if the SQL file contains nothing but SQL statements.

edited Jan 7, 2012 at 17:55

answered Jan 7, 2012 at 8:20

Abhay

6,6852 gold badges26 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ThiefMaster Over a year ago

You cannot assume a SQL file has one query per line.

Raj Over a year ago

you could split by ; but still we might find ; inside some strings

krazybean · Accepted Answer · 2012-01-07 06:35:33Z

0

You can use a regular upload script to obtain the .sql file, make sure you sanitize appropriately the input string to obtain only the .sql file and text type,

move_uploaded_file($_FILES["file"]["tmp_name"],"tmpdb/" . $_FILES["file"]["name"]);

Once you have that, you can either preset their db settings defining the db using

mysql_select_db('dbname');

Then just open the sql file with fopen(); slap that sucker in a variable

$file = fopen("userdb.sql","r");
$usersql = fread($file, 5);
fclose($file);

then just throw it in a mysql_query();

$uploaddb = mysql_query($usersql) or die(mysql_error());

Those are the concepts I would suggest, alternatively you can use shell exec but then that just opens up other security concerns.

answered Jan 7, 2012 at 6:35

krazybean

312 bronze badges

5 Comments

GManz Over a year ago

Why on Earth would you read only 5 bytes? Also, mysql_query() can only do one query at a time if I remember correctly.

krazybean Over a year ago

you're correct could swap out the 5 with a filesize(), mysql query was to handle the full .sql file, if a single .sql file per upload it should be enough.

Vijin Paulraj Over a year ago

@krazybean:Thanks for your valuable response..Should i delete the existing database or whether it'll get replaced?.But in my script,i just want it should be get replaced by the existing database automatically.

Vijin Paulraj Over a year ago

@HoshSadiq,yes,you remembered correctly.mysql_query() can process only one query at a time.

GManz Over a year ago

Thought so, you may wanna use Abhay's solution or just use BigDump. Otherwise you'll have to figure out where to explode on ; in order to distinguish the different queries, which can be a pain if you have records in the sql dump.

GManz · Accepted Answer · 2012-01-07 06:41:35Z

0

You may want to consider using BigDump?

answered Jan 7, 2012 at 6:41

GManz

1,6772 gold badges23 silver badges45 bronze badges

Comments

Vijin Paulraj · Accepted Answer · 2012-01-10 14:16:25Z

0

Eventually,I've got answer for my question myself.I've just pasted the php coding without config and other stuff.

    $filename ="test.sql";        
    mysql_select_db("test"); 

    //truncate the database.

    $result_t = mysql_query("SHOW TABLES");
    while($row = mysql_fetch_assoc($result_t))
    {
       mysql_query("TRUNCATE " . $row['Tables_in_' . $mysql_database]);
    }
    // Temporary variable, used to store current query
    $templine = '';
    // Read in entire file
    $lines = file($filename);
    // Loop through each line
    foreach ($lines as $line)
    {
        // Skip it if it's a comment
        if (substr($line, 0, 2) == '--' || $line == '')
            continue;

        // Add this line to the current segment
        $templine .= $line;
        // If it has a semicolon at the end, it's the end of the query
        if (substr(trim($line), -1, 1) == ';')
        {
            // Perform the query
            mysql_query($templine) or print('Error performing query \'<strong>' . $templine . '\': ' . mysql_error() . '<br /><br />');
            // Reset temp variable to empty
            $templine = '';
        }
    }

answered Jan 10, 2012 at 14:16

Vijin Paulraj

4,6885 gold badges44 silver badges55 bronze badges

1 Comment

Karthi Over a year ago

are get solution?

Collectives™ on Stack Overflow

How to update remote server's database by a .sql file using PHP script?

4 Answers 4

2 Comments

5 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

5 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related