MySQL Export 1

Momozone · Aug 22, 2006

Hi,

Thanks in advance for anyone who helps...

I am looking for a php script that will allow me export data for specific tables in a mysql databse. But I need a drop down that will list all the tables so that I can select which one I want to export from.

Can anyone help?

Regards

MoMoZoNe

jpadie · Aug 22, 2006

phpmyadmin?

Momozone · Aug 22, 2006

As I said I need a php script. Do not want to use phpmyadmin. I have phpmyadmin but use it myself. The script is for accounts department who will need it as simple as:

SELECT TABLE
CLICK EXPORT

Thanks for the reply anyway

MoMoZoNe

KarveR · Aug 22, 2006

see mysql_list_tables

that should enable you to populate a drop-down, then its down to your own ingeuity to create the selects required for each different table (unless you use *), form that point you can search this very forum on how to force a csv download, and you should be set

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

jpadie · Aug 22, 2006

phpmyadmin is entirely php.

but i guess you want something lighter weight.

here is a method for downloading sql insert statements (essentially an export). it can be easily adapated to add a create statement to the beginning of each table.

Code:

<?
$hostname = "";
$username = "";
$password = "";
$dbname = "";
mysql_connect($hostname, $username, $password);
mysql_select_db($dbname);

function getTables(){
	$result = mysql_query("show tables") or die("can't perform query. ".mysql_error());
	while ($row = mysql_fetch_array($result,MYSQL_NUM)):
		$tables[] = $row[0];
	endwhile;
	return $tables;
}
function renderPickerForm(){
	$tables = getTables();
	$string = '
<form method="post" action="'. $_SERVER['PHP_SELF'] .'">
<fieldset>
<legend>Select tables to export</legend>';

	foreach ($tables as $table) {
		$string .= '
<span class="tablecheckbox">
	<label>
		<span class="checkbox">
			<input type="checkbox" value="'.$table.'" name="table[]" />
		</span>
		<span class="label">
			'.$table.'
		</span>
	</label>
</span>';
}
	$string .= '
	<div>
		<input type="submit" name="submit" value="export" />
	</div>
</fieldset>
</form>';
	return $string;
}

function returnTablesToExport() {
	if ( !isset ($_POST['table'] ) ) return false;
	if ( !is_array ($_POST['table']) ) return false;
	$_table = array();
	$permittedtables = getTables();
	foreach ($_POST['table'] as $table):
		if (in_array($table, $permittedtables)):
			$_table[] = $table;
		endif;
	endforeach;
	return $_table;
}

function performExport($tables) {
	$filename = "sqlexport_" . md5(uniqid("",true)) . ".sql";
	$fh = fopen($filename, "wbt") or die("cannot open file for writing");
	set_time_limit(0);
	foreach ($tables as $table):
		$result = mysql_query("Select * from $table");
		while ($row=mysql_fetch_assoc($result)):
			$insert = "";
			foreach ($row as $key=>$val):
				$insert .= "`$key` = '".mysql_escape_string($val)."',";
			endforeach;
			fwrite($fh, "Insert into $table set ". rtrim($insert,",") . ";\r\n");
		endwhile;
		fwrite($fh, "\r\n\r\n####################################\r\n\r\n");
	endforeach;
	fclose($fh);
	serveFile($filename);
}
function serveFile($filename) {
	header("Cache-Control: ");
	header("Pragma: ");
	header("Content-Type: application/octet-stream");
	header("Content-Length: " .(string)(filesize($filename)) );
	header('Content-Disposition: attachment; filename="'.$filename.'"');
	header("Content-Transfer-Encoding: binary\n");
	readfile($filename);
	@unlink($filename);
}
if ( ($tables = returnTablesToExport()) === false):
	echo renderPickerForm();
else:
	performExport($tables);
endif;
?>

Momozone · Aug 23, 2006

jpadie.... That seems to work until I export then it says it could not open file??

Also I need it to export as a CSV file

Regards,

MoMoZoNe

jpadie · Aug 23, 2006

if you can't open the file then the permissions are set incorrectly on your server. the script can be re-written not to use files but then you risk failures due to the size of data in the memory buffer.

personally i'd far rather have a backup in a mysql insert format... but i'm happy to recast this as a csv (in fact i have written this already but need to ask a few questions first:

+ can you tell me whether you have the zip library installed on your php server (see

http://pecl.php.net/package/zip)?

+ what version of php are you running?

the problem is that dumping a csv output of multiple tables to the same file does not make much sense. so to deliver them to the user you need either to zip them up or deliver them by mail. the former is the better solution, i think.

Momozone · Aug 23, 2006

I have PHP 4 on a CentOS box.

I dont have ZIP but can install it? Or can it tar it?

I just need it to export the data cos we are a call centre and need to return data to suppliers in csv.

Thanks for your help.

MoMoZoNe

jpadie · Aug 23, 2006

php 4 has problems in that it cannot use the zlib library for writing archives.

i'll have a think and get back to you.

jpadie · Aug 23, 2006

here is a revised code set.

it relies on the pear class File_Archive to work. if you do not have this installed then you can do so from the command line (or shell) by typing

Code:

pear install --alldeps File_Archive

the code is as follows:

Code:

<?
$hostname = "";
$username = "";
$password = "";
$dbname = "";
mysql_connect($hostname, $username, $password);
mysql_select_db($dbname);

function getTables(){
    $result = mysql_query("show tables") or die("can't perform query. ".mysql_error());
    while ($row = mysql_fetch_array($result,MYSQL_NUM)):
        $tables[] = $row[0];
    endwhile;
    return $tables;
}
function renderPickerForm(){
    $tables = getTables();
    $string = '
<form method="post" action="'. $_SERVER['PHP_SELF'] .'">
<fieldset>
<legend>Select tables to export</legend>';

    foreach ($tables as $table) {
        $string .= '
<span class="tablecheckbox">
    <label>
        <span class="checkbox">
            <input type="checkbox" value="'.$table.'" name="table[]" />
        </span>
        <span class="label">
            '.$table.'
        </span>
    </label>
</span>';
}
    $string .= '
    <div>
        <input type="submit" name="submit" value="export" />
    </div>
</fieldset>
</form>';
    return $string;
}

function returnTablesToExport() {
    if ( !isset ($_POST['table'] ) ) return false;
    if ( !is_array ($_POST['table']) ) return false;
    $_table = array();
    $permittedtables = getTables();
    foreach ($_POST['table'] as $table):
        if (in_array($table, $permittedtables)):
            $_table[] = $table;
        endif;
    endforeach;
    return $_table;
}
function performExport($tables) {
    set_time_limit(0);
    foreach ($tables as $table):
		$filenames[] = "sqlexport_" . $table . md5(uniqid("",true)) . ".csv";
	    $fh = fopen($filenames[count($filenames)-1], "wbt") or die("cannot open file for writing");
        $result = mysql_query("Select * from $table");
        while ($row=mysql_fetch_assoc($result)):
            $insert = "";
            foreach ($row as $key=>$val):
                $insert .= '"'.addslashes($val).'",';
            endforeach;
            fwrite($fh, rtrim($insert,",") . "\r\n");
        endwhile;
		fclose($fh);
    endforeach;
    $zipFile = zipFiles($filenames);
	serveFile($zipFile);
}
function zipFiles($filenames){
	clearstatcache();
	$name = date("Y-m-j"). "-SQL_Export.zip";
	require_once "File/Archive.php"; 
	
	File_Archive::setOption("zipCompressionLevel", 9);
	File_Archive::extract(
    				$filenames,
				    File_Archive::toArchive( 
										$name, 
										File_Archive::toOutput() 
									)
					); 
	return $name;
}
function serveFile($zipFile) {
    header("Cache-Control: ");
    header("Pragma: ");
    header("Content-Type: application/octet-stream");
    header("Content-Length: " .(string)(filesize($zipFile)) );
    header('Content-Disposition: attachment; filename="'.$zipFile.'"');
    header("Content-Transfer-Encoding: binary\n");
	@readfile ($zipFile);
	unlink($zipFile);
}


if ( ($tables = returnTablesToExport()) === false):
    echo renderPickerForm();
else:
    performExport($tables);
endif;
?>

Momozone · Sep 13, 2006

Is there any way of doing this without using pear all i want is a simple csv or xls file, it does not need to be zipped.

Thanks in advance

MoMoZoNe

jpadie · Sep 13, 2006

there is a design choice that you have to make.

it does not make sense for a single csv to have more than one table backed up to it. so each csv = 1 table.

so either

i) the user must select a single table to backup and then get a single csv. or

ii) you want to allow a user to backup more than one table at the same time

if you go down route (ii) you either have to zip the files up and shove it down to the browser or email a number of files. happy to provide a variation for either route (i) or an email alternative.

Momozone · Sep 14, 2006

The user will only ever need to export one table at a time but need to be able to select which table to export.

Thanks for your help on this.

MoMoZoNe

jpadie · Sep 14, 2006

here you go.

i've changed the checkboxes to a select box as you do not want users to be able to backup more than one table at a time.

i've also included a checkbox for users to be able to backup the field headers too.

Code:

<?
$hostname = "";
$username = "";
$password = "";
$dbname = "";
mysql_connect($hostname, $username, $password);
mysql_select_db($dbname);

function getTables(){
    $result = mysql_query("show tables") or die("can't perform query. ".mysql_error());
    while ($row = mysql_fetch_array($result,MYSQL_NUM)):
        $tables[] = $row[0];
    endwhile;
    return $tables;
}
function renderPickerForm(){
    $tables = getTables();
    $string = '
<form method="post" action="'. $_SERVER['PHP_SELF'] .'">
<fieldset>
<legend>Select tables to export</legend>
<div>
<select name="table">
';


    foreach ($tables as $table) {
        $string .= '
			<option value="'.$table.'">'.$table.'</option>';
}
	$string .= "\r\n</select> &nbsp;";
    $string .= '
        <input type="checkbox" name="includeHeaders" value="headers" /> &nbsp; Include Headers';
    $string .= '</div>';
    $string .= '
	<div style="margin-top:10px;">
        <input type="submit" name="submit" value="export" />
    </div>
</fieldset>
</form>';
    return $string;
}

function returnTablesToExport() {
    if ( !isset ($_POST['table'] ) ) return false;
    return array(trim($_POST['table']));
}
function performExport($tables) {
    set_time_limit(0);
    foreach ($tables as $table):
		$cnt = 1;
        $filenames[] = "sqlexport_" . $table . md5(uniqid("",true)) . ".csv";
        $fh = fopen($filenames[count($filenames)-1], "wbt") or die("cannot open file for writing");
        $result = mysql_query("Select * from $table");
        while ($row=mysql_fetch_assoc($result)):
			$insert = "";
			$insert2 = "";
			foreach ($row as $key=>$val):
				if ($cnt===1 && isset($_POST['includeHeaders'])):
					$insert2 .= '"'.addslashes($key).'",';
				endif;
				$insert .= "\"".addslashes($val)."\",";
			endforeach;
			if (!empty($insert2)):
				fwrite($fh, rtrim($insert2,",")."\n");
				$cnt = 2;
			endif;					
			fwrite($fh, rtrim($insert,",")."\n");
        endwhile;
        fclose($fh);
    endforeach;
    //$zipFile = zipFiles($filenames);
    serveFile($filenames[0]);
}
/*
function zipFiles($filenames){
    clearstatcache();
    $name = date("Y-m-j"). "-SQL_Export.zip";
    require_once "File/Archive.php";
    
    File_Archive::setOption("zipCompressionLevel", 9);
    File_Archive::extract(
                    $filenames,
                    File_Archive::toArchive(
                                        $name,
                                        File_Archive::toOutput()
                                    )
                    );
    return $name;
}
*/
function serveFile($file) {
    header("Cache-Control: ");
    header("Pragma: ");
    header("Content-Type: application/octet-stream");
    header("Content-Length: " .(string)(filesize($file)) );
    header('Content-Disposition: attachment; filename="'.$file.'"');
    header("Content-Transfer-Encoding: binary\n");
    @readfile ($file);
    unlink($file);
}


if ( ($tables = returnTablesToExport()) === false):
    echo renderPickerForm();
else:
    performExport($tables);
endif;
?>

KarveR · Sep 14, 2006

jpadie, not sure why you didnt use;

Code:

$tables = mysql_list_tables($dbname);

its kinda shorter

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

jpadie · Sep 14, 2006

@KarveR
the main reason is because that function is deprecated.

as an aside - in the current formulation, it's not shorter! (although i accept that you could recast $dbname to a shorter variable name).

Code:

mysql_query("show tables")
mysql_list_tables($dbname)

KarveR · Sep 14, 2006

Deprecated ... not sure since when, thats a shame, but always found it much nicer than having to do all this to get an array:

Code:

function getTables(){
    $result = mysql_query("show tables") or die("can't perform query. ".mysql_error());
    while ($row = mysql_fetch_array($result,MYSQL_NUM)):
        $tables[] = $row[0];
    endwhile;
    return $tables;
}

vs

$tables=mysql_list_tables($dbname)

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

jpadie · Sep 14, 2006

Code:

$tables=mysql_list_tables($dbname)

that's funny: on my systems (php 4.4.4 and 5.1.4) (and according to the manual) this returns a resource, which means you'd still have to traverse the recordset.

which php version are you using? it would be interesting to see the code changes in the source.

i agree it would be neat if it could return an array (like the PEAR:

B similar function).

KarveR · Sep 14, 2006

it returns a resoucre on mine too, but you only end up traversing it once

PS sorry for the hijack.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

Momozone · Sep 15, 2006

jpadie - workes a treat thanks for you help!

MoMoZoNe

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

MySQL Export 1

IS-IT--Management

Technical User

IS-IT--Management

MIS

Technical User

IS-IT--Management

Technical User

IS-IT--Management

Technical User

Technical User

IS-IT--Management

Technical User

IS-IT--Management

Technical User

MIS

Technical User

MIS

Technical User

MIS

IS-IT--Management

Similar threads

Log in

Part and Inventory Search

Sponsor