Pages

Thursday, August 26, 2010

Spell checker API

Function below uses Yahoo API.

function yahoo_spell_check ($query)
{
// Substitute this application ID with your own application ID provided by Yahoo!.
$appID = "APP_ID";

// URI used for making REST call. Each Web Service uses a unique URL.
$request = "http://search.yahooapis.com/WebSearchService/V1/spellingSuggestion? appid=$appID&query=".urlencode($query);

// Initialize the session by passing the request as a parameter
$session = curl_init($request);

// Set curl options by passing session and flags
// CURLOPT_HEADER allows us to receive the HTTP header
curl_setopt($session, CURLOPT_HEADER, true);

// CURLOPT_RETURNTRANSFER will return the response
curl_setopt($session, CURLOPT_RETURNTRANSFER, true);

// Make the request
$response = curl_exec($session);

// Close the curl session
curl_close($session);

// Get the XML from the response, bypassing the header
if (!($xml = strstr($response, 'Result;

if($data[0] == '')
{
return $query;
}

return $data[0];
}






Function below uses Google's API.

function google_spell_check($query)
{
$url="https://www.google.com/tbproxy/spell?lang=en";// . $_GET['lang'];
$ignoredigits = 1;
$ignorecaps = 1;

$text = urldecode($query);

$body = '';
$body .= '';
$body .= '' . $text . '';
$body .= '
';

$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, $body);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, FALSE);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
$contents = curl_exec($ch);
curl_close($ch);

/* $ttt = XML2Array($contents);
//print_r($ttt);
//foreach($ttt as $key=>$val)
//echo "$key => $val\n";
extract($ttt);
$cc = explode(" ", trim($c));
return $cc[0];
*/


$xml = simplexml_load_string($contents);
$return_str = "";

foreach($xml->children() as $child)
{
$child_arr = preg_split("/[\s]+/", $child);
$return_str .= $child_arr[0] . " ";
}

if($return_str == '')
{
return strtolower($query);
}

return strtolower(trim($return_str));
}

Utility Functions

Here is the list of some of the important Utility functions.

I have this function as a member function in my Utility Class.


function mb_unserialize($serial_str) {
$out = preg_replace('!s:(\d+):"(.*?)";!se', "'s:'.strlen('$2').':\"$2\";'", $serial_str );
return unserialize($out);
}


function getValidFileName($str)
{
return preg_replace('/[^0-9a-z?-????\`\~\!\@\#\$\%\^\*\(\)\; \,\.\'\/\_\-]/i', ' ',$str);
}


function XML2Array ( $xml , $recursive = false )
{
if ( ! $recursive )
{
$array = simplexml_load_string ( $xml ) ;
}
else
{
$array = $xml ;
}

$newArray = array () ;
$array = ( array ) $array ;
foreach ( $array as $key => $value )
{
$value = ( array ) $value ;
if ( isset ( $value [ 0 ] ) )
{
$newArray [ $key ] = trim ( $value [ 0 ] ) ;
}
else
{
$newArray [ $key ] = XML2Array ( $value , true ) ;
}
}
return $newArray ;
}


function set_flash($text,$type) {
$flash['type'] = $type;
$flash['text'] = $text;
$_SESSION['flash'] = serialize($flash);
}

function get_flash() {
if ($flash = $_SESSION['flash']) {
$_SESSION['flash'] = array();
return unserialize($flash);
} else {
return "";
}
}


function smartslashes($text) {
$magic_quotes_gpc = (bool) ini_get('magic_quotes_gpc');
if (!$magic_quotes_gpc) {
return addslashes($text);
} else {
return $text;
}
}

function check_email_address($email_address)
{
//returns 1 if valid email address (only numeric string), 0 if not
if (eregi("^[\+_a-z0-9-]+(\.[\+_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$", $email_address))
return 1;
else
return 0;
}

Friday, July 30, 2010

PHP Allowed Memory Size Exchausted Fatal Error

A functional PHP script returns the following error either on the web page
or in Apache error log file when it exchausted and used up the default memory requirement of 8 MB memory allocation:

PHP Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to allocate … bytes) in …

The error normally occurs when PHP tries to process a big database records or when importing or exporting. To solve the error, there are two resolutions. One is to change and increase the memory limit of the particular PHP script by adding or including an additional line at the top of the script:

ini_set(“memory_limit”,”16M”);

You can assign the memory limit to any amount you like by changing the 16M to other number, such as 12M or 24M. 16M will set the memory limit to 16 megabytes. If this doesn’t work and the PHP error still appearing, increase the memory limit until the PHP scripts running perfectly or the limit of your system
hardware.

To change the memory allocation limit permanently for all PHP scripts running on the server
, modify the PHP.INI configuration file of the server (location depending on your OS and installation method). Search for memory_limit after opening the file in an editor. If the memory_limit doesn’t exist, add the following line. If it’s there, modify the value of the memory_limit:

memory_limit = 12M

The 12M sets the limit to 12 megabytes (12582912 bytes). Change to the value you desirable.

An alternative way is to modify your PHP scripts that generate the error for more efficiency and better data handling.

Friday, July 9, 2010

MYSQL Statements and clauses and PHP and Perl API Function

MYSQL Statements and clauses
ALTER DATABASE

ALTER TABLE

ALTER VIEW

ANALYZE TABLE

BACKUP TABLE

CACHE INDEX

CHANGE MASTER TO

CHECK TABLE

CHECKSUM TABLE

COMMIT

CREATE DATABASE

CREATE INDEX

CREATE TABLE

CREATE VIEW

DELETE

DESCRIBE

DO

DROP DATABASE

DROP INDEX

DROP TABLE

DROP USER

DROP VIEW

EXPLAIN

FLUSH

GRANT

HANDLER

INSERT

JOIN

KILL

LOAD DATA FROM MASTER

LOAD DATA INFILE

LOAD INDEX INTO CACHE

LOAD TABLE...FROM MASTER

LOCK TABLES

OPTIMIZE TABLE

PURGE MASTER LOGS

RENAME TABLE

REPAIR TABLE

REPLACE

RESET

RESET MASTER

RESET SLAVE

RESTORE TABLE

REVOKE

ROLLBACK

ROLLBACK TO SAVEPOINT

SAVEPOINT

SELECT

SET

SET PASSWORD

SET SQL_LOG_BIN

SET TRANSACTION

SHOW BINLOG EVENTS

SHOW CHARACTER SET

SHOW COLLATION

SHOW COLUMNS

SHOW CREATE DATABASE

SHOW CREATE TABLE

SHOW CREATE VIEW

SHOW DATABASES

SHOW ENGINES

SHOW ERRORS

SHOW GRANTS

SHOW INDEX

SHOW INNODB STATUS

SHOW LOGS

SHOW MASTER LOGS

SHOW MASTER STATUS

SHOW PRIVILEGES

SHOW PROCESSLIST

SHOW SLAVE HOSTS

SHOW SLAVE STATUS

SHOW STATUS

SHOW TABLE STATUS

SHOW TABLES

SHOW VARIABLES

SHOW WARNINGS

START SLAVE

START TRANSACTION

STOP SLAVE

TRUNCATE TABLE

UNION

UNLOCK TABLES

USE

String Functions
AES_DECRYPT

AES_ENCRYPT

ASCII

BIN

BINARY

BIT_LENGTH

CHAR

CHAR_LENGTH

CHARACTER_LENGTH

COMPRESS

CONCAT

CONCAT_WS

CONV

DECODE

DES_DECRYPT

DES_ENCRYPT

ELT

ENCODE

ENCRYPT

EXPORT_SET

FIELD

FIND_IN_SET

HEX

INET_ATON

INET_NTOA

INSERT

INSTR

LCASE

LEFT

LENGTH

LOAD_FILE

LOCATE

LOWER

LPAD

LTRIM

MAKE_SET

MATCH AGAINST

MD5

MID

OCT

OCTET_LENGTH

OLD_PASSWORD

ORD

PASSWORD

POSITION

QUOTE

REPEAT

REPLACE

REVERSE

RIGHT

RPAD

RTRIM

SHA

SHA1

SOUNDEX

SPACE

STRCMP

SUBSTRING

SUBSTRING_INDEX

TRIM

UCASE

UNCOMPRESS

UNCOMPRESSED_LENGTH

UNHEX

UPPER

Date and Time Functions
ADDDATE

ADDTIME

CONVERT_TZ

CURDATE

CURRENT_DATE

CURRENT_TIME

CURRENT_TIMESTAMP

CURTIME

DATE

DATE_ADD

DATE_FORMAT

DATE_SUB

DATEDIFF

DAY

DAYNAME

DAYOFMONTH

DAYOFWEEK

DAYOFYEAR

EXTRACT

FROM_DAYS

FROM_UNIXTIME

GET_FORMAT

HOUR

LAST_DAY

LOCALTIME

LOCALTIMESTAMP

MAKEDATE

MAKETIME

MICROSECOND

MINUTE

MONTH

MONTHNAME

NOW

PERIOD_ADD

PERIOD_DIFF

QUARTER

SEC_TO_TIME

SECOND

STR_TO_DATE

SUBDATE

SUBTIME

SYSDATE

TIME

TIMEDIFF

TIMESTAMP

TIMESTAMPDIFF

TIMESTAMPADD

TIME_FORMAT

TIME_TO_SEC

TO_DAYS

UNIX_TIMESTAMP

UTC_DATE

UTC_TIME

UTC_TIMESTAMP

WEEK

WEEKDAY

WEEKOFYEAR

YEAR

YEARWEEK

Mathematical and Aggregate Functions
ABS

ACOS

ASIN

ATAN

ATAN2

AVG

BIT_AND

BIT_OR

BIT_XOR

CEIL

CEILING

COS

COT

COUNT

CRC32

DEGREES

EXP

FLOOR

FORMAT

GREATEST

GROUP_CONCAT

LEAST

LN

LOG

LOG2

LOG10

MAX

MIN

MOD

PI

POW

POWER

RADIANS

RAND

ROUND

SIGN

SIN

SQRT

STD

STDDEV

SUM

TAN

TRUNCATE

VARIANCE

Flow Control Functions
CASE

IF

IFNULL

NULLIF

Command-Line Utilities
comp_err

isamchk

make_binary_distribution

msql2mysql

my_print_defaults

myisamchk

myisamlog

myisampack

mysqlaccess

mysqladmin

mysqlbinlog

mysqlbug

mysqlcheck

mysqldump

mysqldumpslow

mysqlhotcopy

mysqlimport

mysqlshow

perror

Perl API - using functions and methods built into the Perl DBI with MySQL
available_drivers

begin_work

bind_col

bind_columns

bind_param

bind_param_array

bind_param_inout

can

clone

column_info

commit

connect

connect_cached

data_sources

disconnect

do

dump_results

err

errstr

execute

execute_array

execute_for_fetch

fetch

fetchall_arrayref

fetchall_hashref

fetchrow_array

fetchrow_arrayref

fetchrow_hashref

finish

foreign_key_info

func

get_info

installed_versions


last_insert_id

looks_like_number

neat

neat_list

parse_dsn

parse_trace_flag

parse_trace_flags

ping

prepare

prepare_cached

primary_key

primary_key_info

quote

quote_identifier

rollback

rows

selectall_arrayref

selectall_hashref

selectcol_arrayref

selectrow_array

selectrow_arrayref

selectrow_hashref

set_err

state

table_info

table_info_all

tables

trace

trace_msg

type_info

type_info_all

Attributes for Handles

PHP API - using functions built into PHP with MySQL
mysql_affected_rows

mysql_change_user

mysql_client_encoding

mysql_close

mysql_connect

mysql_create_db

mysql_data_seek

mysql_db_name

mysql_db_query

mysql_drop_db

mysql_errno

mysql_error

mysql_escape_string

mysql_fetch_array

mysql_fetch_assoc

mysql_fetch_field

mysql_fetch_lengths

mysql_fetch_object

mysql_fetch_row

mysql_field_flags

mysql_field_len

mysql_field_name

mysql_field_seek

mysql_field_table

mysql_field_type

mysql_free_result

mysql_get_client_info

mysql_get_host_info

mysql_get_proto_info

mysql_get_server_info

mysql_info

mysql_insert_id

mysql_list_dbs

mysql_list_fields

mysql_list_processes

mysql_list_tables

mysql_num_fields

mysql_num_rows

mysql_pconnect

mysql_ping

mysql_query

mysql_real_escape_string

mysql_result

mysql_select_db

mysql_stat

mysql_tablename

mysql_thread_id

mysql_unbuffered_query

Tuesday, July 6, 2010

Random Password Generation in PHP

PHP
// just so we know it is brokenerror_reporting(E_ALL);
/*

*

* string passType can be alpha, numeric, or alphanumeric defaults to alphanumeric

* int $length is the length of the password, defaults to eight

*

*/
class randomPassword{

function 
__construct($passType='alphanumeric'$length=8$rangeLength=9){
  
$this->setLength($length);
  
$this->setRangeLength($rangeLength);
  
$this->passType $this->setPassType($passType);
}

function 
setRangeLength($rangeLength){
  
$this->rangeLength=$rangeLength;
}
// set the length of the passwordprivate function setLength($length){
  
$this->length=$length;
}

// set the type of passwordprivate function setPassType($passType){
  return 
$passType.'Chars';
}
// return an array of numbersprivate function numericChars(){
  return 
range(0$this->rangeLength);
}
// return an array of charsprivate function alphaChars(){
  return 
range('a''z');
}
// return an array of alphanumeric charsprivate function alphaNumericChars(){
  return 
array_merge($this->numericChars(), $this->alphaChars());
}
// return a string of charsprivate function makeString(){
  
// here we set the function to call based on the password type
  
$funcName $this->passType;
  return 
implode($this->$funcName());
}
// shuffle the chars and return $length of charspublic function makePassword(){
  return 
substr(str_shuffle($this->makeString()), 1$this->length);
}

// end class

  
function randomPassword($length) {
  
// create an array of chars to use as password
  
$chars implode(array_merge(range(0,9), range('a''z')));

  
// randomly snarf $length number of array keys
  
return substr(str_shuffle($chars), 1$length);

}
  echo 
randomPassword(8).'';

try
    {
    
$obj = new randomPassword('alphanumeric'16100);
    echo 
$obj->makePassword().'';
    }
catch(
Exception $ex)
    {
    echo 
$ex->getMessage();
    }
?>

GeoIP Functions


Introduction
The GeoIP extension allows you to find the location of an IP address. City, State, Country, Longitude, Latitude, and other information as all, such as ISP and connection type can be obtained with the help of GeoIP.

Requirements
This extension requires the GeoIP C library version 1.4.0 or higher to be installed. You can grab the latest version from http://www.maxmind.com/app/c and compile it yourself.
By default, you will only have access to the Free GeoIP Country or GeoLite City databases. While this module can work with other types of database, you must buy a commercial license from Maxmind.

Installation
This PECL extension is not bundled with PHP.
Information for installing this PECL extension may be found in the manual chapter titled Installation of PECL extensions. Additional information such as new releases, downloads, source files, maintainer information, and a CHANGELOG, can be located here: http://pecl.php.net/package/geoip.

Resource Types
This extension has no resource types defined.

Predefined Constants
The constants below are defined by this extension, and will only be available when the extension has either been compiled into PHP or dynamically loaded at runtime.

GEOIP_COUNTRY_EDITION (integer)
GEOIP_REGION_EDITION_REV0 (integer)
GEOIP_CITY_EDITION_REV0 (integer)
GEOIP_ORG_EDITION (integer)
GEOIP_ISP_EDITION (integer)
GEOIP_CITY_EDITION_REV1 (integer)
GEOIP_REGION_EDITION_REV1 (integer)
GEOIP_PROXY_EDITION (integer)
GEOIP_ASNUM_EDITION (integer)
GEOIP_NETSPEED_EDITION (integer)
GEOIP_DOMAIN_EDITION (integer)

The following constants are for net speed:
GEOIP_UNKNOWN_SPEED (integer)
GEOIP_DIALUP_SPEED (integer)
GEOIP_CABLEDSL_SPEED (integer)
GEOIP_CORPORATE_SPEED (integer)

Methods
geoip_country_code_by_name -- Get the two letter country code
geoip_country_code3_by_name -- Get the three letter country code
geoip_country_name_by_name -- Get the full country name
geoip_database_info -- Get GeoIP Database information
geoip_id_by_name -- Get the Internet connection speed
geoip_org_by_name -- Get the organization name
geoip_record_by_name -- Returns the detailed City information found in the GeoIP Database
geoip_region_by_name -- Get the country code and region


Tuesday, April 13, 2010

Identifying Serialized String

 Following function return true if the given string is Serialized.



function is_serialized( $data ) {
    // if it isn't a string, it isn't serialized
    if ( !is_string( $data ) )
        return false;
    $data = trim( $data );
    if ( 'N;' == $data )
        return true;
    if ( !preg_match( '/^([adObis]):/', $data, $badions ) )
        return false;
    switch ( $badions[1] ) {
        case 'a' :
        case 'O' :
        case 's' :
            if ( preg_match( "/^{$badions[1]}:[0-9]+:.*[;}]\$/s", $data ) )
                return true;
            break;
        case 'b' :
        case 'i' :
        case 'd' :
            if ( preg_match( "/^{$badions[1]}:[0-9.E-]+;\$/", $data ) )
                return true;
            break;
    }
    return false;
}

Tuesday, April 6, 2010

Remove the special character from string.

Use the following function to remove special character which are not working in URL generation.

$keyword is string having special characters.

{
----
$str = strtolower(preg_replace(array('/[^a-zA-Z0-9 -]/', '/[ -]+/', '/^-|-$/'), array('', '-', ''), remove_accent(trim($keyword))));

----
}


 function remove_accent($str)
{
  $a = array('À', 'Á', 'Â', 'Ã', 'Ä', 'Å', 'Æ', 'Ç', 'È', 'É', 'Ê', 'Ë', 'Ì', 'Í', 'Î', 'Ï', 'Ð', 'Ñ', 'Ò', 'Ó', 'Ô', 'Õ', 'Ö', 'Ø', 'Ù', 'Ú', 'Û', 'Ü', 'Ý', 'ß', 'à', 'á', 'â', 'ã', 'ä', 'å', 'æ', 'ç', 'è', 'é', 'ê', 'ë', 'ì', 'í', 'î', 'ï', 'ñ', 'ò', 'ó', 'ô', 'õ', 'ö', 'ø', 'ù', 'ú', 'û', 'ü', 'ý', 'ÿ', 'A', 'a', 'A', 'a', 'A', 'a', 'C', 'c', 'C', 'c', 'C', 'c', 'C', 'c', 'D', 'd', 'Ð', 'd', 'E', 'e', 'E', 'e', 'E', 'e', 'E', 'e', 'E', 'e', 'G', 'g', 'G', 'g', 'G', 'g', 'G', 'g', 'H', 'h', 'H', 'h', 'I', 'i', 'I', 'i', 'I', 'i', 'I', 'i', 'I', 'i', '?', '?', 'J', 'j', 'K', 'k', 'L', 'l', 'L', 'l', 'L', 'l', '?', '?', 'L', 'l', 'N', 'n', 'N', 'n', 'N', 'n', '?', 'O', 'o', 'O', 'o', 'O', 'o', 'Œ', 'œ', 'R', 'r', 'R', 'r', 'R', 'r', 'S', 's', 'S', 's', 'S', 's', 'Š', 'š', 'T', 't', 'T', 't', 'T', 't', 'U', 'u', 'U', 'u', 'U', 'u', 'U', 'u', 'U', 'u', 'U', 'u', 'W', 'w', 'Y', 'y', 'Ÿ', 'Z', 'z', 'Z', 'z', 'Ž', 'ž', '?', 'ƒ', 'O', 'o', 'U', 'u', 'A', 'a', 'I', 'i', 'O', 'o', 'U', 'u', 'U', 'u', 'U', 'u', 'U', 'u', 'U', 'u', '?', '?', '?', '?', '?', '?');
 $b = array('A', 'A', 'A', 'A', 'A', 'A', 'AE', 'C', 'E', 'E', 'E', 'E', 'I', 'I', 'I', 'I', 'D', 'N', 'O', 'O', 'O', 'O', 'O', 'O', 'U', 'U', 'U', 'U', 'Y', 's', 'a', 'a', 'a', 'a', 'a', 'a', 'ae', 'c', 'e', 'e', 'e', 'e', 'i', 'i', 'i', 'i', 'n', 'o', 'o', 'o', 'o', 'o', 'o', 'u', 'u', 'u', 'u', 'y', 'y', 'A', 'a', 'A', 'a', 'A', 'a', 'C', 'c', 'C', 'c', 'C', 'c', 'C', 'c', 'D', 'd', 'D', 'd', 'E', 'e', 'E', 'e', 'E', 'e', 'E', 'e', 'E', 'e', 'G', 'g', 'G', 'g', 'G', 'g', 'G', 'g', 'H', 'h', 'H', 'h', 'I', 'i', 'I', 'i', 'I', 'i', 'I', 'i', 'I', 'i', 'IJ', 'ij', 'J', 'j', 'K', 'k', 'L', 'l', 'L', 'l', 'L', 'l', 'L', 'l', 'l', 'l', 'N', 'n', 'N', 'n', 'N', 'n', 'n', 'O', 'o', 'O', 'o', 'O', 'o', 'OE', 'oe', 'R', 'r', 'R', 'r', 'R', 'r', 'S', 's', 'S', 's', 'S', 's', 'S', 's', 'T', 't', 'T', 't', 'T', 't', 'U', 'u', 'U', 'u', 'U', 'u', 'U', 'u', 'U', 'u', 'U', 'u', 'W', 'w', 'Y', 'y', 'Y', 'Z', 'z', 'Z', 'z', 'Z', 'z', 's', 'f', 'O', 'o', 'U', 'u', 'A', 'a', 'I', 'i', 'O', 'o', 'U', 'u', 'U', 'u', 'U', 'u', 'U', 'u', 'U', 'u', 'A', 'a', 'AE', 'ae', 'O', 'o');
 return str_replace($a, $b, $str);
}


This will really helps you to remove special character for string.

Wednesday, March 31, 2010

This is how we can use Control Flow Functions in Mysql.

Syntax: 


CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END


CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

Following is the Example for Switch Case query =>

SELECT u.`fname` as 'First Name' , u.`lname` as 'Last Name', u.`email` as 'Email', u.`tel_no` as 'Phone', u.country as 'Country', ug.start_date as 'Start Date', ug.end_date as 'End Date', (


CASE ug.end_date
WHEN '0000-00-00 00:00:00'
THEN 'active'
ELSE IF (
DATEDIFF(
now( ) , ug.end_date
) >0, 'inactive', 'active'
)
END
) AS
Status , ull.last_login_date as 'Last Login Date', ug.name AS
Product FROM `users` u, (



SELECT user_id, user_type_id, start_date, end_date, user_types.name
FROM user_groups, user_types
WHERE user_type_id
IN ( 4 )

AND user_groups.user_type_id = user_types.id
GROUP BY user_id
ORDER BY user_type_id DESC
)ug
LEFT JOIN (

SELECT user_id, max( login_time ) AS last_login_date
FROM user_login_log
GROUP BY user_id
) AS ull ON ug.user_id = ull.user_id
WHERE ug.user_id = u.id



Syntax:

mysql> SELECT IF(1>2,2,3);
-> 3
mysql>
SELECT IF(1<2,'yes','no');
-> 'yes'
mysql>
SELECT IF(STRCMP('test','test1'),'no','yes');



Following is the Example for IF-ELSE query =>


SELECT u.`id` AS 'User_Id', u.`fname` AS 'First_Name', u.`lname` AS 'Last_Name', u.`email` AS 'Email', domains.blog_id, domains.site, domains.status
FROM `users` AS u, `user_system` AS us, (


SELECT wp_blogs.blog_id, wp_blogs.domain AS sub_domain, IF(wp_blogs.public <> 0, 'active', 'inactive') AS status, wp_dm.domain AS site
FROM im500_live_wpmu.`wp_blogs` AS wp_blogs, im500_live_wpmu.`wp_domain_mapping` AS wp_dm
WHERE wp_blogs.blog_id = wp_dm.blog_id
AND wp_blogs.public = '1'
) AS domains


WHERE u.id = us.user_id
AND domains.sub_domain LIKE concat( u.username, '.%' )
AND us.system_id =3





For more details: http://dev.mysql.com/doc/refman/5.5/en/control-flow-functions.html