Related: Exporting Files from MySQL table to txt file

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

rlb1Asked:
Who is Participating?
 
Ray PaseurCommented:
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
 
rlb1Author Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
Have you considered using MySQL's OUTFILE or are you strictly wanting to do this from PHP?
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
Ha. I didn't even look at the PHP code. So simple. :)
0
 
rlb1Author Commented:
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
 
Ray PaseurCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
Ray PaseurCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.