Using MySql Update and PHP for smilies

Question

I am fairly new to PHP and very new to MySql. I found this line of code online and am using it in a function that fetches all of the messages in a conversation (on my messaging system using php/mysql):

        $sql = "UPDATE `conversations_messages` SET message_text = REPLACE(message_text, '\r\n', '<br />')";
mysql_query($sql);

Obviously, this is so that line breaks appear as <br />

My question is this: (how) could I incorporate this code to set :) into an image of a smiley face?

Please help me. Thank you in advance.

Answer 1

This code is awful. You should not execute it.

The database should always contain the data the user entered. No HTML code! If one deviates from this rule, one should know exactly why and when.

You should transform characters into smileys or transform \n into <br> while outputting the text instead. For example like this:

$text = htmlspecialchars($text);
$text = str_replace("\n", "<br>", $text);
$text = str_replace(":)", '<img src="smiley.png" />', $text);
echo $text;

while $text is filled previously by a select statement.

Answer 2

Using UTF-8 character directly

You may use (assuming UTF-8) directly "smiley face character", its hex sequence is 0xE2 0x98 0xBA (e298ba):

mysql> SELECT CHAR(0xe298ba USING UTF8);
+---------------------------+
| CHAR(0xe298ba USING UTF8) |
+---------------------------+
| ☺                         |
+---------------------------+
1 row in set (0.00 sec)

I do NOT recommend doing this (but you it's possible), if you really want to store modified data in the database rather use HTML entry &#x263a (which won't put any special characters into database).

UPDATE `conversations_messages` 
    SET message_text = REPLACE(message_text, ':)', '☺')

Of course this assumes you don't do any html filtering (like htmlspecialchars()) after loading data from database.

But I wouldn't store modified data in the database (at least not without storing original version), imagine scenario when you would want to add new smiley face after "processing" every message you already have (or worse remove it)...

If you (for some reason) really have to replace it as a par of "model" rather use:

SELECT REPLACE(`message_text`, ':)', '☺') AS message_text

Or rather build stored function which will do multiple steps (replace newlines, smiley faces and so on).

SELECT MyProcessingFunction(`message_text`) AS message_text

Much more suitable way of doing this will be "decorating text" when it's printed out

function process_text($text)
{
    $text = htmlspecialchars($text);
    $text = str_replace( '\n', '<br />', $text);
    $text = str_replace( ':)', '&@x263a;', $text);
    return $text;
}

$message_text = do_some_magic();
echo process_text($message_text);

This way you still have original available and can change formatting on the fly without corrupting old data.

Using <img /> tag

Much more common way (at least in my experience) is using images (small images):

function process_text($text)
{
    $text = htmlspecialchars($text);
    $text = str_replace( '\n', '<br />', $text);
    $text = str_replace( ':)', '<img class="smiley" src="..." alt=":)" />', $text);
    return $text;
}

This will give you much more freedom (use your own smileys, style them a bit) when doing things.

Another tips

You can use nl2br() instead of replacing newlines manually (but last time I've worked with it, it wasn't XHTML compatible... which it is since 4.0.5).

Also sanitize user inputs (database input - prepared statements and parameters; output - htmlspecialchars() or equivalent) to prevent users from XSS or just screwing up your page.