Sujet Précédent Sujet Suivant

Sauvegarde automatique d'une table dans une BD Mysql

hamzvb Messages postés 15 Date d'inscription jeudi 23 avril 2009 Statut Membre Dernière intervention 2 juin 2011 - 23 nov. 2010 à 15:25
hamzvb Messages postés 15 Date d'inscription jeudi 23 avril 2009 Statut Membre Dernière intervention 2 juin 2011 - 8 déc. 2010 à 08:53
bonjour, je travail sur un site web en php et je veux dans l'espace administrateur ajouter une interface qui lui permet de lancer une sauvegarde automatique d'une table précise dans la BD Mysql.j'ai trouvé le script "mysqldump" mais j'ai pas su comment le rendre fonctionnel dans mon application !!!! est ce que quelqu'un peut me dire comment inserer ce script dans mon application ??
merci d'avance

voici le code du script : 
<?php
set_time_limit(0);
/*---------------------------------------------------+
| mysqldump.php
+----------------------------------------------------+
| Copyright 2006 Huang Kai
| hkai@atutility.com
| http://atutility.com/
+----------------------------------------------------+
| Released under the terms & conditions of v2 of the
| GNU General Public License. For details refer to
| the included gpl.txt file or visit http://gnu.org
+----------------------------------------------------*/
/*
change log:
2006-10-16 Huang Kai
---------------------------------
initial release

2006-10-18 Huang Kai
---------------------------------
fixed bugs with delimiter
add paramter header to add field name as CSV file header.

2006-11-11 Huang Kia
Tested with IE and fixed the  to 
*/
$mysqldump_version="1.02";

$print_form=1;
$output_messages=array();


//test mysql connection
if( isset($_REQUEST['action']) )
{
$mysql_host=$_REQUEST['mysql_host'];
$mysql_database=$_REQUEST['mysql_database'];
$mysql_username=$_REQUEST['mysql_username'];
$mysql_password=$_REQUEST['mysql_password'];

if( 'Test Connection' == $_REQUEST['action'])
{
_mysql_test($mysql_host,$mysql_database, $mysql_username, $mysql_password);
}
else if( 'Export' == $_REQUEST['action'])
{
_mysql_test($mysql_host,$mysql_database, $mysql_username, $mysql_password);
if( 'SQL' == $_REQUEST['output_format'] )
{
$print_form=0;

//ob_start("ob_gzhandler");
header('Content-type: text/plain');
header('Content-Disposition: attachment; filename="'.$mysql_host."_".$mysql_database."_".date('YmdHis').'.sql"');
echo "/*mysqldump.php version $mysqldump_version */\n";
_mysqldump($mysql_database);

//header("Content-Length: ".ob_get_length());

//ob_end_flush();
}
else if( 'CSV' == $_REQUEST['output_format'] && isset($_REQUEST['mysql_table']))
{
$print_form=0;

ob_start("ob_gzhandler");

header('Content-type: text/comma-separated-values');
header('Content-Disposition: attachment; filename="'.$mysql_host."_".$mysql_database."_".$mysql_table."_".date('YmdHis').'.csv"');
//header('Content-type: text/plain');
_mysqldump_csv($_REQUEST['mysql_table']);
header("Content-Length: ".ob_get_length());
ob_end_flush();
}
}

}

function _mysqldump_csv($table)
{
$delimiter= ",";
if( isset($_REQUEST['csv_delimiter']))
$delimiter= $_REQUEST['csv_delimiter'];

if( 'Tab' == $delimiter)
$delimiter="\t";


$sql="select * from `$table`;";
$result=mysql_query($sql);
if( $result)
{
$num_rows= mysql_num_rows($result);
$num_fields= mysql_num_fields($result);

$i=0;
while( $i < $num_fields)
{
$meta= mysql_fetch_field($result, $i);
echo($meta->name);
if( $i < $num_fields-1)
echo "$delimiter";
$i++;
}
echo "\n";

if( $num_rows > 0)
{
while( $row= mysql_fetch_row($result))
{
for( $i=0; $i < $num_fields; $i++)
{
echo mysql_real_escape_string($row[$i]);
if( $i < $num_fields-1)
echo "$delimiter";
}
echo "\n";
}

}
}
mysql_free_result($result);

}


function _mysqldump($mysql_database)
{
$sql="show tables;";
$result= mysql_query($sql);
if( $result)
{
while( $row= mysql_fetch_row($result))
{
_mysqldump_table_structure($row[0]);

if( isset($_REQUEST['sql_table_data']))
{
_mysqldump_table_data($row[0]);
}
}
}
else
{
echo "/* no tables in $mysql_database */\n";
}
mysql_free_result($result);
}

function _mysqldump_table_structure($table)
{
echo "/* Table structure for table `$table` */\n";
if( isset($_REQUEST['sql_drop_table']))
{
echo "DROP TABLE IF EXISTS `$table`;\n\n";
}
if( isset($_REQUEST['sql_create_table']))
{

$sql="show create table `$table`; ";
$result=mysql_query($sql);
if( $result)
{
if($row= mysql_fetch_assoc($result))
{
echo $row['Create Table'].";\n\n";
}
}
mysql_free_result($result);
}
}

function _mysqldump_table_data($table)
{

$sql="select * from `$table`;";
$result=mysql_query($sql);
if( $result)
{
$num_rows= mysql_num_rows($result);
$num_fields= mysql_num_fields($result);

if( $num_rows > 0)
{
echo "/* dumping data for table `$table` */\n";

$field_type=array();
$i=0;
while( $i < $num_fields)
{
$meta= mysql_fetch_field($result, $i);
array_push($field_type, $meta->type);
$i++;
}

//print_r( $field_type);
echo "insert into `$table` values\n";
$index=0;
while( $row= mysql_fetch_row($result))
{
echo "(";
for( $i=0; $i < $num_fields; $i++)
{
if( is_null( $row[$i]))
echo "null";
else
{
switch( $field_type[$i])
{
case 'int':
echo $row[$i];
break;
case 'string':
case 'blob' :
default:
echo "'".mysql_real_escape_string($row[$i])."'";

}
}
if( $i < $num_fields-1)
echo ",";
}
echo ")";

if( $index < $num_rows-1)
echo ",";
else
echo ";";
echo "\n";

$index++;
}
}
}
mysql_free_result($result);
echo "\n";
}

function _mysql_test($mysql_host,$mysql_database, $mysql_username, $mysql_password)
{
global $output_messages;
$link = mysql_connect($mysql_host, $mysql_username, $mysql_password);
if (!$link)
{
   array_push($output_messages, 'Could not connect: ' . mysql_error());
}
else
{
array_push ($output_messages,"Connected with MySQL server:$mysql_username@$mysql_host successfully");

$db_selected = mysql_select_db($mysql_database, $link);
if (!$db_selected)
{
array_push ($output_messages,'Can\'t use $mysql_database : ' . mysql_error());
}
else
array_push ($output_messages,"Connected with MySQL database:$mysql_database successfully");
}

}

if( $print_form >0 )
{
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<head>
<title>mysqldump.php version <?php echo $mysqldump_version; ?></title>
</head>


<?php
foreach ($output_messages as $message)
{
    	echo $message."
";
}
?>
<form action="" method="post">
MySQL connection parameters:
Host:,
"  />,

----

Database:,
"  />,

----

Username:,
"  />,

----

Password:,
"  />,

----

Output format:,
<select name="output_format" >
<option value="SQL" <?php if( isset($_REQUEST['output_format']) && 'SQL' == $_REQUEST['output_format']) echo "selected";?> >SQL</option>
<option value="CSV" <?php if( isset($_REQUEST['output_format']) && 'CSV' == $_REQUEST['output_format']) echo "selected";?> >CSV</option>

</select>




  
Dump options(SQL):
  Drop table statement:,
 />,

----

Create table statement:,
 />,

----

Table data:,
/>

  
Dump options(CSV):
  Delimiter:,
<select name="csv_delimiter">
<option value="," <?php if( isset($_REQUEST['output_format']) && ',' == $_REQUEST['output_format']) echo "selected";?>>,</option>
<option value="Tab" <?php if( isset($_REQUEST['output_format']) && 'Tab' == $_REQUEST['output_format']) echo "selected";?>>Tab</option>
<option value="|" <?php if( isset($_REQUEST['output_format']) && '|' == $_REQUEST['output_format']) echo "selected";?>>|</option>
</select>,

----

Table:,
"  />,

----

Header:,
/>





</form>

</html>

<?php
}
?>

4 réponses

Réponse 1 / 4
cod57 Messages postés 1653 Date d'inscription dimanche 7 septembre 2008 Statut Membre Dernière intervention 11 septembre 2013 19
23 nov. 2010 à 15:55
bonjour

as tu une erreur ?
il nous manque ta page administration ?

a++
0
Réponse 2 / 4
hamzvb Messages postés 15 Date d'inscription jeudi 23 avril 2009 Statut Membre Dernière intervention 2 juin 2011 1
24 nov. 2010 à 09:08
bonjour ;
mon problème que j'ai pas su intégrer le script dans mon application,pour le rendre fonctionnel.Quand je l'exécute une liste des lignes s'affichent et j'ai vu que dans les tutos en ligne qu'une s'interface doit s'afficher pour sélectionner la base de données et la table à sauvegarder dans un fichier excel !!!!
voici ou j'ai trouvé le script:
http://www.mt-soft.com.ar/2007/12/21/php-script-mysqldump/

merci pour votre aide
0
Réponse 3 / 4
cod57 Messages postés 1653 Date d'inscription dimanche 7 septembre 2008 Statut Membre Dernière intervention 11 septembre 2013 19
24 nov. 2010 à 09:53
Bonjour

Le script de dump fonctionne
tu dois avoir une erreur sur ta page.
je l'ai installé vite fait sur mon serveur
le script semble fonctionnel j'ai juste corrigé un petit truc, ma version ...

a++

dump.php 

<?php
set_time_limit(0);
/*---------------------------------------------------+
| mysqldump.php
+----------------------------------------------------+
| Copyright 2006 Huang Kai
| hkai@atutility.com
| http://atutility.com/
+----------------------------------------------------+
| Released under the terms & conditions of v2 of the
| GNU General Public License. For details refer to
| the included gpl.txt file or visit http://gnu.org
+----------------------------------------------------*/
/*
change log:
2006-10-16 Huang Kai
---------------------------------
initial release

2006-10-18 Huang Kai
---------------------------------
fixed bugs with delimiter
add paramter header to add field name as CSV file header.

2006-11-11 Huang Kia
Tested with IE and fixed the  to 
*/
$mysqldump_version="1.02";

$print_form=1;
$output_messages=array();


//test mysql connection
if( isset($_REQUEST['action']) )
{
$mysql_host=$_REQUEST['mysql_host'];
$mysql_database=$_REQUEST['mysql_database'];
$mysql_username=$_REQUEST['mysql_username'];
$mysql_password=$_REQUEST['mysql_password'];

if( 'Test Connection' == $_REQUEST['action'])
{
_mysql_test($mysql_host,$mysql_database, $mysql_username, $mysql_password);
}
else if( 'Export' == $_REQUEST['action'])
{
_mysql_test($mysql_host,$mysql_database, $mysql_username, $mysql_password);
if( 'SQL' == $_REQUEST['output_format'] )
{
$print_form=0;

//ob_start("ob_gzhandler");
header('Content-type: text/plain');
header('Content-Disposition: attachment; filename="'.$mysql_host."_".$mysql_database."_".date('YmdHis').'.sql"');
echo "/*mysqldump.php version $mysqldump_version */\n";
_mysqldump($mysql_database);

//header("Content-Length: ".ob_get_length());

//ob_end_flush();
}
else if( 'CSV' == $_REQUEST['output_format'] && isset($_REQUEST['mysql_table']))
{
$print_form=0;

ob_start("ob_gzhandler");

header('Content-type: text/comma-separated-values');
header('Content-Disposition: attachment; filename="'.$mysql_host."_".$mysql_database."_".$mysql_table."_".date('YmdHis').'.csv"');
//header('Content-type: text/plain');
_mysqldump_csv($_REQUEST['mysql_table']);
header("Content-Length: ".ob_get_length());
ob_end_flush();
}
}

}

function _mysqldump_csv($table)
{
$delimiter= ",";
if( isset($_REQUEST['csv_delimiter']))
$delimiter= $_REQUEST['csv_delimiter'];

if( 'Tab' == $delimiter)
$delimiter="\t";


$sql="select * from `$table`;";
$result=mysql_query($sql);
if( $result)
{
$num_rows= mysql_num_rows($result);
$num_fields= mysql_num_fields($result);

$i=0;
while( $i < $num_fields)
{
$meta= mysql_fetch_field($result, $i);
echo($meta->name);
if( $i < $num_fields-1)
echo "$delimiter";
$i++;
}
echo "\n";

if( $num_rows > 0)
{
while( $row= mysql_fetch_row($result))
{
for( $i=0; $i < $num_fields; $i++)
{
echo mysql_real_escape_string($row[$i]);
if( $i < $num_fields-1)
echo "$delimiter";
}
echo "\n";
}

}
}
mysql_free_result($result);

}


function _mysqldump($mysql_database)
{
$sql="show tables;";
$result= mysql_query($sql);
if( $result)
{
while( $row= mysql_fetch_row($result))
{
_mysqldump_table_structure($row[0]);

if( isset($_REQUEST['sql_table_data']))
{
_mysqldump_table_data($row[0]);
}
}
}
else
{
echo "/* no tables in $mysql_database */\n";
}
mysql_free_result($result);
}

function _mysqldump_table_structure($table)
{
echo "/* Table structure for table `$table` */\n";
if( isset($_REQUEST['sql_drop_table']))
{
echo "DROP TABLE IF EXISTS `$table`;\n\n";
}
if( isset($_REQUEST['sql_create_table']))
{

$sql="show create table `$table`; ";
$result=mysql_query($sql);
if( $result)
{
if($row= mysql_fetch_assoc($result))
{
echo $row['Create Table'].";\n\n";
}
}
mysql_free_result($result);
}
}

function _mysqldump_table_data($table)
{

$sql="select * from `$table`;";
$result=mysql_query($sql);
if( $result)
{
$num_rows= mysql_num_rows($result);
$num_fields= mysql_num_fields($result);

if( $num_rows > 0)
{
echo "/* dumping data for table `$table` */\n";

$field_type=array();
$i=0;
while( $i < $num_fields)
{
$meta= mysql_fetch_field($result, $i);
array_push($field_type, $meta->type);
$i++;
}

//print_r( $field_type);
echo "insert into `$table` values\n";
$index=0;
while( $row= mysql_fetch_row($result))
{
echo "(";
for( $i=0; $i < $num_fields; $i++)
{
if( is_null( $row[$i]))
echo "null";
else
{
switch( $field_type[$i])
{
case 'int':
echo $row[$i];
break;
case 'string':
case 'blob' :
default:
echo "'".mysql_real_escape_string($row[$i])."'";

}
}
if( $i < $num_fields-1)
echo ",";
}
echo ")";

if( $index < $num_rows-1)
echo ",";
else
echo ";";
echo "\n";

$index++;
}
}
}
mysql_free_result($result);
echo "\n";
}

function _mysql_test($mysql_host,$mysql_database, $mysql_username, $mysql_password)
{
global $output_messages;
$link = mysql_connect($mysql_host, $mysql_username, $mysql_password);
if (!$link)
{
   array_push($output_messages, 'Could not connect: ' . mysql_error());
}
else
{
array_push ($output_messages,"Connected with MySQL server:$mysql_username@$mysql_host successfully");

$db_selected = mysql_select_db($mysql_database, $link);
if (!$db_selected)
{
array_push ($output_messages,'Can\'t use $mysql_database : ' . mysql_error());
}
else
array_push ($output_messages,"Connected with MySQL database:$mysql_database successfully");
}

}

if( $print_form >0 )
{
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<head>
<title>mysqldump.php version <?php echo $mysqldump_version; ?></title>
</head>


<?php
foreach ($output_messages as $message)
{
    	echo $message."
";
}
?>
<form action="" method="post">
MySQL connection parameters:
Host:,
"  />,

----

Database:,
"  />,

----

Username:,

"  />,

----

Password:,
"  />,

----

Output format:,
<select name="output_format" >
<option value="SQL" <?php if( isset($_REQUEST['output_format']) && 'SQL' == $_REQUEST['output_format']) echo "selected";?> >SQL</option>
<option value="CSV" <?php if( isset($_REQUEST['output_format']) && 'CSV' == $_REQUEST['output_format']) echo "selected";?> >CSV</option>

</select>




  
Dump options(SQL):
  Drop table statement:,
 />,

----

Create table statement:,
 />,

----

Table data:,
/>

  
Dump options(CSV):
  Delimiter:,
<select name="csv_delimiter">
<option value="," <?php if( isset($_REQUEST['output_format']) && ',' == $_REQUEST['output_format']) echo "selected";?>>,</option>
<option value="Tab" <?php if( isset($_REQUEST['output_format']) && 'Tab' == $_REQUEST['output_format']) echo "selected";?>>Tab</option>
<option value="|" <?php if( isset($_REQUEST['output_format']) && '|' == $_REQUEST['output_format']) echo "selected";?>>|</option>
</select>,

----

Table:,
,

----

Header:,
/>





</form>

</html>

<?php
}
?>
0
Réponse 4 / 4
hamzvb Messages postés 15 Date d'inscription jeudi 23 avril 2009 Statut Membre Dernière intervention 2 juin 2011 1
8 déc. 2010 à 08:53
merci beaucoup mon ami ca marche à merveille !!!! le problème était dans ma page !!!
0

Discussions similaires

Comment réaliser une sauvegarde de base MS SQL via un raccourci sur le bureau
virturel -
jbi13 -

10 réponses
Sauvegarde automatique de ma base de données Oracle
cs_shaloom -
cantador -

3 réponses
Défilement automatique d'une page
cs_scls19fr -
DevDeb -

19 réponses
recuperer les noms de champs d'une table
dimabab -
XARK -

13 réponses
Afficher les colonnes d'une table
farouk_ch -
farouk_ch -

2 réponses
Rejoignez-nous