Blog

Add a new blog
February 23

MySQL sql_mode - Get and Set sql_mode Settings to solve

Check your current sql_mode SELECT @@GLOBAL.sql_mode;          or,  SELECT @@sql_mode; Edit your my.cnf and remove  "ONLY_FULL_GROUP_BY". if you don't have sql_mode, just add sql_mode to .conf. Restart mysql server. sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION ...

January 6

Search and update / replace part of text with MySQL

Update / replace text with mysql. UPDATE tbl_name SET     field_name = REPLACE(field_name,         "string_to_find",         "string_to_replace") WHERE     conditions; where state can be optional. ...

November 11

setup MySQL on ubuntu 18.04 server

Install mysql sudo apt update sudo apt install mysql-server sudo mysql_secure_installation ...

September 9

How to do a case sensitive search in WHERE clause in MySQL?

Tip for search in MySQL with case sensitive:  SELECT *  FROM table_name WHERE BINARY field_name LIKE '%search_string%'; ...

March 11

MySql correct font display issue with asian font from DB by using UTF-8

Use set_charset to turn current charset to utf8, and you will be able to display Asian fonts.   $db_link = mysqli_connect($db['host'],$db['user'],$db['pass'],$db['db']); //  echo "Initial character set is: " . $db_link -> character_set_name();   $db_link -> set_charset("utf8"); // Change character set to utf8 //  echo "Current character set is: " . $db_link -> character_set_name(); ...

February 12

MySQL - use LIMIT on select and still getting total rows found

Use SQL_CALC_FOUND_ROWS in your SELECT query and then run SELECT FOUND_ROWS() after that: SELECT SQL_CALC_FOUND_ROWS field_name,filed_name2 FROM tables WHERE CLAUSE LIMIT X; SELECT FOUND_ROWS() ; ...

April 17

Remove or revoke DB access or privileges from mysql user both local and remote

Remove or revoke DB access or privileges from mysql user both local and remote: REVOKE ALL PRIVILEGES ON *.* FROM 'plugins_dbuser'@'localhost'; REVOKE ALL PRIVILEGES ON *.* FROM 'plugins_dbuser'@'%'; ...

April 15

Update a column value, replacing part of a string

here is how we can replace partial of the string on all rows in MySQL:  UPDATE my_table_name SET my_field_name = REPLACE(my_field_name, 'unwanted-string', 'new-string'); ...

February 9

MySQL - Merge two user groups users into one current group, and delete the second group.

Merge two user groups users into one current group, and delete the second group. Note: some users might be in both groups already. # Show users in groupID 111 are also in groupID 222 SELECT user_id FROM user_group  WHERE user_group_id={111} AND user_id IN (SELECT user_id FROM user_group WHERE user_group_id={222} )  # Change user_group_id 222 to 111. Use 'UPDATE IGNORE'  assume user_id & user_group_id are unique index UPDATE IGNORE user_group  SET user_group_id={111} WHERE...

Send us a message. We will reply as soon as we can.