transfer data from a DB to another

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

bouncer
Forum Contributor
Posts: 162
Joined: Wed Feb 28, 2007 10:31 am

transfer data from a DB to another

Post by bouncer »

hi there

here's my problem, i want to make a php script that get all the data from a table in DB1 and then put that same data in the same table but in a different DB

after this i want to run the same script with a bat file so i can associate this to a task, so i can run this script every 4hours for example

can someone give some tips and examples....i start with php a few weaks ago :)

thks in adv
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

Post by jmut »

I would assume you are using mysql as sql server.
mysqldump command is your friend. Don't think you need a php script for this.
bouncer
Forum Contributor
Posts: 162
Joined: Wed Feb 28, 2007 10:31 am

Post by bouncer »

jmut wrote:I would assume you are using mysql as sql server.
mysqldump command is your friend. Don't think you need a php script for this.
yes i'm using mysql

how can i make this with mysqldump command ?

if i use mysqldump command i can still run that every 4h ?

where i add that command ?

sorry for my noob questions :wink:

thks in adv
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

Post by jmut »

mysqldump is for dumping mysql data. You can dump it in simple text file e.g. You could find the command within bin/ directory of your server. Cannot help you with this one. Read the help associated with it to understand how to use.
You should use some other means to do repetative tasks (every 4 hours as you requested).
Cannot give you complete solution because:
1. Don't know exact situation and it seems you use windows which I have problems with.
2. Try it yourself and if you have specific problems someone here will help you.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Look into the replication services MySQL offers.
bouncer
Forum Contributor
Posts: 162
Joined: Wed Feb 28, 2007 10:31 am

Post by bouncer »

thks jmut, feyd

just one more question, can i create a SQL script (.sql file) that will gather all the data from a table in DB1 and export it to the same table in DB2, in the end i'll get the same result as if i use mysqldump ?

or this are different things ?

sorry for all this noob stuff :wink:

thks in advc
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

With replication you don't have to create a file.
bouncer
Forum Contributor
Posts: 162
Joined: Wed Feb 28, 2007 10:31 am

Post by bouncer »

feyd wrote:With replication you don't have to create a file.
i want something like this....

Code: Select all

<?
$dbhost = 'localhost';
$dbuser = 'user';
$dbpass = '';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

$dbname = 'dbname';
mysql_select_db($dbname, $conn);

$tableName = 'tablename';
$backupFile = 'tablename.sql';
$query = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName";
$result = mysql_query($query);

/*  not sure about this last part ... just change the DB and query */
$dbname = 'dbname1'; //can i have the same var with a different value from the one above ?
mysql_select_db($dbname1, $conn);

$query = "LOAD DATA INFILE 'backupFile' INTO TABLE $tableName";
$result = mysql_query($query); //can i run different queries with the same var ?

mysql_close($conn);
?>
but it dont work i guess it is because of the $conn, or something wrong with query...dont have sure,any advice for this code ?

thks in advc
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

If you are going to do this with PHP code (instead of using the wonderful suggestions already offered) you are going to need to change your query syntax to use database-name.table-name syntax. Even if you select a database to use.
bouncer
Forum Contributor
Posts: 162
Joined: Wed Feb 28, 2007 10:31 am

Post by bouncer »

Everah wrote:If you are going to do this with PHP code (instead of using the wonderful suggestions already offered) you are going to need to change your query syntax to use database-name.table-name syntax. Even if you select a database to use.
i dont use any of the wonderful sugestions already offered, because i cant find a good example for my kind of problem, all i have found was this example, can you give me some tips about this code ?

is this code better than the other ?

correct me if i'm wrong, this code makes a backup of all DB, what i have to change here so i can make only a table backup and make that happen every 4 hours.

Code: Select all

<?php
include 'config.php';
include 'opendb.php';

$backupFile = $dbname . date("Y-m-d-H-i-s") . '.gz';
$command = "mysqldump --opt -h $dbhost -u $dbuser -p $dbpass $dbname | gzip > $backupFile";
system($command);
include 'closedb.php';
?>
this code is already working, now i'm getting all the DB backup, but now i want to backup only a specific table and then i want to transfer that to the same table but in a different DB how can i do that with msqldump ?

thanks in advance
Rovas
Forum Contributor
Posts: 272
Joined: Mon Aug 21, 2006 7:09 am
Location: Romania

Post by Rovas »

Read here more information on mysqldump http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html. I think you should use the option

Code: Select all

-tab [i]name[/i]
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

bouncer wrote:i dont use any of the wonderful sugestions already offered, because i cant find a good example for my kind of problem, all i have found was this example, can you give me some tips about this code ?
:?

http://www.google.com/search?q=mysql+replication
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post by Begby »

What exactly are you trying to do? Are you trying to backup your database every 4 hours?

If so then you want to use mysqldump as was suggested, but you don't need to use any php code for it. You can run it on the command line in windows or linux or macos or whatever. You can even run it from a different pc and connect to your mysql server on a network very easily.

If its on windows you can put the command directly into a batch file (without PHP) and add it to task scheduler.

On linux you can create a shell script to run the dump and make it a cron job.

Also, every 4 hours isn't a bad idea, but you will want multiple copies. Lets say you are coding and mess up and write a delete query and forget to use a WHERE clause. What if you don't notice for four hours and your backup gets overwriiten? Or what if your backup is scheduled to run 2 seconds after you make your mistake? You need to be able to go back further in time with several database images just in case.

Also, replication does not equal backup. You still need to backup the database.
bouncer
Forum Contributor
Posts: 162
Joined: Wed Feb 28, 2007 10:31 am

Post by bouncer »

Begby wrote:What exactly are you trying to do? Are you trying to backup your database every 4 hours?
i'm trying to backup not a database but only a table from it, and then i want to export that backup to the same table (same name and fields) but in a different database.

but i'm searching for a mysqldump command/tag that allow me to select only one table and then reload the data to the same table but in another database...so far i just have this:

Code: Select all

$backupFile = $dbname . date("Y-m-d-H-i-s") . '.gz';
$command = "mysqldump --opt -h $dbhost -u $dbuser -p $dbpass $dbname | gzip > $backupFile";
system($command);
thanks in advance
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

As long as your user has privileges for both databases, you can do this with PHP. I am not sure it is the best way (I think you can set up a job in SQLYog, for example, that will go as far as new records in a field of a table, and will do two way synchronization). But if you were going to do this in PHP, you would hit the server, then select from one table in one database and update a different table in a different database.

Code: Select all

<?php
if (!$con = mysql_connect('localhost', 'user', 'pass'))
{
  die('Could not connect: ' . mysql_error());
}

// Get the stuff from the first table
$sql = "SELECT * FROM `database1`.`table1`";

if (!$result = mysql_query($sql))
{
  die('Could not query: ' . mysql_error());
}

while ($row = mysql_fetch_array($result))
{
  // Insert into the other table
  $sql = "INSERT INTO `database2`.`table2` (`field1`, `field2`) VALUES ('{$row['field1']}', '{$row['field2']}')";

  // yadda yadda, error handling, etc
}
?>
THIS IS NOT MEANT TO BE A SOLUTION BUT RATHER A CONCEPT THAT YOU MIGHT BE ABLE TO USE.
Post Reply