Home arrow PHP arrow Website Database Basics With PHP and MySQL

Website Database Basics With PHP and MySQL

The World Wide Web (WWW) does only one thing - provide information. If you have information about something, you can share it with the world by building a website. As your website grows you may run into two problems: Your website has so much information that visitors can't quickly find what they want and visitors want to give you information. Both of these problems can be solved by building a database on a website. This introductory article shows you how to do this using basic PHP-MySQL interaction.

  1. Website Database Basics With PHP and MySQL
  2. HTML talks to PHP talks to MySQL
  3. Verifying form data
  4. Using cookies to identify and track visitors
  5. Weird SQL: What The Books Don't Tell You
  6. Checkboxes and other HTML form processing
By: Thomas Kehoe
Rating: starstarstarstarstar / 100
January 11, 2000

print this article


  • Why put a database on a website?
  • Why PHP and MySQL?
  • Reference documentation
  • Software applications you'll need
  • Running PHP
  • Running MySQL

      Why put a database on a website?

      The World Wide Web (WWW) does only one thing: provide information. If you have information about something, you can share it with the world by building a website. As your website grows you may run into two problems:
      1. Your website has so much information that visitors can't quickly find what they want.
      2. Visitors want to give you information.
      Both of these problems can be solved by building a database on a website.

      My Stuttering Science & Therapy Website has a page for persons who stutter to find penpals. This page became very popular. Nearly one thousand stutterers poured out their life stories, wanting to share their experiences with like souls. Men, women, young, old, students, attorneys, nurses, firefighters, from all over the world. Eventually the file took the better part of an hour to download on a 28.8K modem.

      Maintaining the webpage took too much of my time. For every stutterer's request I approved, I rejected two or three requests from people who hadn't bothered to read that this webpage was for stutterers. Usually these were from teenagers. Some of these were sexually explicit.

      Then there were the bad e-mail addresses. AOL users didn't understand that they had to attach "@aol.com" at the end of their e-mail address.

      I needed a database. Users would fill out neat forms listing their age, location, profession, etc. Other users could search just for the people they wanted to meet, e.g., nursing students who stutter, or young women who stutter in Ohio. Within seconds users would find just who they were looking for.

      The software could check if e-mail addresses were valid. Teenagers who didn't check the "stutterer" box could submit their penpal requests — and these requests wouldn't be read by users looking for stutterers.

      Databases are everywhere on the WWW. Yahoo! and eBay are databases. When you track your Federal Express package, you search for it in a database. Amazon.com is a huge database of millions of books, CDs, and other merchandise.

      Why PHP and MySQL?

      There are many database applications. I'd used Filemaker Pro for almost 15 years, and 4th Dimension (4D) for six years. These applications run on Windows and the Macintosh. Filemaker Pro is easy to set up and use. You just type in the fields you want; click if they're text, numbers, dates, photos, etc.; resize boxes and change text colors to look nice on your monitor, and you're done. 4D is similar. but with more advanced "pro" features.

      Filemaker Pro and 4D databases can be put on websites. I decided not to use these for three reasons:

      1. My website runs on a UNIX server.
      2. I kept running into things Filemaker Pro can't do.
      3. Filemaker Pro and 4D can interact with other applications, but this can be difficult.
      As far as I know, every major commercial website database uses a database called SQL. SQL is not a database application, but rather is a langauge for querying a database. It stands for Structured Query Language. The most popular "pro" SQL database application is Oracle. The big boys use this, and it costs a fortune.

      In recent years several companies have developed "open source" SQL applications. The most popular is MySQL. It's more or less free, and more or less as powerful as Oracle, at least for small to medium-sized databases. MySQL runs under UNIX (there are also Windows versions).

      To run MySQL on a website, you need a scripting language to make your HTML pages talk to the database. Perl used to be popular, but the consensus seems to be that PHP is better. Don't ask me to explain the differences — I used Perl once and it worked fine, but everyone seems to be using PHP.

      The other main scripting langauge is Java. Java has the advantage of running client-side scripts, in other words, programs can be downloaded and run on the visitor's computer. This is a good idea if a program will be run many times, and the user has a slow modem connection. I don't know much about Java — again, it seems like everyone uses PHP with MySQL, and this works for me so I haven't learned Java.

      Reference documentation

      This is a tutorial. I'll tell you how to use the most popular features of PHP and MySQL. You'll also need reference documentation, to look up features I skipped or covered quickly.

      The powerful UNIX operating system runs most web servers. UNIX is not like Windows or the Macintosh. MySQL runs only on UNIX (a Windows version is under development). I use O'Reilly's UNIX In A Nutshell reference book.

      HTML is the language for the static (text, graphics) and structural parts of websites.

      PHP is the language I use for the dynamic or interactive parts of websites.

      SQL is a language for interacting with databases. MySQL is a database that understands the SQL language.

      • I've tried three reference books, and the one I like is Introduction To SQL, by Rick van der Lans.
      • On-line documentation is on the MySQL website.
      • O'Reilly's MySQL & mSQL covers what's unique about MySQL, but you still need a SQL reference book.
      • The MySQL e-mail list gets 50+ messages a day.
      There is also third-party documentation and tutorials on the WWW, including:

      Software applications you'll need

      PHP and MySQL are more or less free, but getting the applications to run may be a challenge. If you have a computer running UNIX, you can download ( PHP, MySQL) and install the applications. The documentation above explains how.

      For me it's easier to pay for an account on a UNIX server, and let someone else do the administration. A web search for "MySQL website hosting" will turn up many host companies. I'm happy with phpwebhosting.com.

      You'll need a World Wide Web browser, such as Netscape Navigator or Microsoft's Internet Explorer.

      To access a remote UNIX server from a personal computer, you'll need a software application called a "terminal emulator". A terminal emulator opens a window into which you can type command lines to do stuff on the UNIX server. If you used computers before the Macintosh and Windows, this will be familiar to you. I don't know about terminal emulators for Windows. Macintosh terminal emulators are available from White Pine Software. You may be able to find an old shareware terminal emulator, but it may not connect via TCP/IP (in other words, it'll call a computer directly via a modem, but won't connect via the Internet).

      If you're using a remote UNIX server, you'll also need a file transfer (FTP) application. You'll use this to move documents you created on your computer to the UNIX server. On the Macintosh, the most popular FTP applications is Fetch.

      Another software application you'll need is an ASCII text editor. ASCII is "plain text" letters, numbers, and punctuation. It doesn't have bold or italic or different fonts or font sizes. Most word processors will "Save As…" a file to "text" or ASCII. I find this doesn't work well in Microsoft Word 98, and I hate how it goes into "browser mode" when it sees HTML code, so I don't recommend using Word 98. Instead, I use BBEdit, which runs on the Macintosh. Keith Edmunds wrote an excellent review of 19 text editing applications with an eye for how well they write PHP code.

      Running PHP

      Let's see if PHP runs. We'll start with printing "Hello, world." on your browser.

      In your text editor, create a document called helloworld.php. In the document, enter the following code:

      <html> <body> <?php print "Hello, world."; ?> </body> </html>
      Save the document as ASCII text.

      If you're running PHP on your computer, open the document with your browser. You should see "Hello, world." in the browser window.

      If you're running PHP on a remote UNIX server:

      1. Save the document as ASCII text.
      2. Connect to the Internet.
      3. Transfer the file (using Fetch or equivalent) helloworld.php to your server. You could use your terminal emulator and a UNIX text editor to create the document helloworld.php on the UNIX server, but I find it easier to use my computer's text editor and then transfer the file.
      4. Use your browser to go to the file helloworld.php on your UNIX server. I.e., use Internet Explorer or Netscape Navigator to go to http://www.yourwebsite.com/helloworld.php.
      You should see "Hello, world." in the browser window. Remember this three-step process for viewing your PHP creations: save as ASCII text, transfer to the UNIX server, view with your browser.

      If it didn't work:

      1. Use Fetch or your terminal emulator to make sure the file helloworld.php is on your UNIX server. There may have been a problem transfering the file.
      2. PHP may not be running. Contact your system administrator.

      Running MySQL

      Now we'll check if MySQL is running. If you're using a remote UNIX server, use your terminal emulator to go to your UNIX server.

      At your prompt, type mysql -u username -p. The server should then ask for your password. Then you'll get a welcome message and the prompt changes to mysql>. The whole exchange should look like:

      $ mysql -u username -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 31110 to server version: 3.22.25-log Type 'help' for help. mysql>
      If your prompt changes to mysql>, then MySQL is running.

      If it didn't work:

      If you got a response such as

      bash: mysql: command not found
      then MySQL is not installed on your computer. Contact your system administrator.

      If MySQL is running, enter your database by typing (replace "dbname" with the name of your MySQL database):

      use dbname;
      You should get the response:

      Database changed

      If it didn't work:

      If you got a response such as

      ERROR 1044: Access denied for user
      the problem may be that you need to create a database. My system administrator did this when he set up my account, so I don't know how to do it. Look in the MySQL documentation.

      Now create a table in your database. Replace "tablename" with the name of your table.

      CREATE TABLE tablename ( first_name VARCHAR (25), last_name VARCHAR (25) );
      Now check that your table is there by typing:

      show tables;
      You should get a list of tables:

      +------------------+ | Tables in dbname | +------------------+ | tablename | +------------------+ 2 rows in set (0.00 sec)
      If PHP and MySQL are running, then the next chapter will show you how to make HTML forms run PHP scripts that query a MySQL database. Now quit MySQL by typing

      You should get your UNIX prompt back. Now that everything is running, we'll work on getting PHP and MySQL to talk to each other and to HTML.

      >>> More PHP Articles          >>> More By Thomas Kehoe

      blog comments powered by Disqus
    1. escort Bursa Bursa escort Antalya eskort


      - Hackers Compromise PHP Sites to Launch Attac...
      - Red Hat, Zend Form OpenShift PaaS Alliance
      - PHP IDE News
      - BCD, Zend Extend PHP Partnership
      - PHP FAQ Highlight
      - PHP Creator Didn't Set Out to Create a Langu...
      - PHP Trends Revealed in Zend Study
      - PHP: Best Methods for Running Scheduled Jobs
      - PHP Array Functions: array_change_key_case
      - PHP array_combine Function
      - PHP array_chunk Function
      - PHP Closures as View Helpers: Lazy-Loading F...
      - Using PHP Closures as View Helpers
      - PHP File and Operating System Program Execut...
      - PHP: Effects of Wrapping Code in Class Const...

      Developer Shed Affiliates


      Dev Shed Tutorial Topics: