Writing to files

peteraub

I have written a script to dump database data to a file with sql statements for a backup. The problem is that I get new lines in the text file where I haven't specified and each statement doesn't end up on a single line as I would like. Does anyone have any ideas as to why this is happening.
Thanks

dalecosp

Almost certainly there is an error in your script.

😉

superwormy

Post code...

peteraub

$result = mysql_query("SELECT * FROM table");

while($row = mysql_fetch_array($result)) {
$append = "\"INSERT INTO table VALUES(".$row[field1'].","
.$row['field2'].",".$row['field3'].")\";";
$fp = fopen("$filename", "a") or die("Couldn't open $filename");
fputs($fp, $append."\n");
fclose($fp);
}

This is my script. It runs ok until field $row['field2']. Then it leaves some blank lines after some rows of file and not others. After that it will insert a new line before the comma on some lines and not others.

The final result is scattered lines with new lines starting in different places of row.

LordShryku

$row[field1'] missing quote before field1

prolly not your error, but an error nonr the less..

superwormy

Do your fields have newlines in them?

Also, $append is broken into two lines here... so that has a newline after "\"INSERT INTO table VALUES(".$row[field1'].","

And $filename in the fopen() function shouldn't be in quotes.

peteraub

Thanks for your help guys. It isnt the syntax errors which is stopping it from working. I think the problem may be that some of the data I inserted by copying and pasting and maybe it is picking up carriage returns when it reads data from database. Is there anyway I can get the code to ignore carriage returns when it is reading from database.
Any help would be appreciated.

HalfaBee

Try trim()

HalfaBee

dalecosp

Dunno if trim() would help or not ... guess I could RTFM.

Almost sure that

$var=str_replace("\n", "", $var);

would take care of it ... unless it were Windoze data...

RealBeale

I used to have some scripts that did this sort of thing, but now I backup my databases using mysqldump - it saves having to write a custom script to output the information to a file and then restore it again.

eg. mysqldump -u[user] -p[pass] [dbname] > [filename.sql].

It outputs a file containing create table statements followed by insert statements so you can rebuild the database properly from a backup.

Hope that helps....

Chris

peteraub

Thanks RealBeale
Can you do mysqldump when your database is on webserver and you don't have console window access to database. I have done it for database on my desktop machine but don't know how you do it without console window and database is on web server.

RealBeale

I use mysql dump when I'm doing batch file backups, but if you don't have access to the console you might want to try looking at phpmyadmin. It's a web based MySQL DB management application written in PHP and I'm fairly certain you can do dumps from there. It's easy to use as well.

Beale

dalecosp

If the server admin (hosting company?) has limited your use of the mysql client via the console, probably not.

If Apache has a shell (probably, almost definitely not) you could use shell_exec() or passthru() or similar to attempt running mysqldump.

No cron either, I suppose ?