PHP MySQL Backup Script

[jdeutsch]

I'm trying to create a PHP MySQL backup script to backup some important databases I've got.  I've pretty much got everything set up all right, and it seems to be exporting text to the browser window (later on a backup file), except in some situtations where a specific table has a large number of fields, when trying to print the contents of each table.

The script works.  Some tables, which have only a few fields, less than 15 or so, print fine, table structure, and table contents, however, there are a few tables which have somewhere in the neighborhood of 80-100 fields, and when PHP goes to print the results of the query, I am faced with the error "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in g:\Apache\Apache\htdocs\www\ed_db\untitled-1.php on line 50"

[code]line 50:  while($row=mysql_fetch_array($query,MYSQL_NUM)) {[/code]

This only occurs on these larger tables, so the question is, is there a limit to the size of the array generated by mysql_fetch_array?  And if so, how can I work around this?

Thanks.

[hitman6003]

I don't think there is a limit to the number of elements in the array, but I could be wrong...I've seen tables with 50+ fields that have been pulled just fine.

Make sure that you are using mysql_error() to get any errors that occur.  The mysql_query associated with the loop that generally gives you a problem should give you the error.  Usually the error you get occurs when there is no mysql connection.

Also, why not just use phpMyAdmin to do this for you?  Or even MySQL's own MySQL Administrator?

[redarrow]

each time you point to the script the datbase is backed up ok.

[code]
<?php
  // Enter your MySQL access data 
  $host= 'dbhost';         
  $user= 'dbuser';               
  $pass= 'dbpassword';
  $db=  'db';

  $backupdir = 'backups';   

  // Compute day, month, year, hour and min.
  $today = getdate();
  $day = $today[mday];
  if ($day < 10) {
      $day = "0$day";
  }
  $month = $today[mon];
  if ($month < 10) {
      $month = "0$month";
  }
  $year = $today[year];
  $hour = $today[hours];
  $min = $today[minutes];
  $sec = "00";


  // Execute mysqldump command.
  // It will produce a file named $db-$year$month$day-$hour$min.gz
  // under $DOCUMENT_ROOT/$backupdir
  system(sprintf(
    'mysqldump --opt -h %s -u %s -p%s %s | gzip > %s/%s/%s-%s%s%s-%s%s.gz',                                                 
    $host,
    $user,
    $pass,
    $db,
    getenv('DOCUMENT_ROOT'),
    $backupdir,
    $db,
    $year,
    $month,
    $day,
    $hour,
    $min
  )); 
  echo '+DONE'; 
?>
[/code]

[ryanlwh]

you can just run the mysqldump command from the shell if you have access. or set a cronjob to run it daily.

[jdeutsch]

Thanks for all the replies and advice.  I actually figured out my problem.  I know I could have tried using the command line, or a cron job, but, this was part test, part experiement for me, to see if I could do it, as I'm still kind of a novice when it comes to PHP.

I don't quite remember how I figured it out, because I started over from scratch, and tried recreating the code from memory, but obviously, I made some change that fixed everything.

Thanks again for the help.