DB Backup function issues

[sford999]

Hi, I have the following function to back up the MySQL database, but its not doing it correctly

function backup($tables, $backupfolder, $zip)
{
	$db = Database::getDatabase();
	$backupfolder = $_SERVER['DOCUMENT_ROOT'].'/'.$backupfolder;
	$database = _CONFIG_DB_NAME;

	// Get all of the tables
    if (!$tables)
    {
        $tables = array();
        $result = $db->query('SHOW TABLES');
        if ($result)
        {
            while ($row = $db->getRow($result))
            {
                $tables[] = $row[0];
            }
        }
        else
        {
			// Error
        }
    }
    else
    {
        $tables = is_array($tables) ? $tables : explode(',', $tables);
    }
	// If we have tables in the $tables array, process them	
    if ($tables)
    {
		$return = '';
		// Cycle through the array
		foreach($tables as $table)
		{
			$result		= $db->getRow('SELECT * FROM '.$table);
			$num_fields = $db->result->columnCount();
	
			$return .= 'DROP TABLE IF EXISTS '.$table.';';
			$row2    = $db->getRow('SHOW CREATE TABLE '.$table);
			$return .= "\n\n".$row2[1].";\n\n";
			
			for ($i = 0; $i < $num_fields; $i++) 
			{
				foreach($result as $row)
				{
					$return .= 'INSERT INTO `'.$table.'` VALUES(';
					for($j=0; $j<$num_fields; $j++) 
					{
						$row[$j] = addslashes($row[$j]);
						$row[$j] = preg_replace("#\n#", "\\n", $row[$j]);
						if (isset($row[$j])) 
						{ 
							$return .= '\''.$row[$j].'\'' ; 
						} 
						else 
						{ 
							$return .= '""'; 
						}
						if ($j<($num_fields-1)) 
						{ 
							$return .= ', '; 
						}
					}
					$return .= ");\n";
				}
			}
			$return .="\n";
		}
        // Save the backup file
		$handle = fopen($backupfolder.$filename,'w+');
		if(fwrite($handle, $return))
		{
			fclose($handle);
			// Success
		}
		else
		{
			// Error
		}
    }
}

But its not backing up correctly as its putting each character into separate columns.

 

Example:

INSERT INTO `users` VALUES('a', 'd', 'm', 'i', 'n', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ');
INSERT INTO `users` VALUES('a', 'd', 'm', 'i', 'n', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ');
INSERT INTO `users` VALUES('5', 'f', '4', 'd', 'c', 'c', '3', 'b', '5', 'a', 'a', '7', '6', '5', 'd', '6', '1', 'd', '8');
INSERT INTO `users` VALUES('5', 'f', '4', 'd', 'c', 'c', '3', 'b', '5', 'a', 'a', '7', '6', '5', 'd', '6', '1', 'd', '8');
INSERT INTO `users` VALUES('a', 'd', 'm', 'i', 'n', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ');
INSERT INTO `users` VALUES('a', 'd', 'm', 'i', 'n', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ');

Can anyone see what's wrong here?

[sford999]

Anyone?

[mac_gyver]

your ->getRow() method returns one row from the result set each time it is called. you are then looping over that one row in the foreach($result as $row){} loop. $row is not each row, but it is each column value in a row. your for($j=0; $j<$num_fields; $j++){} loop is looping over each character of each column value from the first row in the result set.

 

your code is also looping over the number of columns (the first for(){} loop), then looping over the rows (the foreach(){} loop, assuming it was actually rows your were looping over), then looping over the number of columns again (the second for(){} loop.)

 

once you have your code looping over each row from the result set, you don't need most of the code you have posted. you can use array_map() to operated on each column/field in the $row array and you don't need to get a count of the columns in order to loop over the columns because you don't need to loop over the columns at all. you can simply implode() the data to make the values ( ... ) statement.

Edited July 4, 2013 by mac_gyver

[AbraCadaver]

Ever heard of mysqldump? http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

[sford999]

Ever heard of mysqldump? http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

 

That's ok if the server the site is hosted on has exec() or shell_exec() functions enabled, I'm trying to write the php script so that it does not need those functions.

 

The only part that's tripping me up now is:

foreach($tables as $table)
		{
			$result		= $db->getRow('SELECT * FROM '.$table);
			$num_fields = $db->result->columnCount();
	
			$return .= 'DROP TABLE IF EXISTS '.$table.';';
			$row2    = $db->getRow('SHOW CREATE TABLE '.$table);
			$return .= "\n\n".$row2[1].";\n\n";
			
			for ($i = 0; $i < $num_fields; $i++) 
			{
				foreach($result as $row)
				{
					$return .= 'INSERT INTO `'.$table.'` VALUES(';
					for($j=0; $j<$num_fields; $j++) 
					{
						$row[$j] = addslashes($row[$j]);
						$row[$j] = preg_replace("#\n#", "\\n", $row[$j]);
						if (isset($row[$j])) 
						{ 
							$return .= '\''.$row[$j].'\'' ; 
						} 
						else 
						{ 
							$return .= '""'; 
						}
						if ($j<($num_fields-1)) 
						{ 
							$return .= ', '; 
						}
					}
					$return .= ");\n";
				}
			}
			$return .="\n";
		}

I'm not exactly sure how I write it so that it uses array_map() and implode() as mentioned previously.

[mac_gyver]

I'm not exactly sure how I write it so that it uses array_map() and implode() as mentioned previously.

 

the third paragraph were suggestions on how to simplify the logic. you have to fix the problems mentioned in the first two paragraphs to even get the code to retrieve the data from a table and loop over it.

[sford999]

I've completely re-written the function in a different way and got it working now.

 

Now to write the restore function lol