?
Solved

Related:  Exporting Files from MySQL table to txt file

Posted on 2011-09-12
10
Medium Priority
?
364 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 60

Assisted Solution

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

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 60

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 111

Accepted Solution

by:
Ray Paseur earned 1200 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
 
LVL 60

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 111

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 60

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 111

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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…
Suggested Courses
Course of the Month16 days, 17 hours left to enroll

864 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