logo

PHP for Domino Developers, Part 1, MySQL

Whether you like it or not you are going to have to entertain the idea that Domino may not be around for ever. What will become of it in the years to come is anybody's guess. Will it survive, but with a DB2 backend? Will it get swallowed up by the migty, yet cumbersome, WebSphere? These are questions I get asked all the time. So, for the record, let me say I have no idea whatsoever. Either way it goes without saying that we all need to take this in to account when we consider our future career plans. Personally, I have been spending quite some time trying to expand my skillset. In particular I have been getting to grips with the LAMP platform - Linux, Apache, MySQL and PHP.

Whilst I am aware that this probably isn't the best of career moves, financially speaking, there is something else that drives me. It is easy and free to setup a LAMP environment in your own home. All you need is a spare machine that you can install the likes of Red Hat Linux on. If you can't do this then there's a myriad of internet hosts out there that offer hosting packages at next-to-nothing prices. My favourite being Easily's Virtual Hosting package. Either way, it's a lot easier than trying to get yourself a working install of something like WebSphere.

Once you have yourself a working LAMP setup you can start to learn about a whole new side to web development. As Domino developers we have it easy. We don't need to worry about how data is stored and how we handle this data when a form is submitted over the web. It's all pretty much done for us. I would even go as far as to say my dad could create a fully-functioning website with Domino. Now, don't get me wrong, I think this is a great thing. What worries me is that when you step outside of Domino development you may be in for a shock. It's no longer simply a case of adding fields to a form, adding a submit button and then creating a view. Most other systems will, without doubt, use a relational database to store the submitted information. You need to plan, design and create these databases. You then need to actually program forms and buttons and write the routines to enter the data to the backend and to retrieve it for web presentation.

It might sound like a lot of hard work but it can also be extremely rewarding. When you are completely in control of all the logic you can do whatever you like. Domino does a lot of things and it does them well. However, there are things it can't do. There are also things it doesn't do so well and the control over which you have absolutely no power. Welcome to LAMP, where anything is possible.

This is the first of a series of articles I plan to write that will introduce this other side to web development. What's important is that I will approach it from the perspective of a Domino developer. Starting with this article that will try and describe how you take the first step of creating a database. Followed by articles that will discuss how to replicate the features we are so used to using - forms, subforms, views, ACLs, authentication, full-text searching, agents etc. Needless to say we've had it easy as Domino developers.

Assumptions:

First things first. This article is not going to tell you how to setup and install Linux. Nor will it tell you how to configure LAMP on the machine. If you install Red Hat 9 with the web server option and make sure you select the right components you should be all set. If you have any trouble there are loads of articles already out there and loads of forums where you can get any question at all answered. Alternatively just get yourself some cheap hosting.

The L of LAMP is obviously for Linux. Although you can easily add the combination of MySQL and PHP to a Windows machine, I will assume you are doing so on Linux. I will also assume that you did this on a remote machine and you will be developing on a separate, Windows, PC. I will also assume you know a little bit about SQL. Enough to feel comfortable with the ideas of databases, tables and datatypes.

It might sound like more hassle than it's worth, using Linux. The reason I favour this approach is that I think it's most likely what I would encounter in the real-world. Consider it best-practice. There's no harm in having your own Linux machine where you can get to grips to what is the most common OS of all web servers.

Just remember. If I can do it then there's no reason why any of you guys can't.

Getting started:

When developing your application you will need to interact with the MySQL server. Instead of logging on to the Linux server and issuing MySQL commands at the console I would recommend you download the MySQL Control Center. You can use this to write your own SQL commands or perform standard operations like creating databases, users and tables without needing to access the server.

Before you can start using it you will need to add your Windows machine to the list of hosts allowed to access the server. This does require you to login to the server. Laucn a Terminal window and, at the command line, type "mysql" before pressing enter. This will launch the MySQL Monitor where you can perform SQL operations on the backend databases. In this case we need to issue a GRANT statement to add a new user. We will give full privileges to a user called "root" from any machine ('%') to the all databases. The command is:

GRANT ALL ON *.* TO 'root'@'%';

Here's what it looks like on the server. Don't be worried about the message telling us no rows were altered. This is normal.

MySQL command line

The above command is for a super-user known as root and with no password. If you want to be a little more cautious and add a normal user with a password, whilst limiting the operations they can perform and on which database, you would use something like:

GRANT SELECT,INSERT,UPDATE,DELETE ON codestore.* TO 'jake'@'192.168.0.6' INDENTIFIED BY 'password';

Once done, you can check the user(s) is added using some basic SQL commands. Still at the MySQL command, switch to the "mysql" database by typing "use mysql;". Then type "SELECT * FROM user;". You should see a table of data. In there is the user you just added.

Note: Don't equate this step with ACLs and/or Domino Directory users. There is an extra level required to manage users of your website that we will get to much later.

This should be all you need to do on the server. Log out and return to your Windows PC. Install and launch the MySQL Control Center. When it's loaded click on the New Connection icon and enter the details in to the boxes, like below:

MySQL Control Center - new connection

In this case I am connecting to a server known as "epswebsvr01" and as the super-user we created called root, who has no password. Press the Add button once you're done and it will return you to the Console. Double-click on the new connection on the pane in the left. If all has gone well you will see a list of databases, as below. Double click a database and you will connect to it. In the shot below I am looking at the tables in the mysql database. Notice the table called "user" that we were just looking at. If you double-clicked this table you will also get see all the rows in the table.

MySQL Control Center - listing table

Before you carry on reading this, you might want to spend a few minutes getting to know MySQL CC. It's going to be your friend!

Creating a Database:

What's the first thing you do when you start a new Domino site? Create a new database! It couldn't be easier really could it. Simply click File - Database - New from the Domino Designer menu. Depending on how you choose to do it, creating a database with MySQL isn't much more complicated. You can do it with a simple SQL CREATE command:

CREATE DATABASE codestore;

Or, from the Control Center you can simply create a new database from the Actions menu:

MySQL Control Center - new database

Simple as that. Nothing there yet though. Just an empty database with nothing in it. No tables, no nothing. We will get to that in a future article.

Summary:

Ok, so this was supposed to introduce you to PHP and I've not even mentioned it yet. Hold your horses. One step at a time. Before we get started with PHP you have to have a working install of MySQL. Hopefully, having followed all the steps above, you now have one. In the next couple of articles I will introduce PHP coding to you and then build on this foundation and look at how we create a "form" in PHP. For this we will be creating both tables with SQL and pages with PHP.


Feedback

  1. Suggestion for those without a spare machine

    An alternative to Linux for those who have a single windows dev machine is to get the all inclusive Big Apache package. It has all the free stuff packaged into a single install. Includes Apache, mySQL, perl, PHP, JBoss with Tomcat, OpenSSL.

    I look forward to the rest of the series. I am from a Domino dev background and have been playing with PHP, mySQL very superficially over the last couple of months.

    I would have thought, though, that there would be more interest in articles covering Servlet/JSPs in light of the close ties with WebSphere. Maybe Tomcat for Domino Developers?

      • avatar
      • Jake
      • Fri 3 Oct 2003

      Re: Suggestion for those without a spare machine

      Thanls Neil. For those interested, you can find it here - http://www.bigapache.org/

      You're right, a Tomcat for Domino Developers is a good idea. One I will keep in mind for the future.

      Jake

      Hide the rest of this thread

        • avatar
        • Cat
        • Thu 26 Feb 2004

        Re: Suggestion for those without a spare machine

        www.bigapache.org - down for the last two days (Feb 25, 26 2004) Cat

    1. Re: Suggestion for those without a spare machine

      I definitely see the value now with PHP and JSP's with tomcat. After sept 11 companies have had to look for cheaper alternatives. Im not saying that cheaper is the most cost effective... but that if Linux was able to penetrate market share, and carry along tomcat and apache... php is also part of the LAMP package and is definitely evolving to turn out to become a more feasible alternative. I don't see why reading php in a url is a shame in a corporate environment anymore... php has gained respect and will continue to move along.

  2. Hmm, why so?

    Jake, you state in your opening comments about moving into LAMP that "Whilst I am aware that this probably isn't the best of career moves, financially speaking, there is something else that drives me"

    Why so? I am not disagreeing as much as not understanding why you say this... I too am a Domino developer... but as most of my projects are web based, I want to get into an environment where licensing and no. of users is not a concern (with regards to licensing, CALs and all the other ways one gets soaked...) thus my drive to become a LAMP developer. I really enjoy Domino development and enjoy the RAD and security aspects of Domino (and I am a PCLP in fact), however, my interest in the opensource movement is steadily increasing...

    Comments? I'd appreciate your details on this...

      • avatar
      • Jake
      • Fri 3 Oct 2003

      Re: Hmm, why so?

      "Whilst I am aware that this probably isn't the best of career moves, financially speaking, there is something else that drives me"

      I meant two things by this. Firstly that, it's my understanding that, LAMP jobs aren't as readily available as say Notes jobs are. I've said before that I see PHP as something of a hobbyist's tool. There are jobs out there but I'm not learning LAMP in the expectaction that my career is headed that way.

      Talking of hobbyists. I am one. Which brings me to the second point. I am driven by my love of my chosen profession. I love messing with code and learning new technologies. I love PHP because, from the outset, it's been designed to do what I want a web "server" to do. It's a breath of fresh air after spending so long under the shackles of Domino.

      Hope that clears it up, Jake

      Show the rest of this thread

  3. thanks for writing about your experiences w/ this

    Hi Jake-

    Just wanted to say thanks for writing about your experiences w/ all this. I have been meaning to begin dabbling with MySQL and PHP, and will enjoy following along.

    -Seth Rhode Island USA

  4. Re: PHP for Domino Developers, Part 1, MySQL

    Hi there! Great article. I just start to play with Apache+mySQL+PHP and I had a lot of problems to setup my only windows machine with all thoses programs. I´ve found as tool called EasyPHP (http://www.easyphp.org). It is a great all in one packadge with Apache + mySQL + PHP setup in just one click.

    I hope that help some one.

    Valdecir Carvalho São Paulo, Brasil - sorry about my poor English =)

    • avatar
    • Darryl
    • Fri 3 Oct 2003

    Great start

    I have been moving from Domino to LAMP for about 6 months now and will be watching your experiences with interest. This month has been frustrating. We spent so much time trying to find a way around the 64k lookup limit (good article by the way) when I know this just isn't a concern for PHP/MySQL.

    A warning for all Domino developers out there! I know for a fact that if our managers knew more about PHP/MySQL (mainly their incredible performance and ease of development as well as their cost effectiveness) they will start to seriously consider why they pay so much to have us figure out problems like the one above.

    Keep up the good work Jake.

    • avatar
    • HUK
    • Sat 4 Oct 2003

    Bravo

    Thank you jake! i can't wait till you get into code. I have been doing only web development with domino and have been wanting to get into LAMP, you will be the catalyst!

  5. Recommend Dreamweaver

    Great article !

    Like you, I'm a seasoned Domino developer who has fallen under the spell of PHP/MySQL.

    For anyone out there who is ready to take the next step with PHP/MySQL, I would thoroughly recommend the latest version of Macromedia Dreamweaver (MX 2004).

    It fully supports PHP/MySQL and includes some terrific PHP "Server Behaviours" that really help to cut down on the amount of hand coding that you need to do.

    My favourites are the "User Authentication" behaviours : "Log In User", "Restrict Access To Page", etc. You just point and click to add them to your web pages.

    It's not quite as straight forward as Domino development, but's it's pretty close. You still need to roll up your sleeves and tinker with the PHP code every now and again, but that's half the fun !

    Download a trial version and give it a go !

  6. Web interface

    There is a web interface tool available for MYSQL. Using this we can perform all kinds of operations with the database. I hope this kind og tool will be really useful for domino developers

  7. WIMP !

    By coincidence last month I had an urgent need to learn PHP for a potential new project. I went for the WIMP approach ! Windows, IIS, MySQL and PHP. Rather than struggle with Linux, I noticed MySQL came with an IIS addon. All seemed to install and work ok, and within the 2 days I gave myself had the client's semi-finished website up and running on my desktop machine.

    I didn't get the job though!

    Give me Domino though any day.

      • avatar
      • Jake
      • Thu 9 Oct 2003

      Re: WIMP !

      LOL. WIMP. I love it ;-)

  8. Welcome to Linux

    Jake, I don't know if you have used Linux for a long time or not, but for the NOVICE people there is a package you can install which will help with the database creation and other tools. The package is called WEBMIN. This tool is very good because it runs off the apache server and you can interact within the browser. Need to add an user and you don't the SQL command then let webmin do it for you.

    Webmin does more that just MySQL, but for the novice's it is a good start.

    You can download webmin at http://www.webmin.com

    HTH -- Cheers

    • avatar
    • Jack
    • Sat 29 Nov 2003

    mysqlcc segmentation fault - solved

    I was getting segmentation fault in linux mysqlcc and equivalent errors on Win98. Seems it was down to a combination of specifying a password for 'root' and trying to connect through a firewall that was blocking port 3306 or on linux just trying local access (localhost).

    I think the connection failure mode for mysqlcc could be a little more robust!

    What I don't have skills for yet is working out how to remove the password from root so I had to create a new user with no password.

    All seems to be working now. Hope it doesn't take another whole week to get though part 2.

      • avatar
      • villa
      • Fri 5 Dec 2003

      Re: mysqlcc segmentation fault - solved

      Was MySQL running when you opened MySQLCC?

      I have found that MySQLCC tries to reopen it's last connection, but if the server is down, I was getting a segmentation fault...

      Show the rest of this thread

  9. mysqlcc

    hi,

    i have a problem i am running mysql in linux machine. I wrongly deleted root user and now i am not having permissions to access the root user. Can any one help to solve this problem

    Regards,

    Venkatesh.

      • avatar
      • Jake Howlett
      • Thu 17 Mar 2005

      Re: mysqlcc

      This might help: http://www.notestips.com/80256B3A007F2692/1/TAIO-6AAGZU

Your Comments

Name:
E-mail:
(optional)
Website:
(optional)
Comment:



Navigate other articles in the category "LAMP"

« Previous Article Next Article »
None Found   PHP for Domino Developers, Part 2, Files

About This Article

Author: Jake Howlett
Category: LAMP
Keywords: LAMP; PHP; MySQL; database;

Options

Feedback
Print Friendly

Let's Get Social


About This Website

CodeStore is all about web development. Concentrating on Lotus Domino, ASP.NET, Flex, SharePoint and all things internet.

Your host is Jake Howlett who runs his own web development company called Rockall Design and is always on the lookout for new and interesting work to do.

You can find me on Twitter and on Linked In.

Read more about this site »