First time I've posted something for critique, but here goes. I'm pretty new to OO and using the comments in the style of phpDoc too so sorry if they're not what your used to
Be gentle
Any suggestions, bugs, comments would be great.
Specification: To be able to transfer data from a table in a database held on one server, to an indentical table in another database, potentially running on a totally different server within the network.
At the moment the largest table that will be transferred will be around 10'000 rows.
My Class:
Code: Select all
<?php
/**
* TransferData Class
*
* Transfers data from a specified mysql database table
* to an identical table in another mysql database
*
* @package V2
* @author Dave Baker
* @copyright Dave Baker
* @version 1.0
*
*/
class TransferData{
/**
* Email address where errors will be sent
*
* @var string
* @access private
*/
private $errors_mail_to = 'xx@xx.com';
/**
* Host name / IP Address for source mysql database
*
* @var string
* @access private
*/
private $source_host = '';
/**
* User name for source mysql database
*
* @var string
* @access private
*/
private $source_user = '';
/**
* Password for source mysql database
*
* @var string
* @access private
*/
private $source_password = '';
/**
* Source database name
*
* @var string
*/
private $source_database = '';
/**
* Host name / IP Address for destination mysql database
*
* @var string
* @access private
*/
private $destination_host = '';
/**
* Username for destination mysql database
*
* @var string
* @access private
*/
private $destination_user = '';
/**
* Password for destination mysql database
*
* @var string
* @access private
*/
private $destination_password = '';
/**
* Destination database name
*
* @var string
*/
private $destination_database = '';
/**
* Create the destination database if it doesn't exist?
*
* @var boolean
*/
private $create_destination_database = false;
/**
* Create the destination table if it doesn't exist?
*
* @var boolean
*/
private $create_destination_table = false;
/**
* Append flag
* If true then data appended to table in destination database, if false table is emptied before data is inserted
*
* @var boolean
* @access private
*/
private $append = false;
private $mysqli_type = array(
0=>"DECIMAL",
1=>"TINYINT",
2=>"SMALLINT",
3=>"INT",
4=>"FLOAT",
5=>"DOUBLE",
7=>"TIMESTAMP",
8=>"BIGINT",
9=>"MEDIUMINT",
10=>"DATE",
11=>"TIME",
12=>"DATETIME",
13=>"YEAR",
14=>"DATE",
16=>"BIT",
246=>"DECIMAL",
247=>"ENUM",
248=>"SET",
249=>"TINYBLOB",
250=>"MEDIUMBLOB",
251=>"LONGBLOB",
252=>"BLOB",
253=>"VARCHAR",
254=>"VARCHAR",
255=>"GEOMETRY"
);
/**
* Constructor
*
* @return void
*/
public function __construct(){
require_once dirname(__FILE__) . '/class.errorhandler.php';
$errors = new ErrorHandler();
$errors->set_mail_flag(false);
$errors->set_to($this->errors_mail_to);
$errors->set_echo_errors(true);
}
/**
* Sets connection details for the source database
*
* @param string $hostname
* @param string $username
* @param string $password
* @param string $database_name
* @return void
*/
public function set_source_db($hostname, $username, $password,$database_name){
$this->source_host = $hostname;
$this->source_user = $username;
$this->source_password = $password;
$this->source_database = $database_name;
}
/**
* Sets connection details for the destination database
*
* @param string $hostname
* @param string $username
* @param string $password
* @param string $database_name
* @return void
*/
public function set_destination_db($hostname, $username, $password,$database_name){
$this->destination_host = $hostname;
$this->destination_user = $username;
$this->destination_password = $password;
$this->destination_database = $database_name;
}
/**
* Transfers the data (uses the mysqli library).
*
* @param string $source_table
* @param string $destination_table
*
*/
public function transfer_data($source_table, $destination_table = ''){
//set destination table if no user intput
if ($destination_table == ''){$destination_table = $source_table;}
//check and create destination database
if ($this->create_destination_database){$this->create_destination_dbs();}
//connect to source
$source_conn = new mysqli($this->source_host, $this->source_user, $this->source_password, $this->source_database)
or trigger_error(mysqli_connect_error(),E_USER_WARNING);
//get source data
$source_table_data = $source_conn->query("SELECT * FROM $source_table;")
or trigger_error(mysqli_error($source_conn), E_USER_WARNING);
//create destination table
if ($this->create_destination_table){
//generate field list for the create table function
$field_list = mysqli_fetch_fields($source_table_data);
$fields = '';
foreach($field_list as $field){
$fields .= sprintf("`%s` %s (%s),\n",$field->name,$this->mysqli_type[$field->type],$field->length);
}
$fields = substr($fields,0,-2);
$this->create_destination_tbl($destination_table,$fields);
}
//connect to destination
$destination_conn = new mysqli($this->destination_host, $this->destination_user, $this->destination_password, $this->destination_database)
or trigger_error(mysqli_connect_error(), E_USER_WARNING);
//if append is false, clear out destination table
if (!$this->append){
$destination_conn->query("DELETE FROM $destination_table;")
or trigger_error(mysqli_error($destination_conn), E_USER_WARNING);
}
//get the first row and field names.
$row = $source_table_data->fetch_assoc();
$columns = '(';
$data = '(';
//create column and data strings
foreach ($row as $name => $value){
$columns .= "$name,";
$data .= "'$value',";
}
$columns = substr($columns,0,-1) . ')';
$data = substr($data,0,-1) . ')';
//transfer first row
$destination_conn->query("INSERT INTO $destination_table $columns VALUES $data;")
or trigger_error(mysqli_error($destination_conn), E_USER_WARNING);
//rinse and repeat for remaining data
while ($row = $source_table_data->fetch_assoc()){
$data = '(';
foreach ($row as $value){
$data .= "'$value',";
}
$data = substr($data,0,-1) . ')';
$destination_conn->query("INSERT INTO $destination_table $columns VALUES $data;")
or trigger_error(mysqli_error($destination_conn), E_USER_WARNING);
}
//close down
$source_table_data->close();
$source_conn->close();
$destination_conn->close();
}
/**
* Creates the database (if required) in the destination MYSQL Server
*
*/
private function create_destination_dbs(){
$conn = new mysqli($this->destination_host,$this->destination_user, $this->destination_password)
or trigger_error(mysqli_connect_error(), E_USER_ERROR);
$conn->query("CREATE DATABASE IF NOT EXISTS {$this->destination_database};")
or trigger_error(mysqli_error($conn), E_USER_ERROR);
$conn->close();
}
/**
* Creates the destination table in the destination database
*
* @param string $destination_table
* @param string $fields
*/
private function create_destination_tbl($destination_table, $fields){
$conn = new mysqli($this->destination_host,$this->destination_user, $this->destination_password,$this->destination_database)
or trigger_error(mysqli_connect_error(), E_USER_ERROR);
$conn->query("CREATE TABLE IF NOT EXISTS $destination_table ($fields);")
or trigger_error(mysqli_error($conn), E_USER_ERROR);
$conn->close();
}
/**
* Flags whether the destination database needs to be created
*
* @param string $create_database
*/
public function set_create_database($create_database){
$this->create_destination_database = $create_database;
}
/**
* Flags whether the destination table needs to be created
*
* @param string $create_table
*/
public function set_create_table($create_table){
$this->create_destination_table = $create_table;
}
/**
* Flags whether data is appended to destination
*
* @param boolean $append
*/
public function append_to_table($append){
$this->append = $append;
}
}
?>