return mysql error in a variable using system function in php

Question

I am trying to import a database using system() in php. It works fine when my sql file is correct. lets say i have an error in the sql file(for eg. syntax error). I want that exact error in a variable so I can maintain a log and alert purpose.

<?php
    $lastline = system("mysql -u root mydbname < /home/mydbname.sql ",$retval);
    echo "\nretval:";
    print_r($retval);
    echo "\nlastline:";
    print_r($lastline);
?>

Unfortunately I don't get any errors in return when my sql is improper When I run the above code I get to see the errors on terminal but it does not save the errors in the variables.

Try `exec()`. It returns the output as an array. – Michel May 15 '18 at 10:59 — Michel, May 15 '18 at 10:59

George K · Accepted Answer · 2018-05-15T11:31:24.813

<?php

$filename = '/home/mydbname.sql';
$mysql_host = 'localhost';
$mysql_username = 'root';
$mysql_password = '';
$mysql_database = 'mydbname';

// Connect to MySQL server & Select database
mysql_connect($mysql_host, $mysql_username, $mysql_password) or die('Error connecting to MySQL server: ' . mysql_error());
mysql_select_db($mysql_database) or die('Error selecting MySQL database: ' . mysql_error());

// Temporary variable, used to store current query
$templine = '';
$lines = file($filename);
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 = '';
     }
}
 echo "Tables imported successfully";
?>

Maybe importing this way would work, not sure this is what you need but it should work. Code from How to import .sql file in mysql database using php

You could use the --force (-f) flag so that MySQL doesn't stop and just log any errors to the console:

mysql -u userName -p -f -D dbName < script.sql
<?php
    $lastline = system("mysql -u root -f mydbname < /home/mydbname.sql ",$retval);
    echo "\nretval:";
    print_r($retval);
    echo "\nlastline:";
    print_r($lastline);
?>

PHP docs on exec() specify an additional 2 parameters for output and return value so maybe try getting both and one might include an error message.

Hope this helps

return mysql error in a variable using system function in php

1 Answers1