Export/Import some data from database with PHP

Question

I want to export and import from my database, including a WHERE clause, to export some data and not all (For example: Exporting all data tables where numberID = 25). So, It's possible to do that? And, If it is possible, how I could import those data? Removing first previous data?

I was trying to export them from my database, but it doesn't work: What I did wrong? I'm working with XAMPP. Thanks for all!

<?php

//ENTER THE RELEVANT INFO BELOW
$mysqlDatabaseName ='myDatabase';
$mysqlUserName ='myUser';
$mysqlPassword ='myPass';
$mysqlExportPath =$_SERVER['DOCUMENT_ROOT'] . '/backup.sql';
$mysqlHostName ='localhost';

$command='mysqldump --opt -h' .$mysqlHostName .' -u' .$mysqlUserName .' -p' .$mysqlPassword .' ' .$mysqlDatabaseName .' > ' .$mysqlExportPath;
exec($command);

?>

So, where is the SQL statement you're trying to get to work? — ChrisW, Sep 20 '13 at 09:59
open up `phpmyadmin` make a `SELECT` for whatever rows you want and tick "check all" on the bottom there is a "export" click and there you go. — Kyslik, Sep 20 '13 at 10:04
With 'mysqldump' is no sufficient? where must be my sql statement? thanks ChrisW — Jaco, Sep 20 '13 at 10:05
Thanks Kyslik, but i want to use php because the users can't access to phpmyadmin — Jaco, Sep 20 '13 at 10:08
Ah, I didn't realise you had so little understanding of what you want to do. You really need to do some googling on MySQL / PHP - and **please make sure** you use PDO functions, not `mysql_*` ones (try a tutorial such as http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers) — ChrisW, Sep 20 '13 at 10:11
Thanks ChrisW, but all my project have procedural programming... — Jaco, Sep 20 '13 at 10:26

score 2 · Accepted Answer · answered Sep 20 '13 at 10:02

2

Try not to use mysqldump (it is more like an administration/backup tool and to my knowledge can only export whole tables), but try a SELECT statement with a WHERE clause for reading your data. Export it in the file format you would like to, e.g. XML oder CSV. For importing the data again, use a DELETE statement for deleting the present data (also applying the WHERE clause) and use INSERT statements for each record in the input file.

answered Sep 20 '13 at 10:02

Matthias Wuttke

1,982
2
21
38

Thanks Matthias Wuttke, I'll try it! – Jaco Sep 20 '13 at 10:13
Can you tell me an example to how export my query in .xml or .sql? thanks! – Jaco Sep 20 '13 at 10:15
@JuanAntonio - why do you think you need to export your query? – ChrisW Sep 20 '13 at 10:25
I understood that i need to export my result data from my query and to import, read the archive. Isn't it? – Jaco Sep 20 '13 at 10:37
No, not at all. See my answer. – ChrisW Sep 20 '13 at 10:40
Have a look here: http://stackoverflow.com/questions/4249432/export-to-csv-via-php - use this code to export the database table content to a CSV file; then there is plenty of code on how to import the CSV file again. – Matthias Wuttke Sep 20 '13 at 10:59

score 1 · Answer 2 · answered Sep 20 '13 at 10:39

If you have a table such as

ID | COL1 | COL 2
-----------------
 1 |  A1  |  A2
 2 |  B1  |  B2

Use the following PHP code to display the whole table

<?php
$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');

foreach($db->query('SELECT * FROM table') as $row) {
    echo $row['field1'].' '.$row['field2']; //etc...
}
?>

This code is take ad verbatim from the link I provided earlier (I am being particularly kind - the vast majority of people wouldn't give you a link and then when you clearly hadn't read it still give you the code...).

The query can be extended to something such as 'SELECT * FROM table WHERE ID=2'. Of course, you can still put any kind of HTML in the PHP forloop to display the data anyway you want

Mina · Answer 3 · 2013-09-20T10:19:36.007

As already stated I wouldn't use mysqldump but a select query. Having said that you can add a --where statement to mysqldump as described on the MySQL Documentation

mysqldump -t -u [username] -p[password] [database] [table] --where=[statement]

Also, you are trying to save the .sql file in your document root, you need to check the permissions. However having the document root writable by the Apache user is not a good idea.

Export/Import some data from database with PHP

3 Answers3