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.
    Advertisements