Page 1 of 1

how to export database in PHP

Posted: Fri Jul 24, 2009 8:07 am
by waseem83
Can any body tell me the php script for exporting database ?

Re: how to export database in PHP

Posted: Fri Jul 24, 2009 8:08 am
by UnknownOne
You need to declare the following PHP variables for the script to work correctly and modular.
$host - MySQL server (usualy localhost)
$user - MySQL user account password
$pass - MySQL database name
$db - MySQL user account
$table - MySQL database table you want to export
$file - The filename you will be downloading

Code: Select all

 
<?php
$host = 'localhost';
$user = 'mysqlUser';
$pass = 'myUserPass';
$db = 'myDatabase';
$table = 'products_info';
$file = 'export';
 
$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die("Can not connect.");
 
$result = mysql_query("SHOW COLUMNS FROM ".$table."");
$i = 0;
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$csv_output .= $row['Field']."; ";
$i++;
}
}
$csv_output .= "\n";
 
$values = mysql_query("SELECT * FROM ".$table."");
while ($rowr = mysql_fetch_row($values)) {
for ($j=0;$j<$i;$j++) {
$csv_output .= $rowr[$j]."; ";
}
$csv_output .= "\n";
}
 
$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");
print $csv_output;
exit;
?>
 
Credit to DanielRo

You could also try:

Code: Select all

 
<?php
// database constants
// make sure the information is correct
define("DB_SERVER", "localhost");
define("DB_USER", "root");
define("DB_PASS", "password");
define("DB_NAME", "tutorials");
 
// connection to the database 
$dbhandle = mysql_connect(DB_SERVER, DB_USER, DB_PASS) 
   or die("Unable to connect to MySQL"); 
 
// select a database to work with 
$selected = mysql_select_db(DB_NAME, $dbhandle) 
   or die("Could not select examples"); 
 
// return all available tables 
$result_tbl = mysql_query( "SHOW TABLES FROM ".DB_NAME, $dbhandle ); 
 
$tables = array(); 
while ($row = mysql_fetch_row($result_tbl)) { 
   $tables[] = $row[0]; 
} 
 
$output = "<?xml version=\"1.0\" ?>\n"; 
$output .= "<schema>"; 
 
// iterate over each table and return the fields for each table
foreach ( $tables as $table ) { 
   $output .= "<table name=\"$table\">"; 
   $result_fld = mysql_query( "SHOW FIELDS FROM ".$table, $dbhandle ); 
 
   while( $row1 = mysql_fetch_row($result_fld) ) {
      $output .= "<field name=\"$row1[0]\" type=\"$row1[1]\"";
      $output .= ($row1[3] == "PRI") ? " primary_key=\"yes\" />" : " />";
   } 
 
   $output .= "</table>"; 
} 
 
$output .= "</schema>"; 
 
// tell the browser what kind of file is come in
header("Content-type: text/xml"); 
// print out XML that describes the schema
echo $output; 
 
// close the connection 
mysql_close($dbhandle); 
?>
 
This one below will send it to the browser with an open/save dialogue box

Code: Select all

 
<?php
  //
  // establish database connection
  //
  $conn = mysql_connect( 'MYSQL_HOST', 'MYSQL_USERNAME', 'MYSQL_PASSWORD' ) or die( mysql_error( ) );
  mysql_select_db( 'MYSQL_DATABASE', $conn ) or die( mysql_error( $conn ) );
  //
  // execute sql query
  //
  $query = sprintf( 'SELECT * FROM MYSQL_TABLE' );
  $result = mysql_query( $query, $conn ) or die( mysql_error( $conn ) );
  //
  // send response headers to the browser
  // following headers instruct the browser to treat the data as a csv file called export.csv
  //
  header( 'Content-Type: text/csv' );
  header( 'Content-Disposition: attachment;filename=export.csv' );
  //
  // output header row (if atleast one row exists)
  //
  $row = mysql_fetch_assoc( $result );
  if ( $row )
  {
    echocsv( array_keys( $row ) );
  }
  //
  // output data rows (if atleast one row exists)
  //
  while ( $row )
  {
    echocsv( $row );
    $row = mysql_fetch_assoc( $result );
  }
  //
  // echocsv function
  //
  // echo the input array as csv data maintaining consistency with most CSV implementations
  // * uses double-quotes as enclosure when necessary
  // * uses double double-quotes to escape double-quotes 
  // * uses CRLF as a line separator
  //
  function echocsv( $fields )
  {
    $separator = '';
    foreach ( $fields as $field )
    {
      if ( preg_match( '/\\r|\\n|,|"/', $field ) )
      {
        $field = '"' . str_replace( '"', '""', $field ) . '"';
      }
      echo $separator . $field;
      $separator = ',';
    }
    echo "\r\n";
  }
?>
 
You can visit:

Code: Select all

http://www.stevensmedia.com/downloads/s ... t2csv.phps
and follow steps from there. Pretty coools apps I've collected from the net. Hope they help you. ;)

Re: how to export database in PHP

Posted: Fri Jul 24, 2009 8:34 am
by waseem83
when i run your given code The output is like


<?xml version="1.0" ?>
- <schema>
- <table name="mpe_banner">
<field name="id" type="int(11)" primary_key="yes" />
<field name="ban_imgpath" type="varchar(200)" />
</table>
- <table name="mpe_categories">
<field name="id" type="int(11)" primary_key="yes" />
<field name="cat_name" type="varchar(200)" />
<field name="cat_display" type="varchar(10)" />
<field name="cat_current_date" type="date" />
<field name="cat_sequence" type="int(11)" />
</table>
- <table name="mpe_custom_offers">
<field name="id" type="int(11)" primary_key="yes" />
<field name="cus_offer_name" type="varchar(200)" />
<field name="cus_current_date" type="date" />
<field name="cus_img_path" type="varchar(100)" />
</table>
- <table name="mpe_customer">
<field name="id" type="int(11)" primary_key="yes" />
<field name="gender_title_id" type="int(11)" />
<field name="cus_first_name" type="varchar(200)" />
<field name="cus_surname" type="varchar(200)" />
<field name="cus_daytime_phone" type="varchar(100)" />
<field name="cus_evening_phone" type="varchar(100)" />
<field name="cus_email" type="varchar(200)" />
<field name="cus_home_address" type="varchar(1000)" />
<field name="cus_date_registered" type="date" />
<field name="cus_country" type="varchar(200)" />
<field name="cus_active_status" type="varchar(100)" />
<field name="cus_ref_id" type="varchar(20)" />
<field name="cus_subcribe_email" type="varchar(20)" />
</table>
- <table name="mpe_deal">
<field name="id" type="int(11)" primary_key="yes" />
<field name="model_id" type="int(11)" />
<field name="network_id" type="int(11)" />
<field name="maker_id" type="int(11)" />
<field name="category_id" type="int(11)" />
<field name="dea_duration_months" type="int(11)" />
<field name="dea_monthly_cost" type="float" />
<field name="dea_saving" type="float" />
<field name="dea_phone_price" type="float" />
<field name="dea_handset_cost" type="float" />
<field name="dea_tariff" type="float" />
<field name="dea_includes" type="varchar(200)" />
<field name="dea_offer" type="varchar(200)" />
<field name="dea_effective_cost_permonth" type="float" />
<field name="dea_commission" type="float" />
<field name="dea_profit" type="float" />
<field name="dea_cashback" type="float" />
<field name="dea_current_date" type="date" />
<field name="cus_offer_id" type="int(11)" />
<field name="dea_best_deal" type="varchar(10)" />
<field name="dea_refer" type="double" />
</table>
- <table name="mpe_gender_title">
<field name="id" type="int(11)" primary_key="yes" />
<field name="title" type="varchar(20)" />
</table>
- <table name="mpe_loginarea">
<field name="id" type="int(11)" primary_key="yes" />
<field name="log_insurance" type="varchar(2000)" />
<field name="log_headset" type="varchar(2000)" />
<field name="log_Contract" type="varchar(2000)" />
<field name="log_yournumber" type="varchar(2000)" />
<field name="log_recom_frd" type="varchar(2000)" />
</table>
- <table name="mpe_makers">
<field name="id" type="int(11)" primary_key="yes" />
<field name="mak_name" type="varchar(200)" />
<field name="mak_current_date" type="date" />
</table>
- <table name="mpe_models">
<field name="id" type="int(11)" primary_key="yes" />
<field name="cus_offer_id" type="int(11)" />
<field name="mod_name" type="varchar(200)" />
<field name="mod_desc" type="varchar(3000)" />
<field name="mod_feature" type="varchar(3000)" />
<field name="mod_specs" type="varchar(3000)" />
<field name="mod_current_date" type="date" />
<field name="mod_img_path" type="varchar(1000)" />
</table>
- <table name="mpe_monthly_cost">
<field name="id" type="int(11)" primary_key="yes" />
<field name="mon_description" type="varchar(30)" />
</table>
- <table name="mpe_networks">
<field name="id" type="int(11)" primary_key="yes" />
<field name="net_name" type="varchar(200)" />
<field name="net_current_date" type="date" />
<field name="net_img_path" type="varchar(1000)" />
</table>
- <table name="mpe_orders">
<field name="id" type="int(11)" primary_key="yes" />
<field name="deal_id" type="int(11)" />
<field name="ord_date" type="date" />
<field name="ord_payment_status" type="varchar(100)" />
<field name="customer_id" type="int(11)" />
<field name="ord_status" type="varchar(100)" />
<field name="ord_reference_no" type="varchar(500)" />
<field name="ord_current_date" type="date" />
</table>
- <table name="mpe_products">
<field name="id" type="int(11)" primary_key="yes" />
<field name="category_id" type="int(11)" />
<field name="maker_id" type="int(11)" />
<field name="network_id" type="int(11)" />
<field name="pro_current_date" type="date" />
<field name="pro_sequence" type="int(11)" />
<field name="pro_display" type="varchar(20)" />
</table>
- <table name="mpe_reviews">
<field name="id" type="int(11)" primary_key="yes" />
<field name="model_id" type="int(11)" />
<field name="rev_person_name" type="varchar(200)" />
<field name="rev_handset_ratings" type="int(11)" />
<field name="rev_description" type="varchar(2000)" />
<field name="rev_approval" type="varchar(20)" />
<field name="rev_date" type="date" />
</table>
- <table name="mpe_rights">
<field name="id" type="int(11)" primary_key="yes" />
<field name="name" type="varchar(500)" />
<field name="fkMenuId" type="int(11)" />
<field name="MenuHide" type="varchar(11)" />
<field name="createdon" type="date" />
<field name="createdby" type="varchar(500)" />
</table>
- <table name="mpe_role_right">
<field name="id" type="int(11)" primary_key="yes" />
<field name="roleid" type="int(11)" />
<field name="rightid" type="int(11)" />
</table>
- <table name="mpe_roles">
<field name="id" type="int(11)" primary_key="yes" />
<field name="name" type="varchar(500)" />
<field name="createdon" type="date" />
<field name="createdby" type="varchar(500)" />
</table>
- <table name="mpe_stock">
<field name="id" type="int(11)" primary_key="yes" />
The XML page cannot be displayed

Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.


--------------------------------------------------------------------------------

Only one top level element is allowed in an XML document. Error processing resource 'http://wks-server/test/'. Line 2, Pos...

<field name="ord_current_date" type="date" /></table><table name="mpe_products"><field name="id" type="...
"b"> <field name="model_id" type="int(11)" />
<field name="sto_serialno" type="varchar(500)" />
<field name="sto_colour" type="varchar(200)" />



But i need to have .sql file just like we click on export in phpmyadmin

Re: how to export database in PHP

Posted: Fri Jul 24, 2009 8:50 am
by Eric!
Here are 3 common methods: LOAD DATA INFILE, mysqldump() and phpmyadmin

http://www.php-mysql-tutorial.com/wikis ... bases.aspx

I personally use phpmyadmin because I'm lazy.

Re: how to export database in PHP

Posted: Fri Jul 24, 2009 9:03 am
by waseem83
Actually i want to write a code so that after each 30 minutes or specific time. I get back up sql file.

Thank you for help ,i m now going to study ur given tutorials.