DZone Snippets: mysql code

Query MySQL accent insensitive in latin1_general_ci

ki4ngel (Benoit Asselin) — Sat, 21 Jun 2008 10:34:59 GMT

Query MySQL accent insensitive in latin1_general_ci



SELECT * FROM `table`WHERE `text` LIKE CONVERT(_utf8 '%cinema%' USING utf8) COLLATE utf8_general_ci ;

ki4ngel (Benoit Asselin) — Tue, 13 May 2008 17:31:35 GMT



CREATE TABLE table_destination SELECT * FROM table_source ;



CREATE TABLE table_destination LIKE table_source ;

INSERT INTO table_destination SELECT * FROM table_source ;

ki4ngel (Benoit Asselin) — Sat, 19 Jan 2008 22:05:22 GMT



SELECT * FROM texts WHERE content REGEXP '[^a-z]Hello[^a-z]' ;

If you really want to force a REGEXP comparison to be case sensitive, use the BINARY keyword to make one of the strings a binary string.



SELECT * FROM texts WHERE content REGEXP BINARY '[^a-zA-Z]Hello[^a-zA-Z]' ;

Warning: some characters do not work. Example :



SELECT * FROM texts WHERE content REGEXP '[^\w]Hello[^\w]' ;

ki4ngel (Benoit Asselin) — Fri, 23 Nov 2007 22:07:58 GMT

So you can use this request MySQL before all others, for fix your problems :



...

mysql_query( "SET NAMES 'utf8' " );

...

ki4ngel (Benoit Asselin) — Sun, 18 Nov 2007 20:57:07 GMT



function f_magic_quotes($text) {

	if ( !get_magic_quotes_gpc() ) {

		return addslashes($text);

	} else {

		return $text;

	}

}



function f_clean_quotes($text) {

	if ( !get_magic_quotes_gpc() ) {

		return $text;

	} else {

		return stripslashes($text);

	}

}