Writing to flat file

Exporting MySql data into a flat file

[beinghuman]

I am looking for some code to enable me to export the data in my table into a flat text file (.txt).

The table has 6 columns and about 50 entries in it.

Any help would be greatly appreciated.
Thanks in advance.

[jatar_k]

welcome to WebmasterWorld beinghuman,

try this page
[dev.mysql.com...]

look for select into outfile and mysqldump they offer some solutions

[Salsa]

Query the table and format the data into a $variable as you want it to appear in the text file. Then:

$fh = fopen("text_file_name.txt", "w"); //open or create for writing 
fwrite($fh,$variable); //write to file 
fclose($fh); //close file

[Salsa]

Jatar: Awesome! ;)

[jatar_k]

hehe

there are really a ton of ways to do it

you can grab it all from mysql with php and pretty much write it in any format you want, doesn't even take much time.

but...it depends on what you are planning to do with it.

If you want to use it to put back into mysql at a later date, such as a backup or moving servers, then mysqldump is your best bet

if you want to put it into excel or something then write it out as a csv

endless possibilities ;)

[beinghuman]

Thanks Jatar for the welcome.

I have looked at what you suggested but since I am new at php I could not follow what the site was saying. Maybe if I can explain myself better you will get a better Idea.

So far I got
<?php
require_once 'db_connect.php';

/// older stuff
$result = mysql_query( "SELECT * FROM categories" )
or die("SELECT Error: ".mysql_error());
$num_rows = mysql_num_rows($result);
$category_code=mysql_result($result,"category_code");
$category_name=mysql_result($result,"category_name");
$category_id=mysql_result($result,"category_id");
$category_display=mysql_result($result,"category_display");

$entry_line = "Name: $category_name\n Code: $category_code \n ID: $category_id\n Display: $category_display \n";
$fp = fopen("logs1.txt", "a");
fputs($fp, $entry_line);
fclose($fp);
?>

I manages to create the logs1.txt and it writes to it as in the Name: Code etc... but all the entries have the number 3 next to them (first category_id in the database) and nothing else.
Basically I need the script to output all the data in one table as a friend of mine requires it to do some java script work.
Any help would be greatly appreciated.

[Forse]

Why not export mysql data to xml? It's really esy to implement, u can get the classes and examples here: [phpclasses.org...]

[beinghuman]

Thanks for the link, I looked at the site and downloaded the package specified but it keeps giving me multiple errors even after I followed the instructions and installed the table etc...

Thanks anyway for your advice

[Salsa]

...or you can try finishing your PHP code.

See if this helps:

... 
$result = mysql_query( "SELECT * FROM categories")  
 or die("SELECT Error: ".mysql_error()); 
$entry_lines = ""; 
while ($query_data = mysql_fetch_array($result)) {  
 $entry_lines .= "Name: ".$query_data['category_name']."\n"; 
 $entry_lines .= "Code: ".$query_data['category_code']."\n"; 
 $entry_lines .= "ID: ".$query_data['category_id']."\n"; 
 $entry_lines .= "Display: ".$query_data['category_display']."\n\n"; 
) 
$fp = fopen("logs1.txt", "w");  
fputs($fp, $entry_lines); // fwrite() alias 
fclose($fp);  
...

or, you can always do mysqldump from the command line:

mysqldump -hlocalhost -ubeinghuman -p database_name.categories -> logs1.txt

...then put the file in a word processor and search and replace to get the format you want.

[beinghuman]

Thanks Salso that worked just great though it initially gave me a parse error on this line
".$query_data['category_display']."\n\n";
)

so I changed the ) into } and it worked fine.
Thank you very much, I greatly appreciated it.

[Salsa]

Yeah, typo there. When testing, I'm always surprised when I get everything right the first time.

I'm glad it worked for you.

[freeflight2]

you do not even need php for that... something like
mysql -e "SELECT * from categories" --batch --raw >outputfile.txt from the shell would do the same job (in php you might put this line into an exec();)

[jatar_k]

but would the web user be allowed to execute that?

and if so probably better to do

/usr/local/bin/mysql -u username -p somedb -e "SELECT * from categories" mysql --batch --raw > /known/stable/path/outputfile.txt