PHP class help?

[letsforum]

I am trying to backup SQL and I an not sure that i need to include Drop Table and Create Table in the SQL???

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] = str_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";
    }

[Elizine]

Hi,

You don’t need to include Drop Table and Create Table in the SQL. Just fololw a simple syntax if you want to backup the complete table data -

SELECT * Into new_tablename
FROM old_tablename

[felgall]

@Elizine

You don’t need to include Drop Table and Create Table in the SQL

Yes you do. If the database is completely lost or totally corrupted then you need those statements to recreate the table from your backup.

A backup should never be stored on the same computer as the original as then the loss of the original will probably result in the backup being lost as well.

[SpacePhoenix]

Be aware that the old mysql_* extension was deprecated in the current version of PHP (5.5) and is being removed from the next version (7). You should be migrating over to using either the mysqli_* extension or PDO

[TomB]

@SpacePhoenix

Be aware that the old mysql_* extension was deprecated in the current version of PHP (5.5) and is being removed from the next version (7). You should be migrating over to using either the mysqli_* extension or PDO

If anyone’s interested I created a really quick and dirty wrapper for mysql here: https://github.com/TomBZombie/MySqlExtensionReplacment/blob/master/mysql.php it just forwards mysql_* functions to PDO equivalents. Yes, it’s not the best solution but when you have an old project with tens of thousands of lines of code this method is far quicker!

[letsforum]

@letsforum

mysql

Many say that including Drop Table can cause errors during import of the Sql file and in fact phpMyadmin export function doesn’t add it!

I am gonna just comment it our in the script! What do you think.

[letsforum]

Please check my code and suggest me the alternative to mysql* please???

<?php
$dbhost = '';
$dbuser = '';
$dbpass = '';
$dbname = '';

function backup_tables($host,$user,$pass,$name,$tables = '*')
{

    $link = mysql_connect($host,$user,$pass);
    mysql_select_db($name,$link);
    mysql_query("SET NAMES 'utf8'");

    //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] = str_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);
}

backup_tables($dbhost,$dbuser,$dbpass,$dbname);
?>

[felgall]

@letsforum

in fact phpMyadmin export function doesn’t add it

it normally uses ‘if exists’ on the create but there is an option to add the drop.

I almost always add the drop as I have occasionally had problems without it.

[letsforum]

@letsforum

mysql*

Thanks so much! Do you know what alternative I can use for mysql* in this script?

[Antnee]

It’s funny you did this, Tom. I considered writing one myself but decided I’d rather see code rewritten or die on PHP7