Category: MySQL

To import DB using command line on server:

mysql -u [User name of the Database] -p [Database Name] < [.SQL file name]

it will ask for the password, we need to paste the password using right click and then hit enter

in MySQL file my.conf

innodb_buffer_pool_size=12M
innodb_additional_mem_pool_size=500K
innodb_log_buffer_size=500K
innodb_thread_concurrency=2

key_buffer_size = 32M
thread_cache_size = 8
query_cache_size = 256M
query_cache_limit = 256M
tmp_table_size = 32M
max_heap_table_size = 32M?

We found sometimes when we download a DB from a server which have uppercase and lowercase mix data and when we import it in our local machine the case of table name and fields changed.

Continue reading “MySQL my.ini settings for some purpose”

There are some words due to collation shows special characters in front end .

========================================================

If &nbsp; we need to remove from mysql field we can do following

mysql_query(“UPDATE products SET `displayname` = REPLACE(displayname, CHAR(160), ‘ ‘), `name` = REPLACE(name, CHAR(160), ‘ ‘) WHERE product_id=’$product_id’ “);

================================================================

We can replace them in MySQL like below:

UPDATE products SET long_desc = REPLACE(long_desc, “’”, “‘”) WHERE product_id = 1256
UPDATE products SET long_desc = REPLACE(long_desc, “®”, “‘”) WHERE product_id = 1544
UPDATE products SET long_desc = REPLACE(long_desc, “�”, ” “) WHERE product_id = 1544

About PDO : ?http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html

Discussion on sql injection

http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1

For generating a random number between any two numbers we can use below code

update gurtestimonials set testrating = 5 – FLOOR( RAND( ) * ( 5 -4 +1 ) );

maxnum-FLOOR( RAND( ) * ( maxnum?-minnum +1 ) );

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

`

SELECT id, SUBSTRING_INDEX(name, '-', 1),marks, SUBSTRING_INDEX(name, '-', -1) as code_value ?FROM test ORDER BY code_value

`