Solved

Related:  Exporting Files from MySQL table to txt file

Posted on 2011-09-12
10
345 Views
Last Modified: 2013-12-13
Heilo,
Your solution almost worked for me.  

Experts:
 I have one problem...  I am only getting one row of data, and each field is on a new line.  I need to get each row on a new line and output the entire table.  How do I correctly structure?

Thanks for your help!



<?php  


mysql_connect("localhost","username","password") or die("Unable to connect to SQL server");
mysql_select_db('database') or die("Unable to SELECT DB");

echo "Connected to DB";
echo "<br /><br />";



$q="SELECT * FROM product";  
$result = mysql_query($q) or die( "Error: " . mysql_error() );  
//$counter=0;  
while( $row = mysql_fetch_assoc($result) )  
{  
        //++$counter;  
        $f = fopen("googlebase.txt","w");  
        fwrite($f,explode("\n",$row));  
        fclose($f);  
}   
?> 

Open in new window

0
Comment
Question by:rlb1
  • 5
  • 3
  • 2
10 Comments
 

Author Comment

by:rlb1
ID: 36523140
Note:

fwrite($f,explode("\n",$row));  should be   fwrite($f,implode("\n",$row));

I was just trying things to see what I could get to work...
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 200 total points
ID: 36523864
Have you considered using MySQL's OUTFILE or are you strictly wanting to do this from PHP?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36523897
In other words, does this not work:

SELECT {column list}
INTO OUTFILE 'googlebase.txt' LINES TERMINATED BY '\r\n'
FROM product;

Open in new window


For example, in an example using cities, {column list} might be generated as one string value like:
CONCAT('"', `name`, '", "', `country`, '", ', `population`) 

Open in new window


This will create the ["some city", "usa", 1000000] output if you want a CSV file.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36523916
And even easier than that, you can use the FIELDS TERMINATED BY option.
Here is the reference for OUTFILE: http://dev.mysql.com/doc/refman/5.0/en/select.html
If you want PHP, I will defer to folks like hielo and Ray_Paseur. Hopefully they stop by shortly.
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 300 total points
ID: 36524370
Have a look at this while loop:
while( $row = mysql_fetch_assoc($result) )  
{  
        //++$counter;  
        $f = fopen("googlebase.txt","w");  
        fwrite($f,explode("\n",$row));  
        fclose($f);  
}

Open in new window

On line 4 it opens a file for writing.  On line 5 it writes one line (the line retrieved in the while() iterator).  On line 6 it closes the file.  Then the while() iterator retrieves the next row, and the whole process begins again.

What I think I would do is move the fopen() statement above the while() loop.  And move the fclose() statement below the while() loop.  Thus the file will get opened, the while() loop will retrieve the rows one at a time, the fwrite() will write the rows into the file, and when the results set is exhausted and while() is no longer true, the loop will terminate and the file will get closed.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36524493
Ha. I didn't even look at the PHP code. So simple. :)
0
 

Author Comment

by:rlb1
ID: 36524590
Ray,
That is Perfect.  I moved the fclose below, but forgot about moving the fopen above too.  That solves it!

One more little question:  How do you make the results of a .csv file appear on individual rows.  This works, but when I open it in excel, all results are on one line.

Thanks for your help!

<?php    
  
  
mysql_connect("localhost","username","password") or die("Unable to connect to SQL server");  
mysql_select_db('database') or die("Unable to SELECT DB");  
  
echo "Connected to DB";  
echo "<br /><br />";  
  
  
  
$q="SELECT * FROM product";    
$result = mysql_query($q) or die( "Error: " . mysql_error() );    
//$counter=0;    
$f = fopen("googlebase.csv","w");
while( $row = mysql_fetch_assoc($result) )    
{    
        //++$counter;    
            
        fwrite($f,implode(",",$row));    //WORKS
            
}     
fclose($f);


?>

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 36524607
Try the PHP built-in function.  Here's the link.  It would replace the fwrite() function.
http://us2.php.net/manual/en/function.fputcsv.php

fputcsv($f, $row);
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36524975
Again, very nice Ray. As an aside, note that you changed Ray's suggested explode("\n", $row) to implode(",", $row) which is likely why you got one line. I have also found that some times Windows does not treat new line correctly without carriage return, so "\r\n".
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 36525852
There is a built-in PHP constant for the end-of-line character, PHP_EOL.  It is context aware and works correctly on Unix, PC and Mac installations.  It is not needed with fputcsv() because fputcsv() will add the correct end-of-line characters.  Over time I have come to prefer PHP_EOL instead of \n or \r\n because PHP_EOL will be right no matter where I run my script.

Best to all, ~Ray
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
.htaccess file settings 4 36
Not needed 13 57
File not deleting using unlink 6 12
Can't connect to my database 2 13
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now