1

I'm using PHP to copy table for backup.

Code is simple. 

///////////////////////////////////////////////////////////////
                //  BACKUP TABLE
///////////////////////////////////////////////////////////////
mysqli_query($conn, "DROP TABLE SerialTable3");
mysqli_query($conn, "CREATE TABLE SerialTable3 LIKE SerialTable2");
mysqli_query($conn, "INSERT INTO SerialTable3 (SELECT * FROM SerialTable2)");

mysqli_query($conn, "DROP TABLE SerialTable2");
mysqli_query($conn, "CREATE TABLE SerialTable2 LIKE SerialTable1");
mysqli_query($conn, "INSERT INTO SerialTable2 (SELECT * FROM SerialTable1)");

mysqli_query($conn, "DROP TABLE SerialTable1");
mysqli_query($conn, "CREATE TABLE SerialTable1 LIKE SerialTable");
mysqli_query($conn, "INSERT INTO SerialTable1 (SELECT * FROM SerialTable)");
///////////////////////////////////////////////////////////////

Drop table works as I confirm. Create Table also works.

But Insert SQL doesn't work... I saw a lot of other people doing this to copy table. But in my case, never works...

I know it's all same but I tried

mysqli_query($conn, "INSERT INTO SerialTable1 SELECT * FROM SerialTable");
mysqli_query($conn, "INSERT INTO SerialTable1 AS SELECT * FROM SerialTable");
mysqli_query($conn, "INSERT INTO SerialTable1 AS (SELECT * FROM SerialTable)");

What's wrong? As dropping and creating works, connection has no problem too.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Myeong-jae Kim
  • 133
  • 3
  • 11
  • this should work : http://stackoverflow.com/a/2415879/724913 – arkoak Apr 20 '15 at 06:49
  • May be the column names are missing ... you tried added columns of the table in your insert query before fetching the values from the other table? – Preeti Maurya Apr 20 '15 at 06:50
  • How do I confirm error message? I'm working on hosting server. And remote access from other IP is not allowed in my hosting server. So I can't confirm error message from MySQL – Myeong-jae Kim Apr 20 '15 at 06:51
  • Oh.. I forgot to add other tries. Yes. I tried to add columns as well... still doesn't work – Myeong-jae Kim Apr 20 '15 at 06:52
  • http://stackoverflow.com/questions/2415855/run-mysql-create-table-by-select-another-and-copied-the-indexes-automatically look here this is the same Problem i think, and it's solved – LFS96 Apr 20 '15 at 06:55
  • Try use [`mysqli_error`](http://php.net/manual/en/mysqli.error.php) to retrieve the last mysql error, to see what's going wrong. – ivan.sim Apr 20 '15 at 06:56
  • I just confirmed that mysqli_error() returns nothing. I have a guess.. Isn't this perhaps because insert SQL and Create SQL are running in 2 different thread? And maybe insert SQL will work before create SQL work? Is my guess possible? – Myeong-jae Kim Apr 20 '15 at 07:01
  • I just found that it could be the problem caused by wordpress. I'm working on wordpress website adding new custom theme. Maybe mysqli_query is connected to some callback function as wordpress function. I'm really noob of wordpress. Any help? – Myeong-jae Kim Apr 20 '15 at 07:29
  • [Solved!]It was about wordpress issue. all of my mysqli_query was excuting twice so all of my logic was messed up. Everytime when I use mysqli_query, $wpdb object automatically excute my query once more. It was my problem. And I just changed all of my code using mysqli_query to $wpdb->query() – Myeong-jae Kim Apr 20 '15 at 08:57

0 Answers0