Hi,
I have two servers. The question is i want to select the information form one server's table and insert it into other server's table. for that how many files i need.
I am able to select table data form server one but i don't know how to insert it into server two's table.
can any one help me or give me brief idea which will work
use this as your note:
1. make each every row of your table1 result into an array;
the result should be like this:
$table1Array = Array(Array][0],Array][1],Array][2],Array][3],Array][4]);then use the php code tag "foreach()" to separate this arrays into single array.
2. if you already have an array for the first row
use insert statement into your sql connection
insert into table2 ('column1','column2','column3') values('value1','value2','value3');3.loop into each every single arrays.
please click solve if it helps you.
i have these fields
$sql="SELECT * FROM import_address_book LIMIT 0 , 30";
$result=mysql_query($sql) or die();
while($row=mysql_fetch_array($result))
{
$id[] = $row[id];
$mobile_no[] = $row[mobile_no];
$user_id = $row[user_id];
$userEmail = $row[userEmail];
$name = $row[name];
$email = $row[email];
$type = $row[type];
$country = $row[country];
$gpwd = $row[gpwd];
$status = $row[status];
$dateCreated = $row[dateCreated];
$gender = $row[gender];
$city = $row[city];
$income = $row[income];
$profession = $row[profession];
$experience = $row[experience];
$pincode = $row[pincode];
$blacklist = $row[blacklist];
}
now can u tell me how to form an array to store. i think u under stand my problem
yes, but I will give you a very long winded answer, I hope you find it helpful.
ever use a class before, this would be the perfect time, so I made one for you.
<?php
// assuming you named the class file import.class.php
import('import.class.php');
// instantiate your class (get an empty container);
$i = new import();
// get blank array
$records = array();
// fill blank array with 30 records from your select_30 call ( you can do more depending on what you are doing)
// calls server db 1 from the class
$records = $i->select_30();
// each $record is an object of type import with all of the data in it.
foreach($records as $record) {
//calls server db 2 'skyserver' in the class
$record->skyserver_insert($record->get_id());
// you may not have to pass the id into the insert call, you need to read the notes in the file
// as I assumed that may be an auto-increment field
}
?>here is the class. import.class.php
<?php
// begin generated class -->
/*
* -------------------------------------------------------
* CLASSNAME: import
* GENERATION DATE: 15.09.2011
* CLASS FILE: C:\wamp\www\PHP_LIB\sql_class_generator\generated_classes\import.class.php
* FOR MYSQL TABLE: import_address_book
* FOR MYSQL DB: test
* -------------------------------------------------------
*/
// server 1
define('DB_HOST_1','localhost');
define('DB_USER_1','root');
define('DB_PASS_1','');
define('DB_BASE_1','test');
// server 2 referenced in the code below as 'skyserver' calls
define('DB_HOST_2','skyserver.com');
define('DB_USER_2','root');
define('DB_PASS_2','');
define('DB_BASE_2','live');
// **********************
// CLASS DECLARATION
// **********************
// class : begin
class import {
// **********************
// ATTRIBUTE DECLARATION
// **********************
var $id; // KEY ATTR. WITH AUTOINCREMENT
var $mobile_no; // (normal Attribute)
var $user_id; // (normal Attribute)
var $userEmail; // (normal Attribute)
var $name; // (normal Attribute)
var $email; // (normal Attribute)
var $type; // (normal Attribute)
var $country; // (normal Attribute)
var $gpwd; // (normal Attribute)
var $status; // (normal Attribute)
var $dateCreated; // (normal Attribute)
var $gender; // (normal Attribute)
var $city; // (normal Attribute)
var $income; // (normal Attribute)
var $profession; // (normal Attribute)
var $experience; // (normal Attribute)
var $pincode; // (normal Attribute)
var $blacklist; // (normal Attribute)
// **********************
// CONSTRUCTOR METHOD
// **********************
function import() {
}
// **********************
// GETTER METHODS
// **********************
function get_id() {
return $this->id;
}
function get_mobile_no() {
return $this->mobile_no;
}
function get_user_id() {
return $this->user_id;
}
function get_userEmail() {
return $this->userEmail;
}
function get_name() {
return $this->name;
}
function get_email() {
return $this->email;
}
function get_type() {
return $this->type;
}
function get_country() {
return $this->country;
}
function get_gpwd() {
return $this->gpwd;
}
function get_status() {
return $this->status;
}
function get_dateCreated() {
return $this->dateCreated;
}
function get_gender() {
return $this->gender;
}
function get_city() {
return $this->city;
}
function get_income() {
return $this->income;
}
function get_profession() {
return $this->profession;
}
function get_experience() {
return $this->experience;
}
function get_pincode() {
return $this->pincode;
}
function get_blacklist() {
return $this->blacklist;
}
// **********************
// SETTER METHODS
// **********************
function set_id($val) {
$this->id = $val;
}
function set_mobile_no($val) {
$this->mobile_no = $val;
}
function set_user_id($val) {
$this->user_id = $val;
}
function set_userEmail($val) {
$this->userEmail = $val;
}
function set_name($val) {
$this->name = $val;
}
function set_email($val) {
$this->email = $val;
}
function set_type($val) {
$this->type = $val;
}
function set_country($val) {
$this->country = $val;
}
function set_gpwd($val) {
$this->gpwd = $val;
}
function set_status($val) {
$this->status = $val;
}
function set_dateCreated($val) {
$this->dateCreated = $val;
}
function set_gender($val) {
$this->gender = $val;
}
function set_city($val) {
$this->city = $val;
}
function set_income($val) {
$this->income = $val;
}
function set_profession($val) {
$this->profession = $val;
}
function set_experience($val) {
$this->experience = $val;
}
function set_pincode($val) {
$this->pincode = $val;
}
function set_blacklist($val) {
$this->blacklist = $val;
}
// **********************
// print_to_screen()
// simply see what is held in this contianer on your webpage all at once.
// **********************
function print_to_screen() {
echo "id = ". $this->id ." \n <br />";
echo "mobile_no = ". $this->mobile_no ." \n <br />";
echo "user_id = ". $this->user_id ." \n <br />";
echo "userEmail = ". $this->userEmail ." \n <br />";
echo "name = ". $this->name ." \n <br />";
echo "email = ". $this->email ." \n <br />";
echo "type = ". $this->type ." \n <br />";
echo "country = ". $this->country ." \n <br />";
echo "gpwd = ". $this->gpwd ." \n <br />";
echo "status = ". $this->status ." \n <br />";
echo "dateCreated = ". $this->dateCreated ." \n <br />";
echo "gender = ". $this->gender ." \n <br />";
echo "city = ". $this->city ." \n <br />";
echo "income = ". $this->income ." \n <br />";
echo "profession = ". $this->profession ." \n <br />";
echo "experience = ". $this->experience ." \n <br />";
echo "pincode = ". $this->pincode ." \n <br />";
echo "blacklist = ". $this->blacklist ." \n <br />";
echo "end p_t_s function \n ";
}
// **********************
// INSERT
// **********************
/* this function to insert is for db_host_1 it is expecting id to be an auto-increment field.
** if id is not auto-increment you simpley need to add it as the first field you are sending in your 'name' and 'value' spots below.
*/
function insert() {
$this->id = ""; // clear key for autoincrement
$sql = "INSERT INTO import_address_book ( mobile_no,user_id,userEmail,name,email,type,country,gpwd,status,dateCreated,gender,city,income,profession,experience,pincode,blacklist ) VALUES ( '".$this->slashes($this->mobile_no)."','".$this->slashes($this->user_id)."','".$this->slashes($this->userEmail)."','".$this->slashes($this->name)."','".$this->slashes($this->email)."','".$this->slashes($this->type)."','".$this->slashes($this->country)."','".$this->slashes($this->gpwd)."','".$this->slashes($this->status)."','".$this->slashes($this->dateCreated)."','".$this->slashes($this->gender)."','".$this->slashes($this->city)."','".$this->slashes($this->income)."','".$this->slashes($this->profession)."','".$this->slashes($this->experience)."','".$this->slashes($this->pincode)."','".$this->slashes($this->blacklist)."' )";
$dblink = null;
try {
$dblink = mysql_connect(DB_HOST_1,DB_USER_1,DB_PASS_1);
mysql_select_db(DB_BASE_1,$dblink);
} catch(Exception $ex) {
echo "Could not connect to " . DB_HOST_1 . ":" . DB_BASE_1 . "\n";
echo "Error: " . $ex->message;
exit;
}
$retid = mysql_query($sql,$dblink) or die(mysql_error());
if (!$retid) {
echo( mysql_error());
}
// this is setting this id from the last insert id in database, you may want or not want to do it this way
// may want to alter this code.
$this->id = mysql_insert_id($dblink);
if(is_resource($dblink)) {
mysql_close($dblink);
}
return $retid;
}
// **********************
// UPDATE
// **********************
function update($id) {
$sql = " UPDATE import_address_book SET mobile_no = '$this->mobile_no',user_id = '$this->user_id',userEmail = '$this->userEmail',name = '$this->name',email = '$this->email',type = '$this->type',country = '$this->country',gpwd = '$this->gpwd',status = '$this->status',dateCreated = '$this->dateCreated',gender = '$this->gender',city = '$this->city',income = '$this->income',profession = '$this->profession',experience = '$this->experience',pincode = '$this->pincode',blacklist = '$this->blacklist' WHERE id = $id ";
$dblink = null;
try {
$dblink = mysql_connect(DB_HOST_1,DB_USER_1,DB_PASS_1);
mysql_select_db(DB_BASE_1,$dblink);
} catch(Exception $ex) {
echo "Could not connect to " . DB_HOST_1 . ":" . DB_BASE_1 . "\n";
echo "Error: " . $ex->message;
exit;
}
$retid = mysql_query($sql,$dblink) or die(mysql_error());
if (!$retid) {
echo( mysql_error());
}
if(is_resource($dblink)) {
mysql_close($dblink);
}
return $retid;
}
// **********************
// SELECT METHOD / LOAD
// **********************
/**
* @type - public
* @desc - load this object
* @param - select_by_id($id)
* @return - sets this object from "$id"
* @vers - 1 loads a single instance off of $id
* @Mod -
**/
function select_by_id($id) {
$dblink = null;
try {
$dblink = mysql_connect(DB_HOST_1,DB_USER_1,DB_PASS_1);
mysql_select_db(DB_BASE_1,$dblink);
} catch(Exception $ex) {
echo "Could not connect to " . DB_HOST_1 . ":" . DB_BASE_1 . "\n";
echo "Error: " . $ex->message;
exit;
}
$sql = "SELECT * FROM import_address_book WHERE id = ".$this->slashes($id);
$retid = mysql_query($sql,$dblink) or die(mysql_error());
if (!$retid) {
echo( mysql_error());
}
if ($row = mysql_fetch_array($retid)) {
$this->id = $row['id'];
$this->mobile_no = $row['mobile_no'];
$this->user_id = $row['user_id'];
$this->userEmail = $row['userEmail'];
$this->name = $row['name'];
$this->email = $row['email'];
$this->type = $row['type'];
$this->country = $row['country'];
$this->gpwd = $row['gpwd'];
$this->status = $row['status'];
$this->dateCreated = $row['dateCreated'];
$this->gender = $row['gender'];
$this->city = $row['city'];
$this->income = $row['income'];
$this->profession = $row['profession'];
$this->experience = $row['experience'];
$this->pincode = $row['pincode'];
$this->blacklist = $row['blacklist'];
}
if(is_resource($dblink)) {
mysql_close($dblink);
}
return $this;
}
/**
* @type - public
* @desc - load a group of this object
* @param - select_30()
* @return - sets this object from "$id"
* @vers - 1
* @Mod - loads the whole table, depending on query, right now set to return 30 records as original code on page.
**/
// I named this select_30 simply because you put a limit on your query of 30 recs.
function select_30() {
$return_array = array(); // this is what we pass back to the caller
$dblink = null;
try {
$dblink = mysql_connect(DB_HOST_1,DB_USER_1,DB_PASS_1);
mysql_select_db(DB_BASE_1,$dblink);
} catch(Exception $ex) {
echo "Could not connect to " . DB_HOST_1 . ":" . DB_BASE_1 . "\n";
echo "Error: " . $ex->message;
exit;
}
// lets get all of the records, or you can use yours, limiting to 30
$sql="SELECT * FROM import_address_book LIMIT 0 , 30";
// or select all of them
//$sql = "SELECT * FROM import_address_book";
$retid = mysql_query($sql,$dblink) or die(mysql_error());
if (!$retid) {
echo( mysql_error());
}
while ($row = mysql_fetch_array($retid)) {
// instantiate a new class object
$imp = new import();
// fill it with data
$imp->id = $row['id'];
$imp->mobile_no = $row['mobile_no'];
$imp->user_id = $row['user_id'];
$imp->userEmail = $row['userEmail'];
$imp->name = $row['name'];
$imp->email = $row['email'];
$imp->type = $row['type'];
$imp->country = $row['country'];
$imp->gpwd = $row['gpwd'];
$imp->status = $row['status'];
$imp->dateCreated = $row['dateCreated'];
$imp->gender = $row['gender'];
$imp->city = $row['city'];
$imp->income = $row['income'];
$imp->profession = $row['profession'];
$imp->experience = $row['experience'];
$imp->pincode = $row['pincode'];
$imp->blacklist = $row['blacklist'];
// save to our array to be returned later...
array_push($return_array,$imp);
}
if(is_resource($dblink)) {
mysql_close($dblink);
}
// return your (up to) 30 objects in an array
return $return_array;
}
// **********************
// DELETE
// **********************
/**
* @type - public
* @desc - delete this object
* @param - delete($id)
* @return - boolean
* @vers - 1
* @Mod -
**/
function delete($id) {
$dblink = null;
try {
$dblink = mysql_connect(DB_HOST_1,DB_USER_1,DB_PASS_1);
mysql_select_db(DB_BASE_1,$dblink);
} catch(Exception $ex) {
echo "Could not connect to " . DB_HOST_1 . ":" . DB_BASE_1 . "\n";
echo "Error: " . $ex->message;
exit;
}
$sql = "DELETE FROM import_address_book WHERE id = $id;";
$retid = mysql_query($sql,$dblink) or die(mysql_error());
if (!$retid) {
echo( mysql_error());
}
if(is_resource($dblink)) {
mysql_close($dblink);
}
return $retid;
}
/****** This is for your second server! ***/
/****** This is for your second server! ***/
/****** This is for your second server! ***/
/****** I did not know the name of your table that is on server 2. so the code below for server 2 is currently looking for a
/****** table named import_address_book you may need to change table names if function call below.
/******
// **********************
// INSERT
// **********************
/* this function to insert is for db_host_2 it is expecting id to be an auto-increment field.
** if id is not auto-increment you simpley need to add it as the first field you are sending in your 'name' and 'value' spots below.
** and change the (line 410) where you would set this id off of the insert id (You will have to customize this code a little before running!
**
*/
function skyserver_insert() {
$this->id = ""; // clear key for autoincrement
$sql = "INSERT INTO import_address_book ( mobile_no,user_id,userEmail,name,email,type,country,gpwd,status,dateCreated,gender,city,income,profession,experience,pincode,blacklist ) VALUES ( '".$this->slashes($this->mobile_no)."','".$this->slashes($this->user_id)."','".$this->slashes($this->userEmail)."','".$this->slashes($this->name)."','".$this->slashes($this->email)."','".$this->slashes($this->type)."','".$this->slashes($this->country)."','".$this->slashes($this->gpwd)."','".$this->slashes($this->status)."','".$this->slashes($this->dateCreated)."','".$this->slashes($this->gender)."','".$this->slashes($this->city)."','".$this->slashes($this->income)."','".$this->slashes($this->profession)."','".$this->slashes($this->experience)."','".$this->slashes($this->pincode)."','".$this->slashes($this->blacklist)."' )";
$dblink = null;
try {
$dblink = mysql_connect(DB_HOST_2,DB_USER_2,DB_PASS_2);
mysql_select_db(DB_BASE_2,$dblink);
} catch(Exception $ex) {
echo "Could not connect to " . DB_HOST_2 . ":" . DB_BASE_2 . "\n";
echo "Error: " . $ex->message;
exit;
}
$retid = mysql_query($sql,$dblink) or die(mysql_error());
if (!$retid) {
echo( mysql_error());
}
$this->id = mysql_insert_id($dblink);
if(is_resource($dblink)) {
mysql_close($dblink);
}
return $retid;
}
// **********************
// UPDATE
// **********************
function skyserver_update($id) {
$sql = " UPDATE import_address_book SET mobile_no = '$this->mobile_no',user_id = '$this->user_id',userEmail = '$this->userEmail',name = '$this->name',email = '$this->email',type = '$this->type',country = '$this->country',gpwd = '$this->gpwd',status = '$this->status',dateCreated = '$this->dateCreated',gender = '$this->gender',city = '$this->city',income = '$this->income',profession = '$this->profession',experience = '$this->experience',pincode = '$this->pincode',blacklist = '$this->blacklist' WHERE id = $id ";
$dblink = null;
try {
$dblink = mysql_connect(DB_HOST_2,DB_USER_2,DB_PASS_2);
mysql_select_db(DB_BASE_2,$dblink);
} catch(Exception $ex) {
echo "Could not connect to " . DB_HOST_2 . ":" . DB_BASE_2 . "\n";
echo "Error: " . $ex->message;
exit;
}
$retid = mysql_query($sql,$dblink) or die(mysql_error());
if (!$retid) {
echo( mysql_error());
}
if(is_resource($dblink)) {
mysql_close($dblink);
}
return $retid;
}
// **********************
// SELECT METHOD / LOAD
// **********************
/**
* @type - public
* @desc - load this object
* @param - select_by_id($id)
* @return - sets this object from "$id"
* @vers - 1
* @Mod -
**/
function skyserver_select_by_id($id) {
$dblink = null;
try {
$dblink = mysql_connect(DB_HOST_2,DB_USER_2,DB_PASS_2);
mysql_select_db(DB_BASE_2,$dblink);
} catch(Exception $ex) {
echo "Could not connect to " . DB_HOST_2 . ":" . DB_BASE_2 . "\n";
echo "Error: " . $ex->message;
exit;
}
$sql = "SELECT * FROM import_address_book WHERE id = ".$this->slashes($id);
$retid = mysql_query($sql,$dblink) or die(mysql_error());
if (!$retid) {
echo( mysql_error());
}
if ($row = mysql_fetch_array($retid)) {
$this->id = $row['id'];
$this->mobile_no = $row['mobile_no'];
$this->user_id = $row['user_id'];
$this->userEmail = $row['userEmail'];
$this->name = $row['name'];
$this->email = $row['email'];
$this->type = $row['type'];
$this->country = $row['country'];
$this->gpwd = $row['gpwd'];
$this->status = $row['status'];
$this->dateCreated = $row['dateCreated'];
$this->gender = $row['gender'];
$this->city = $row['city'];
$this->income = $row['income'];
$this->profession = $row['profession'];
$this->experience = $row['experience'];
$this->pincode = $row['pincode'];
$this->blacklist = $row['blacklist'];
}
if(is_resource($dblink)) {
mysql_close($dblink);
}
return $this;
}
/* get all records from the skyserver */
function skyserver_select_all() {
$return_array = array();
$dblink = null;
try {
$dblink = mysql_connect(DB_HOST_2,DB_USER_2,DB_PASS_2);
mysql_select_db(DB_BASE_2,$dblink);
} catch(Exception $ex) {
echo "Could not connect to " . DB_HOST_2 . ":" . DB_BASE_2 . "\n";
echo "Error: " . $ex->message;
exit;
}
$sql = "SELECT * FROM import_address_book";
$retid = mysql_query($sql,$dblink) or die(mysql_error());
if (!$retid) {
echo( mysql_error());
}
if ($row = mysql_fetch_array($retid)) {
// instantiate a new class object
$imp = new import();
// fill it with data
$imp->id = $row['id'];
$imp->mobile_no = $row['mobile_no'];
$imp->user_id = $row['user_id'];
$imp->userEmail = $row['userEmail'];
$imp->name = $row['name'];
$imp->email = $row['email'];
$imp->type = $row['type'];
$imp->country = $row['country'];
$imp->gpwd = $row['gpwd'];
$imp->status = $row['status'];
$imp->dateCreated = $row['dateCreated'];
$imp->gender = $row['gender'];
$imp->city = $row['city'];
$imp->income = $row['income'];
$imp->profession = $row['profession'];
$imp->experience = $row['experience'];
$imp->pincode = $row['pincode'];
$imp->blacklist = $row['blacklist'];
// save to our array to be returned later...
array_push($return_array,$imp);
}
if(is_resource($dblink)) {
mysql_close($dblink);
}
// return array of import objects
return $return_array;
}
// **********************
// DELETE
// **********************
/**
* @type - public
* @desc - delete this object
* @param - delete($id)
* @return - boolean
* @vers - 1
* @Mod -
**/
function skyserver_delete($id) {
$dblink = null;
try {
$dblink = mysql_connect(DB_HOST_2,DB_USER_2,DB_PASS_2);
mysql_select_db(DB_BASE_2,$dblink);
} catch(Exception $ex) {
echo "Could not connect to " . DB_HOST_2 . ":" . DB_BASE_2 . "\n";
echo "Error: " . $ex->message;
exit;
}
$sql = "DELETE FROM import_address_book WHERE id = $id;";
$retid = mysql_query($sql,$dblink) or die(mysql_error());
if (!$retid) {
echo( mysql_error());
}
if(is_resource($dblink)) {
mysql_close($dblink);
}
return $retid;
}
/**
* @author - Dana Dymacek
* @type - public
* @desc - $str
* @param - adding slashes if necessary
* @return - db safe string
* @vers - 1
* @Mod -
**/
function slashes($str) {
if (!empty($str)) {
return mysql_real_escape_string($str);
} else {
return $str;
}
}
/**************************************************************/
/* ADD YOUR CUSTOM FUNCTIONS BELOW */
/**************************************************************/
// class : end
}
?>set your my define db_host_1 and db_host2 etc. as different servers with the proper credentials
noticed one error it is in this function:
function skyserver_select_all() {
instead of:
if ($row = mysql_fetch_array($retid)) {
// it should be
while ($row = mysql_fetch_array($retid)) {If you guys understood the OP, you're doing better than me. What the hell did it mean?
it sounds like he has 2 webservers / databases on different servers. like one at godaddy (host A), one at Surpass (host B). it sounds like he has duplicate databases one on each site or at least it appears as though he needs to pull data from host A and send a query to host B with that set of data. I have a class generator that makes these calls so I quick made him a class. he just needs to define db_host_1 and db_host_2 (and a couple of other things) in the class and he should be able to connect to both servers with one file. The rest of the class code was just a bonus not for their specific needs
hey ddymacek why did you give him such long code to explain? he's just a novice.
@ chand8, to trouble shoot your problem
echo the variable row "$row", you will get a result in an array.
then after you echo it use now the foreach as i told you in number 2 notes
then you can now distribute it to the necessary variable.
decade: if the original poster has an issue with my answer then fine, but you provided no useful answer to the question they asked. they asked how to do something, they can use the code I provided right out of the box and plug right into their program as it is. It will work with the table that he laid out in his post right after yours. they could also choose not to use it, which is fine, the question was how many files he needed, the answer is 1. Make a class and re-usable functions, that class can also handle data transactions from page to page, they may be a novice but that is the best time to learn how to do something correctly, and looking at programming through oop techniques usually opens up a whole new way to look at, process and deal with your data and transactions that you will make over and over again. write the code once, keep main logic off of your web pages, and re-use it a thousand times. I simply could have typed this in and given this as my answer to him showing him nothing... instead of this short answer simply telling them to 'use a class' and it only takes one file, I actually showed them how to do it, with actual code they can use. That's all I have to say about this issue. good luck chand8, hope you find the answer you are looking for.
Perhaps leading the threadstarter through the process would be useful. I've been using php for years, but OOP still gives me a nosebleed.
ANyway:
Using that info:
$dbh1 = mysql_connect($hostname, $username, $password);
$dbh2 = mysql_connect($hostname, $username, $password, true); //4th param MUST be true
mysql_select_db('database1', $dbh1);
mysql_select_db('database2', $dbh2);
mysql_query('select * from tablename', $dbh1); //impt to use link - $dbh1
mysql_query('select * from tablename', $dbh2); // this points to 2nd connectionSeems straightforward enough.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.