connecting to a mysql database

introduction
a lot of web based applications rely on the use of a database in order to store their data. in this article i will discuss how to connect to a database using php.

the code
connecting to a database is really quite simple in php. you will need to know the following information about the database in order to establish a connection: server, name, username, and password. to make it easy to visualize i prefer to set variables for each and then use the variable names when i make the actual connection call:

<?php
$db_name = "my_database";
$db_serv = "localhost";
$db_user = "my_username";
$db_pass = "my_password";

$db_conn = mysql_connect($db_serv, $db_user, $db_pass);
mysql_connect($db_name, $db_conn);
?>

that’s it! now that you have established a connection you can make queries to the database to recall data:

<?php
$sql = "SELECT * FROM some_table";
$result = mysql_query($sql);
?>

conclusion
its really quite simple and storing information in a database not only makes a site run more smoothly, but it also allows you to create powerful content management options so you can quickly update information on the site without changing anything in the site’s files themselves. this is great for template based sites where the pages stay pretty much the same and the content inside them varies depending on a given combination of variables.

password encryption

introduction
ive worked on a lot of programs and websites that involve requiring the user to login in order to gain access and it occurred to me that there is a useful bit of information that i can share from these experiences. what i am referring to is the encryption process to protect the password of the user. more often than not i use an encryption method called md5. happens to have a built in function that will convert any string into an md5 quite easily. combine that with a login/signup form and a database to store it in, and you’re well on your way to a secure login system.

md5
md5 (message-digest algorithm 5) is a widely used cryptographic hash function with a 128-bit hash value. as an internet standard (rcf 1321), md5 has been employed in a wide variety of security applications, and is also commonly used to check the integrity of files. an md5 hash is typically expressed as a 32 digit hexadecimal number. for example, the md5 of the word ‘the’ would be ’8fc42c6ddf9966db3b09e84365034357′

the code
as i already stated, in php there is a predefined function to convert a string into an md5 hash. its quite simple actually: md5("the"); would be the method used to return the hash stated above. let’s say you created a signup form that looked something like the following:

<form name="signup" method="post" action="">
<input type="text" name="username" />
<input type="password" name="password" />
<input type="submit" name="submit" value="Login" />
</form>

once the user clicks submit php can be used to process the information and store it in a database to be recalled later (assuming we already have a ):

<?php
if ($_POST) {
	$username = $_POST["username"];
	$password = md5($_POST["password"]);

	$sql = "INSERT INTO users (username, password)
		VALUES ('$username','$password')";
	$result = mysql_query($sql);
}
?>

notice that i encrypt the password before storing it into the database. this way if anyone ever gets access to your database, they’ll have a rather difficult time deciphering what the passwords are. to login to the software a similar form would be used however this time, after converting the entered password to an md5 we would do a look up to see if the users exists and if the password is correct:

<?php
if ($_POST) {
	$username = $_POST["username"];
	$password = md5($_POST["password"]);

	$sql = "SELECT * FROM users WHERE username = '$username'";
	$result = mysql_query($sql);

	// make sure there is a user with this username
	if (mysql_num_rows($result) == 0) {
		$msg = "No Such User!";
	// there is a user, now check the password
	} elseif ($password != $row["password"]) {
		$msg = "Incorrect Password!";
	} else {
		$msg = "Logged In";

		// set the user to logged in
		$_SESSION["logged_in"] = true;
	}
}
?>

and there you have it
this is a good way to protect the information between users as well as reassure them that their login information is encrypted. md5 is not the be-all-end-all for encryption methods, and while difficult, it is possible to reverse engineer the hashes, but it is certainly a great first line of defense when creating password protected logins.

php goal thermometer

introduction
you’ve seen them all over the internet: non-profit ‘money raised’ goal thermometers. you know, where the ‘temperature’ is the current amount of money raised. i just got finished constructing one an i thought id share.

gd library
the gd graphics library is a library for dynamically manipulating images. it can create images composed of lines, arcs, text (using program-selected fonts), other images, and multiple colors. version 2.0 adds support for truecolor images, alpha channels, resampling (for smooth resizing of truecolor images), and many other features. gd is extensively used with php and has been included by default as of php 4.3. to create the goal thermometer we are going to utilize the power of this library.

getting started
we are going to be using php to create an image and then reference the php file in the src attribute of an img tag in html. the browser has to know to render the file as an image so we will need to set the header to reflect that:

<?php
header("Content-type: image/jpg");

we will also include some get variables in the query string of the src attribute to tell the php file the size of the goal thermometer so the next step is to grab those values:

$width = $_GET['width'];
$height = $_GET['height'];
$goal = $_GET['goal'];
$current = $_GET['current'];

then we use the information to calculate our current progress towards our goal. notice that i’ve put a check to set the goal progress to 100% if we’ve exceeded it, this way the goal thermometer doesn’t display anything higher than 100%:

if ($current > $goal) {
	$percent= "100%!";
} else {
	$percent = round(($current/$goal)*100) . "%";
}

begin image creation
now we have some data to work with so we can begin to create the image using the gd library. first let’s just create the canvas for the image:

$im = imagecreate($width, $height);

now we can define the colors we will be using for the image as well as the text size:

$background_color = imagecolorallocate($im,200,200,200);
$fill_color = imagecolorallocate($im,255,0,0);
$notch_color = imagecolorallocate($im,255,200,200);
$text_color = imagecolorallocate($im, 0, 0, 0);
$text_size = 5;

the next step is to determine how much fill color to use, or in other words, what ‘temperature’ to set the goal thermometer at. since i’m constructing a horizontal goal thermometer i work with the $width variable:

$fill = (($current/$goal)*width);

once we have our fill amount we need to create a rectangle of that size to mimic the goal thermometers’s mercury:

imagefilledrectangle($im, 0, 0, $fill, $height, $fill_color);

next i will generate the notches along the goal thermometer so someone viewing it can get a bearing on how far along the progress is. for this example i’m going to put a notch every 10% of goal thermometer size:

$inc = $width / 10;
for($i=0;$i<=$width;$i+=$inc){
	if ($i > 0) imageline($im,$i,0,$i,4,$notch+color);
}

to make the goal thermometer easier to read, i want the current progress displayed in the center of the thermometer. so we calculate the dead center of the goal thermometer and set coordinates accordingly (don’t forget to adjust the coordinates relative to the font size):

$text_x = ($width / 2) - 12;
$text_y = ($height / 2) - 7;
imagestring($im, $text_size, $text_x, $text_y,  $percent, $text_color);

display the image
now that php has generated all the pieces of the image all we have left to do is put them together and then free up the memory that was used to generate the image.

imagejpeg($im);
imagedestroy($im);
?>

make sure to use the imagedestroy() whenever creating an image. using php to generate images is easy and powerful but it does use a substantial amount of memory and if you use it too often without freeing the memory (let’s say in an image gallery for example) you may experience server bloat. save the document as thermometer.php.

the only step left is to call the php image from html:

<img src="thermometer.php?width=200&height=15&goal=1000&current=200" />

the above img tag will generate a goal thermometer that’s 200px wide and 15px tall that has currently raised $200 of a $1000 goal. to make it completely dynamic you’d probably want to calculate the current when the pages loads rather than leaving it as a static number.

closing
you can see just how easy it really is to use php and the gd library to create images. there are plenty of practical uses for this other than a simple goal thermometer. one that immediately comes to mind is a captcha device.

php

introduction
php is a scripting language originally designed for producing dynamic web pages. it has evolved to include a command line interface capability and can be used in standalone graphical applications.

while php was originally created by rasmus lerdorf in 1995, the main implementation of php is now produced by the php group and serves as the de facto standard for php as there is no formal specification. php is free software released under the php license, however it is incompatible with the glp due to restrictions on the usage of the term php.

php is a widely-used general-purpose scripting language that is especially suited for web development and can be embedded into html. it generally runs on a web server, taking php code as its input and creating web pages as output. it can be deployed on most web servers and on almost every operating system and platform free of charge. php is installed on more than 20 million websites and 1 million web servers. the most recent major release of php was version 5.2.6 on May 1, 2008.

history
php originally stood for personal home page. it began in 1994 as a set of common gateway interface binaries written in the c programming language by the danish/greenlandic programmer rasmus lerdorf. lerdorf initially created these personal home page tools to replace a small set of perl scripts he had been using to maintain his personal homepage. the tools were used to perform tasks such as displaying his resume and recording how much traffic his page was receiving. he combined these binaries with his form interpreter to create php/fi, which had more functionality. php/fi included a larger implementation for the c programming language and could communicate with databases, enabling the building of simple, dynamic web applications. lerdorf released php publicly on june 8, 1995 to accelerate bug location and improve the code. this release was named php version 2 and already had the basic functionality that php has today. this included perl-like variables, form handling, and the ability to embed html. the syntax was similar to Perl but was more limited, simpler, and less consistent.

zeev suraski and andi gutmans, two israeli developers at the technion iit, rewrote the parser in 1997 and formed the base of php 3, changing the language’s name to the recursive initialism php: hypertext preprocessor. the development team officially released php/fi 2 in n1997 after months of beta testing. afterwards, public testing of php 3 began, and the official launch came in june 1998. suraski and gutmans then started a new rewrite of php’s core, producing the zend engine in 1999. they also founded zend technologies in ramat gan, israel.

on may 22, 2000, php 4, powered by the zend engine 1.0, was released. on July 13, 2004, php 5 was released, powered by the new zend engine II. php 5 included new features such as improved support for object-oriented programming, the php data objects extension (which defines a lightweight and consistent interface for accessing databases), and numerous performance enhancements. the most recent update released by the php group is for the older php version 4 code branch. as of august, 2008 this branch is up to version 4.4.9. php 4 is no longer under development nor will any security updates be released.

in 2008, php 5 became the only stable version under development. late static binding has been missing from php and will be added in version 5.3. php 6 is under development alongside php 5. major changes include the removal of register_globals, magic quotes, and safe mode.

php does not have complete native support for unicode or multibyte strings; unicode support will be included in php 6. many high profile open source projects ceased to support php 4 in new code as of february 5, 2008, due to the gophp5 initiative, provided by a consortium of php developers promoting the transition from php 4 to php 5.

it runs in both 32-bit and 64-bit environments, but on windows the only official distribution is 32-bit, requiring windows 32-bit compatibility mode to be enabled while using iis in a 64-bit windows environment. there is a third-party distribution available for 64-bit windows.

usage
php is a general-purpose scripting language that is especially suited for web development. php generally runs on a web server, taking php code as its input and creating web pages as output. it can also be used for command-line scripting and client-side gui applications. php can be deployed on most web servers, many operating systems and platforms, and can be used with many relational database management systems. it is available free of charge, and the php group provides the complete source code for users to build, customize and extend for their own use.

php primarily acts as a filter, taking input from a file or stream containing text and/or php instructions and outputs another stream of data; most commonly the output will be html. it can automatically detect the language of the user. from php 4, the php parser compiles input to produce bytecode for processing by the zend engine, giving improved performance over its interpreter predecessor.

originally designed to create dynamic web pages, php’s principal focus is server-side scripting, and it is similar to other server-side scripting languages that provide dynamic content from a web server to a client, such as mcrosoft’s asp.net system, sun microsystems’ jsp, and mod_perl. php has also attracted the development of many frameworks that provide building blocks and a design structure to promote rapid application development (rad). some of these include cakephp, prado, symfony and zend framework, offering features similar to other web application frameworks.

the lamp architecture has become popular in the web industry as a way of deploying web applications. php is commonly used as the ‘p’ in this bundle alongside linux, apache and mysql, although the ‘p’ may also refer to python or perl.

as of april 2007, over 20 million internet domains were hosted on servers with php installed, and php was recorded as the most popular apache module. significant websites are written in php including the user-facing portion of facebook, wikipedia (mediawiki), yahoo!, myyearbook, and tagged.

starting and stopping a session

when developing web apps, many times the application at hand involves needing the user to log in to use the app. this works especially well when, let’s say, the app needs to load information based on who the user is. one way this can be done is to check who the user is on every page and load the information when the page is served. this might work on smaller apps but what if the application is a social networking tool with a coupe hundred thousand members. if too many people are logged in and clicking around at once, the app will be generating far to many database connections, or bloating the server severely. instead, i prefer to load the information that i will require for the duration of the user’s experience into session variables that will remain in tact as long as the user is logged in.

to do this there are 3 basic steps: 1) begin the session; 2) load the data you will need into variables that will remain in place; 3) destroy the session when the user is done so the next person does accidentally, or maliciously, use the previous user’s info.

starting a session in php is rather easy. it can be executed with one simple command:

session_start();

that’s it! typically i start the session as the very first line in my code. once the session has started you can then assign the session variables. the $_SESSION variable is a global array that contains virtually as many pieces of data as you might want. simply attaching a key to the session variable and assigning it a value will set it. lets say i want to store the time the session started:

$_SESSION['start_time'] = time();

just place that code on the very next line after the session is started and the value returned from time(); will be stored in $_SESSION['start_']. pretty simple right?

now lets get a little more serious. lets create something useful. lets say a user is logging in and you want to load that user’s info and maintain it throughout the session. lets assume the user fulled out a form where the fields were named username and password and that user information is stored in a mysql table named user_profiles. the code might look something like this:

session_start();
if ($_POST['username']) {

	$username = $_POST['username'];
	$password = $_POST['password'];
	$sql = "SELECT * FROM user_profiles WHERE username = '$username'";
	$result = mysql_query($sql);

	if (!$result) exit('no such user');
	$row = mysql_fetch_assoc($result);

	if ($row['password'] == $password) {
		$_SESSION['user_id'] = $row['user_id'];
		$_SESSION['username'] = $row['username'];
		$_SESSION['first_name'] = $row['first_name'];
		$_SESSION['last_name'] = $row['last_name'];
	} else {
		exit('incorrect password');
	}

}

here we have checked the user’s username, if the user exists we then check if the password matched the one on file. when that checks out, we set a few session variables that will remain in place for the duration of the session. any time we want to reference the user’s first name we simply reference $_SESSION['first_name'] rather than looking it up again.

to end the session you will use a few php commands: 1) identify the session you want to end; 2) unload all the session variables; 3) end the session. let’s say our user just clicked on a ‘log out’ button, the code might look something like this:

session_start();
if ($_POST['logout']) {

	$_SESSION = array();
	session_destroy();

}

notice i used session_start() again. whenever it is used it will either start a new session or refer to the current session. if we had multiple sessions open at once we would want to refer to the session’s name inside the parentheses: session_start('my_session')

that’s pretty much it. you can see how using sessions can come in handy and how using them in conjunction with database driven applications is almost a must to reduce server bloat.

adding data to a sql column value

i came across an interesting challenge the other day that i found a rather simple solution to.

i was trying to update a SQL table to add information to the end of a column value. i simply wanted to add some text to the end of what was already stored there. i will be honest, i dont consider myself to be a mysql expert by any means and my first approach was to bring the data into php, add the extra text and then update the table.

wrong way:

$new_data = "some value";
$sql = "SELECT * FROM table WHERE id = 1";
$result = mysql_result($sql);
$row = mysql_fetch_assoc($result);
mysql_free_result($result);
$new_col = $row['column'] . $new_data;
$sql = "UPDATE table SET column = $new_col WHERE id = 1";
$result = mysql_query($sql)

with the use of mysql’s CONCAT function this can be simplified into much more efficient coding.

right way:

$new_data = "some value";
$sql = "UPDATE table SET column = CONCAT(column, $new_data) WHERE id = 1";
$result = mysql_query($sql);

thats it! 8 lines of code reduced to 2 lines. gotta love taking advantage of built in functionality!

document word replacement

while working on a contract recently i came across an issue that involved word replacement on pages throughout the site. the client wanted the ability to use a global variable that would represent the total number of subscribers to the site. that variable would then automatically be replaced be the actual number. however, the CMS that i constructed for the client involved a WYSIWYG editor (tinyMCE) and would not allow PHP to be inserted into the text. my solution was to combine php, mysql and javascript in a way that would replace a specific tag withing any page.

for the purpose of this example, let’s say all page content loads in a container div with the id=’container’ and that all text elements within that div are contained in ‘p’ tags. start out by writing a javascript function to replace all instances of the tag within the container p’s:

function globals(txt) {

	var page = document.getElementById("container");
	var p = page.getElementsByTagName("p");

	for(var i = 0; i < p.length; i++( {
		var x = p[i].innerHTML;
		if (x.indexOf('[subscribers]' >= 0) {
			x = p[i].innerHTML.replace("[subscribers]",txt);
			p[i].innerHTML = x;
		}
	}

}

then bring in the data from a table containing the global vars:

<?php
$sql = "SELECT * FROM globals_table WHERE ref_name='subscribers'";
$result = mysql_query($sql);
$row = mysql_fetch_assoc($result);
?>

finally add a call to the function at the bottom of the page:
(here i combine javascript with shorthand php)

<script type="text/javascript">
<!--
globals('<?=number_format($globals['subscribers'])?>');
// -->
</script>

thats it! now whenever a pages loads, the global ‘subscribers’ value is brought in from the database. and send to a javascript function (via php) that will loop through all <p> tags inside the container div and replace any instance of “[subscribers]” with the actual number