Export table to sql

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Peuplarchie
Forum Contributor
Posts: 148
Joined: Sat Feb 04, 2006 10:49 pm

Export table to sql

Post by Peuplarchie »

Good day to you,
I have added an options to my db production site which is to back up the table into csv file, see code below.

Now I would like to be able to export to sql file, how would I do it without using phpMyadmin , I would like to use a function like the following.

Code: Select all

<?php
$host = 'localhost';
$user = '....';
$pass = '....';
$db = '....';
$table = $_GET[table];
$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 = "Backup__".$table."__".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;
echo "Exporting...";
?>

Thanks!
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: Export table to sql

Post by Jonah Bron »

A quick google brings some excellent results

http://www.google.com/search?q=php+expo ... l+sql+file
User avatar
Peuplarchie
Forum Contributor
Posts: 148
Joined: Sat Feb 04, 2006 10:49 pm

Re: Export table to sql

Post by Peuplarchie »

I always "stfw" before posting a request if I came here it is because I didn't found what I was looking for after a few ours of serach,
I need a script that I can trigger from a link that would bring up the sql file to download.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Export table to sql

Post by mikosiko »

Peuplarchie wrote: Now I would like to be able to export to sql file,
Could you please clarify what exactly do you mean by "export to a sql file" ?
User avatar
Peuplarchie
Forum Contributor
Posts: 148
Joined: Sat Feb 04, 2006 10:49 pm

Re: Export table to sql

Post by Peuplarchie »

I would like to take my mysql table and download it as an sql file
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Export table to sql

Post by mikosiko »

Peuplarchie wrote:I would like to take my mysql table and download it as an sql file
do you mean the table structure?

if so... read the sentence SHOW CREATE TABLE ....
http://dev.mysql.com/doc/refman/5.0/en/ ... table.html
User avatar
Peuplarchie
Forum Contributor
Posts: 148
Joined: Sat Feb 04, 2006 10:49 pm

Re: Export table to sql

Post by Peuplarchie »

structure and data
User avatar
Peuplarchie
Forum Contributor
Posts: 148
Joined: Sat Feb 04, 2006 10:49 pm

Re: Export table to sql

Post by Peuplarchie »

I don't want to copy the table to another table.
I would like to backup the table structure and data and download into a .sql file.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Export table to sql

Post by mikosiko »

Peuplarchie wrote:structure and data
mysqldump

or if you want to do it programing you can use together
- SHOW CREATE TABLE sentence for the structure and format the result and this sentence to produce a csv file with the date
- SELECT * FROM your-table INTO OUTFILE 'your-file'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

and yes... you can execute both from PHP... only limitation is in the case of the SELECT INTO OUTFILE sentence is that the file is created in the server
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: Export table to sql

Post by Jonah Bron »

And change the Content-type header appropriately with the header() function.
Post Reply