Archive for October, 2008

Article | No Comments | October 17th, 2008

This quick tutorial illustrates using functions that generate HTML to create start and end table rows. This is a technique, creating three functions, two to create the start (including header row) and end table sections and a middle section generated by looping construct that outputs HTML table rows filled in with data. This is great for standardizing and gaining control over the HTML tables your applications generate. A lot better than a bunch pages with code randomly strewn through HTML tables. It keeps the code in one place, it keeps you pages simple by only having to drop a function call into it where you want the table. YOu can take advantage of the function to set the appearance or names of table headings.

Tip It’s a good practice to name all functions that generate HTML starting with “make_”. This makes it clear which functions output HTML.

	function make_users_table_start() {
	$content = '<table width="100%" border="0" cellspacing="1"
	 cellpadding="2" bgcolor="#000000">
<tr bgcolor="#e7e7d6">
<td><font face="Verdana,Arial,sans-serif"
 size="-1"><strong>User Name
 </strong></font>

 </td><td><font
 face="Verdana,Arial,sans-serif" size="-1">
 <strong>Name</strong></font></td><
 td><font face="Verdana,Arial,sans-serif" size="-1">
 <strong>Email Address</strong></font></td>

 <td><font face="Verdana,Arial,sans-serif" size="-1">
 <strong>Member Group</strong></font></td><td>
 <font face="Verdana,Arial,sans-serif" size="-1">
 <strong>Member Since</strong></font>

 </td>';
	return $content;
}

function get_alt_row_color($i,$light_color="#ffffff",$dark_color="#E6EFFD") {
if ($i % 2 == 0) {
		return $light_color;
	} else {
		return $dark_color;
	}
}

The get_alt_row_color() function adds a little eye candy and readability to the table.

function make_users_table_end() {
$content = '</table>';
return $content;
}

function browse_users() {
$sql = "SELECT
 user_id, user_pass, personal_name, family_name,
 user_group, email_address,
 DATE_FORMAT(date_created, $date_format) AS member_since
  FROM user ORDER BY user_id DESC";
$result=mysql_query($sql);
if (!$result) {
    print mysql_error()." ERROR - browse query failed.";
}

while($row = mysql_fetch_array($result)) {
		$content .= '<tr bgcolor="'. get_alt_row_color($line_count). '">';
		$content .= '<td class="normalprint"><font
		 face="Verdana,Arial,sans-serif" size="-1">' ."\n";
		$content .= '<a href="user-detail.php'. make_sid()
		 .'&user_id='. $row[user_id] .'">'. $row[user_id]
		  .'</a></font></td>

		 <td class="normalprint"><font face="Verdana,Arial,sans-serif"
		  size="-1">'. $row[personal_name] .' '. $row[family_name];
		$content .= '<td class="normalprint"><font
		 face="Verdana,Arial,sans-serif" size="-1">' ."\n";
		$content .= $row[email_address];
		$content .= "</font>\n\t</td>\n";
		$content .= '<td class="normalprint"><font
		 face="Verdana,Arial,sans-serif" size="-1">' ."\n";
		$content .= $user_group[$row[user_group]];
		$content .= "</font>\n\t</td>\n";
		$content .= '<td class="normalprint"><font
		 face="Verdana,Arial,sans-serif" size="-1">'.
		  $row[member_since] ."</td>\n";
		$content .= "</font>\n\t</td>\n</tr>\n";
		$line_count++;
	}
}

Adding the output of this query is as easy as adding three funtion calls to a PHP page. It doesn’t even matter hwat directory it’s in.

print make_users_table_start();

print browse_users($start_list);

print make_users_table_end();

Tip Don’t forget the print statement! Otherwise, you’ll get nothing.

Or you can create a wrapper function for all of them. Place it anywhere on any PHP page and you’re ready to go!

function browse_box() {

print make_users_table_start();

print browse_users($start_list);

print make_users_table_end();
}

Article | No Comments | October 17th, 2008

This is a brief guide to installing PHP (a server-side web page scripting language) and MySQL (a popular relational database system) on the NT platform.

Important Before installing PHP and MySQL on Windows NT, you must logon as a user with administrator privileges.

PHP

You need to download two files to install PHP for Windows (and you must have some way of unzipping “zip” compressed files (e.g. Winzip).

  1. Go to http://www.php.net
  2. (Select a mirror site close to you)
  3. Click on the ‘Downloads’ link.
  4. Download the zip package file (currently “PHP 4.1.1-Win32.zip”)
  5. Also download the installer (currently “PHP 4.1.1-installer.exe”)

Unzip the zip file to a folder (e.g. C:php) put the “PHP 4.1.1-installer.exe” file in the same folder and double click it to run it. It will install it just like a normal piece of Windows software – Just follow the onscreen instructions.

After installing you need to configure the “php.ini” file which needs to be saved in C:Windows. There will be an example one given to you in your php installation folder which is ideal to use. (It only needs changing if you install it in a different folder or want non-default options).

Note You can see some (old) instructions for installing PHP on Windows at: http://www.e-gineer.com/instructions/install-php3xx-for-iis4x-on-windowsnt.phtml

MySQL

Go to http://www.mysql.com and click on the ‘Downloads’ link. Go for the stable version, (currently v3.23.47). Scroll down to the Windows Downloads section and click on the link to download the zip file. (As said, currently: ‘mysql-3.23.47-win.zip’)Extract this to a folder (e.g. C:MySQL) and then double click on the “Setup.exe” file to install.

That’s it!

Make sure the web server is running and your PHP files are in the web publishing directory for them to work as intended. To run the MySQL server, create a shortcut to the “winmysqladmin.exe” in the folder C:mysqlbin (this may get generated automatically but on my installation it didn’t!) and place it in your start menu or on your desktop. This will start and stop your MySQL server when required.

Tip If you are new to MySQL then a helpful tool that provides a Windows graphical front-end is MySQL Front available from http://www.mysqlfront.de

Article | No Comments | October 17th, 2008

Note This is a reprint of an article on Perl text normalization. It refers to Perl code not PHP code. Hope you find it useful.

Text is text, right? Not necessarily true when you are concerned with writing web-based applications. Because the net is composed of many different kinds of computers, a CGI program cannot just assume a one size fits all attitude. It must expect text will be coming from a variety of sources and formats. There are three main formats of text in use today, differing only in the way the end of line or newline is marked. The PC uses a carriage-return linefeed pair (CRLF) to mark the newline. Mac newlines are just a single carriage-return (CR). And Unix/Linux systems employ a single linefeed. If you ever wonder why your files appear to “shrink” when uploaded to a web server, it’s because of those unnecessary characters the PC text format adds (typically conversion takes place when files are uploaded in ASCII mode by FTP). But enough history, lets look at what a CGI-forms interface sees when it receives input from the user.

Attaining a State of Normalcy

I think it’s good practice (as a friendly Perl expert once taught me) to normalize all text before attempting to process it. If you try to operate on text expecting each line to end with a linefeed but the lines actually end with a carriage return linefeed pair, the results may be unexpected. By normalizing text, you won’t have to deal with several different kinds of linefeed all mixed together. The main parts of your CGI program will only have to understand one format, not three. Since you can’t predict what browser or operating system and hence format of text, it’s best to normalize all text input to a common format. One user may be on a Unix system, then another may come along on a Mac—if you just post their data to a text file it will end up scrambled by the mix of different newlines.

Note: For this reason, the W3C has reccommended in the HTML4.0 specification that all browsers normalize TEXTAREA (and I suppose TEXT input content) to CRLF format). But it will be a long time before you can rely on it. I see Netscape 1.1 occassionally in my logs to this day [at the time of writing, 1996-97].

Because Unix/Linux is the operating system widely running web servers, this example will normalize to the Unix single linefeed. But you can normalize to any format, such as to the PC format if you are running Perl on Windows NT or the Mac format if your web server runs under Mac OS.

#--------------#
# Normalize textarea
# input to Unix newline format.
# Input:
# $text: string to be normalized
# Note: HTML4.0
# recommends browsers normalize
# their textarea
# content to CRLF format. This, I hope
# will eventually make
this step unnecessary.
#---------------#

# Convert PC newline (CRLF)
# to Unix newline format (LF)

$text =~ s/\r\n/\n/g;

# Convert Mac newline (CR)
# to Unix newline format (LF)

$text =~ s/\r/\n/g;

Other Text Tricks

Now that you have the text input from the form normalized, what are other considerations? A line that contains only spaces or a few tab characters is not really useful–the extra characters are unnecessary baggage. So you can remove them.

# remove spaces and tabs from blank lines
$text =~ s/\n[ \t]+/\n/g;

If you have no need to retain format of the original plain text, then you can remove leading and trailing line breaks. This is fine if the text is only intended for inclusion in an HTML document (except for PRE, which retains formatting), say in a paragraph element.

# remove leading and trailing line breaks
$text =~ s/^\n+|\n+$//g;

Returning From Normal

You can also convert text normalized to Unix format back into PC or Mac formats.

# Translate to target line format.
#----------------------------------#

#Change to Mac format
# LF to CR
$text =~ s/\n/\r/g;
# Translate to target line format.
#-----------------------------------#

# Put Unix into PC format
# LF to CRLF
$text =~ s/\n/\r\n/g;

Does Normalizing Matter?

Yes. Because as long as Mac browsers submit Mac format text and PC browsers submit DOS format text, you will need to normalize TEXTAREA input. Of course, you do not have to normalize INPUT text because it is not a multi-line input. Perl always works with text in its native format, so must normalize. It’s best to normalize text to the format native to the server hosting the website. Unless you have a special need, such as writing in PC format for download and editing on PC’s.

PHP and Normalization

I’m uncertain whether other languages accept and process text in native format. I’ve not really had any trouble nor have I had to normalize TEXTAREA input in PHP. Maybe it normalizes input or most browsers today are submitting normalized text [in 2002].

Article | No Comments | October 17th, 2008

nswers to some frequently asked questions and solutions to some common PHP programming tasks. (With a few comments on how PHP syntax relates to Perl).

How Do I Tell if PHP is Working?

You can check to see if PHP is running and correctly configured by making a simple one line script:

	<?php print phpinfo(); ?>

I usually call this phpinfo.php and upload it to the public web server directory, then point my browser to it. It displays the configuration of PHP in great detail.

How do I make a PHP page?

This may sound a little silly, but often people are confused about how a HTML page differs from a PHP page. A PHP page is just a regular plain text file like any other web page except that it has a “php file extension,” usually “php” and meaning that this page will be run through PHP before going to the browser. Any PHP program code in the page will be executed. Any HTML will be passed on to the browser untouched. Making a PHP enabled web page is as easy as opening a new file in your favorite HTML or text editor. The editor can be as simple as Notepad or as complex as HomeSite. The choice is yours.

Type

	<?php
	   print "Hello World";
	?>

Save this file with the php file extension, such as “hello.php” (without the quotes) to your hard disk.

Upload the file to your PHP enabled web server. It must be in your web server’s document root or a directory below it (this is usually a directory called public_html or www or something similar).

Load the page in your web browser, the URL should look something like

	http://www.example-domain.com/hello.php

Remember, that you may have to change the extension to suit how the web server is configured. If you’re using PHP3, you may have to give your file the php3 extension instead of the standard php.

How do I quote strings?

PHP syntax takes some inspiration from the popular Perl programming language. In Perl, variable substitution is done in a double quotish context but not in a single quotish context. PHP follows this rule. To see how this works, run

$test = "Test";
print "Test Double Quotish Context: $test
";
print 'Test Single Quotish Context: $test
';

By single quote context, I mean any characters enclosed in single quotes. By double quote context, I mean any string of characters enclosed by double quotes. Variable substitution refers to replacing the value of a variable where that variable name appears in the string.

The type of quotes you use to enclose a string literal can also affect the use of character sequences used to escape certain special characters:

print '<pre>';
print "This is a double quote context\n";
print 'This is a single Quote context\n';
print '</pre>';

When printed to the browser, the second line will contain “\n” instead of a newline.

This is a double quote context
This is a single Quote context\n

Not understanding how quotes work can lead to frustration and mysterious errors.

For example take a look at the code below.

$dir='arcade';
$handle=opendir('/usr/www/users/userid/vdomain/test/games/$dir/');

The opendir() function gives mysterious errors until you stop looking at the path and notice the single quotes. This literally evaluates to

/usr/www/users/userid/vdomain/test/games/$dir/

which will give errors, instead of

/usr/www/users/userid/vdomain/test/games/arcade/

where the $dir variable’s value gets properly substituted into the directory path string.

For this to work correctly use double quotes instead instead of single quotes:

$dir='arcade';
$handle=opendir("/usr/www/users/userid/vdomain/test/games/$dir/");

PHP also allows string literals to be broken over multiple lines. Very useful for organizing or structuring HTML tags, such as for indenting tags used in TABLEs. Notice that spaces are significant in multi-line quoted material. (This is a little easier than Perl, but lacks that language’s mechanism for specifying the start and end quote symbols that makes dropping HTML blocks into code a snap).

print "
<p>
This is a multiple
 line quote
</p>

Printing to Web Pages and First Steps to Functions


How do I include PHP values in a HTML tag?

Although it may seem a strange question, beginners often wonder how they can output values as attributes into HTML tags, not just between them. Maybe this is because PHP code sections look like HTML tags and they are unaware that you can intermingle HTML and PHP code anywhere on the page. This includes the “inside” portion of tags. Although PHP code looks like a tag itself, it is not restricted to any particular place in HTML. A web page is just on long stream of text where PHP can itself generate HTML codes. Values from variables can be inserted into HTML tags by printing them where you want them to appear in the HTML code.

An easy way to include a <?php … ?> tag in an HTML tag (<body>) is shown here.

<body bgcolor="<?php print $bgcolor; ?>" text="<?php print $textcolor; ?>">

Mixing code directly into HTML tag attributes can easily become confusing and error prone. The following code is cleaner and more controlled way of specifying values in a PHP generated tag:

<?php
$bgcolor = "#fffff";
$text = "#000000";
$link = "#0000FF";
print "<body bgcolor=$bgcolor text=$text link=$link>";
?>

In this example, I make it easier on the programmer by not quoting attribute values. This is not the best practice. The HTML specification requires that certain attribute values be quoted. It’s usually a good idea to always quote, but of course stray quotes can easily bring down your program. Try this:

<?php
$bgcolor = "#fffff";
$text = "#000000";
$link = "#0000FF";
print '<body bgcolor="'. $bgcolor .'" text="'. $text .'" link="'. $link .'">';
?>

I use the single quote for static text portions of the string where I do not need variable substitution. Values are included using the concatenation operator, which combines string values into a single string. This may be a little slower but it’s a lot clearer. It’s also easy to add or insert another string into the output should another attribute be needed.

Using a Function to Make HTML

Instead of writing the same code over and over for each tag, you could make this into a function.

<?php
function html_body($bgcolor,$text,$link ) {
	$bgcolor = "#fffff";
	$text = "#000000";
	$link = "#0000FF";
	print '<body bgcolor="'. $bgcolor .'" text="'. $text .'" link="'. $link .'">';
}
?>

The function definition can be placed in a configuration page included into any pages where you wish to control the body attributes. Instead of the tag you call the function that generates it. You can reuse the code anywhere the tag is needed, thus saving time, effort and errors.

<?php
function make_html_body($bgcolor,$text,$link ) {
	$bgcolor = "#fffff";
	$text = "#000000";
	$link = "#0000FF";
	print '<body bgcolor="'. $bgcolor .'" text="'. $text .'" link="'. $link .'">';
}
?>
<head>
<title>Page With A Dynamic Personality</title>

</head>
<?php print make_html_body("#fffff","#000000","#0000FF"); ?>

<h2>Page With A Dynamic Personality</h2>

To drop the HTML output into our page we need only print the value returned from the make_html_whatever() function.

To see how it works, copy the code into a PHP page and run it. The function definition does not have to be literally in the page as I show here, but can be included from another file.

However, sometimes having the function return its value by printing directly to the browser can cause problems. Another programmer may want to use the same function on a page that handles cookies. The print statement may interfere with this. Can we create a function that can be used in any context, with the programmer deciding how to use the output? Yes, here’s how:

<?php
function make_html_body($bgcolor,$text,$link )
{
	$bgcolor = "#fffff";
	$text = "#000000";
	$link = "#0000FF";
	$content = '<body bgcolor="'. $bgcolor;
	$content .= '" text="'. $text;
	$content .= '" link="'. $link .'">';
	return $content;
}
?>

Instead of printing the output directly to the web browser through PHP’s built-in output stream, we return it as the value of the function. I’ve broken the sting into three separate concatenations (using the .= operator that both puts the strings together and assigns the new value to the variable), just to show you how it can be done. Often you’ll build up the output value from a series of string operations. You don’t have to specify a return value, because PHP returns the last value assigned in a function. But it’s good documentation practice to explicitly show the return statement. If you’re concerned about efficiency, just comment out the return statement.

You’ve now just learned a very important principle in programming. It’s called modularity and shows you the advantages of reusing code, containing code in packages that are easy to locate all in one place (the place where you keep all the functions used on your web pages) and write functions that return values, which can be used without regard to their context. You don’t know it, but you are on your way to understanding object oriented programming. But unfortunately, you’re also leaving behind the easy days of just mixing code in with HTML. When it comes to large web applications it’s often better when the script is generating all HTML directly from a series of print statements or a function.

Web Pages With Variable Content


How do I include variable content in a PHP page?

You may have seen on quite a few sites when they link to a file they just link to “www.example-domain.com/?page=pic.html&pic=jane-austin.jpg” or such. The idea is to link to a PHP page that includes the selected picture inside a web page without having to make a separate html file for every single picture, for example.

Unless you need to apply a universal template to the pages, you can simply make this a PHP page that accepts and displays variable content. PHP is a preprocessor, which means it automatically reads the page and executes code it finds. Any variables from a link are automatically brought into existence.

For this example, If you do not need templates to be varied by the image, just create a PHP page with whatever HTML you want on it, then include:

	<img src=<?php print $pic; ?> alt="">

Unfortunately, PHP does not have an easy way of getting the height and width of an image so they can be added to the IMG tag. (However, an imaginative programmer might create snippets of JavaScript to peek at the height and width and document.write() them out to the IMG tag. But I’ll leave that for the future.

If the page is index.php, you can call it with only the domain name

	domain/?$pic=victorian-lady.jpg

or by specifying a directory.

	domain/directory/?$pic=victorian-lady.jpg

Basics of Accessing the CGI Enviroment


Getting Information About Web Browser Capabilities

I am not an advocate of serving different content based on which web browser the user is visiting. But sometimes it is unavoidable. Or you may want to collect information to tune your web pages to the particular capabilities of your site’s audience. When a browser requests a page from a web server a number of environment variables are set. These are fairly standardized, although some web servers and software (such as PHP) may create or set new one not in a standard RFC paper. Software used to browse the web is called a “user agent” because it acts as an agent for the user, retrieving and displaying requested files. The browser is the client and the web server is, naturally, the server in this relationship.

$env["HTTP_USER_AGENT"] = getenv("HTTP_USER_AGENT");
$env["HTTP_ACCEPT"] = getenv("HTTP_ACCEPT");

Peeking Into the HTTP Environment

You may be curious about what environment variables are available. Here is a small script to display some of the most common and standard HTTP environment variables.

$env["REMOTE_ADDR"] = getenv("REMOTE_ADDR");
$env["SERVER_SOFTWARE"] = getenv("SERVER_SOFTWARE");
$env["SERVER_NAME"] = getenv("SERVER_NAME");
$env["GATEWAY_INTERFACE"] = getenv("GATEWAY_INTERFACE");
$env["SERVER_PROTOCOL"] = getenv("SERVER_PROTOCOL");
$env["SERVER_PORT"] = getenv("SERVER_PORT");
$env["REQUEST_METHOD"] = getenv("REQUEST_METHOD");
$env["PATH_INFO"] = getenv("PATH_INFO");
$env["PATH_TRANSLATED"] = getenv("PATH_TRANSLATED");
$env["DOCUMENT_ROOT"] = getenv("DOCUMENT_ROOT");
$env["SCRIPT_NAME"] = getenv("SCRIPT_NAME");
$env["QUERY_STRING"] = getenv("QUERY_STRING");
$env["REMOTE_HOST"] = getenv("REMOTE_HOST");
$env["AUTH_TYPE"] = getenv("AUTH_TYPE");
$env["REMOTE_USER"] = getenv("REMOTE_USER");
$env["REMOTE_IDENT"] = getenv("REMOTE_IDENT");
$env["CONTENT_TYPE"] = getenv("CONTENT_TYPE");
$env["CONTENT_LENGTH"] = getenv("CONTENT_LENGTH");
$env["HTTP_ACCEPT"] = getenv("HTTP_ACCEPT");
$env["HTTP_HOST"] = getenv("HTTP_HOST");
$env["HTTP_USER_AGENT"] = getenv("HTTP_USER_AGENT");
$env["HTTP_REFERER"] = getenv("HTTP_REFERER");
$env["HTTP_REFERRER"] = getenv("HTTP_REFFERER");

print "<table border=1 cellspacing=0 cellpadding=2>";
print "<caption>Environment Variables</caption>";
while ( list( $key, $val ) = each( $env ) ) {

	print "<tr><td bgcolor=#CCCCCC
	><b>$key</b></td><td bgcolor=#EEEEEE><i>$val</i></td></tr>";
}
print "</table>";
}

Why do I use getenv() instead of looking into the GLOBALS array for these values? Because anyone can spoof those values in the globals array. We cannot trust these same values when they come from the GLOBALS array, but getenv() retrieves the values directly from the environment. Many of these values are important to maintaining security on the web, such as REMOTE_HOST (for access to user’s IP address) or PATH_INFO (sometimes carrying crucial information to scripts).

You may notice a few strange things about environment variables. For historical reasons, both the misspelled REFERRER (the misspelled version) and REFERRER are available. Handling referring URLs can be tricky because some older web servers still use the misspelled version, so you should check for a value in both. Sometimes variables become obsolete. For example, the HTTP_FROM variable generally returns nothing in most modern browsers. Originally it was for returning the user’s email address in order to make filling out forms easy, but was dropped for privacy and anti-spam reasons.

How Can I See All the CGI Variables?

When debugging, you may want to see all the variables posted by a form or associated with a link (variables and values coming in from the CGI environment). Sometimes there may be an extra variable you had not thought was being submitted or you might be lacking a needed variable. HTTP_POST VARS holds all the variables coming from an HTML form submission.

// Dump POST Variables

	if($HTTP_POST_VARS)
	{
		print "<table border=1 cellspacing=0 cellpadding=2>";
		print "<caption>Values submitted via POST method</caption>";
		while ( list( $key, $val ) = each( $HTTP_POST_VARS ) )
		{
			print "<tr><td bgcolor=#CCCCCC><b>$key</b></td>

<td bgcolor=#EEEEEE><i>$val</i></td></tr>";
		}
		print "</table>";
	}

To look at the variables coming from a scripted page that is activated by a link, check HTTP_GET_VARS.

// Dump GET Variables

print "<table border=1 cellspacing=0 cellpadding=2>";
print "<caption>Values submitted via GET method</caption>";
while ( list( $key, $val ) = each( $HTTP_GET_VARS ) ) {

	print "<tr><td bgcolor=#CCCCCC><b>$key</b></td>

	<td bgcolor=#EEEEEE><i>$val</i></td></tr>";
}
print "</table>";

Both HTTP_GET_VARS and HTTP_POST_VARS are variables introduced into the CGI environment by PHP itself (they are arrays created when a PHP page executes). These are not part of the standard environment if PHP were not running (as far as I know).

Article | No Comments | October 17th, 2008

This is a brief guide to installing PHP (a server-side web page scripting language) and MySQL (a popular relational database system) on the NT platform.

Important Before installing PHP and MySQL on Windows NT, you must logon as a user with administrator privileges.

PHP

You need to download two files to install PHP for Windows (and you must have some way of unzipping “zip” compressed files (e.g. Winzip).

  1. Go to http://www.php.net
  2. (Select a mirror site close to you)
  3. Click on the ‘Downloads’ link.
  4. Download the zip package file (currently “PHP 4.1.1-Win32.zip”)
  5. Also download the installer (currently “PHP 4.1.1-installer.exe”)

Unzip the zip file to a folder (e.g. C:php) put the “PHP 4.1.1-installer.exe” file in the same folder and double click it to run it. It will install it just like a normal piece of Windows software – Just follow the onscreen instructions.

After installing you need to configure the “php.ini” file which needs to be saved in C:Windows. There will be an example one given to you in your php installation folder which is ideal to use. (It only needs changing if you install it in a different folder or want non-default options).

Note You can see some (old) instructions for installing PHP on Windows at: http://www.e-gineer.com/instructions/install-php3xx-for-iis4x-on-windowsnt.phtml

MySQL

Go to http://www.mysql.com and click on the ‘Downloads’ link. Go for the stable version, (currently v3.23.47). Scroll down to the Windows Downloads section and click on the link to download the zip file. (As said, currently: ‘mysql-3.23.47-win.zip’)Extract this to a folder (e.g. C:MySQL) and then double click on the “Setup.exe” file to install.

That’s it!

Make sure the web server is running and your PHP files are in the web publishing directory for them to work as intended. To run the MySQL server, create a shortcut to the “winmysqladmin.exe” in the folder C:mysqlbin (this may get generated automatically but on my installation it didn’t!) and place it in your start menu or on your desktop. This will start and stop your MySQL server when required.

Tip If you are new to MySQL then a helpful tool that provides a Windows graphical front-end is MySQL Front available from http://www.mysqlfront.de

Article | No Comments | October 17th, 2008

One of the impressive features of MySQL is its full support for ODBC (Open DataBase Connectivity). The source for the ODBC driver and interface are available for those interested in furthering open source development or customizing ODBC access. All ODBC 2.5 functions and many others are supported. You may use Microsoft Access to connect to your MySQL server, which this tutorial is based on. MyODBC is the ODBC driver for the MySQL database server produced by TCX Data-Consult in Sweden. MySQL provides support for ODBC by means of the MyODBC program.

Why Connect Using MyODBC?

With MyODBC you may:

  • Connect to a remote database server from anywhere you have access to a desktop application, such as Microsoft Access.
  • Export a database to the remote server.
  • Import a database from the remote server.
  • Link a local database to a remote database.

Exporting. It’s useful to export a database when you are first populating a remote or online database. If you have an existing database that you want to put online, exporting it from Access to MySQL through an ODBC connection greatly simplifies the process. It allows even novices to get their database to the online server without requiring any knowledge of Unix shell commands or utility applications. It’s important to note that exporting is a static process transferring a table from the local to the remote server once each time. There is usually no feedback beyond a progress indicator. When you export a table, SQL statements are sent to the remote SQL server to create the table (CREATE TABLE) and to insert the data being transferred (INSERT). If the table has already been created, a CREATE TABLE statement is not issued and the new information is transferred using (UPDATE).

Importing. Importing a table from a remote database can be very useful when you want to create a report from information stored in an online database server. By importing a table into Access, you can generate charts or reports from data gathered online easily. It’s important to note that importing is also a static process transferring a table from the remote to the local server once each time. There is usually no feedback beyond a progress indicator.

Linking. When you link a local table to a remote table, you allow desktop users to make changes to the remote table through the graphical user interface of Access. For example, if a table row is added or modified through an Access form attached to a particular table linked to the remote table, the remote table will be updated to include the changes. This can help novices to maintain online database information in an easy to use and familiar environment. One of the benefits of linking tables is that any changes made by the user to the local database are also made to the remote database. This makes the whole processing of coordinate remote and local databases more visible.

All these tasks can be accomplished through use of the MySQL command line monitor from the Unix shell, but only for the initiated. Sometimes experts may be more comfortable working from the command line, issuing SQL queries to affect the remote database but for many people (clients, office workers) ODBC is the easy way to get their data to the remote server or produce nicely formatted charts and reports from online data. MyODBC is an important item in the developer’s toolkit. It allows you to offer clients an easy and flexible interface to their online database through an application and platform they are familiar with. For the web applications developer, allowing users to update their online database from the desktop can be a time-saver. Data entry and database management may be accomplished from the desktop, while a PHP script generates pages dynamically based on the content stored in database tables.

Note Microsoft Access is not available for the Macintosh. However, it is possible to exchange or share data in the following ways:

Getting Started With MyODBC


Downloading MyODBC

The MyODBC download package is available at the MySQL site http://www.mysql.com/ in the Downloads section. Scroll down to the MySQL Related Software and choose Downloads for MyODBC. (On the main site, MyODBC links are available from http://www.mysql.com/download_myodbc.html)

Tip for a faster download, try one of the MySQL mirror sites. Choose the mirror that is closest to you. MySQL Mirror Sites

MyODBC is available for the Windows 9x and NT platforms as well as various flavors of Unix (including Linux). The source code for MyODBC is included in the distribution. If you only want to use the MyODBC application, you can safely ignore the various makefiles and C code included with it in the download package. Just install the MyODBC executable program (it’s easy, see below).

Caution MyODBC is distributed in separate versions for Windows 9x and NT because of a bug in Microsoft ODBC setup that prevents it from detecting the operating system. The current version of MyODBC is 2.5. The latest version of the MyODBC download package for Windows 9x myodbc-2.50.28-win95.zip available at http://www.mysql.com/Downloads/MyODBC/myodbc-2.50.28-win95.zip Make sure you are using the latest version containing all the updates and fixes. MyODBC archive is about 1.5K and downloads in a few mintues at 56k.

Installing MyDOBC

Once you’ve download MyODBC into a convenient directory on your hard disk, you can open the archive and decompress the files inside. I suggest using WinZip or other application that makes the job of extracting files easy.

You can start WinZip by double-clicking on the file in the Windows Explorer. I keep mine set to the classic version, which makes the Install button readily available. This is a cool feature that will automatically create a temporary directory, unzip the archive, extract the files and start installation by running setup.exe if it finds one. Here’s what the screen looks like just before installation.

Don’t forget to tell WinZip that the installation is complete so it can close its window and clean up the files.

When you start the installation process, the first window you see will be the Microsoft ODBC Setup dialog. Click Continue.

At this point, the Install Drivers dialog window should appear. (If you do not reach this point in the installation, something is wrong with the way Windows is setup. Please contact Microsoft support to help resolve this issue.) Select MyODBC and click OK (or just click OK).

If have trouble installing the driver or are merely curious, you will find version related installation options by clicking on the Advanced button. This button brings up the Advanced Installation Options dialog window.

Further information about the driver version is available in the Versions dialog, by clicking on the Versions button in the Advanced Installation Options dialog.

If the MyODBC driver was successfully installed, the next dialog you see will be the Data Sources dialog. This dialog is part of the Microsoft ODBC system and not a part of MyODBC. You should see the sample data source installed by MyODBC, called sample-MySQL (MySQL) in the data sources driver window. Although you could select the MySQL entry and push the Setup button to bring up the MyODBC setup panel, we will be configuring the remote connection from within MS Access. That is so we can create a data source tailored to our project.

When you’re done, you should see a message telling you that the new ODBC driver has been successfully installed. Click OK.

  • You may save the Access data from tables, queries, and all or selected portions of datasheets to file formats that can be opened by Office 98 for the Macintosh applications.
  • Share data from a network server using Microsoft FoxPro for Macintosh users and Microsoft Access for PC users.
  • Share data using ODBC drivers.

Configuring and Connecting


Connecting to An Established Remote Database

Once you have installed the driver, we will use Access to connect to the remote database. The database must be setup for remote access. At many hosting providers, this is done only by request. Please ask your administrator or hosting provider support team about activating remote access to your MySQL database. Setting up a Data Source, choosing a ODBC driver and creating a Data Source Name (DSN) for Windows applications to reference your database can be a daunting task. There are many settings involved and many terms which you may be unfamiliar with. But broken down, the steps are relatively easy to follow and fall into place.

Click File | Get External Data | Import. Click New in the Data Source window. The MyODBC driver dialog should appear.

Creating a New Data Source

At this point you need to create a new data source. A data source is where data comes from through a ODBC connection. The Database Source Name (DSN) is the name of the database. When you create the database source, give it any name you like.

Using the Create a New Data Source Wizard

The first step is to create a new Windows data source. This allows Windows applications to connect to your ODBC database. Select User Data Source. Click Next.

The next screen presents you with a list of available ODBC drivers you can use to create a data source. (You can have as many data sources as you need, each using any ODBC driver available). Select the MySQL driver. It will be identified as to name, version and the developer, TCX. Click Next.

A purely informational screen appears, displaying the data source type and choice of ODBC driver. As the dialog says, the next step will be to optionally configure the driver-specific settings. Click Next.

MyODBC Setup

The MyODBC driver settings panel should appear. This panel allows you to specify the Windows DSN name, the remote server settings and other compatibility settings. You may give any value for the Windows DSN that is unique (not already used by another data source) to your ODBC sources list. The default port setting should be correct for most situations. Values for Server, User, Password and Port filed do not have to be specified in the ODBC Setup screen. If do specify them here, the values will be used as the default values later when you attempt to make a connection. The values can optionally be changed at that time. (Note: the MySQL manual says “If you specify the option Read options from C:\my.cnf, the groups client and odbc will be read from the `C:\my.cnf’ file. You can use all options that are usable by mysql_options().”

A quick reference to the settings.

Windows DSN Name of your database used by Windows (this can be anything you like, whatever helps you to remember and recognize the database is fine)
MySQL Host IP address or domain of host database server (obtained from your hosting provider)
MySQL Database Name this is the name of the remote database (obtained from your hosting provider or specified by you when the database was created)
User this is the user name for access to the remote database (obtained from your hosting provider)
Password this is the password used to access the remote database (obtained from your hosting provider; you must have the correct permissions level to do certain operations like creating and dropping tables. Some administrators will give you up to three user id password pairs, one for read only access (SELECT), one for read/write access (SELECT,INSERT,UPDATE), one for administrator level control (CREATE TABLE, DROP TABLE, ALTER TABLE plus all other statements).

Tip This tutorial uses Microsoft Access for illustration. To make Access work better with MyODBC, you should consider the following steps.

  • You should have a primary key in the table.
  • You should have a timestamp in all tables you want to be able to update.
  • Only use double float fields. Access fails when comparing with single floats.
  • Set the `Return matching rows’ option field when connecting to MySQL.
  • Access on NT will report BLOB columns as OLE OBJECTS. If you want to have MEMO columns instead, you should change the column to TEXT with ALTER TABLE.
  • Access can’t always handle DATE columns properly. If you have a problem with these, change the columns to DATETIME.
  • In some cases, Access may generate illegal SQL queries that MySQL can’t understand. You can fix this by selecting “Query | SQL Specific | Pass-Through” from the Access menu.

Once the data source has been created, you are back in the Select Data Source dialog. Select the data source you just created from the list. Click OK. That should create a duplicate table on the remote database with all your data. You won’t see anything to confirm the data has been transferred. You need to link the table to the remote database.

Importing and Exporting Tables


Importing a Table From the Remote Database

Open the Access database. On the database window (the one with the tabs), select the tables pane. Go to File, then Get External Data. Choose Import from the menu.

In the dialog, select the choice “To an External File or Database.” The SaveAs dialog will appear. In the Save as type select box, choose ODBC Databases (). Click Import.

The Import Objects dialog will appear. If the table you wish to import is listed, select it. Click OK.

Access will begin importing the table by querying the remote database through the ODBC connection. This connection can be slow for a large table, so have a cup of coffee while Access does its work.

Exporting a Table to the Remote Database

Open the Access database. On the database window (the one with the tabs), select the tables pane. Select the table to export. Go to File, then Get External Data.

The Export dialog will appear. If the table name is what you want, click OK. The Select Data Source dialog appears. Select the Machine Data Source tab.

We will create a new data source just for your this database. See the previous section on creating a new data source. This will make it easy to connect again to the database. Click New. The Create New Data Source appears. Select User Data Source. Click Next. Select the MySQL driver from the list. Click Next. The panel will display your driver info. Click Finish.

See the previous instructions for setting up the MyODBC driver. The MyODBC driver dialog should appear.

Once the data source has been created, you are back in the Select Data Source dialog. Select the data source you just created from the list. Click OK. That should create a duplicate table on the remote database with all your data. You won’t see anything to confirm the data has been transferred. You need to link the table to the remote database.

To link the table, select it in the table pane. Go to File | Get External Data | Link Tables. In the Link dialog, select from the Files of type list ODBC Databases (). The Data Source Dialog will appear. From the Machine Data Source tab, select your alumni database source name again. A series of dialogs will come up. One may ask you what tables you want to link, select the alumni table. Another may ask you what column you want to uniquely identify records, use the record ID column (usually the PRIMARY KEY) if you have one.

Now, a second table will appear in the panel with the same name, but a “world” icon showing that it is remote. You can work with this table like any other, creating a report based on it or a form that you can use to update the remote table.

Caution It may be necessary to change some of the column names in the table you are going to export. MySQL reserves certain words for its own use. Most of the SQL keywords are reserved. For example UPDATE is a reserved word, so you can’t use it as a column name. Except that function names do not clash with table or column names. For example, ABS is a valid column name. I am not completely sure about this and would appreciate any observations you have.

Open a blank Access database, go to File | Get External Data | Link Tables | Select from files of type ODBC, select Machine Data Sources tab, select your database name from the list, click OK.

A Link Tables dialog should come up. You should see a list of table names. Select the table you wish to link to. Click OK.

A Select Unique Identifier dialog should come up. You should see a list, select the column that you want to represent the primary key (or columns if more than one column is required to uniquely identify a row). Click OK.

You should see a remote table in the Tables tab (the globe indicates a table linked to a remote data source). Open the table, you should see the data in your remote table.

Caution Microsoft Access2000 has bug affecting the export of tables to the remote database. Table names are not sent in the SQL query when a database is exported. If you just go to File | Export | Select ODBC, choose MyODBC an ODBC error will occur. There is a hotfix available for this bug, thanks to the diligence of the MySQL community and responsiveness of Microsoft. The only word I have on this issue is from user group member who communicated with a member of the Microsoft Jet development team. They replied “We have found the problem with Access 2000 and MySQL/MyODBC. The problem is indeed on the Access side of ODBC, not the driver side. The Jet team has made a fix and will release with the next scheduled update. Thank you for bringing this issue to our attention. Jim Sturms. Access Program Manager.” At the time of writing a hotfix was available through Microsoft’s beta test program. However, linking to existing tables in the database is unaffected. Someone using Access 97/95 can connect to the database and export the data. Once the tables are online, you should be able to link to the table and modify it as needed.

MyODBC Compatibility and Options


ODBC Compatibility

Here are a few notes on making your data compatible with ODBC. They are from the MySQL manual.

The “zero” values are special, but you can store or refer to them explicitly using the values shown in the table. You can also do this using the values ‘0’ or 0, which are easier to write. “Zero” date or time values used through MyODBC are converted automatically to NULL in MyODBC 2.50.12 and above, because ODBC can’t handle such values.

MyODBC defines BLOB values as LONGVARBINARY and TEXT values as LONGVARCHAR.

The special date ‘0000-00-00′ can be stored and retrieved as ‘0000-00-00′. When using a ‘0000-00-00′ date through MyODBC, it will automatically be converted to NULL in MyODBC 2.50.12 and above, because ODBC can’t handle this kind of date.

String functions now return VARCHAR rather than CHAR and the column type is now VARCHAR for fields saved as VARCHAR. This should make the MyODBC driver better, but may break some old MySQL clients that don’t handle FIELD_TYPE_VARCHAR the same way as FIELD_TYPE_CHAR.

Understanding MyODBC Options

MyODBC allows the user to specify several options affecting the behavior of the ODBC connection. I don’t know what all of the options do, but will present those I have used or can find a description of. I will be updating these in the future.

Don’t optimize column width.

Return matching rows.

Trace MyODBC. This option activates logging of SQL statements sent to the MySQL server. It is useful when you encounter difficulties connecting to the database. The log is written to the file “myodbc.log” on the C:\ drive. (Note that you must use MYSQL.DLL and not MYSQL2.DLL for this option to work!) Once you have generated a log file, check the queries that MyODBC sends to the MySQL server; You should be able to find the queries by searching for the string “>mysql_real_query” in the “myodbc.log” file.

Allow BIG results. SQL_BIG_RESULT can be used with GROUP BY or DISTINCT to tell the optimizer that the result set will have many rows. In this case, MySQL will directly use disk based temporary tables if needed. MySQL in this case will prefer to do a sort instead doing a temporary table with a key on the GROUP BY elements.

Don’t prompt on connect. When initiating an ODBC connection, do not ask the user to manually enter username and password (and possibly other information).

Simulate ODBC 1.0. Behave like older ODBC.

Ignore # in #.table.

Use manager cursors (experimental).

Don’t use setlocale.

Pad CHAR to full length.

Return table names in SQLDescribeCol.

Use compressed protocol.

Ignore space after function names.

Don’t optimize column width.

Force use of named pipes.

Change BIGINT columns to INT.

No catalog (experimental).

Read options from C:\my.cnf.

Safety (use this when you have problems).

Article | No Comments | October 17th, 2008

One of the most powerful features of PHP is its ability to reduce the amount of site maintenance you need to do. By setting up a consistent site template, you can reduce the effort needed to create new pages, and you can also make it much easier to change the design of your entire site. This tutorial takes you through a simple example of how to set up and access a site template.

Tip It may not be immediately clear that any pages including headers and footers must be “made dynamic” by converting them from static HTML pages to PHP pages. For example, to make your homepage capable of including headers and footers rename it with the .php3 extension (or whatever extension you are using to denote PHP scripts on your server) and add the PHP code for the includes. Included files are not required to have the .php3 extension, but can be called .html or even a made up type like .inc or .inf. Whatever they are called, included files will be executed.

The first thing you need to do is design your HTML header and footer. This is all of the information that is presented the same way in all of your pages. The header typically includes the HEAD section of your site, perhaps a button bar and site advertising, and the page heading. The footer typically includes a copyright notice and contact information for your site. Here are some examples:

header1:

<HEAD>
<TITLE>>My Cool Site</TITLE>
</HEAD>
<BODY BGCOLOR=”#FFFFFF”>
<H1>The Coolest Site Ever</H1>

footer1:

<PRE>
</PRE>
<CENTER>
<A HREF=”http://www.coolsite.loc/legal.php3″>Copyright</A> 1999 By Me<BR>
Written by: <I>The Author</I><BR>
Questions? Write to <B>me@coolsite.loc</B><BR>
</CENTER>
</BODY>

Dynamic Headers and Footers


The next step is to replace the portions of these files that you want to be dynamic with php variables. This can include such things as title, keywords, description, email contact, and more. The basic element of this step is adding the statement for each dynamic text chunk. The examples as modified are below:

header2:

<HEAD>
<TITLE><?php print $strTitle; ?></TITLE>
</HEAD>
<BODY BGCOLOR=”#FFFFFF”>
<H1><?php print $strCaption; ?></H1>

footer2:

</PRE>
<CENTER>
<A HREF=”http://www.coolsite.loc/legal.php3″>Copyright</A>
1999 By Me<BR>
Written by: <I><?php print $strAuthor;
?></I><BR>
Questions? Write to <B><?php print $strContact;
?></B><BR>
</CENTER>
</BODY>

Separating Content From Markup


Pretty simple, eh? OK, now we’re going to get a bit more complicated. Say you want to be able to change the values of the dynamic content across your entire site very rapidly. The easiest way to do this is to move the information into a global configuration file, and store page-specific portions of it in an array. For those new to programming, any array is simply a list that can be accessed through either a numeric or a textual key. An example of a config file follows:

config1:

<?PHP $aryConfig = array(
“main” => array (
“title” => “My Cool First Page” ,
“caption” => “Welcome to the Coolest Site Ever” ,
“author” => “The Author” ,
“contact” => “me@coolsite.loc” ) ,
“second” => array (
“title” => “My Cool Second Page” ,
“caption” => “More Coolness!” ,
“author” => “The Other Author” ,
“contact” => “other@coolsite.loc” )
);
?>

The configuration file is inlcuded just prior to including your header. This makes the values if all the oncfiguration variables available to code througout the entirie scope of the page. Now that the config file is in place, the header and footer need to be modified to read the array We still want to be able to override the global values locally so we will make sure to only use the global values if there is no existing value. This is done as follows:

header3:

<?PHP
if (empty ($strTitle)) { $strTitle = $aryConfig[$strPagename][“title”]; }
if (empty ($strCaption)) { $strCaption =
$aryConfig[$strPagename][“caption”]; }
?>
<HEAD>
<TITLE><?php print $strTitle; ?></TITLE>
</HEAD>
<BODY BGCOLOR=”#FFFFFF”>
<H1><?php print $strCaption; ?></H1>

footer3:

<?PHP
if (empty ($strAuthor)) { $strAuthor = $aryConfig[$strPagename][“author”] } ;
if (empty ($strContact)) { $strContact = $aryConfig[$strPagename][“contact”] } ;
?>
<PRE>
</PRE<
<CENTER>
<A HREF=”http://www.coolsite.loc/legal.php3″>Copyright</A> 1999 By Me&ltBR>
Written by: <I><?php print $strAuthor; ?></I><BR>
Questions? Write to <B><?php print $strContact; ?></B>&ltBR>
</CENTER>
</BODY>

And finally, the main page needs to be updated to access the config file as follows:

main2:

<?PHP
$strPagename = ‘main';
require (‘/path/to/includes/config.php3′);
require (‘/path/to/includes/header.php3′);
?>
<P>This is a the text of your page. Make it as fancy as you want!</P>
<?PHP
require (‘/path/to/includes/footer.php3′);
?>

As an additional step, it may be easier for you to move the actual page content to a separate file. This will allow easy content changes without worrying about messing up the PHP code. This requires a change to the main file and the addition of a contents file:

main3:

<?PHP
$strPagename = ‘main';
require (‘/path/to/includes/header.php3′);
require (‘/path/to/includes/contents.html’);
require (‘/path/to/includes/footer.php3′); ?>

contents1:

This is a the text of your page. Make it as fancy as you want!

This should be a good starting point for your forays into the jungle of dynamic site building. Have fun!

Tip You may be wondering how to make your index or home page load automatically now that it is dynamic. On the Apache web server, this is as simple as creating an .htaccess file in the directory containing a line including a DirectoryIndex directive that tells the server what files to consider “default” pages. You want to add the name of your PHP enabled home page to the list as follows. Make sure you put it first in the list to give it priority over other default pages but also keep at least one static default page in there for safety.

DirectoryIndex index.php3 index.html

Editor’s note: the example HTML code in this article is available in the phphelp examples file in the download area. (Corrections made to this article 11/13/99 and 2/9/2000).