November | 2010 | sam j levy

If you are working with MySQL database (UTF-8) and handle input that is being copied from other sources- you may run into issues with special characters such as ellipses, long dashes, and special quotes.

Inserting these characters without addressing them can lead to nasty results later on, like this: â€¨

Here is a useful function that replaces the most common special characters with their UTF equivalents

// replace special characters
function replace_schars($str)
{
     $badwordchars=array(
         "\xe2\x80\x98", // left single quote
         "\xe2\x80\x99", // right single quote
         "\xe2\x80\x9c", // left double quote
         "\xe2\x80\x9d", // right double quote
         "\xe2\x80\x93", // long dash
         "\xe2\x80\x94", // long dash
         "\xe2\x80\xa6" // elipses
     );
     $fixedwordchars=array(
         "'",
         "'",
         '"',
         '"',
         '-',
         '-',
         '...'
     );
     return (str_replace($badwordchars,$fixedwordchars, $str));
}

// replace special characters

function replace_schars($str)

{

$badwordchars=array(

"\xe2\x80\x98", // left single quote

"\xe2\x80\x99", // right single quote

"\xe2\x80\x9c", // left double quote

"\xe2\x80\x9d", // right double quote

"\xe2\x80\x93", // long dash

"\xe2\x80\x94", // long dash

"\xe2\x80\xa6" // elipses

);

$fixedwordchars=array(

"'",

'"',

'-',

'...'

);

return (str_replace($badwordchars,$fixedwordchars, $str));

}

I needed a PHP function that would convert URL’s echo’d from MySQL into clickable links. The best function I could find comes from WordPress code, and works beautifully on its own. Additionally supports e-mail and FTP.

<?php
function _make_url_clickable_cb($matches) {
    $ret = '';
    $url = $matches[2];
 
    if ( empty($url) )
        return $matches[0];
    // removed trailing [.,;:] from URL
    if ( in_array(substr($url, -1), array('.', ',', ';', ':')) === true ) {
        $ret = substr($url, -1);
        $url = substr($url, 0, strlen($url)-1);
    }
    return $matches[1] . "<a href=\"$url\" rel=\"nofollow\" target=\"_blank\">$url</a>" . $ret;
}
 
function _make_web_ftp_clickable_cb($matches) {
    $ret = '';
    $dest = $matches[2];
    $dest = 'http://' . $dest;
 
    if ( empty($dest) )
        return $matches[0];
    // removed trailing [,;:] from URL
    if ( in_array(substr($dest, -1), array('.', ',', ';', ':')) === true ) {
        $ret = substr($dest, -1);
        $dest = substr($dest, 0, strlen($dest)-1);
    }
    return $matches[1] . "<a href=\"$dest\" rel=\"nofollow\" target=\"_blank\">$dest</a>" . $ret;
}
 
function _make_email_clickable_cb($matches) {
    $email = $matches[2] . '@' . $matches[3];
    return $matches[1] . "<a href=\"mailto:$email\">$email</a>";
}
 
function make_clickable($ret) {
    $ret = ' ' . $ret;
    // in testing, using arrays here was found to be faster
    $ret = preg_replace_callback('#([\s>])([\w]+?://[\w\\x80-\\xff\#$%&amp;~/.\-;:=,?@\[\]+]*)#is', '_make_url_clickable_cb', $ret);
    $ret = preg_replace_callback('#([\s>])((www|ftp)\.[\w\\x80-\\xff\#$%&amp;~/.\-;:=,?@\[\]+]*)#is', '_make_web_ftp_clickable_cb', $ret);
    $ret = preg_replace_callback('#([\s>])([.0-9a-z_+-]+)@(([0-9a-z-]+\.)+[0-9a-z]{2,})#i', '_make_email_clickable_cb', $ret);
 
    // this one is not in an array because we need it to run last, for cleanup of accidental links within links
    $ret = preg_replace("#(<a( [^>]+?>|>))<a [^>]+?>([^>]+?)</a></a>#i", "$1$3</a>", $ret);
    $ret = trim($ret);
    return $ret;
}
?>

<?php

function _make_url_clickable_cb($matches) {

$ret = '';

$url = $matches[2];

if ( empty($url) )

return $matches[0];

// removed trailing [.,;:] from URL

if ( in_array(substr($url, -1), array('.', ',', ';', ':')) === true ) {

$ret = substr($url, -1);

$url = substr($url, 0, strlen($url)-1);

}

return $matches[1] . "<a href=\"$url\" rel=\"nofollow\" target=\"_blank\">$url</a>" . $ret;

}

function _make_web_ftp_clickable_cb($matches) {

$ret = '';

$dest = $matches[2];

$dest = 'http://' . $dest;

if ( empty($dest) )

return $matches[0];

// removed trailing [,;:] from URL

if ( in_array(substr($dest, -1), array('.', ',', ';', ':')) === true ) {

$ret = substr($dest, -1);

$dest = substr($dest, 0, strlen($dest)-1);

}

return $matches[1] . "<a href=\"$dest\" rel=\"nofollow\" target=\"_blank\">$dest</a>" . $ret;

}

function _make_email_clickable_cb($matches) {

$email = $matches[2] . '@' . $matches[3];

return $matches[1] . "<a href=\"mailto:$email\">$email</a>";

}

function make_clickable($ret) {

$ret = ' ' . $ret;

// in testing, using arrays here was found to be faster

$ret = preg_replace_callback('#([\s>])([\w]+?://[\w\\x80-\\xff\#$%&~/.\-;:=,?@\[\]+]*)#is', '_make_url_clickable_cb', $ret);

$ret = preg_replace_callback('#([\s>])((www|ftp)\.[\w\\x80-\\xff\#$%&~/.\-;:=,?@\[\]+]*)#is', '_make_web_ftp_clickable_cb', $ret);

$ret = preg_replace_callback('#([\s>])([.0-9a-z_+-]+)@(([0-9a-z-]+\.)+[0-9a-z]{2,})#i', '_make_email_clickable_cb', $ret);

// this one is not in an array because we need it to run last, for cleanup of accidental links within links

$ret = preg_replace("#(<a( [^>]+?>|>))<a [^>]+?>([^>]+?)</a></a>#i", "$1$3</a>", $ret);

$ret = trim($ret);

return $ret;

}

sam j levy

Monthly Archives: November 2010

PHP Replace special characters before MySQL insertion

PHP Make URL into Hyperlink