How to transfer data from one database to another database

PHP
1

hii FRIENDSsssss…
When we upload CSV the data must transfer one database to another database using PHP ?
Can anyone tell plzzzzzzzzzzzzzzz…

2

Sorry but your question is not very clear. You say when you upload a csv file you want data to be transferred from one database to another. Do you mean you want to read a csv file and insert the information from the csv file into a database?

What database(s) are you using? What PHP code have you got so far? Have you got database schema?

3

when i load the csv file, the data must store db1 to db2…
Data must transfer from db1 to db2…

4

If you’re using MySQL or any relational database for that matter you would just loop through the data in the csv file and insert it into tables in the database.

5

I’m using this code, its correct or wrong…

<?php

$connect2 = mysql_connect("localhost", "root", "1234");
$database1 = "bulksms1"; // destination database
mysql_select_db($database1, $connect2);
set_time_limit(0);

$database = 'sms'; //original database
$connect = mysql_connect("localhost", "root", "1234");

mysql_select_db($database, $connect);

$tables = mysql_query("SHOW TABLES FROM $database");

while ($line = mysql_fetch_row($tables)) {
    $tab = $line[0];
    mysql_query("DROP TABLE IF EXISTS $database1.$tab");
    mysql_query("CREATE TABLE $database1.$tab LIKE $database.$tab") or die(mysql_error());

    mysql_query("INSERT INTO bulksms1.csvv SELECT * FROM sms.csv");
//    echo "Table: <b>" . $line[0] . " </b>Done<br>";
}
?>
6

DROP table removes the table completely.

And CREATE TABLE syntax is not the same as INSERT syntax.

You might be able to get by using TRUNCATE TABLE followed by INSERT
https://dev.mysql.com/doc/refman/5.6/en/truncate-table.html

Or maybe using SHOW TABLE CREATE results

But this approach seems very risky, inefficient, and time consuming to me.

Can’t you do an export of one to use as an import for the other?

https://dev.mysql.com/doc/refman/5.6/en/mysqldump-copying-database.html

7
  1. Log in to the phpMyAdmin instance on the source server.
  2. Click the Export tab.
  3. From the database drop-down, select the wordpress database.
  4. Choose between a Quick or a Custom export. The Quick method should work most of the time, though if you need to get more granular with your export options,
    a. Choose which tables to export.
    b. Choose the template, character set, and compression for the export.
    c. Choose to display comments, enclose the export in a transaction, disable foreign key checks, dump table.
  5. Object creation options: Add statements.
  6. Data dump options: Use INSERT DELAYED or INSERT IGNORE statements, select the function to use when dumping data, select the syntax to use when inserting data, maximal length of created query.
  7. Select the format from the Format drop-down.
  8. Click the Go button.
8

Try the code below -

   ?php
    $data = mysql_query("SELECT x,y,z FROM db1.table WHERE where country='usa'");
    $values = Array();
    while ($row = mysql_fetch_assoc($data)) {
        // do some work...
        $row['x'] = mysql_real_escape_string($row['x']);
        $row['y'] = mysql_real_escape_string($row['y']);
        $row['z'] = mysql_real_escape_string($row['z']);
        $values[] = "('$row[x]','$row[y]','$row[z]')";
    }
    mysql_query("insert into db2.table (x,y,z) VALUES ".implode(',',$values)."");
    ?
9

The mysql_* extension has been removed from PHP version 7, you should now be using either the mysqli_* extension or PDO.

Do both databases have the same structure for all tables?

What about creating two database connections (1 to each database) and having the insert queries run twice, once against each database?

10

I’m using Mysql

11

Thank u Frnd
I’ll try

12

On mysql
:- Using select… into outfile and load data

In the server where dbSource is:

select … from dbSource.tblSource into outfile [your destination file] …

Copy the file to the destination server.

In the server where dbDestination is:

load data local infile [your file] …

but always keep in mind :- The fields in the select statement must be in the same order as the fields specified in the field list in the insert portion
Check MySQL reference manual for the appropriate usage of select… into outfile and load data…

13

Thank u :blush:Surveen

14

wel come shammu…

1 Like
15

i think u too busy…

16

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.