Blog

Add a new blog
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...

October 23

Create a MySQL user to have both local and remote access for all databases

Create a mysql user:   CREATE USER 'myUser'@'localhost' IDENTIFIED BY 'YourNewPassword';  CREATE USER 'myUser'@'%' IDENTIFIED BY 'YourNewPassword'; Then GRANT ALL ON *.* TO 'myUser'@'localhost';  GRANT ALL ON *.* TO 'myUser'@'%'; Optional: limit user to single database: GRANT ALL ON name_of_database.* TO 'myUser'@'localhost'; GRANT ALL ON name_of_database.* TO 'myUser'@'%'; ...

October 23

Query to enable a single user to access a remote MySQL database

Create a remote user for single DB access. GRANT ALL PRIVILEGES ON new_db.* TO 'new_db_user'@'%' IDENTIFIED BY 'YourUserPassword'; FLUSH PRIVILEGES; ...

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