Author: admin

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