PHP transfer data from one database to another

Use mysqldump Database and mysql Database together, connected by a PHPMyAdmin mysql move table to another database.

Move table from one database to another in MySQL

Contents

how to insert data from one database table to another database table in mysql?
ALTER TABLE .. can be used to copy table from one database to another mysql workbench:

alter table my_old_db.mytable rename my_new_db.mytable

Warning : as you asked, this is a move, not a mysql copy table structure from one database to another
But you will keep all the Database table data temp(and not integrity all the constraints if they apply in your case)

Regarding php, php is able to run sql commands therefor it won’t be a problem (i can be more precise if you wish).

mysql Copy table to another database

mysql copy table to another database

CREATE TABLE firstDataBase.table1 SELECT * FROM SecondDataBase.table1

where firstDataBase is the destination and SecondDataBase is the source

using PHPMyAdmin

If you are using PHPMyAdmin, it could be really easy to use. Suppose you have following simple steps to mysql move table to another database:

Modern software systems are diverse - different data structures and formats are used. But be that as it may, the data in such systems is of great value. Indeed, at any time the system can be replaced with another and it may be necessary to transfer of data from one database to another . A brief overview of this topic will be given below.

Obviously, two different systems have different databases (DB) - their type, table structure, and so on may differ (databases with SQL support are usually used). How, then, to extract the data and transfer it to another database while preserving all the information? It is difficult to do without the help of a specialist, we will briefly consider what he will need to do.

First of all, you need to access the old database and get an idea of ​​its structure. After drawing up the schema of the old database, you need to analyze the schema of the new database - where the data will be imported. Finally, all that remains is to use a convenient programming language (PHP, C ++, etc.) and write code to extract data from one database to another. When the code is ready, you need to carefully test it and you can begin to start the data transfer process between databases.

The structure of the code for transferring data between databases is standard. Typically, a scheme is used in which the code first fetches all the data from the database and puts it into an associative array. The keys of such an array are the names of the columns, and the values ​​of the array, respectively, are the values ​​of the columns.

Since the names of the columns most likely differ between the bases, it will be necessary from the above analysis to compose an array in the code for transforming the data. Namely, such an array will indicate the correspondence between the name of the old column and the name of the column in the new database. The data will remain the same, only the column names will change.

When the array with data from the old database and the array for transformation is ready, a loop is used to traverse the array and insert data from this array into the columns in the new database in accordance with the correspondence of the columns of these two databases.

You need to take into account the fact that the amount of data can be significant and therefore it will take a lot of time for the code to work. Therefore, it is important before starting to properly configure the server for export and import of data between databases data:

  • remove restrictions on the running time of the script;
  • allocate enough RAM;
  • use a powerful processor, etc.

But that's not all, since the speed of data migration will depend on the brevity and speed of code execution. Therefore, the code must perform a minimum of queries to the database, it must be strictly optimized. If the capabilities of the database allow, you should try to make one massive query instead of single queries, which can be divided into transactions if necessary.

Thus, transferring data from one database to another is a feasible task that should be entrusted to a specialist. If you have long wanted change your system to another (its type is not important - web, desktop software, etc.), but you hesitate because of data transfer - don't worry, you can install a new system and perform a lossless data transfer.

Hi i have a php script that retrieves all data from one database to be transferred to another database. The problem is that the database I am retrieving has 2,778 total rows and after i run my script the new database has 2,658 total rows. Whats going on?

Here is the php code.

<?php
error_reporting(E_ALL);
/*
First of all, connect to the kmedianet_main table and retrieve all files to be transferred
Uses the PEAR DB class,
*/
require("./DB.php");

$dbun = "example"; // database username
$dbpas = "example"; // database password
$dbname = "example"; // database name
$dsn = "mysql://$dbun:[email protected]/$dbname";
$conn =& DB::connect($dsn);
if (DB::isError ($conn))
die ("Cannot connect: " . $conn->getDebugInfo () . "\n");
// echo "connected: <Br>";
// echo "got cats: <Br>";
/*
fetch all data from the main table and prepare it for entry into mp3s
*/
$raw_data = array();
$q = 'SELECT users.id, users.username, users.password, users.email, users.ip, users.reg_date, users_profile.fname, users_profile.lname, users_profile.gender, users_profile.birthday, users_profile.country, users_profile.age FROM `users`,`users_profile` WHERE users.id = users_profile.mid';
$r = $conn->query($q);
if(DB::isError($r)) // kill on errors to ensure corrupt data is prevented from entering database
{
die("Error loading from main table: " . $r->getDebugInfo());
}

$x = 0; // initiate incrementer
while($row=$r->fetchRow())
{
// data straight from database
$raw_data[$x] = $row[0];
$raw_data[$x] = $row[1];
$raw_data[$x] = $row[2];
$raw_data[$x] = $row[3];
$raw_data[$x] = $row[4];
$raw_data[$x] = $row[5];
$raw_data[$x] = $row[6];
$raw_data[$x] = $row[7];
$raw_data[$x] = $row[8];
$raw_data[$x] = $row[9] . " 00:00:00";
$raw_data[$x] = $row[10];
$raw_data[$x] = $row[11];

//$raw_data[$x] = strtotime
$join = strtotime($raw_data[$x]);
$raw_data[$x] = date("Y-m-d 00:00:00",$join);
$x++; // increase incrementor so that data is not overwritten
}
//echo "<pre>";
//print_r($raw_data);
//echo "</pre>";
// echo "sorted data: <Br>";
/*
connection with the _main database is over. Now connect to the _music database
*/
$dbun = "example"; // database username
$dbpas = "example"; // database password
$dbname = "example"; // database name
$dsn = "mysql://$dbun:[email protected]/$dbname";
$conn =& DB::connect ($dsn);
if (DB::isError ($conn))
die ("Cannot connect to kmediacom_music: " . $conn->getDebugInfo () . "\n");
// echo "connected to 2nd database: <Br>";

/*
Now add each row of data to the new database
*/
foreach($raw_data as $file)
{
$q = ""; // reset the query string for each iteration
$q = "INSERT IGNORE INTO `users` (user_id, user_name, password, email, first_name, last_name, sex, age, country, usr_status, doj, dob) VALUES ";
$q .= "(";
$q .= "'".$file."',";
$q .= "'".$file."',";
$q .= "'".$file."',";
$q .= "'".$file."',";
$q .= "'".$file."',";
$q .= "'".$file."',";
$q .= "'".$file."',";
$q .= "'".$file."',";
$q .= "'".$file."',";
$q .= "'Ok',";
$q .= "'".$file."',";
$q .= "'".$file."'";
$q .= ")"; // construct the INSERT query
$r = $conn->query($q);
//if(DB::isError($r))
//  echo "Error inserting into new database on record" . $file . " : ". $r->getDebugInfo() . "<Br> $q";
}

// echo "inserted data into new database: <Br>";
?>

php

0 0

Share

Edited 9 Years Ago by happygeek because: fixed formatting

  • 3 Contributors
  • 3 Replies
  • 934 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by welbyobeng

Answered by nikesh.yadav 4 in a

please also check, is it not problem of time out

All 3 Replies

andyhoneycutt 0 Newbie Poster

14 Years Ago

Because your script is inserting some rows without failing, I would suggest taking a look at your destination table and ensure that the columns are of appropriate length and type to handle the data you are trying to give it. For instance, you may be trying to stuff a varchar(50) into a varchar(40) which would result in the problems you're seeing.

-Andy

0 0

Share

nikesh.yadav 4 Posting Whiz in Training

14 Years Ago

please also check, is it not problem of time out

0 0

Share

welbyobeng 3 Junior Poster in Training

14 Years Ago

Is not the time out becuase my php time out has no limit. Also if For instance, if I may be trying to stuff a varchar(50) into a varchar(40) which would result in the problems am not seeing then why has it copy other rows leaving some

How to fetch data from one table to another table in PHP?

Follow the below Steps:.
Open XAMPP server and start Apache and MySQL..
Open your browser and type “localhost/phpmyadmin”. Create a database named “geeks_database”.
Now create a table named table1 with 4 columns and click on save..
Now open the SQL column in the database server and insert records into it..

Can PHP communicate with database?

PHP 5 and later can work with a MySQL database using: MySQLi extension (the "i" stands for improved) PDO (PHP Data Objects)

How to copy table from one database to another in MySQL using PHP?

The fastest way to copy a table in MySQL:.
Right-click the table you want to copy in Database Explorer and select Duplicate Object..
In the dialog that opens, select the destination db..
Select to copy the table data or structure only..
Specify the name of the new table, and click OK..