Solved

Related:  Exporting Files from MySQL table to txt file

Posted on 2011-09-12
10
347 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 109

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
 
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 109

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 109

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
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…

777 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