Quick and Dirty Emoticons - Quick and Dirty Emoticons

(Page 2 of 3 )

Before we get to the coding, I'll explain how to create the MySQL table, and also how we can go about inserting the emoticons into the database.

Run the following queries in the MySQL console application:

CREATE DATABASE mydatabase;
USE mydatabase;

CREATE TABLE emoticons (
emote VARCHAR(10) NOT NULL,
image VARCHAR(30) NOT NULL
);

The emote-column will hold the text, while the image column will contain the filename of the image that will replace every instance of emote in a string of text.

Now that we've created the MySQL table, it's time to insert some rows into it.

Inserting Emoticons Into The Database
The following query is just an example. You may insert as many emoticons as you'd like:

INSERT INTO emoticons (emote, image)
VALUES (":)", "icon_smile.gif");

INSERT INTO emoticons (emote, image)
VALUES (";)", "icon_wink.gif");

INSERT INTO emoticons (emote, image)
VALUES (":(", "icon_sad.gif");

I have provided a few emoticons below. Right click on the images, and select "Save Picture As". Save them in a folder named "images", "emoticons" or something similar.

icon_smile.gif
icon_wink.gif
icon_sad.gif

You may of course create more emoticons, and insert them in your database if you'd like to.

Putting It All Together
Just having an emoticon table won't do us any good. This is where PHP comes into play. Place the following code in a .php file:

<?PHP

// Configuration part
$dbhost = "localhost"; // Database host
$dbname = "mydatabase"; // Database name
$dbuser = "dbuser"; // Database username
$dbpass = "dbpass"; // Database password
$path = "images"; // Path to the directory where the emoticons are

// Connect to database
$db = mysql_connect($dbhost, $dbuser, $dbpass) or die("Error: Couldn't connect to database");
mysql_select_db($dbname, $db) or die("Error: Couldn't select database.");

// Query the database, and assign the result-set to $result
$query = "SELECT emote, image FROM emoticons";
$result = mysql_query($query);

// Loop through the results, and place the results in two arrays
while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$emotes[] = $row['emote'];
$images[] = "<img src='" . $path . "/" . $row['image'] . "'>";
}

$text = "<strong>Emoticons</strong><br>
:) ;) :( :P<br><br>
Neat? :)";

// The line below replaces the emotes with the images
echo str_replace($emotes, $images, $text);

?>

Let's look at some parts of the code that you may be unfamiliar with:

// Loop through the results, and place the results in two arrays
while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$emotes[] = $row['emote'];
$images[] = "<img src='" . $path . "/" . $row['image'] . "'>";
}

The while loop iterates through the MySQL result attained from the query and places it into two different arrays: one array for each of the selected columns.

PHP will automatically increment the index of the array when you don't put anything between the brackets of the array you're assigning the value to. 

// The line below replaces the emotes with the images
echo str_replace($emotes, $images, $text);

Let me explain how the str_replace() function works in the code above.

If you feed the first two attributes of the function with arrays, then it will iterate through the values of the array. Take a look at the example below:

$search = array(0 => a, 1 => b, 2 => c);
$replace = array(0 => c, 1 => b, 2 => a);
str_replace($search, $replace, "a b c");

$search[0] would be replaced by $replace[0], $search[1] would be replaced by $replace[1], and so on.. After processing the final value would be "b c a". This function is useful in situations where you have two related arrays that require swapping or replacing of common data.

Next: Conclusion >>
 

More MySQL Articles
More By Havard Lindset

Comments On: Quick and Dirty Emoticons

>>> Be the FIRST to comment on this article!