web based gantt charts

regular expressions

i had to work with regular expressions today and i realized i have never posed about them but i used them often. a regular expression is a way of matching patterns in a string. most programming languages have a way to work with them. in php the method i prefer to use is preg_match() and in javascript its match(). ill just show some very very basic regular expressions to describe how they work. first, a few of the basic special characters that i use rather often:

. – any character
^ – when at the beginning of the expression it means the very first character in the expression, however when inside of a pair square brackets it denotes the NOT logical operator
$ – when at the end of the expression it means the end of the entire expression, otherwise it means an actual dollar sign
* – 0 or more of the preceding character
? – 0 or 1 of the preceding character
{n} – n number of the preceding character
{n,m} – between n and m instances of the preceding character
| – OR logical operator
() – used to group things together, can also be used for back references as $n where n represents the n’th set of parenthesis (starting at 0)
\s – white space
\d – a digit
‘-’ – a dash implies a range between the two characters on either site (they must match in type)
\ – this escapes the following character in the event that the character would otherwise be a reserved character in regular expressions

there are many more, in fact regular expressions can get so complex that some even consider it to be a pseudo-language of its own. in any result i’ll try to think of a situation where i can use at least a few of these characters and then write out the expression in plain English.

^[A-Z]([^.]\s)*hello?(\s[^.])*\.$

this would mean any grammatically correct sentence that begins with a capital letter followed by any number of characters and spaces that are not periods, followed by zero or one instance of the word ‘hello’, followed by any number of characters and spaces other than a period, and finally ending with a period

Jun 28, 2011

multithreading in php

ok… you got me… there is no such thing as actual multithreading in php. however, i have come up with a method that worked for me on a project where i had to write a web crawler and it the spider hasn’t failed yet. the basic idea is this: while php cannot multithread, your server certainly can so rather than try to jumo through a bunch of hoops to make php do what you want, just rely on the server to do the work.

the parent file
in order to multithread you must have a parent file that creates the threads (or children as i will refer to them for the remainder of this article). you will most likely have to account for the available resources and have the parent file check those resources to make sure that there is enough “room” for another child to be created, otherwise wait until there is “room.” another thing to pay attention to is the default max_execution_time in php. my method to handle both of these issues is to first set php’s max_execution_time to 0 (effectively turning it off) and then to store a running list of children in a database table to keep track of the count.

ini_set('max_execution_time', 0);
$maxThreadsAllowed = 50;
$activeThreads = 0;
$cyclesToRun = 250;
$count = 0;
$keepRunning = TRUE;
while ($keepRunning) {
    if ($activeThreads <= $maxThreadsAllowed) {
        exec('php -f childThread.php >> threadlog.txt &');
        $count++;
    }
    $sql = "'SELECT count(*) FROM threads WHERE completed = 0';
    $result = mysql_query($sql);
    $activeThreads = mysql_result($result, 0);
    if ($activeThreads == 0 && $count &;t= $cyclesToRun) {
        $break;
    }
}

initially there are no threads so i set $activeThreads to 0. for the purposes of this article i am assuming that the system will remain stable as long as there are no more than 50 threads running. the first time through the loop will always run since $keepRunning is initially TRUE. as long as there aren’t too many active children, a new child will be created each pass, otherwise it will be skipped and the count will be checked again. once the $activeCount hits 0 and the $count matches the $cyclesToRun the loop will break. you might ask yourself how the $activeCount will ever be more than 0 based on this code, the answer is in the child file.

the child file
the child file is where all the actual actions that are to be performed take place. in this example it will simply say 'Hello World.' the key here is to insert a new row into the threads table right away so that when the parent checks the $activeCount, there will be something there. then do the work. finally, update the row and set completed to 1 so that the thread is not included in the $activeCount.

$pid = getmypid();
$sql = 'INSERT INTO threads (pid, completed) VALUES (' . $pid . ', 0)';
$result = mysql_query($sql);
echo 'Hello World';
$sql = 'UPDATE threads SET completed = 1 WHERE pid = ' . $pid;
$result = mysql_query($sql);

thats it, albeit in a very basic way. the threads table tracks all the currently running children and the parent checks the table to ensure that there aren’t too many threads running. once all the threads are completed and the total number of cycles has been fulfilled the loop will break and the parent will stop.

assumptions
i am assuming that you already have a connection to a database and that whichever user php is running as has the proper permissions necessary to run exec() commands, user ini_set() and write to a threadlog.txt file

Mar 9, 2010

nested select statements

problem
i came across this while trying to put together a complex sql query that would find specific items within a data set. at first i did this by writing a query and pulling the info into an array in php. then creating a data set from that to build another query from:

$sql = "SELECT * FROM table WHERE column = value";
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result) {
	if ($row["column"] == "some value") $array[] = $row;
}
$sql = "SELECT * FROM table WHERE column IN (".implode(",",$array).")";
$result = mysql_query($sql);

solution
you can see how having to do this a few times can become rather tedious to hone in on a small data set. as of MySQL 5, however, you can nest SELECT statements to produce very complex queries that can help narrow down your result:

SELECT model
FROM cars
WHERE make IN (
	SELECT *
	FROM automakers
	WHERE automaker = "Honda"
	)

conclusion
while this is only a simple representation of the capabilities, you can see how nesting a few SELECT statements can reduce the number of subsequent queries you need to make in order to get the result set that you desire.

Feb 3, 2009

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.

Feb 3, 2009

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.

Articles, MySQL

Dec 3, 2008

mysql

introduction
mysql is a relational database management system (rdbms) which has more than 11 million installations. the program runs as a server providing multi-user access to a number of databases.

mysql is owned and sponsored by a single for-profit firm, the swedish company mysql ab, now a subsidiary of sun microsystems, which holds the copyright to most of the codebase. the project’s source code is available under terms of the , as well as under a variety of proprietary agreements.

“mysql” is officially pronounced /maɪˌɛskjuːˈɛl/ (my s q l), not “my sequel” /maɪˈsiːkwəl/. this adheres to the official ansi pronunciation; sequel was an earlier ibm database language, a predecessor to the sql language. the company does not take issue with the pronunciation “my sequel” or other local variations

uses
mysql is popular for web applications and acts as the database component of the lamp, bamp, mamp, and wamp platforms (linux/bsd/mac/windows-apache-mysql-/perl/python), and for bug tracking tools like bugzilla. its popularity for use with web applications is closely tied to the popularity of and ruby on rails, which are often combined with mysql. and mysql are essential components for running popular content management systems such as expression engine, drupal, e107, joomla!, wordpress and some bittorrent trackers. wikipedia runs on mediawiki software, which is written in and uses a mysql database. several high-traffic web sites use mysql for its data storage and logging of user data, including flickr, facebook, wikipedia, google, nokia and youtube.

platforms and interfaces
mysql is written in c and c++. the sql parser uses yacc and a home-brewed lexer.

mysql works on many different system platforms, including aix, bsdi, freebsd, hp-ux, i5/os, linux, mac os x, netbsd, novell netware, openbsd, ecomstation , os/2 warp, qnx, irix, solaris, symbian, sunos, sco openserver, sco unixware, sanos, tru64, windows 95, windows 98, windows me, windows nt, windows 2000, windows xp, and windows vista. a port of mysql to openvms is also available.

libraries for accessing mysql databases are available in all major programming languages with language-specific apis. in addition, an odbc interface called myodbc allows additional programming languages that support the odbc interface to communicate with a mysql database, such as asp or coldfusion. the mysql server and official libraries are mostly implemented in ansi c/ansi c++.

to administer mysql databases one can use the included command-line tool (commands: mysql and mysqladmin). also downloadable from the mysql site are gui administration tools: mysql administrator and mysql query browser. both of the gui tools are now included in one package called tools/5.0.html mysql gui tools.

in addition to the above-mentioned tools developed by mysql ab, there are several other commercial and non-commercial tools available. examples include phpmyadmin, a free web-based administration interface implemented in php, and navicat lite edition, a free desktop based gui tool.

Oct 31, 2008

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.

Oct 29, 2008

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!

JavaScript/Ajax, MySQL, PHP

Oct 10, 2008