LinuxQuestions.org
Share your knowledge at the LQ Wiki.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices


Reply
  Search this Thread
Old 03-07-2018, 06:40 AM   #1
manolakis
Member
 
Registered: Nov 2006
Distribution: xubuntu
Posts: 451

Rep: Reputation: 31
PHP script to copy database


Hello,

I am trying to write some php script to copy one table from one mysql database to another. I use mysqli to connect to the databases but nothing works. Can anyone please show me how to do that?
I first want to select all the records from one database and then use insert statements to put the data into the other database.
The table structure of the databases are the same.
Any advice will be really helpful. Just to note that I only want to use php for that purpose.

Thank you.
 
Old 03-07-2018, 07:31 AM   #2
TB0ne
LQ Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 20,086

Rep: Reputation: 4809Reputation: 4809Reputation: 4809Reputation: 4809Reputation: 4809Reputation: 4809Reputation: 4809Reputation: 4809Reputation: 4809Reputation: 4809Reputation: 4809
Quote:
Originally Posted by manolakis View Post
Hello,
I am trying to write some php script to copy one table from one mysql database to another. I use mysqli to connect to the databases but nothing works. Can anyone please show me how to do that? I first want to select all the records from one database and then use insert statements to put the data into the other database.The table structure of the databases are the same. Any advice will be really helpful. Just to note that I only want to use php for that purpose.
The best advice here would be for you to read the "Question Guidelines" link in my posting signature. We're happy to help you, but you're asking us to write your entire script, and (essentially) give you a programming class to do what you're after. No, we will not do that.

When you post what you have done/tried on your own, and tell us where you're stuck, we'll be happy to help. There are THOUSANDS of samples and examples of how to connect PHP to MySQL you could find with a brief Google search. Start there.
 
Old 03-08-2018, 06:56 AM   #3
manolakis
Member
 
Registered: Nov 2006
Distribution: xubuntu
Posts: 451

Original Poster
Rep: Reputation: 31
MySQL INSERT ERROR

Code:
Table locations{
Geometry g 
double x PRIMARY KEY
double y PRIMARY KEY
double DU
date date_
}
I have a table like the one listed and I want to insert to that table some data. The problem is that my insert statement fails.
I use the following statement but the statement fails. Does anybody know what is going wrong?
Code:
INSERT INTO 'locations' (location,latitude,longitude,DU,date_) VALUES (NULL,'23.43,'-64.06,'268,'2018-3-8') ON DUPLICATE KEY UPDATE location = NULL,latitude='23.43',longitude='-64.06',DU='268',date_='2018-3-8'
 
Old 03-08-2018, 07:03 AM   #4
keefaz
LQ Guru
 
Registered: Mar 2004
Distribution: Slackware
Posts: 6,154

Rep: Reputation: 698Reputation: 698Reputation: 698Reputation: 698Reputation: 698Reputation: 698
At first glance, table fields don't match with statement, VALUES has misplaced quotes...
 
Old 03-08-2018, 09:18 AM   #5
dugan
LQ Guru
 
Registered: Nov 2003
Location: Canada
Distribution: Slackware
Posts: 8,148

Rep: Reputation: 3273Reputation: 3273Reputation: 3273Reputation: 3273Reputation: 3273Reputation: 3273Reputation: 3273Reputation: 3273Reputation: 3273Reputation: 3273Reputation: 3273
You don't need a script. Use mysqldump to dump the table, and then pipe the results into mysql for the other database.
 
Old 03-08-2018, 12:48 PM   #6
astrogeek
Moderator
 
Registered: Oct 2008
Distribution: Slackware [64]-X.{0|1|2|37|-current} ::12<=X<=14, FreeBSD_10{.0|.1|.2}
Posts: 4,664
Blog Entries: 6

Rep: Reputation: 2525Reputation: 2525Reputation: 2525Reputation: 2525Reputation: 2525Reputation: 2525Reputation: 2525Reputation: 2525Reputation: 2525Reputation: 2525Reputation: 2525
You have fallen into a pattern of posting questions which do not include necessary details and which say literally or effectively, "Despite my best efforts, it doesn't work", then asking for othrers to provide a complete code solution. This is not acceptable behavior at LQ.

Please review the Site FAQ for guidance in posting your questions and general forum participation.

You say that your insert statement fails, but you did not provide the error message which would tell us, and you, why it is failing. As noted by others there are obvious quoting and column type errors, but the error message should tell you that.

If you must do this with PHP (mysqldump may fit your description better), and the table structure is the same, then the overall task is fairly straight forward: Select from source, insert to destination. One solution might be like this PHP pseudo-code:

Code:
$source_conn = mysql_connect(... source DB params ...)
$source_result_set = mysql_query('SELECT * FROM Table WHERE Params', $source_conn)

$dest_conn = mysql_connect(... destination DB params ...)

for each ($row = mysql_fetch_assoc($source_result_set)){
         mysql_query('INSERT INTO Table field1="$row[field1]", field2="$row[field2]", ...', $dest_conn)
}
Adapt that to your case and see if you can get it working. If you cannot get it to work after reading any error messages, then post your actual code and queries, along with the error messages they produce, and someone here will try to help.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Cannot get my php script to UPDATE mysql database countrydj Linux - Software 2 04-11-2011 09:09 PM
Shell script to copy database archivelogs sequentially from one directory to another rajeshkumar.dba Programming 3 06-02-2009 08:12 AM
Cannot connect to mysql database using php script shan_nathan Programming 4 10-17-2008 11:58 PM
PHP Script for Database retrieval?? ajeetraina Linux - Server 1 03-11-2008 09:49 AM
PHP Script for Importing vCards into a MySQL Database? amocjr Linux - Software 1 01-20-2007 03:54 PM

LinuxQuestions.org > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 11:04 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration