Practical PHP and MySQL Web Site Databases: A Simplified Approach (2013)
Chapter 1. Create and Test a MySQL Database and Table
This chapter introduces the concept of a database and a practical way of testing it. Using the projects, you will create a MySQL database and a table. As you work through the projects, you will become familiar with the phpMyAdmin interface.
This chapter has the following main sections:
· Definitions
· The free tool for developing and maintaining interactive databases
· Using phpMyAdmin to create a database and a table
· Exploring SQL
· Deleting databases, tables, and rows.
Defining the Term Database
Databases can be used to store products, details of customers, records of members of a society or a club, and much more. They can store names, passwords, addresses, e-mail addresses, registration dates, blog entries, and telephone numbers. Databases can be regarded as folders containing tables of data. The table of data, like all tables, has columns and rows; however, the rows in database tables are called records. A typical database table is shown in Table 1-1.
Table 1-1. A typical database
Defining Developer, Administrator, and User
In this book, the term “developer” (a.k.a. “webmaster”) means the person who designs and produces the database; he or she will integrate the database into a web site. Sometimes I will use the term “webmaster” or “web designer.” When I do, it usually means the same thing as “developer.” The words “administrator” and “membership secretary” have the same meaning in some of the book’s tutorials, which are based on building a database for a club. The word “administrator” means the person responsible for monitoring and maintaining the content of the database tables. Clearly, one person can be both a developer and an administrator. However, most developers will maintain the structure of a database but will not want the hassle of amending and deleting records; that should be the role of an administrator (a club or society’s membership secretary, say).
The “user” is any member of the general public viewing and possibly interacting with a web site database. For security reasons, users have extremely limited access to the database; however, they will be allowed to register for membership, log in to a special section, or change their password.
Caution The organization commissioning a database must conform to the Data Protection Act for the territory in which the database is developed. This is especially important if that data is going to be used for profit. Conformance usually means obtaining a license. In addition the developer and administrator must normally sign a document confirming that they will never disclose the details of persons recorded in the database. In the UK, the Information Commissioner’s Office (ICO) requires an annual license fee based on the revenues of the organization that owns the database. Currently, there is no equivalent in the USA, but privacy laws differ between states. It is essential that you understand and obey the data-protection laws for your client’s territory.However, you do not have to apply for a license if you use fictitious data in a database for the purpose of learning and experimenting with databases.
Defining Interactive Web Sites
Interactive web sites are often called dynamic web sites; however, I prefer to use the word interactive because dynamic can signify so many things. For instance, it can mean moving, powerful, eye catching, flashy, exciting. To a beginner, none of those meanings define a web page that interacts with a user.
Dynamic is so often used to mean exciting, but there is little excitement to be seen in an interactive registration form. Dynamic is also a musical term meaning changes or variations in loudness or speed. If dynamic can refer to change, why were dynamic templates designed to provide consistency from one web page to another? The term interactive has one clear meaning and will be used from now on in this book.
MySQL (with PHP) allows users and administrators to interact with a database using web site pages. For instance, users can register as members of an organization via a registration page on a web site. Users will be able to supply their personal data for the membership tables. MySQL then enters the users’ input into the administrator’s tables automatically; this lightens the workload of the administrator. The web site’s registration page can be programmed to filter users’ data input and verify it. From an interactive page, users may even be allowed to update their own records in a database.
Interactivity means that the administrator’s workload is greatly reduced, but not completely. For instance, if the database is for a bookshop, the administrator will still have to enter any new titles and prices. On the other hand, an interactive database can be programmed to alert the administrator when the stock of a certain book needs replenishing.
In Chapter 2, you will learn to develop a simple interactive web site.
Only Use MySQL for Interactive Database Tables
A non-interactive database table means that only the administrator can enter or amend the table’s data. A non-interactive database would be more easily created and administered using a spreadsheet or database program, such as Microsoft Excel or Microsoft Access. Web site users cannot interact with such a database. Employing MySQL to create a non-interactive (static) version of the database would be like using a sledgehammer to crack a nut. A static database such as Excel or Access has one advantage, it cannot be easily accessed online by hackers; however, it has to be maintained by an administrator and is very labor intensive. Website users have no input, and they cannot search or update their data.
Using MySQL for a non-interactive version would not reduce the workload of an administrator, he would have to enter all the members’ data and verify that the data is genuine.
Note A few interactive web pages do not need a database in order to function. For instance, a Contact Us form can be regarded as interactive because it takes a user’s input and transmits it to the web site’s owner in the form of an e-mail; this can be achieved easily without a database. In this book, the term “interactive” always means the user can interact with a database.
Methods for Developing and Maintaining Databases
The four methods for managing databases are as follows (with the easiest method on the left and hardest on the right):
PhpMyAdmin — PHP —SQL command line — MS-DOS style command line.
In this book, we will be mainly using the first two methods, but not the MS-DOS-style command line. For interactive databases, you will need some PHP files. You do not need an extensive knowledge of PHP before you can create interactive databases. I introduce the PHP you require in the appropriate place in each project—that is, in context. The step-by-step, fully-worked examples will show you what MySQL and PHP can do and how to do it.
Because of its popularity, GUIs (mouse operated Graphical User Interfaces) have been developed to facilitate the task of developing databases. These are known as development platforms, and the platform used throughout this book is XAMPP.
A Brief Look Inside the Machinery
Databases need a server, a database program, and a PHP processor as shown in Figure 1-1. These can be downloaded as an all-in-one, ready-configured package. The testing and development of the projects in this book are based on the free XAMPP package that is available for all operating systems.
Figure 1-1. A diagram of the machinery for passing database information to and from users
Figure 1-1 shows the main components built into the XAMPP development platform. They are as follows:
· Apache is the web page server used by the great majority of hosts and on local computers for developing databases. PHP files and databases need a server in order to work.
· MySQL is the database, and it provides management tools.
· The PHP processor checks for errors and processes the PHP files that are needed to make databases interact with users.
· phpMyAdmin is a mouse-operated program for creating and maintaining databases and their tables.
A single all-in-one package such as XAMPP contains the four programs and is called a WAMP (Windows, Apache, MySQL, and PHP). In WAMPs such as XAMPP, the main components are preconfigured so that they can talk to each other. The equivalent on a Mac computer is MAMP, and on a Linux computer it is LAMP.
The folder htdocs is part of XAMPP and is the storage area for your web pages. Apache and MySQL, by default, look in htdocs for your web pages. These pages may be designed to allow users to interact with the database. Other pages will operate unseen as they transmit information back and forth between user and database. The pages are usually HTML and PHP files or a combination of both.
Caution Everything inside the envelope in the diagram shown in Figure 1-1 will be already installed on a remote host, but you should never use a remote host to create a database while you are learning. For security reasons, do not use a remote host until you have become proficient. Always learn and develop a database using a WAMP on your own computer. Note that a WAMP installed on your own computer is purely a development tool. The database, when developed and thoroughly tested, can eventually be uploaded to a host to make it available to users. Uploading a data base is covered in Chapter 7.
A Free Development Platform for Testing
You will not be able to test your work in the normal way—that is, by using a browser to view a database and PHP files located on your hard drive. However, you can develop, test, and view your database and PHP files by using a WAMP on your computer. This book assumes that you will use XAMPP on your own computer while you are learning, and for developing future database-driven websites.
I have omitted instruction on the EASYPHP and WAMPServer programs to save space; they are very similar and are as effective as XAMPP. I use XAMPP because I am used to it, and I think the interface is slightly better than the others (my opinion only).
Caution The earlier projects in this book are necessarily simple and are not secure enough to be uploaded to a host. When you have gained experience and confidence, and you are sure that you understand the security issues, you could adapt the book’s later projects for use in your own websites and then upload them to a remote host.
Using XAMPP on Your Own Computer
The XAMPP package is free and is preconfigured so that the components will talk to each other. This eliminates the hassle of the usual practice of downloading several individual components and then configuring them to work together.
At the time of writing, the most recent version of XAMPP is version 1.8.1. This version is used throughout the book. It has component versions as follows: Apache 2.4.3, MySQL 5.5.27, PHP 5.4.7, and phpMyAdmin 3.5.2.2. The package and its components are improved with each release, but the processes described in this book are rarely affected because the updates are usually backward compatible.
Caution Make sure that the package you intend to use contains version 3.5.2.2 of phpMyAdmin or later. All the instructions that follow do not relate to earlier versions; they had a few minor flaws, such as the interface column headings not lining up properly with the content, and also there was some occasional odd behavior. The new version seems slick and flawless.
Before I give you the instructions for downloading XAMPP, I need to settle a question that bothers every beginner concerning the transferring of a developed database from XAMPP to the remote host. If you use XAMPP on your own computer, a question will arise, as stated in the title of the next section.
Will I Be Able to Transfer the Database from XAMPP to a Remote Host?
The main thought that haunts a beginner is “If I develop a database on a local WAMP, will I be able to move it easily to a remote host?” Beginners have every reason to be worried because most manuals rarely give even a hint on this topic. However, the answer is “Yes, you will be able to move the database.” You will find full instructions in Chapter 7.
Now I will provide the information for downloading and installing XAMPP.
Caution Should you wish to explore other free WAMPs, it is possible to install both EASYPHP and XAMPP on the same computer. However, make sure one of them is shut down before opening the other; otherwise, they will fight for the same ports and cause annoying problems.
Download and Install XAMPP
XAMP is free and needs no configuring. To download the package, go to:
http://www.apachefriends.org/en/xampp-windows.html
The home page varies from time to time, so you may have to explore the buttons on the tool bar a little to load the screen shown in Figure 1-2.
The download page will state that you must have C++ MS VC 2008 runtime libraries installed. These are normally already installed in modern versions of Windows; however, the XAMPP installation page provides a URL where you can download the libraries if necessary.
Scroll right down the download page until you see the section illustrated in Figure 1-2.
Figure 1-2. Installing XAMPP
I chose the zip version for Windows, and this installed in 32-bit and 64-bit computers without any problems. I also used the installer version on another computer and found no difference in operation or appearance.
Download the file into your Downloads folder and then double-click it to unzip it into a new folder named xampp on the root of the hard drive; to avoid security issues, don’t install it in the Program Files folder. If your main hard drive is C:, the default folder for the installation will then be C:\xampp. During the installation, you may see some black screens with white text—just keep going until the installation is completely finished. You may see a window named XAMPP Options. The installation may demand a restart; my installations did not. You will be asked if you want to load the XAMPP control panel; click Yes. If XAMPP is running, you will see an icon in the Notification area like the one shown in Figure 1-3.
Figure 1-3. The XAMPP icon
The items on the XAMPP control panel labeled Running usually appear automatically, and you will then be able to stop the various modules. If they do not start automatically, click the start buttons on the XAMPP control panel for Apache and MySQL. If a button says Stop, that module is already running. What next? When the interface appears, change the language to your version of English. If you are asked about running the modules as services, choose to run Apache and MySQL as services, and then those modules will automatically start when you double-click the XAMPP desktop icon.
Caution The XAMPP icon in the Notification area is the same color and shape as the Java update icon.
Create a shortcut on your Desktop for XAMPP’s htdocs folder, and place it alongside the XAMPP icon as shown in Figure 1-4. Use this shortcut for loading your PHP files into the C:\xampp\htdocs folder.
Figure 1-4. Time-saving shortcuts
If a desktop icon was not created during the installation, I recommend that you go to the C:\xampp folder, and then create a Desktop shortcut for the xampp-control.exe file.
For maximum convenience, put the two Desktop items side by side as shown in Figure 1-4. One icon starts and stops XAMPP, and the other allows you to create and modify pages directly in the XAMPP htdocs folder.
One common problem is that Skype uses the same port as Apache. So if users have Skype running, Apache won’t start. You can change the ports in Skype’s advanced options screen. If you have web deployment Agent Services running, you will have to stop that to enable Apache to run.
Starting XAMPP
From here onward, to test your pages in XAMPP, double-click the desktop icon and check that Apache and MySQL have started. If they have not started, click the start buttons for each and then minimize the control panel.
The XAMPP control panel is shown in Figure 1-5.
Figure 1-5. The XAMPP control panel
Note that, under Service, I have shown that the first three modules are running as services, as indicated by the selected boxes. This ensures that those modules will run as soon as you start XAMPP.
Always minimize the control panel so that you have a clear desktop for starting work on your databases.
After starting Apache and MySQL, you can test your installation and examine all of the XAMPP examples and tools; to do this, enter the following address in your browser.
http://localhost/ or http://127.0.0.1/
Closing XAMPP
Close XAMPP when you have finished testing your database and PHP files. This will free up memory for tasks other than database development. To close down, click the minimized XAMPP control panel on the task bar and then click the Quit button on the control panel as shown in Figure 1-6. Alternatively, you can right-click the icon in the Notification area and then click Quit.
Figure 1-6. Closing down the XAMPP program
The security of a database and its data is extremely important. XAMPP provides an interface for making the database and tables on your computer safe from harmful interference, this is described next.
The XAMPP Security Console
The initial installation of XAMPP has the username root, and there is no password. If you use those settings on your own computer, there is a security risk when connected to the Internet. If you work in the same room with other people, the password will protect against interference as long as the password is not divulged to the other people. As a best practice, you should password-protect your working environment, and XAMPP has a Security Console that simplifies this task.
Start XAMPP by double-clicking the desktop icon. Then enter the following URL in the address field of a browser:
http://localhost/security/
A page will appear as shown in Figure 1-7. Select your language in the left panel.
Figure 1-7. The XAMPP Security Console
The unprotected components are indicated by boxes with a red background. Click the URL that is circled in Figure 1-7 and you will be taken to the page shown in Figure 1-8. Only the top half of the page is shown because the password is sufficient protection; the rest of the page can be ignored.
Figure 1-8. The XAMPP form for entering a password
Enter a password and confirm it. Then click the Password changing button.
Your data will now be more secure. If you go back to the security screen, you should see that (some of) those red labels are now green. You may have to restart XAMPP to see the changes.
We are now going to look at phpMyAdmin, which is used to administer your databases. Starting phpMyAdmin is very easy, and you can start it without running XAMPP. Let’s first look at how the two tools work together.
Accessing phpMyAdmin Using XAMPP
Using the address field of any browser, enter the following URL:
http://localhost/phpmyadmin/ or http://127.0.01/phpmyadmin/
Be sure to include the http://; otherwise, a browser like Chrome will treat it as a search string.
Note The sections describing the use of phpMyAdmin apply to any of the development platforms: XAMPP, WAMPServer, and EASY PHP.
You set the password in XAMPP earlier, so whenever you access phpMyAdmin you will need to log in using that password. This prevents Internet robots and human beings from interfering with your database. The latter case is very important if you work in an office with others—you could have a spy or mischievous meddler in the place where you work. When you access phpMyAdmin, a dialog box will appear as shown in Figure 1-9.
Figure 1-9. Enter the password in the dialog box to access phpMyAdmin
Enter your username (usually “root”) and password, and then click the button labeled Go. phpMyAdmin loads rather slowly, but it will eventually appear.
Note that open source programs are continually being improved and upgraded, and you may find that you have a newer version of phpMyAdmin in your XAMPP package than that used in this book. You may also see upgrade messages alerting you to a new version in the phpMyAdmin main window. Where personal data is concerned, security is paramount, so these incremental updates are a good thing for you, though they do mean that some of the screenshots in the book no longer accurately reflect what you see on screen. Don’t worry if an interface looks a little different from the ones shown in this book, the usage will normally be similar.
The phpMyAdmin interface may look a little daunting at first, but we’ll cover the relevant parts of it when we need to use them. For the moment, you can close the phpMyAdmin window and we’ll return to XAMPP.
You now know how to install and secure XAMPP, and you also learned how to start and stop XAMPP. Most of what you have just read will probably be very new, but there are some parts of XAMPP that you will recognize because they follow the normal Windows organization of files and folders.
The Familiar Bits
Within the XAMPP package, the structure of the folders and files will be familiar to Windows users, although their names may not be recognizable.
The XAMPP folders are shown in Figure 1-10.
Figure 1-10. The folders in the XAMPP package; the EASYPHP equivalent of htdocs is www
In Figure 1-10, note the htdocs folder. This is where you will place all your PHP files and the html pages for your website and databases.
Within the XAMPP folder, you will find a folder called MySQL. This folder contains a folder called data where the databases and tables will reside. Regard a database as a folder; a database must have a unique name. A file within the data folder contains all the information about the database, and it has the file type *.opt.
Tables are files; when you have created any tables, these will also live inside the folder named data and they will have the file type *.frm.
Now that you’re familiar with the look and feel of the tools you’ll be using, you’re ready to move ahead. The next section will take you nearer to creating your first database and table.
Planning a Database: The Essential First Step
The first and most important stage is to plan the database so that you have something practical to play with. Let’s assume we need to plan a database for the membership of an organization. Follow these steps:
1. Decide on a name for the database. We will give this database the name simpleIdb. Remember that the database is like an empty folder that will eventually contain one or more tables. The last part of the name, …Idb, stands for Interactive Database.
2. Assemble the data items into a table. I have given this table the name users. Decide what information you want in the table; your decision is not binding because you can change any part of the database during development. Let’s suppose we need five pieces of information about the users. I have set out some typical data in Table 1-1 earlier in the chapter and in Table 1-2.
Table 1-2. My draft plan for the database table named “users”
Each row in a table is called a record, and each cell is called a field. A database can contain more than one table. I have used some fictitious names to help plan the table. The first column is labeled user_id, and this column is additional to the five columns of data. The column user_id will be explained later; just accept it for the moment and be sure to leave it empty. Also, leave the registration dates empty because this is an automatic entry; it does not need examples, nor does it need allocated space.
3. Now we must allocate some space for the data. Table 1-3 shows the number of characters I have allocated for each item.
Table 1-3. The number of characters to allow for each piece of data in the table named “users”
4. Write down or print the two tables, and keep them close at hand because you will be referring to them in the next stages.
5. Now decide on a username and password for the database, and enter that information in your notebook. Four pieces of information are required: the name of the database, the host, the password, and the username. In this project, these are as follows:
Name: simpleIdb
Host: localhost
Password: hmsvictory
User: horatio
Next we will create our first database using phpMyAdmin.
Create a Database Using phpMyAdmin
There is no need to start XAMPP to access phpMyAdmin, although you can if you wish. Note that you will need to have MySQL running, though. If, for some reason, you previously stopped this service, you will need to open up XAMPP to start it again. Open a browser, and then access phpMyAdmin by typing the following in the address field:
http://localhost/phpmyadmin
Click the Databases tab in the top menu. You will then see the interface shown in Figure 1-11.
Figure 1-11. The phpMyAdmin interface for creating the database
Type a name for the database. For this example, it will be simpleIdb, all in lowercase except for the uppercase letter I (for Interactive) in the last three letters. Then click the Create button (ignore the Collation field). After you click the Create button, the top part of the interface does not change. However, lower down you will see a list of items with check boxes. Figure 1-12 shows the lower part of the page and a list with check boxes.
Figure 1-12. In the lower part of the page, select the box next to the name of your new database
When you select the box next to your new database as shown in Figure 1-12, click Check Privileges and you will be taken to a screen where you will see a list of users that have access to the database. To make the database secure, you must add a username and password. Click the words Add User as shown in Figure 1-13.
Figure 1-13. The Add user icon is circled in this screenshot
Clicking Add User will load the Add a new User screen, shown next in Figure 1-14.
Figure 1-14. This screen enables you to add a user and a password
Caution Adding a username and password is absolutely essential; otherwise, your database will be insecure and vulnerable to attack by unscrupulous individuals or their robots. This is the most important habit to cultivate. Be sure to record the user and password details in your note book. Keeping a detailed record will save you hours of frustration later.
Using the pull-down menus, accept the default Use text field in the first field and enter the username in the field to right of it. In the second field labeled Host, select local. The word localhost will appear in the field on the right. Localhost is the default name for the server on your computer. Enter a password in the third field, and confirm your password by retyping it in the lower field. The Generate Password button will create a random strong password if you want something unique.
Scroll down, and where it says Global privileges (Check All/Uncheck All), click Check All. Because you are the webmaster, you need to be able to deal with every aspect of the database; therefore, you need all the privileges. If you add other users, you need to restrict their privileges by deselecting boxes such as Drop, Delete, and Shutdown.
Scroll down to the bottom of the form, and click the Add User button (or the Go button on some versions). You have now created the database and secured it against attack. The database can be regarded as an empty folder that will eventually contain one or more tables.
Note If you get lost when using phpMyAdmin and can’t see what you should do next, always click the little house at the top of the left panel. Hover over the icon to ensure that it is the Home button.
Now we will create our first table.
Create a Table Using phpMyAdmin
The GUI for inserting one or more data tables into a database is phpMyAdmin. It will give you complete control over your table(s), including troubleshooting and backing up.
Next, click the name of your new database; you will find it on the left panel. You will then see the screen shown in Figure 1-15.
Figure 1-15. Click the Go button, shown circled, to create the table
Enter a name for the table, and specify the number of columns. Then click the Go button (shown circled in Figure 1-15). You will be taken to a screen showing the columns flipped 90 degrees so that columns look like rows; this is shown in Figure 1-16. The fields are empty and waiting for you to define the table.
Figure 1-16. The six rows represent six columns. The column titles will be entered in the fields on the left
Use the data from Tables 1-2 and 1-3 that we planned earlier, and enter the column name, data type, and number of characters. The details for creating the users table are given in Table 1-4.
Table 1-4. The attributes for the users table
Accept all the default settings for each item except for the user_id. Here, you will need to select UNSIGNED, PRIMARY, and the type; also select the A_I box.
The various categories under the heading Type will be explained later; the heading Length/Values refers to the maximum number of characters. The Length/Values for the registration_date is left blank because the length is predetermined. Do not enter anything under the headings Default and NULL. The attribute UNSIGNED means that the user_id integer cannot be a negative quantity. The Index for the user_id is the primary index, and A_I means Automatically Increment the id number; as each user is registered to the database, he or she is given a unique number. The number is increased by one as each new user is added. The screen for specifying the attributes is shown in Figure 1-17.
Figure 1-17. This screen allows you to specify column titles and the type of content
The rows represent columns and they are very wide; you may have to scroll horizontally to enter some of the information. You will find more options as you scroll right, but we will not need them for this tutorial.
So how do you fill out the fields? Enter the six column titles in the fields on the left under the heading Name. Enter the type of column in the second column of fields under the heading Type. Select them from the Pull-down menus. The types used in this table are as follows:
· MEDIUMINT can store integers ranging from minus 8,388,608 to plus 8,388,607. You could choose the next smallest category SMALLINT if the number of users will never exceed 65,535.
· VARCHAR specifies a variable-length string of characters from 1 to 255 long.
· CHAR is a string of characters traditionally used for passwords. Be sure to give this 40 characters so that your database is able to encrypt the password using the function SHA1('$p'). MySQL then converts a password into an encrypted string of 40 characters. A user’s password can be, say, 6 to 12 characters long, but it will still be stored in the database as an encrypted 40-character string. This will be discussed further in Chapter 2, together with an alternative encrypted function md5(). Incidentally, SHA stands for Secure Hash Algorithm.
· DATETIME stores the date and time in the format YYYY-MM-DD-HH:MM:SS.
Enter the number of characters in the fourth column of fields under the heading Length/Values. Refer to Table 1-4 for these numbers.
Under the heading Default, accept the default None. This field is where you can enter a default value if you wish.
Now scroll right. Under the heading Attributes, use the drop-down menu to select UNSIGNED for user_id. This ensures that the integer range becomes zero to 16,777,215. This is because a negative quantity is not applicable for the user_id.
The next two entries concern only the user_id. Scroll right so that you can see the headings shown in Figure 1-18.
Figure 1-18. Two extra entries for the user_id column
For the user_id, under the heading Index, click the drop-down menu to enter PRIMARY. The user_id should always be a primary index.
Under the heading A_I, select the topmost check box so that the user_id number is automatically incremented when each new record is added to the database.
Now scroll to the bottom and click the SAVE button.
Caution If you forget to select the A_I box for user_id, you will receive an error message when you later try to enter the second record. The message will say that you are trying to create a duplicate value “0” for id_user.
Some people prefer a blend of GUI and command-line for programming a table, phpMyAdmin allows you to do this by means of SQL. However, this book will mainly use the phpMyAdmin GUI. The SQL alternative is described next. You can skip this section if you wish, but I recommend that you come back to it at some future date because you will undoubtedly come across SQL in other more advanced manuals.
The SQL Alternative
The next section describes a slightly quicker way of using phpMyAdmin for creating a database and a table. The SQL part of MySQL stands for Structured Query Language; it is the official language for MySQL databases, and you will be pleased to read that it uses plain English commands. The only problem is that it is easier to create typographical errors or spelling mistakes in the SQL window than in the phpMyAdmin interface shown earlier in Figure 1-17.
Using SQL, a database can be created complete with a password and username. This saves several steps.
I assume you have created the database simpleIdb, so we cannot use that name again. Let’s assume that an administrator (Adrian) wishes to create a database called members using the following information:
Database name: members;
Privileges: all
Username: adrian
Password: stapler12
Figure 1-19 shows the details entered into an SQL window.
Figure 1-19. The SQL window
In phpMyAdmin, return to the home page so that you are no longer dealing with simpleIdb. Click the SQL tab (shown circled) to reveal an SQL window.
The details shown in Figure 1-19 must be entered in the following format:
CREATE DATABASE members;
GRANT ALL
ON members.*
TO 'adrian'
IDENTIFIED BY 'stapler12';
Enter each item on a separate line by pressing Enter after a line. The SQL keywords (like CREATE DATABASE) are traditionally in uppercase. Other items are normally entered in lowercase. Note the semicolons and the single quotes—these are important. When you are satisfied with the entries, click the Go button.
Figure 1-20 shows the screen confirming that the database was successfully created, including its security features.
Figure 1-20. Showing confirmation that the database was created (which might not be dsiplayed in later versions of phpMyAdmin)
Now we will create a table named users in the members database using the SQL window.
Click the members database in the left panel of phpMyAdmin. If the members database does not show, refresh the page so that it does appear. Open the SQL window, and enter this:
CREATE TABLE users (
user_id MEDIUMINT (6) UNSIGNED
AUTO_INCREMENT,
fname VARCHAR(30) NOT NULL,
lname VARCHAR(40) NOT NULL,
email VARCHAR(50) NOT NULL,
psword CHAR(40) NOT NULL,
registration_date DATETIME,
PRIMARY KEY (user_id)
);
Figure 1-21 shows the details entered into the SQL window.
Figure 1-21. Creating a table in the SQL window of phpMyAdmin
Note that the brackets are all normal brackets, not curly brackets. Press the Enter key after each line, and remember to put the closing bracket and the semicolon at the end of the last line. Each item is separated by a closing comma (lines 3 through 8); if your table has six columns, you should have six commas. Click the Go button, and the table will be created.
Tip I encourage you to explore the SQL topic just described. The ability to work with SQL will be a very useful alternative sometime in the future. You may wish to refer to the tutorial on: http://dev.mysql.com/doc/refman/5.0/en/tutorial.html.
Deleting Databases and Tables
When learning, beginners often need to start over after creating a database or a table. The learner may wish to delete earlier attempts. When I first used phpMyAdmin, I got carried away and created several databases and tables. I then decided to clear up the mess and delete some of them.
First you will learn how to delete a database. Run XAMPP, and then load phpMyAdmin by entering the following into a browser’s address field:
http://localhost/phpmyadmin/
or
http://127.0.01/phpmyadmin/
Select the Databases tab (shown circled in Figure 1-22), and then select the box next to the database to be deleted. In this example, the members2 database was selected for deletion.
Figure 1-22. Deleting the database named members2
When you have selected the database to be deleted, click the icon labeled Drop (shown circled at the bottom right). You will be asked if you really want to delete the database; go ahead and complete the deletion. Everything associated with that database will be deleted, including its tables.
You may wish to preserve a database but delete all or one of its tables. In phpMyAdmin, in the left panel, find the database containing the table(s) to be deleted; click the database. In the next screen, you will see the table(s) and select the box next to the table(s) you wish to delete. Figure 1-23 shows that I chose to delete a table called dingdongs in the database called members4.
Figure 1-23. Deleting a table in phpMyAdmin
Click the icon labeled Drop (shown circled), and you will be asked if you really want to delete the table(s). You can choose between “Drop” and “Cancel.”
Summary
In this chapter, we defined a database and then looked at a free platform for developing and testing databases and PHP files. I hope you were able to download and install XAMPP. You explored phpMyAdmin and then learned how to use it to create your first database and a table. SQL was investigated as an alternative method for creating a database and a table. You also learned about using SHA to secure passwords and the requirement for setting password fields long enough to include a full SHA hash. The chapter then explained how to delete databases and tables using phpMyAdmin. You discovered that the Drop icon is used to delete a table or a database. In the next chapter, we will create and test simple interactive web pages.