So I exported a part of data from a table using the command:
<?php
$con = mysqli_connect('localhost','root','','database');
$sql = "SELECT * INTO OUTFILE 'data.sql' FROM table WHERE PID<50";
mysqli_query($con, $sql);
mysqli_close($con);
?>
It exported successfully, I got a 'data.sql' file in my directory and the content is exactly what I chose(data with PID from 1 to 49). However whenever I tried to import it back using phpmyadmin into a new table with same name, same columns, in a different database, it keeps showing error
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
Full Error message:
SQL query:
2 Austin Benedict Trisanto 002 240000 2015-07-01 1970-01-01 \N 20000 4
3 Faylisha Syema 001 120000 2015-07-01 \N \N 0 2
4 Tiara Aurellia Prasetyo 003 230000 2015-07-01 1970-01-01 \N 0 2
5 Darrell Maximilian Dirgantara 004 210000 2015-07-01 1970-01-01 \N 0 2
6 mikaela reva calista 005 210000 2015-07-01 1970-01-01 \N 0 2
7 Gisella Joycelin Tian Sibarani 006 220000 2015-07-01 1970-01-01 \N 15000 3
8 Putu Pooja Sita Laticia 007 230000 2015-07-01 1970-01-01 \N 0 2
9 Mikhael Lucius Arkananta Prama 008 230000 2015-07-01 1970-01-01 \N 0 2
MySQL said: Documentation
3 Faylish' at line 1
What exactly could be going wrong ? or what I am doing wrong ? should I import it back using php too ?
All answers are greatly welcomed. Thank you in advance.
What you have for import isn't in SQL format.
Additionally, I've never had much luck running it through PHP and phpMyAdmin.
I am only answering this in this fashion, because it is my preferred method of backing up, and restoring databases.
NOTE You have to have shell access for this method.
To back up the database. Use the following:
$ mysqldump -u UserName -p PassWord your_database > /directory/backup_file.sql
Or You can use mysql cli to output a query:
$ mysql -e "select * from yourTable" -u userName -p passWord YourDataBase > /directory/backup_file.sql
To import your file to your new db:
$ mysql -u userName -p passWord newDatabase < /directory/backup_file.sql
With the import, you won't have to specify the table, as that will be stored in the sql file.
Might not be what you are hoping for, but this is a valid alternative to using PHP to do this. And in fact it's the preferred method as it is quick, easy, reliable and effective. There is no code except for your SQL query that is needed. Less code = Less chance of breaking.