Page 1 of 1

[SOLVED] fwrite() to write output of DB to file

Posted: Wed May 07, 2008 8:32 am
by Janco
Hi all,

I hope you can help. I need to use the fwrite() to write the output of a select query to a file with a special delimiter separating the fields and pad certain fields.

I tried to do the above with the normal MySQL "INTO OUTFILE........" but because I have to pad certain fields and use a "รพ" as a delimiter things just went wrong because sed and awk aren't up to the challenge.

The reason for this situation is that I have a User DB of several stores and I'm in the process of streamlining some processes. Once every day the database has to be dump/extracted to a file which will be sent to all the stores and then the file has to be imported by the store software - which is written in COBOLT - thus the reason for the specifics.

The way that I have it is that first I do:
$query="select * from table";
$result=mysql_query($query) or die (mysql_error());
while ($row=mysql_fetch_array($result)) {
//specify all 78 rows
$var1 = $row['col1'];
$var2 = $row['col2']; //blah blah blah etc
}

// Write to file section
$file = "FILE";
$fo = fopen($file,'w');
$string = <--- What do I add here, do I add all 78 rows as "$var1,$var2...$var78\n"?
fwrite($fo, $string);
fclose($fo);

Where do I add the delimiter in all this, do I take the $var1 and sprintf() it first before I pass it to the fwrite() and is the query correct so far and with it add each row the file?

I've tried a couple of things and nothing seems to be working, can you please help me??

Re: fwrite() to write output of DB to file

Posted: Wed May 07, 2008 8:42 am
by DeFacto
is that what you are looking for?
Click on me

Re: fwrite() to write output of DB to file

Posted: Wed May 07, 2008 9:18 am
by Janco
Impressive - just a shame that I haven't got the foggiest idea of what is going on in that script I'm VERY new to PHP.

Thank you for the reply/post I'll try to implement the code and see what happens but first it's me and the PHP tutorials so that at least I know what I'm implementing.

The Output file is a normal text file in Linux but the code you sent me will definitely come in handy, thank you.

Re: fwrite() to write output of DB to file

Posted: Wed May 07, 2008 2:21 pm
by Janco
I tried the code I was sent but it works 100 if you have a section in your HTML page where you want to export to XLS/CSV but for what I want to do...unfortunately it doesn't work.

The script that I'm trying to write will be called by a shell script to do the extract to file then using scp send it to all the stores were the file will be imported by the POS

What I did was:

<------- snip --------->
while ($row=mysql_fetch_array($result)) {
$var1= $row['col1'];
$var2=$row['col2'];
$var3=$row['col3'];

$file = "FILENAME";
$fo = fopen($FILENAME, 'w');
$string = '$var1,$var2,$var3\n';
fwrite($fo, $string);
fclose($fo);
}
?>
What happens is that the rows aren't being appended to the file, they keep on overwriting the previous line and I end up with only the last row's data in FILENAME.

Can anyone point out why this is happening, am I missing a piece of code somewhere that will append each row to a new line instead of overwriting it?

Re: fwrite() to write output of DB to file

Posted: Wed May 07, 2008 2:47 pm
by EverLearning
Your 'mode' parameter to fopen() is wrong. If you want to append to file use 'a'.

Code: Select all

$fo = fopen($FILENAME, 'a');
PHP Manual is a great resource :) Use it.

Re: fwrite() to write output of DB to file

Posted: Wed May 07, 2008 3:10 pm
by Janco
Thank you for the tip, I'll keep it in mind next time I'm close to a PC Book shop.

Re: fwrite() to write output of DB to file

Posted: Wed May 07, 2008 3:14 pm
by EverLearning
PHP Manual is an online resource. Click on the link and browse around a bit :D

Re: fwrite() to write output of DB to file

Posted: Wed May 07, 2008 3:24 pm
by Janco
Now I feel like a total ass! I was so caught up in the fwrite it never crossed my mind to look at the fopen() function, but thank you for pointing that out...being a newbie and under pressure makes you miss the small things.