6

I need to do a dump of a table on a remote server, but I can't access the server directly. The only access I have is through PHP scripts.

Is there some way in which MySQL will return an 

INSERT INTO `table_name` (`field1`, `field2`) VALUES ('a', 'b'), ('c', 'd')

statement, like what mysqldump will return?

I don't have access to phpMyAdmin, and I preferably don't want to use exec, system or passthru.

See this question for another export method

Community
  • 1
  • 1
Jrgns
  • 22,631
  • 17
  • 67
  • 75

6 Answers6

5

1) can you run mysqldump from exec or passthru
2) take a look at this: http://www.php-mysql-tutorial.com/perform-mysql-backup-php.php

UnkwnTech
  • 79,308
  • 64
  • 178
  • 223
0

If you can use php-scripts on the server i would recommend phpmyadmin. Then you can do this from the web-interface.

Espo
  • 39,502
  • 20
  • 127
  • 156
0

You should check out PHPMyAdmin, it is a php based MySQL administration tool. It supports backups and recovery for the database as well as a 'GUI' to the database server. It works very well.

Bryan Rehbein
  • 9,445
  • 3
  • 35
  • 41
0

I'm pretty sure phpMyAdmin will do this for you.

David Thibault
  • 8,120
  • 3
  • 34
  • 50
0

This

select 'insert into table table_name (field1, field2) values'
       || table_name.field1 || ', ' || table_field2 || ');'
from   table_name

should get you started. Replace || with the string concatenation operator for your db flavour. If field1 or field2 are strings you will have to come up with some trick for quoting/escaping.

agnul
  • 11,222
  • 14
  • 60
  • 83
0

Here is one approach generating a lot of separate query statements. You can also use implode to more efficiently combine the strings, but this is easier to read for starters and derived from this you can come up with a million other approaches.

$results = mysql_query("SELECT * FROM `table_name`");
while($row = mysql_fetch_assoc($results)) {

    $query = "INSERT INTO `table_name` ";
    $fields = '(';
    $values = '('; 

    foreach($row as $field=>$value) {
        $fields .= "'".$field."',";
        $values .= "'".mysql_escape_string($value)."',";
    }

    //drop the last comma off
    $fields = substr($fields,0,-1);
    $values = substr($values,0,-1);

    $query .= $fields . " VALUES " . $values;

    //your final result
    echo $query;
}

See if that gets you started

B. Desai
  • 16,092
  • 5
  • 22
  • 43
  • My problem with this solution is that it doesn't return an accurate INSERT statement. It will, for example, enclose integers in single quotes. – Jrgns Sep 15 '08 at 14:43
  • Is it returning integers in single quotes or with accent graves (`)? – Brian Warshaw Sep 15 '08 at 17:32
  • This line: $values .= "'".mysql_escape_string($value)."',"; encloses ALL values in single quotes ('). Generally it isn't a problem, but it isn't as accurate as PHP's putcsv function would do it. – Jrgns Oct 13 '08 at 09:08