27866

PHP Mysql copy a record into a different database

Question:

I have two separate remote databases, the table in both databases is identicle and I want to copy a record from the old database to the new using PHP

While this <a href="https://stackoverflow.com/questions/9932232/the-best-way-to-copy-new-records-from-one-database-to-another" rel="nofollow">the best way to copy new records from one database to another</a> gives the solution as

mysqli_query($db1,"SELECT field1,field2,field3 FROM table1"); mysqli_query($db2,"INSERT INTO table1 (field1,field2,field3)");

Because of the number of fields involved I want to try and avoid naming all the fields

I was thinking of something like this...

$m = mysqli_query($db1,"SELECT * FROM table1");

****THIS IS WHERE I'M STUCK****

HOW DO I GET TO THIS FROM THE ABOVE STATEMENT?

$values = "'".implode("','",array_values($m))."'"; $columns = implode(",",array_keys($m));

So I can do this

mysqli_query($db2,"insert into table1 ($columns) values ($values)")

I'm aware I will need to change the PRIMARY KEY id to null.

Answer1:

To fetch the actual column names you could do like this:

$sql="SELECT column_name FROM information_schema.columns WHERE table_schema = 'database_name' AND table_name = table1";

Fetch the above into an array.

$result = $mysqli->query($query); $cols = $result->fetch_array(MYSQLI_NUM);

and create comma-separated list into $columns-variable

$columns = implode(",", $cols);

For the values, just do a regular select-statement:

$sql="SELECT * FROM table1"; $result = $mysqli->query($query); $vals = $result->fetch_array(MYSQLI_NUM);

Answer2:

in the end it was quite simple to modify my own code and create the array using mysqli_fetch_assoc

$m = mysqli_fetch_assoc(mysqli_query($db1,"SELECT * FROM table1"));

and then to make the id null

$m["id"] = 'replacethis'; $values = str_replace("'replacethis'","null","'".implode("','",array_values($m))."'"); $columns = implode(",",array_keys($m));

then finally...

mysqli_query($db2,"insert into table1 ($columns) values ($values)");

Recommend

  • jqGrid - multiple validation of fields
  • Extract specific fields from text file
  • Read certain key from certain section of ini file (sed/awk ?)
  • Grails Packaging and Naming Conventions
  • AWS SES Production Access setup with Meteor
  • Singular Value Decomposition Implementation
  • Easier way to do non-recursive list on gcs bucket outside appengine
  • Unknown C# type
  • What is the first step to using a REST API in Rails?
  • how to automatically enter password when using ssh?
  • Insert space after period using sed
  • Importing Excel files with a large number of columns header into mysql with c#
  • phpmailer - How to verify a sent email arrived at its destination
  • How to name a 'group' check box in Adobe Reader when wanting to fill form by FDF / XFDF
  • CRASH: *** -[__NSArrayM objectAtIndex:]: index 4294967295 beyond bounds [0 .. 9]
  • R sqldf renaming a field in a select statement
  • Python PIL to extract number from image
  • .NET video play library which allows to change the playback rate?
  • How to 'create temp table as select' in Slick?
  • uniform generation of points on 3D box
  • MySQL Order by column = x, column asc?
  • how to avoid repetitive constructor in children
  • SharedPreferences or SQLite Database?
  • How to get Eclipse Oxygen to run on Java 9
  • Q promise. Difference between .when and .then
  • Control modification in presentation layer
  • Is there a javascript serializer for JSON.Net?
  • How can I send an e-mail from a vbs script
  • Sails.js/waterline: Executing waterline queries in toJSON function of a model?
  • PHP - How to update data to MySQL when click a radio button
  • Can Jackson SerializationFeature be overridden per field or class?
  • Join two tables and save into third-sql
  • Where to put my custom functions in Wordpress?
  • How to handle AllServersUnavailable Exception
  • Weird JavaScript statement, what does it mean?
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Buffer size for converting unsigned long to string
  • how does django model after text[] in postgresql [duplicate]
  • Binding checkboxes to object values in AngularJs
  • jQuery Masonry / Isotope and fluid images: Momentary overlap on window resize