Write data to file from MySQL table

dcjones

Hi all,

I am trying to extract data from a table and write it to a text file. This is what I have so far:

$table = "LHRHAL_Depart";

$mysql = mysql_query("SELECT * FROM $table");
$row = mysql_fetch_assoc($mysql);

$arr=array($row);  

$fp=fopen("HAL.txt","w+");  

foreach($arr as $key => $value){  

fwrite($fp,$value."\t");  

}  

fclose($fp);

The issue I have, the text file is empty which tells me I am doing this completly wrong. Can anyone point me in the right direction.

dalecosp

Have you tried using [man]file_put_contents/man? It takes a layer of complexity off the file handling code.

As for what's wrong in your code, not certain; could be a permissions thing (might help in that case to call [man]is_writeable/man on the file first), or it could be your handling (I would never call fwrite in a loop like that I don't think ... what if you concatenate the string first and then do the writing?).

The other thing you might look into is simply doing "into outfile" in your SQL. If you have permissions to do that from your database, it might be the simplest answer.

HTH,

bradgrafelman

One problem I see is this:

$arr=array($row);

[man]mysql_fetch_assoc/man already returns an array, so you've now (needlessly) buried the array of columns inside of another array.

In addition, note that [man]fputcsv/man would allow you to write an entire 1-dimensional array at once (plus it would add nifty features like automatically escaping/quoting values in true CSV-like form).

Also, you should always check to see if [man]mysql_query/man returned boolean FALSE (indicating an error occurred) and, if so, outputting/logging the SQL error (see [man]mysql_error/man) and perhaps also the query itself.

dalecosp

And, since Brad's pointed THAT out, this:

$row = mysql_fetch_assoc($mysql);

... would indicate that you only expect to return one row from the DB in the first place. If that's not true, it's usually more like:

$arr=array();
$mysql = mysql_query("SELECT * FROM $table");
if ($mysql) {
   while($row = mysql_fetch_assoc($mysql)) {
      $arr[]=$row['something'];
   }
}

... but it all kinda depends on the data.

dcjones

Hi and many thanks for your replies,

I now have the following which is writing the data from the table into my file but the content of the file starts and ends with " like:

"1750VS658 C0623ACCACC3A319 GVAIR343 "
"0450BA012 C0436SINSIN3A340 GCIVS74409L "
"0450IB7571 C0436SINSIN3A340 GCIVS74409L "

How can I remove the quotes before writing to the file?

dcjones

Hi,

I have managed to get my code to work as required but it looks messy, could this be written cleaner and with less code. The data in the table has spaces and must remain when writtenm to the HAL.002 file.

Example data when written to HAL.002

1425VS7814 T1433LCALCA1D117 5BDCK 320 17
1425BA287 C SFOSFO5D554 GBYGC 744 C54
1425AC855 T1430YVRYVR3D329 CGHKW 333 29

$table = "LHRHAL_Depart";

$fp=fopen("HAL.002","w+");

$arr=array();
$mysql = mysql_query("SELECT * FROM $table");
if ($mysql) {
   while($row = mysql_fetch_assoc($mysql)) {
		$list = array (
    		array($row['Col1']."".$row['Col2']."".$row['Col3']."".$row['Col4']),
		);
		foreach ($list as $fields) {
   		 	fputcsv($fp, $fields );
		}
	} 
}
fclose($fp);

// below is the only way I could come up with to remove the start and end quotes from the records (lines) in the HAL.002 file.

$myFile = "HAL.002";
$fh = fopen($myFile, 'r');
$theData = fread($fh, filesize($myFile));
fclose($fh);


$ReplaceThese = array("\'","\"","\""); 
         $buffer = str_replace($ReplaceThese, '', $theData); 

$myFile = "HAL.002";
$fh = fopen($myFile, 'w+');
    $writeData_d = $buffer;
    fwrite($fh, $writeData_d);
    fclose($fh);

bradgrafelman

Again, [man]mysql_fetch_assoc/man already returns an array of columns... why are you trying to bury that data inside of more complex (and ultimately useless) array structures? Why aren't you simply using the array that the function returns?

dcjones

Hi Brad,

If I remove:

$list = array (
	array($row['Col1']."".$row['Col2']."".$row['Col3']."".$row['Col4']),
);

then the script looks like:

$table = "LHRHAL_Depart";

$fp=fopen("HAL.002","w+");

$arr=array();
$mysql = mysql_query("SELECT * FROM $table");
if ($mysql) {
   while($row = mysql_fetch_assoc($mysql)) {
		foreach ($row as $fields) {
   		 	fputcsv($fp, $fields );
		}
	} 
}
fclose($fp);

$myFile = "HAL.002";
$fh = fopen($myFile, 'r');
$theData = fread($fh, filesize($myFile));
fclose($fh);


$ReplaceThese = array("\'","\"","\""); 
         $buffer = str_replace($ReplaceThese, '', $theData); 

$myFile = "HAL.002";
$fh = fopen($myFile, 'w+');
    $writeData_d = $buffer;
    fwrite($fh, $writeData_d);
    fclose($fh);

but there is no data written to the table. What am I missing?

bradgrafelman

You aren't passing [man]fputcsv/man an array. There is no need for a foreach() loop - just pass the array directly to the function and it will handle writing out each individual field on its own. (After all, that's the whole point of using that function.. if it couldn't handle the entire array, you might as well just be using fwrite() instead!)

Also, all of this:

$myFile = "HAL.002"; 
$fh = fopen($myFile, 'r'); 
$theData = fread($fh, filesize($myFile)); 
fclose($fh); 


$ReplaceThese = array("\'","\"","\""); 
         $buffer = str_replace($ReplaceThese, '', $theData); 

$myFile = "HAL.002"; 
$fh = fopen($myFile, 'w+'); 
    $writeData_d = $buffer; 
    fwrite($fh, $writeData_d); 
    fclose($fh);

is (at best) unnecessary. If you want [man]fputcsv/man to format the data in a certain way, then do so by specifying the arguments that the function expects. Don't try to take the default options and then later mangle the data on your own.

(However, I suspect the data will be more to what you're expecting once you start using fputcsv() correctly.)

Jacobdixon

Are you finding problem with fetching data from database? Had you checked the database connectivity

PHP Application Development