1)->Copy one table data to another into same database
==================================================
insert into products(product_id,manufacturer_ref,sku,name,long_desc,dimension_unit,notes,rrp,our_price,sale_price,clearance_price,other_stock,weight,range_name,supplier,family)
select product_id,product_ref,product_name,product_short_description,product_description,product_dimensions,product_additional_notes,product_rrp,product_price,product_sale_price,product_clearance_price,product_stock,product_weight,product_range,product_supplier,family_id from products_live
2)->Copy one table data to another into same database and concat two field
=======================================================================
insert into products_t(title,description,price,link,image_link)
select product_short_description,product_description,product_rrp,
CONCAT('http://www.lighting-by-gabrielli.co.uk/products/',
REPLACE((SELECT name FROM categories WHERE categories.category_id = products.category_id), ' ', '-'),
'/',product_id,'/',product_name,'/'),
CONCAT ('http://www.lighting-by-gabrielli.co.uk/_images/products/',product_id, '_2.jpg')
from products
3)->Copying one database table to another database table on same server
=========================================================================
$mss_sc = array("mss_ltd"=>array( "host_name"=> "localhost","username" => "root","password" => "","db_name" => "grand_kitchen")
);
foreach($mss_sc as $site_name => $site_val){
$conn = mysql_connect($site_val['host_name'],$site_val['username'],$site_val['password']) or die(mysql_error());
mysql_select_db($site_val['db_name'],$conn)or die("error in select in db");
///// for creating and inserting product ////////////////////////////
mysql_query("DROP TABLE products");
mysql_query("CREATE TABLE products LIKE mss_sc.products") or die(mysql_error());
mysql_query("INSERT INTO products SELECT * FROM mss_sc.products") or die(mysql_error());
///// for creating and inserting category ////////////////////////////
mysql_query("DROP TABLE category");
mysql_query("CREATE TABLE category LIKE mss_sc.category") or die(mysql_error());
mysql_query("INSERT INTO category SELECT * FROM mss_sc.category") or die(mysql_error());
mysql_close($conn);
}
4)->Copying one database table to another database table on diffrent server
=========================================================================
$mss_sc = array("grand-kitchens"=>array("host_name"=> "grand-kitchens.co.uk",
"username" => "web150-majestic",
"password" => "majestic123!",
"db_name" => "web150-majestic"),
"mss_ltd"=>array( "host_name"=> "217.199.187.63",
"username" => "cl51-gkgranite",
"password" => "2VEB-Ry2N",
"db_name" => "cl51-gkgranite")
);
$dblink1=mysql_connect('92.60.121.118', 'mss_sc', 'mss_sc') or die(mysql_error()); // connect server 1
mysql_select_db('mss_sc',$dblink1) or die(mysql_error()); // select database 1
foreach($mss_sc as $site_name => $site_val){
$dblink2=mysql_connect($site_val['host_name'], $site_val['username'], $site_val['password']) or die(mysql_error()); // connect server 2
mysql_select_db($site_val['db_name'],$dblink2) or die(mysql_error()); // select database 2
///// for creating and inserting product ////////////////////////////
$table='products';
mysql_query("DROP TABLE IF EXISTS $table",$dblink2) or die(mysql_error());
$tableinfo = mysql_fetch_array(mysql_query("SHOW CREATE TABLE $table ",$dblink1)); // get structure from table on server 1
mysql_query(" $tableinfo[1] ",$dblink2); // use found structure to make table on server 2
$result = mysql_query("SELECT * FROM $table ",$dblink1); // select all content
while ($row = mysql_fetch_array($result, MYSQL_ASSOC) ) {
mysql_query("INSERT INTO $table (".implode(", ",array_keys($row)).") VALUES ('".implode("', '",array_values($row))."')",$dblink2); // insert one row into new table
}
///// for creating and inserting category ////////////////////////////
$table='category';
mysql_query("DROP TABLE IF EXISTS $table",$dblink2) or die(mysql_error());
$tableinfo = mysql_fetch_array(mysql_query("SHOW CREATE TABLE $table ",$dblink1)); // get structure from table on server 1
mysql_query(" $tableinfo[1] ",$dblink2); // use found structure to make table on server 2
$result = mysql_query("SELECT * FROM $table ",$dblink1); // select all content
while ($row = mysql_fetch_array($result, MYSQL_ASSOC) ) {
mysql_query("INSERT INTO $table (".implode(", ",array_keys($row)).") VALUES ('".implode("', '",array_values($row))."')",$dblink2); // insert one row into new table
}
mysql_close($dblink2);
}
mysql_close($dblink1);
5) ->
=========================================================================
SELECT schooname, COUNT(*), GROUP_CONCAT(jobassign.id) AS jobassignids FROM `jobassign` LEFT JOIN job ON job_id=job.`id` GROUP BY job.`id`
It will put all jobs with comma separated values in jobassignids column so we don't have to loop through the records to get all the related ids.
You can use GROUP_CONCAT where you want its a useful function.
6) -> Removing , and concat etc
========================================================================
SELECT REPLACE( email, ',', '' ) AS email, CONCAT( REPLACE(first_name, ',', '' ), " ", REPLACE(last_name, ',', '' ) ) AS name
FROM `user` group by email LIMIT 10001 , 15000
