how to export database in PHP
Moderator: General Moderators
how to export database in PHP
Can any body tell me the php script for exporting database ?
-
UnknownOne
- Forum Newbie
- Posts: 5
- Joined: Thu Jul 23, 2009 8:52 am
Re: how to export database in PHP
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
Credit to DanielRo
You could also try:
This one below will send it to the browser with an open/save dialogue box
You can visit:
and follow steps from there. Pretty coools apps I've collected from the net. Hope they help you. 
$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;
?>
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);
?>
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";
}
?>
Code: Select all
http://www.stevensmedia.com/downloads/s ... t2csv.phpsRe: how to export database in PHP
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
<?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
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.
http://www.php-mysql-tutorial.com/wikis ... bases.aspx
I personally use phpmyadmin because I'm lazy.
Re: how to export database in PHP
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.
Thank you for help ,i m now going to study ur given tutorials.