Creating a New Database with phpMyAdmin

WampServer includes phpMyAdmin in it’s installation.  PhpMyAdmin is the most popular PHP-based database configuration utility, although there other options, such as Adminer, are starting to gain traction.  Since most web hosts include phpMyAdmin in their set of default tools, even if you use other tools, it’s good to have a basic understanding of phpMyAdmin.

To launch phpMyAdmin for WampServer, click on the WampServer icon in the system tray and choose phpMyAdmin from the menu (or you can open it directly in your web browser at http://localhost/phpmyadmin/.)  All MySQL installations have two databases which are built in:  information_schema and mysql.  These databases contain the MySQL metadata and configuration details.  In general, you do not want to edit these databases directly, but it’s fine to query these databases to learn about MySQL.

To create a new database, click on the Databases tab.  At the bottom of the Databases tab is a field to create a new database.  Enter the name of the database that you desire (in my example, I’ll use workroom_inventory).  Note that database names must be a single “word”, but you can use underscores in place of spaces.  In the Collation drop down, select utf8_general_ci.  (Typically, the type of collation in MySQL is not important, but for reasonable flexibility, utf8_general_ci is a good choice.  See the MySQL documentation for more information about collation and character sets.)  Press Create to create the new database.  PhpMyAdmin will display the new database automatically.

You can now create the tables in your database.  Each table in your database stores a particular set of data.  MySQL is a RDBMS, or relational database management system, which means that you will normally have some relationships between the tables.  In database terminology, these are called keys.  Each table should have a primary key (PK), which is a value that uniquely identifies a particular record (row) in the table.  Preferably, the PK is an integer number and the PK column name is the name of the table plus “_ID”.  Thus, if we create a table named user, the PK column would be named user_id.  To create such a table, enter user in the Name field and specify the number of fields (we’ll use 6 to start with).  Press the Create button.

Next, phpMyAdmin will display a form to enter your database column (field) definitions.  The first column should be a PK column user_id.  Select INT (integer) as the type and choose UNSIGNED in the Attributes drop down list.  In the Index list, choose PRIMARY, since this will be our PK.  Finally, check the A_I checkbox.  A_I stands for “auto-increment”, which means that MySQL will automatically assign the next available value for this column when a new record (row) is inserted.  This is a useful feature for PKs, since they must be unique.

Now, we can define the other columns in our database.  For the example of our user table, we’ll define the following columns:

  • username - VARCHAR with length of 25
  • password - VARCHAR with length of 32
  • first_name - VARCHAR with length of 25
  • last_name - VARCHAR with length of 25
  • email_addr - VARCHAR with length of 50

  • For each column and the table itself, choose Collation of utf8_general_ci.  Choose MyISAM as the Storage Engine.  (At this point, the choice of storage engine is not important.  As you develop more sophisticated applications, you will want to learn about the other storage engines, particularly InnoDB, to determine the one best suited to your application.)  Press Save button.  PhpMyAdmin will display the command executed to generate the table and then show the details in tabular form.  Here is the command that was generated and executed:

    CREATE TABLE `workroom_inventory`.`user` (
    `user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    `username` VARCHAR( 25 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
    `password` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
    `first_name` VARCHAR( 25 ) CHARACTER SET ucs2 COLLATE ucs2_general_ci NOT NULL ,
    `last_name` VARCHAR( 25 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
    `email_addr` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
    ) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
    

    Now that the table has been created, we can use phpMyAdmin to populate a few values.  Click on the Insert tab.  Enter the appropriate values, but leave the user_id field blank.  Remember that it is defined as “auto-increment”, so MySQL will automatically assign its value.  Press the Go button to enter the values into your table.  Now click on the Browse tab to display your data.

    That’s it for this lesson.  Hope to see you again soon for our next discussion.

    Installing and configuring PEAR on WampServer

    PEAR is a framework and system for installing a large set of libraries and classes that provide standardized and well-supported functions for PHP. PEAR stands for PHP Extension and Application Repository. Some of the popular packages (PEAR’s name for collections of libraries and classes) include tools for easily building HTML forms, simplified database access, and standardized access to e-mail functions. Almost any common web development function that you can think of probably has a PEAR package.

    Even though PEAR is a framework, it’s not a framework in the traditional sense. Instead, each of the PEAR packages is more or less independent. Of course, you can use the various packages together to essentially build up your own framework. In one of our later tutorials, we’ll look at installing, configuring, and using a popular traditional model-view-controller (MVC) framework called CodeIgniter.

    WampServer actually makes it quite simple to install the PEAR tools and packages, since it comes with a script to do the installation. The installation script must be run as an Administrator in Windows. To begin, open a Windows Command Prompt as Administrator by select Start | Programs | Accessories. Right-click on the Command Prompt menu item and choose Run As Administrator. When prompted, approve running as Administrator.
    At the Administrator Command Prompt, change to the installation directory for PHP for your WampServer installation. Typically, this will be c:\wamp\bin\php\php5.2.11.

    To install PEAR, at the Administrator Command Prompt, run go-pear.bat, which is a wrapper script for PEAR installer. In general, you can accept the default options for the install script, except for the following:
    Public Web Files Directory (probably directory option #9) – Change to c:\wamp\www (your web “home” directory).

    After installing the PEAR packages, the installer will prompt you to update php.ini so that the INCLUDE_PATH contains the path to the PEAR installation. Just accept the defaults again. We will change this setting after the installer completes. Enter this entire path, including the php.ini file name, at the installer prompt.

    After the installer completes, you will need to update the INCLUDE_PATH variable in the php.ini file used by Apache. You can determine the location of this php.ini file by going to http://localhost/ and clicking on the phpinfo() link under Tools. On the phpinfo() page look for the Loaded Configuration File item; this is the php.ini file that you want to use. Open this php.ini file in a plain text editor, such as Notepad. Search for include_path in the file. Add the following line to the file:

    include_path=".;C:\wamp\bin\php\php5.2.11\PEAR"

    Also, you will need to update your PATH environment variable to include this directory, as well, so that you can run the pear.bat update script from any directory. Open the Windows Control Panel and open the System Control Panel applet and go to Advanced tab. In the Advanced tab, click on Environment Variables… button. In the Environment Variables window, locate the PATH variable. Either the User or System PATH variable is fine, but preferably update the System PATH variable. In the Edit System Variable dialog box, navigate to the end of the Variable value field and add ;C:\wamp\bin\php\php5.2.11\PEAR.  Note the semi-colon (;) at the beginning. Press OK and close the Environment Variables window and the Control Panel. For the change to take effect, you must close any Command Prompts that are open and open a new Command Prompt. (It is not necessary to open an Administrator Command Prompt, but you can use an Administrator Command Prompt, if desired.)

    The installation program also creates a registry file named PEAR_ENV.reg in your PHP directory. This script sets the PHP environment variables. Double-click on this file to merge it into your registry. Accept any permission prompts that Windows displays.

    That’s it for the install ation. To use PEAR, open a Windows Command Prompt and enter pear (or pear.bat). If you enter this command without any other parameters, PEAR will display the sub-commands that you can use. The main sub-command that you’ll use is install, which (obviously) installs the PEAR package that you specify. Also, you should probably become familiar with channel-add and channel-discover sub-commands so that you can install third-party PEAR packages (packages from sources other than the main PEAR repository).

    If you want to try out your PEAR installation, you can install the PEAR Date package, which simplifies handling of dates in PHP. To do so, open a Command Prompt and enter:

    pear install Date

    Note PEAR package names are case-sensitive, so you must specify Date (and not date). If everything is set up properly, you should see something similar to the following displayed in the Command Prompt:

    downloading Date-1.4.7.tgz ...
    Starting to download Date-1.4.7.tgz (55,754 bytes)
    .............done: 55,754 bytes
    install ok: channel://pear.php.net/Date-1.4.7

    You can also check that the installation completed successfully by running:

    pear info Date

    You should see a plethora of detail (probably more than you wanted to know!) about the package.

    Set up Subversion using WampServer Apache support

    Subversion is a popular SCM (source code management) application.  Subversion allows you to keep various versions of your web development files.  This way, if you make a mistake or decide you want to go back to an earlier version of your site, you can simply find the desired version.

    Version control and SCM is very complex topic, but we will try to focus on the simple tasks of installing Subversion using Apache for hosting your files via HTTP.  The importance of having HTTP hosting for Subversion is so that you can use the Subversion integration in the Eclipse integrated development environment (IDE).

    The key to a successful Subversion installation is to use the version with the proper binaries (already compiled files) for the Apache server in your WampServer installation.  At the time of this writing, WampServer includes Apache version 2.2.11, which can use Subversion version 1.6.6.  Download Subversion from http://subversion.tigris.org/servlets/ProjectDocumentList?folderID=8100.

    The default installation location in the Subversion installer is C:\Program Files\Subversion\.  However, since we will be using Subversion with our WampServer installation, change the installation directory to c:\wamp\svn.  (“svn” is the common short-hand for Subversion.)  Other than this change, you can use the other installation defaults.

    Subversion stores its data is sets of files called repositories.  You must have at least one repository to use Subversion.  Most of the Subversion application files are command line utilities.  However, we will be insulated from most of these utilities after completing this configuration by using Subversion from Eclipse.  The structure of your repository is very important.  To that end, we will be using the “standard” repository structure recommended in the Subversion “Red Book”, which is the canonical reference for Subversion:

    .../project_name/
    trunk/
    tags/
    branches/

    In this structure, project_name represents the so-called project root directory, which is the top-most directory for files related to project_name.  The trunk sub-directory is where the main code and files for your site or project is stored.  You can think of the trunk as the “main branch” of a tree.  The tags sub-directory contains “snapshots” of your files from particular points in time.  For example, when you release the first version of your application or web site, you’ll probably want to create a version 1.0 snapshot or tag.  In general, you never change files in the tags directory; they are simply reference points in your files.  Finally, the branches directory is used for creating named branches of your main code (i.e., from the trunk).  Typically, branches are used when you need to do parallel development, which means that two development activities on the same set of code (files) is taking place simultaneously.  Usually, at some point, you merge the code from one (or more) branch back into the trunk before continuing development.

    We’ll use a sub-directory of c:\wamp\svn called repos as the home for our repositories (e.g., c:\wamp\svn\repos).  Often, this directory is referred to as the repository “root” directory.  To do this, open a Windows Command Prompt:  Start –> Programs –> Accessories –> Command Prompt and change to the c:\wamp\svn directory:

    cd c:\wamp\svn

    Now create a new subdirectory named repos:

    mkdir repos

    Change to the the repos directory (cd repos) and create a directory with the name of the project. In this example, I’m developing a simple inventory system for a church teachers’ workroom, so I’ll name my project workroom for simplicity:

    mkdir workroom

    Now, we actually create the Subversion repository for this project using the svnadmin command:

    svnadmin create c:\wamp\svn\repos\workroom

    With our Subversion repository created, we turn to actually configuring Apache to be the “front end” for Subversion by providing the HTTP hosting for Subversion, so that we can access our repository directly from Eclipse.

    First, we need to copy the Subversion Apache modules from our Subversion installation folder to the Apache installation folder.  To do this, copy mod_authz_svn.so and mod_dav_svn.so from c:\wamp\svn\httpd to c:\wamp\bin\apache\Apache2.2.11\modules.

    Next, the Apache configuration file (httpd.conf) is updated to load these modules.  Open c:\wamp\bin\apache\Apache2.2.11\conf\httpd.conf with a plain text editor, such as Notepad.  In httpd.conf, look for the lines that start with LoadModule and add the following lines in this section and save the file:

    LoadModule  dav_module             modules/mod_dav.so
    LoadModule  dav_svn_module         modules/mod_dav_svn.so
    LoadModule  authz_svn_module       modules/mod_authz_svn.so

    Next, we set our Subversion configuration parameters in the Apache confiugration file.  At the end (bottom) of httpd.conf add the following lines and save the file:

    <Location /svn/>
        DAV svn
        #specify the root directory for repositories
        SVNParentPath c:/wamp/svn/repos/
    
        #list repositories
        SVNListParentPath on
    
        #access control policy
        	#authentication file
        	AuthzSVNAccessFile conf/apachesvnauth
        	#type of authentication
        	AuthType Basic
    		#the name of the authentication
        	AuthName "Subversion repository"
        	#file with user passwords values
        	AuthUserFile conf/apachesvnpasswd
        	#only allow authorized users to log in
        	Require valid-user
    </Location>
    The first line (“<Location /svn/>”) defines the “alias” that Apache will use for the Subversion repositories.  This means that by entering http://localhost/svn/, you'll be able to access your Subversion repositories.  The SVNListParent Path on directive tells Apache to list the names of the repositories when you browse to http://localhost/svn/.

    Note on the SVNParentPath line that the forward slash (/) is used, rather than the back slash (\) normally used in Windows.  The reason is that Apache uses the Unix directory separator, which is the forward slash.

    Also, you’ll notice that this configuration includes two additional files:  apachesvnauth and apachesvnpasswd.  These are the permissions configuration and user ID/password list files, respectively.  To create the apachesvnpasswd file, return to the Windows Command Prompt from earlier and change to the c:\wamp\bin\apache\Apache2.2.11\conf directory and execute this command:

    htpasswd -cm apachesvnpasswd username

    where username is the user ID that you want to use with Subversion.  You’ll be prompted to enter a password for this account.  The ‘m’ option means that the password will be encrypted using the MD5 algorithm.  (If you want to bypass the encryption and store the password in plain text, just leave off the ‘m’ in the command.)

    To create the apachesvnauth permissions file, open an empty file in a text editor, such as Notepad and enter the following lines:

    [workflow:/]
    username = rw
    * = r

    Replace workflow by the name of the repository that you created earlier and substitute your user ID for username.  This configuration means that your user ID has read and write permissions to all files in the project and ‘* = r’ allows any user to view the files (i.e., read-only access) in the project.  Save this file as apachesvnauth.  (You can also define groups and restrict access to specific sub-directories of a repository, but that is beyond the scope of this tutorial.)

    Now, stop and restart the Apache web server (or the entire WampServer system, if desired).  You can do this by clicking (with left mouse button) on the WampServer icon in the system tray and choose ‘Restart All Services’ from the menu.

    Installing and configuring WampServer on Windows XP, Windows Vista, or Windows 7

    WampServer is full-fledged web environment that runs on Windows and works very much like your web hosting provider’s system, except that it is located entirely on your computer.  With WampServer, you don’t even have to be connected to the Internet to do web design and development!

    WampServer consists of the following components:

    • Windows –  You’ve already got this part!  These instructions apply to Windows XP, Windows Vista, and Windows 7 (and may work on other versions, as well).
    • Apache – The Apache web server is a free, open-source HTTP server that is used on over half of the web servers on the Internet.  It is very well supported and has an active and helpful community, so it is quite easy to get help and find information about it.
    • MySQL – MySQL is a popular free, open-source RDBMS server platform.  As with Apache, it is widely used and has many users who share their experiences and tips.  In addition, one of the great advantages of MySQL is that his native integration with Apache and PHP.
    • PHP – PHP is also free and open-source and is a popular scripting language for developing dynamic interactive web applications.  PHP is both easy to learn, even for beginners, and powerful.

    You can think of Apache, MySQL, and PHP as three legs of a sturdy stool that stand on the foundation of Windows.  You’ll undoubtedly hear about LAMP, which is similar to WAMP, except that the ‘L’ stands for Linux, which is a popular open-source server operating system that runs many web hosting systems.

    To install WampServer, download the installation package from http://www.wampserver.com/en/download.php.  Run the installation package and choose the defaults for each prompt.  This will install WampServer to the c:\wamp folder (directory).

    If you did not choose to launch WampServer after the installation completed, go ahead and run WampServer from the Start Menu:  Start → Programs → WampServer → start WampServer.  If you received any security warnings (very likely in Windows Vista and Windows 7) permit WampServer to run.  These are warnings that WampServer wants to make updates to your system and to use network resources, which is normal since this is a web server environment! You’ll see an icon in the Windows system tray that looks like gauge or speedometer with a needle.  It will show red, then yellow, and finally (hopefully!), the needle will settle all the way to the right and the icon will be white.  Hover the mouse of the icon and it should display ‘WAMPSERVER – server Online’.  If so, everything is good.

    To confirm that the server is running and “serving” web pages, open your web browser and enter http://localhost/ in the address field and press <Enter>.  If all is good, you’ll see a web page with the WampServer logo and Server Configuration information.  This page will show you the versions of Apache, MySQL, and PHP that are installed, as well as the PHP extensions (more about them later!) loaded.  Congratulations!  You have a working web server platform running on your machine.  Wasn’t very hard, was it?!

    If for some reason WampServer is unable to start or, more likely, start completely, see this page for information about troubleshooting problems with WampServer.  Keep this page in mind as you make changes to the WampServer configuration.  It is almost inevitable that something will go wrong, but that shouldn’t deter you from experimenting and learning from your mistakes.  Again, the Apache, MySQL, and PHP communities are very helpful and the Internet is filled with resources on solving these problems.

    The web root folder will be, by default, set to c:\wamp\www.  The web root folder is like the “home” (base) folder for the local Apache web server.  Also, Apache is configured, by default, to “listen” on port 80, which is the standard port for HTTP, the protocol used for web sites. Apache is very flexible and we’ll talk later about how to change some of these common parameters.  If you look in the c:\wamp\www folder, you’ll see a file called index.php.  When you opened http://localhost/ to test your WampServer installation, the page that was actually displayed (really interpreted for display by the Apache HTTP server) was this index.php file.

    But how did Apache know to open index.php specifically?  This is just one of Apache’s huge set of configuration parameters (settings).  The main Apache configuration file is called httpd.conf. (“httpd” stands for HTTP daemon.  The term “daemon” simply means background service and is a term borrowed from Unix.  In other words a daemon that runs in the background and performs tasks without user interaction.)  You’ll find httpd.conf on WampServer in c:\wamp\bin\apache\Apacheversion\conf, where version is the version number of Apache in your WampServer installation (2.2.11 at the time of this writing).  Open httpd.conf with any text editor, such as Notepad.  Take a look through httpd.conf to get familiar with its contents.  In particular, we are looking for a line that contains DirectoryIndex.  When you find it, it will probably contain something like this:

       DirectoryIndex index.php index.php3 index.html index.htm

    Did you see that this line contains index.php?  Remember that we saw that c:\wamp\www folder contained a file named index.php?  The DirectoryIndex directive (Apache’s name for commands!) tells Apache what file names to look for if you don’t specify a particular file name when entering a URL (web address) and the order to look for them.  Therefore, in this case, Apache looks for a file named index.php first and, if it doesn’t find it, then for index.php3 and so on.  Thus, this is how Apache “translated” http://localhost/ to http://localhost/index.php when you tested the WampServer installation.

    The other important item from this discussion is that c:\wamp\www in your Windows file system (directory or folder structure) is essentially the same as the “root” folder of the web server provided by WampServer.  Thus, if you create another sub-folder in c:\wamp\www, such as c:\wamp\www\mysite, you can access it in your web browser by entering http://localhost/mysite.  (Remember that you’ll need to have a file in c:\wamp\www\mysite that Apache can interpret, such as index.html, or you’ll need to specify the specific file to display, such as http://localhost/mysite/myfile.php.)

    However, we haven’t explained the localhost part, yet.  Basically, localhost is just another name (called an “alias” in the web vernacular) for the loopback IP address of your computer, which is usually 127.0.0.1.  This alias is defined, on Windows systems, in the c:\windows\system32\drivers\etc\hosts file.  We’ll come back to hosts when we discuss setting up “virtual hosts” (basically multiple web “sites”) in Apache.  For now, just remember that c:\wamp\www corresponds to the “root” (top-level) directory of http://localhost/.

    That’s it for now.  Leave any questions in the comments and look for the next installment in the series soon.

    Web development on Windows

    Today, we are starting a new series on setting up a web development environment and developing web applications on Windows.  The series will focus on using WampServer, Eclipse, and CodeIgniter PHP framework.

    Some of the topics that we plan to cover include:

    • Installing WampServer on Windows XP, Windows Vista, or Windows 7.
    • Adding additional server versions to WampServer.
    • Creating a new MySQL database via phpMyAdmin.
    • Configuring multiple virtual hosts in Apache.
    • Installing Subversion and set up WebSVN with WampServer.
    • Installing and configure PEAR on WampServer.
    • How to set up Eclipse to work with WampServer environment.
    • How to use Eclipse with local Subversion (or Git?) environment.
    • Free remote Subversion and Git hosting services.
    • Installing Xdebug for PHP on WampServer and Eclipse.
    • Installing and configuring CodeIgniter.
    • Adding Zen Coding plugin to Eclipse.
    • Configuring mod_rewrite on WampServer for URL rewriting.
    • Moving your finished web design to web hosting platform.

    I hope your looking forward to the adventure.  If you have any suggestions for other topics, please leave your suggestions in the comments.