how to export database in PHP
Posted: Fri Jul 24, 2009 8:07 am
Can any body tell me the php script for exporting database ?
A community of PHP developers offering assistance, advice, discussion, and friendship.
http://forums.devnetwork.net/
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;
?>
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.phps