Solved

Related:  Exporting Files from MySQL table to txt file

Posted on 2011-09-12
10
346 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

These days socially coordinated efforts have turned into a critical requirement for enterprises.
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

867 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

19 Experts available now in Live!

Get 1:1 Help Now