mysql, code to extract all content

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
orangeapple
Forum Commoner
Posts: 70
Joined: Tue Jan 06, 2004 1:24 pm
Location: Geneva / Switzerland

mysql, code to extract all content

Post by orangeapple »

Hi,

I'm looking for the mysql code to extract all datae (content only) from all tables.
Does someone know that code ?
Thanks a lot.
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

Extract or Export ? phpmyadmin
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

User avatar
orangeapple
Forum Commoner
Posts: 70
Joined: Tue Jan 06, 2004 1:24 pm
Location: Geneva / Switzerland

Post by orangeapple »

well... i just would like to :

1. get the code of what is done when you export all datae (only datae, but from all tables of the database) in phpmyadmin.

2. Then, i suppose i can create a .sql file with 'fopen' and 'fclose' ?

Is that the proper way to do it ? any other suggestions ?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

read my posted link.
User avatar
orangeapple
Forum Commoner
Posts: 70
Joined: Tue Jan 06, 2004 1:24 pm
Location: Geneva / Switzerland

Post by orangeapple »

what is wrong with this ?:

SELECT * INTO OUTFILE 'c:/backup.sql' FROM microsedb;

this is the error message :

Parse error: parse error in c:\program files\easyphp1-8\www\microsent\pages\backup_answer.php on line 137
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

perhaps you're wrapping the query in single quotes so the single quoates around the filename drop you out of the string?
User avatar
orangeapple
Forum Commoner
Posts: 70
Joined: Tue Jan 06, 2004 1:24 pm
Location: Geneva / Switzerland

Post by orangeapple »

same error message for :

SELECT * INTO OUTFILE "c:/backup.sql" FROM microsedb;
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

if you placed that into the code without sending it to mysql through mysql_query() (and not inside a string correctly) you may have issues.. Is that the line in its entirety?
User avatar
orangeapple
Forum Commoner
Posts: 70
Joined: Tue Jan 06, 2004 1:24 pm
Location: Geneva / Switzerland

Post by orangeapple »

Ok, now I can create a backup file with the following script :

$sql_query = "SELECT * INTO OUTFILE 'c:/backup.sql' FROM login";
$result_sql_query = mysql_query($sql_query);


the result in the outfile is the following :

1 admin1 Fri 26th Mar 2004 06:56 pm Mon 10th May 2004 08:52 pm 1080323790
2 admin1 Tue 30th Mar 2004 07:48 pm Mon 10th May 2004 08:52 pm 1080668898
3 admin1 Tue 30th Mar 2004 08:48 pm Mon 10th May 2004 08:52 pm 1080672515
4 admin1 Mon 05th Apr 2004 08:48 pm Mon 10th May 2004 08:52 pm 1081190916
5 admin1 Mon 05th Apr 2004 08:59 pm Mon 10th May 2004 08:52 pm 1081191554
6 admin1 Mon 05th Apr 2004 09:09 pm Mon 10th May 2004 08:52 pm 1081192181
7 admin1 Mon 05th Apr 2004 09:10 pm Mon 10th May 2004 08:52 pm 1081192215

but this file is not usable for reloading as it should be :

INSERT INTO `login` VALUES (1, 'admin1', '', 'Fri 26th Mar 2004 06:56 pm', 'Mon 10th May 2004 08:52 pm', '1080323790');
INSERT INTO `login` VALUES (2, 'admin1', '', 'Tue 30th Mar 2004 07:48 pm', 'Mon 10th May 2004 08:52 pm', '1080668898');
INSERT INTO `login` VALUES (3, 'admin1', '', 'Tue 30th Mar 2004 08:48 pm', 'Mon 10th May 2004 08:52 pm', '1080672515');
INSERT INTO `login` VALUES (4, 'admin1', '', 'Mon 05th Apr 2004 08:48 pm', 'Mon 10th May 2004 08:52 pm', '1081190916');
INSERT INTO `login` VALUES (5, 'admin1', '', 'Mon 05th Apr 2004 08:59 pm', 'Mon 10th May 2004 08:52 pm', '1081191554');
INSERT INTO `login` VALUES (6, 'admin1', '', 'Mon 05th Apr 2004 09:09 pm', 'Mon 10th May 2004 08:52 pm', '1081192181');
INSERT INTO `login` VALUES (7, 'admin1', '', 'Mon 05th Apr 2004 09:10 pm', 'Mon 10th May 2004 08:52 pm', '1081192215');

like the export file in phpmyadmin.
How can i get the second format ready to reload ?

Tks !!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you have to create it, unless you want to use other tools mentioned already.
Post Reply