This topic is locked

Adding MySQL database export/import capabilities to PHPRunner project.

1/22/2014 5:33:46 PM
PHPRunner Tips and Tricks
admin

This tutorial shows how to export/import capabilities to your PHPRunner project. As opposed to built-in table import/export functions this article explains how to import or export the whole database.
This tutorial is specific to MySQL as it relies on MySQL utilities like mysqldump. Other database provide similar utilities, for instance with SQL Server you can use osql.exe. Here are some examples:

http://stackoverflow.com/questions/122690/what-is-a-simple-command-line-program-or-script-to-backup-sql-server-databases
[size="6"]Export[/size]
Insert a new button into any of your app pages, name it 'Export DB' or similar.

In ClientAfter event use the following code:

location.href="export_db.php";


Other events should be empty.
[size="6"]Import[/size]
Insert a button name 'Run Import' where you need it to be.
ClientBefore code:

params["fname"] = $("#import_fname").val();

$("#form_import").submit();


Server code:

$result["txt"] = $params["fname"];


ClientAfter code:

if(result["txt"])

{

ctrl.setMessage("Imported successfully");

}

else

{

ctrl.setMessage("Choose a file to import");

}


Insert 'PHP code snippet' next to 'Run import' button and add the following code there:

echo "<div style='margin: 5px 10px;'><form name='form_import' id='form_import' target='iframe_import' method='post' action='import_db.php' enctype='multipart/form-data'>

<b>Select *.sql file to import</b>

<input type=file name=import_fname id=import_fname>

</form>

</div>

<iframe id=iframe_import name=iframe_import style='display:none'></iframe>";


To run the import you need to select the import file first and then click 'Run Import' button.
[size="6"]Files[/size]
In the output directory create file named export_db.php and put the following code there:

<?php
include("include/dbcommon.php");
@ini_set("display_errors","1");

@ini_set("display_startup_errors","1");
global $host,$user,$pwd, $sys_dbname;
ob_start();
$path = "c:\\xampp\\mysql\\bin\\mysqldump.exe";
$command = $path." ".$sys_dbname." --add-drop-table --host=$host --user=$user ";

if ($pwd)

$command.= "--password=". $pwd ." ";
system($command);

$dump = ob_get_contents();

ob_end_clean();
header('Content-Description: File Transfer');

header('Content-Type: application/octet-stream');

header('Content-Disposition: attachment; filename='.basename($sys_dbname . "_" . date("Y-m-d_H-i-s").".sql"));

flush();

echo $dump;

exit();

?>


Create file named import_db.php and put the following code there:

<?php
include("include/dbcommon.php");
@ini_set("display_errors","1");

@ini_set("display_startup_errors","1");
global $host,$user,$pwd,$sys_dbname;
$path = "c:\\xampp\\mysql\\bin\\mysql.exe";

$command = $path." ".$sys_dbname." < ".$_FILES['import_fname']['tmp_name']." --host=$host --user=$user ";

if ($pwd)

$command.= "--password=". $pwd ." ";

echo system($command);

?>
B
bakersalah 11/6/2016

Hi,
I have issues with previous code ...
include("include/dbcommon.php");
@ini_set("display_errors","1");

@ini_set("display_startup_errors","1");
global $host,$user,$pwd, $sys_dbname;
( $host , $user , $pwd AND $sys_dbname ) are empty variables
I use PHPRunner v8.1 and v9 Enterprise ed.
Another point, to work around this problem I used SESSION variables to pass these connection parameters like $SESSION["sys_dbname"] from the EVENT After Application Initialized and it works, BUT is it acceptable - SECURITY WISE ?

There is other points relevant: where I can find the db_connect() function and what kind of handler dose it return and wheather it is similar to mysqli_connect() or not ?
Sorry I re-visited this tutorial because I stuck with same problem while building custom export for the database similar to that of phpmyadmin.
Thanks in advance

Baker Salah