MySQL backup code Undefined Variable: return

Question

I'm trying to do a MySQL* backup code that i call when i pressed a button, but the problem is that when I'm about to close the file that i use to write the database occurs this following error.

Notice: Undefined variable: return in C:\

This is my code:

     <? php

    backup_tables('localhost', 'root', '123456', 'students');


/* backup the db OR just a table */
function backup_tables($host,$user,$pass,$name,$tables = '*')
{

$link = mysql_connect($host,$user,$pass);
mysql_select_db($name,$link);

//get all of the tables
if($tables == '*')
{
    $tables = array();
    $result = mysql_query('SHOW TABLES');
    while($row = mysql_fetch_row($result))
    {
        $tables[] = $row[0];
    }
}
else
{
    $tables = is_array($tables) ? $tables : explode(',',$tables);
}
$return = ""; 
//cycle through
foreach($tables as $table)
{
    $result = mysql_query('SELECT * FROM '.$table);
    $num_fields = mysql_num_fields($result);

    $return.= 'DROP TABLE '.$table.';';
    $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
    $return.= "\n\n".$row2[1].";\n\n";

    for ($i = 0; $i < $num_fields; $i++) 
    {
        while($row = mysql_fetch_row($result))
        {
            $return.= 'INSERT INTO '.$table.' VALUES(';
            for($j=0; $j<$num_fields; $j++) 
            {
                $row[$j] = addslashes($row[$j]);
                $row[$j] = ereg_replace("\n","\\n",$row[$j]);
                if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
                if ($j<($num_fields-1)) { $return.= ','; }
            }
            $return.= ");\n";
        }
    }
    $return.="\n\n\n";
}

//save file
$handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
fwrite($handle,$return);
fclose($handle);
}

    ?>

If any of you guys could help me to understand the problem or help me out with another solution i would appreciate very much.

Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and consider using PDO, [it's not as hard as you think](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, May 18 '15 at 20:04
You never actually define the variable `$return` before trying to concatenate with it, `$return. = 'DROP TABLE '.$table...`. Just add `$result = '';` to the beginning of your function. — Jonnix, May 18 '15 at 20:08

spencer7593 · Answer 1 · 2015-05-18T20:56:54.757

1

Your first reference to $return is attempting to append to it. Initialize it first. Do this, at the top of the function.

$return = "";

The mysql interface is deprecated. Use mysqli or PDO instead.

Are you aware that MySQL provides a utility named mysqldump ?

As to the design, this is going to attempt to create the entire script in memory, before it writes it to a file. That will work for small number of tables and a small number of rows. But for really big tables, that's going to be expensive in terms of memory. You might want to consider opening the file and then writing pieces of the backup to the file, rather than trying to build the entire thing in memory.

FOLLOWUP

You've got a serious problem here:

for ($i = 0; $i < $num_fields; $i++) {
  while ($row = mysql_fetch_row($result)) {

    for ($j = 0; $j < $num_fields; $j++) {

The for $i loop doesn't make sense outside of the fetch loop. You want to loop through each row returned, and on each row, you want to loop through the columns on the row.

edited May 18 '15 at 20:56

answered May 18 '15 at 20:11

spencer7593

106,611
15
112
140

And using mysqldump i would solve the memory problem too? i'am already initialized the variable, but know my back.sql is 0KB, so the problem must be in the foreach section, right? – João Joaquim May 18 '15 at 20:40
The MySQL provided `mysqldump` utility produces a file much like the one you appear to be attempting to produce, with a lot more options, and does it much more efficiently than your code will. If this is a learning exercise, then *why on earth* are we using the deprecated mysql interface? I mean, *what the plastic*? – spencer7593 May 18 '15 at 20:59
hmm. i use this code from one of the topics i found here and i thought i would work, but appears is not. So i will search about mysql dump. Thank you. – João Joaquim May 18 '15 at 21:05
You can get code to work, that's not really a problem. But why would you write it using **`mysql_`** functions, rather than using the recommended **`PDO`** or **`mysqli`** interfaces. To be more bulletproof, your code would really need to take into account that an identifier (e.g. a table name) can contain "special" characters, like a space, and your generated `DROP TABLE` and `CREATE TABLE` statements aren't going to recreate that table. You'd need to account for that. – spencer7593 May 18 '15 at 21:12
It's pretty easy to search for information about `mysqldump`. It's documented in the MySQL Reference Manual, [https://dev.mysql.com/doc/refman/5.5/en/mysqldump.html](https://dev.mysql.com/doc/refman/5.5/en/mysqldump.html). – spencer7593 May 18 '15 at 21:13

TimoStaudinger · Answer 2 · 2015-05-18T20:24:06.720

0

You are trying to append a string to the variable $return, which has not been declared at this point:

$return. = 'DROP TABLE '.$table.

Simply declare it before the loop starts:

$return = "";   

foreach($tables as $table) {
    \\ ...     

    $return. = 'DROP TABLE '.$table.;

    \\ ...     
}

edited May 18 '15 at 20:24

answered May 18 '15 at 20:09

TimoStaudinger

41,396
16
88
94

i did what you said and the error is not happening anymore, but the problem is that my back.sql is 0KB now. So i think that jus the $return = ""; is being saved in the file. The problem now would be inside the foreach? – João Joaquim May 18 '15 at 20:34
Could you please add your new code to your original question? Or at least show us where exactly you declared `$return`? – TimoStaudinger May 18 '15 at 20:49
i did inside the function but out of the foreach, cause is the only place the erro not appear. – João Joaquim May 18 '15 at 21:00

MySQL backup code Undefined Variable: return

2 Answers2