Dynamic Signature That Connects To IPB MySQL Database

Now, I'm going to show you how to make something like this:

-- Yes, that is an image! It displays data extracted from a database.

I'm going to show you how his works for an IPB board, but it can be easilly edited for any other forum software.

We will be using PHP to connect to the SQL and then .htaccess to change the PHP into a .gif!

To start this off, we're going to create an image. I wont go into designing it...this is a PHP tutorial remember!
Make it 400px wide and 80px high. Save it as sig.gif.

---

Before I begin on the code, you need to know your SQL prefix. By default this is the name that you use to log into cPanel with. It may be different though so please contact your host if you are unsure.

I have used PREFIX_ to show you where to use this.

---

The code! Open NotePad (or whatever you use) and begin with this:

<?
$db_host = "localhost";
$db_user = "PREFIX_USER";
$db_pass = "DBPASSWORD";
$db_name = "PREFIX_DB";
$db = mysql_connect($db_host,$db_user,$db_pass);
mysql_select_db ($db_name) or die ("Cannot Connect To Database");

Make sure that $db_host,$db_user and $db_pass are all filled in correctly.

---

Now we're going to select queries from the MySQL Database.

query = "SELECT * FROM PREFIX_topics";
$one = mysql_query($query);
$two = mysql_num_rows($one);
$topics = $two. " Topics,";

Edit PREFIX_topics ;)

$query = "SELECT * FROM PREFIX_posts";
$one = mysql_query($query);
$two = mysql_num_rows($one);
$posts = $two. " Posts,";

Edit PREFIX_posts ;)

$query = "SELECT * FROM PREFIX_members";
$one = mysql_query($query);
$two = mysql_num_rows($one);
$users = $two. " Users";

Edit PREFIX_members ;)

---

Now that we have selected queries from the SQL...we're going to output them in the image.

header("Content-type: image/gif");
$calc = $topics;
$im = imagecreatefromgif("sig.gif");
$color = imagecolorallocate($im, 0,12,47);
$txt = (imagesx($im) - 32 * strlen($calc)) / 2;
$txt1 = (imagesx($im) - 17 * strlen($calc)) / 2;
$txt2 = (imagesx($im) - 2 * strlen($calc)) / 2;
imagestring($im, 3, $txt, 55, $topics, $color);
imagestring($im, 3, $txt1, 55, $posts, $color);
imagestring($im, 3, $txt2, 55, $users, $color);
imagegif($im);
imagedestroy($im);
?>

Explinations:
header("Content-type: image/gif"); - This tells the browser that the file type is a gif image.

$im = imagecreatefromgif("sig.gif"); - This "calls" the gif image we created earlyer.

$color = imagecolorallocate($im, 0,12,47); - Sets the text colour - edit 0,12,47, those are RGB attributes.

$txt = (imagesx($im) - 32 * strlen($calc)) / 2;
$txt1 = (imagesx($im) - 17 * strlen($calc)) / 2;
$txt2 = (imagesx($im) - 2 * strlen($calc)) / 2;
imagestring($im, 3, $txt, 55, $topics, $color);
imagestring($im, 3, $txt1, 55, $posts, $color);
imagestring($im, 3, $txt2, 55, $users, $color);

Right, this section is where the text is all written...
On the top 3 lines, the 32 *, 17 * and 2 * are the width possitions of the text. Those are aligned to my image, so edit them apporpiatly for your image.
ame goes for the bottom 3 lines, the 55's are the height possitions of the text, edit them if needed too.
Also in the bottom 3 lines is the didgit 3 - this refers to the font size.

---

Here's the full code:

<?
$db_host = "localhost";
$db_user = "PREFIX_USER";
$db_pass = "DBPASSWORD";
$db_name = "PREFIX_DB";
$db = mysql_connect($db_host,$db_user,$db_pass);
mysql_select_db ($db_name) or die ("Cannot Connect To Database");

$query = "SELECT * FROM PREFIX_topics";
$one = mysql_query($query);
$two = mysql_num_rows($one);
$topics = $two. " Topics,";

$query = "SELECT * FROM PREFIX_posts";
$one = mysql_query($query);
$two = mysql_num_rows($one);
$posts = $two. " Posts,";

$query = "SELECT * FROM PREFIX_members";
$one = mysql_query($query);
$two = mysql_num_rows($one);
$users = $two. " Users";

header("Content-type: image/gif");
$calc = $topics;
$im = imagecreatefromgif("sig.gif");
$color = imagecolorallocate($im, 0,12,47);
$txt = (imagesx($im) - 32 * strlen($calc)) / 2;
$txt1 = (imagesx($im) - 17 * strlen($calc)) / 2;
$txt2 = (imagesx($im) - 2 * strlen($calc)) / 2;
imagestring($im, 3, $txt, 55, $topics, $color);
imagestring($im, 3, $txt1, 55, $posts, $color);
imagestring($im, 3, $txt2, 55, $users, $color);
imagegif($im);
imagedestroy($im);
?>

---

Now, save all that (with relivant changes made) as sig.php. Note that you will only be able to see changes such as text position change online, so please keep fiddleing with it online until it is perfectly aligned.

Upload both the sig.gif and sig.php to your site.

---

We are now going to use .htaccess and mod_rewrite to make sig.php an image.

Open up NotePad and copy this into it:

RewriteEngine on
RewriteRule sig.gif sig.php

sig.php is the file we want to rename and sig.gif is what we want it renamed to.

---

Save this as .htaccess - NOT AS .htaccess.txt!
Then upload it...

Note: Make sure .htaccess doesn't exist already in the directory, if it does then just edit that and add the above code at the bottom of the file and re-upload.

---

We're pretty much done.
Now, visit http://www.yourdomain/sig.gif and it should be an image displaying stats from your IPB forum!