Backup up mysql DB using php builds tables OK but has no data in the tables

Question

I'm trying to backup my mysql database using a php file to save a .gz file onto my server. It's looking very good apart from the file doesn't contain any data..

All of the file produced looks correct apart from this one vital thing!

E.g. here's an example of a line produced by the code

INSERT INTO ticket_sold VALUES("","","","","","","","","","","");

It does this for all tables.

I've based the code on this tutorial so it should work: https://davidwalsh.name/backup-mysql-database-php

Here's my code:

function backup_database_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);
    }

    //cycle through each table and format the data
    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] = 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\n\n";
}

I think it's to do with this area of the code but it's a bit beyond me:

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] = preg_replace("n","\n",$row[$j]);
                if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
                if ($j<($num_fields-1)) { $return.= ','; }
            }

Like I said, the rest of it works great and I'm very excited about the whole thing but just need this last bit to work.

Yes I know I should use PDO, I'll do that once I know this will work.

Yes you should use PDO since that kind of php code is deprecated, But it doesn't mean it's the solution to the problem. What exactly is the problem? — Fil, Jun 23 '16 at 09:34
you are doing a failed multi query. This whole thing is beyond bad. — Drew, Jun 23 '16 at 09:42
The .sql file is created OK, but all of the INSERTs are empty, like this: INSERT INTO ticket_sold VALUES("","","","","","","","","","",""); — GeneralTomfoolery, Jun 23 '16 at 10:06
If you're just trying to back up the database with the current structure then you should be looking along the lines of [this question](http://stackoverflow.com/questions/81934/easy-way-to-export-a-sql-table-without-access-to-the-server-or-phpmyadmin) — d3v_1, Jun 25 '16 at 12:48

score 0 · Answer 1 · answered Jul 06 '16 at 11:34

0

I went with and EXEC statement in the end when my friend showed me how to set up priviledges in phpmysql.

Thank you for you time.

answered Jul 06 '16 at 11:34

GeneralTomfoolery

149
2
14

Backup up mysql DB using php builds tables OK but has no data in the tables

1 Answers1