Guide to PHP and MySQL (Buffalo State College Version)

Chapter 8: Working With the MySQL Database

MySQL Statements

You can use phpMyAdmin on your localhost web server to manage your MySQL databases. Click on your database. Then click on the SQL tab. You can then enter SQL statements in the editor window.

The MySQL create table Statement

You can use the create table statement to create a new table on your localhost server.

PHPMyAdmin SQL Entry Prompt
CREATE TABLE booksTable (
bookID int(9) auto_increment primary key,
bookTitle varchar(100),
authorFname varchar(20),
authorLname varchar(20),
bookISBN varchar(14)
);

Figure 8-1: MySQL CREATE TABLE Code

The MySQL describe table Statement

You can use the describe table statement to view the structure of your table on your localhost server.

PHPMyAdmin Sql Entry Prompt
DESCRIBE booksTable;
Field Type Null Key Default Extra
bookID int(9) NO PRI NULL auto_increment
bookTitle varchar(100) YES NULL
authorFname varchar(20) YES NULL
authorLname varchar(20) YES NULL
bookISBN varchar(14) YES NULL

Figure 8-2: MySQL DESCRIBE Code

The MySQL insert Statement

You can use the insert statement to add data record (row) to your table on your localhost server.

PHPMyAdmin Sql Entry Prompt
INSERT INTO booksTable (bookTitle, authorFname, authorLname, bookISBN)
VALUES ("Tarantula", "Bob", "Dylan", "978-0743230414");
INSERT INTO booksTable (bookTitle, authorFname, authorLname, bookISBN)
VALUES ("1984", "George", "Orwell", "978-0451524935");
INSERT INTO booksTable (bookTitle, authorFname, authorLname, bookISBN)
VALUES ("The Case-Book of Sherlock Holmes ", "Arthur Conan", "Doyle", "978-0199555642");

Figure 8-3: MySQL INSERT Code

The MySQL select ... where Statement

You can use the select statement to retreive a data record (or records) from your table on your localhost server.

PHPMyAdmin Sql Entry Prompt
SELECT * FROM booksTable;
bookID bookTitle authorFname authorLname bookISBN
1 Tarantula Bob Dylan 978-0743230414
2 1984 George Orwell 978-0451524935
3 The Case-Book of Sherlock Holmes Arthur Conan Doyle 978-0199555642

Figure 8-4: MySQL SELECT Code

The MySQL where Statement

You can use the where keyword on your select statement to addd criteria that limits the retreived records(results set).

PHPMyAdmin Sql Entry Prompt
SELECT * FROM booksTable WHERE authorLname = "Dylan";
bookID bookTitle authorFname authorLname bookISBN
1 Tarantula Bob Dylan 978-0743230414

Figure 8-5: MySQL SELECT ... WHERE Code

Working With Multiple MySQL Tables

In most appllications you will need to work with more than one table.

The MySQL join Keyword

You can use the join keyword to relate two or more tables together.

PHPMyAdmin Sql Entry Prompt
SELECT firstName, lastName, DeptName, locationName
FROM employees
JOIN departments ON employees.dept = departments.departmentID
JOIN locations ON departments.location = locations.locationID;
firstName lastName DeptName locationName
Bob Dylan HR New York, NY
Joe Cocker HR Paris, FR
John Prine HR London, UK

Figure 8-6: MySQL join Code

MySQL Aliases

MySQL Column Aliases

You can use the as keyword to provide a more user-friendly name for a field (column). This is known AS a column alias. If the alias value has spaces or mixed case characters you need to enclose the value in quotes.

PHPMyAdmin Sql Entry Prompt
SELECT firstName AS "First Name", lastName AS "Last Name", DeptName AS "Department", locationName AS "Location"
FROM employees
JOIN departments ON employees.dept = departments.departmentID
JOIN locations ON departments.location = locations.locationID;
First Name Last Name Deptartment Location
Bob Dylan HR New York, NY
Joe Cocker HR Paris, FR
John Prine HR London, UK

Figure 8-7: MySQL Column Aliases Code

MySQL Table Aliases

You can use the as keyword to provide a more user-friendly name for a table. This allows you to provide a shorter name when refering to that table.

PHPMyAdmin Sql Entry Prompt
SELECT firstName AS "First Name", lastName AS "Last Name", DeptName AS "Department", locationName AS "Location"
FROM employees AS e
JOIN departments AS d ON e.dept = d.departmentID
JOIN locations AS l ON d.location = l.locationID;
First Name Last Name Deptartment Location
Bob Dylan HR New York, NY
Joe Cocker HR Paris, FR
John Prine HR London, UK

Figure 8-8: MySQL Table Alias (as) Code

Setting up MySQL on bscacad3.buffalostate.edu

On bscacad3.buffalostate.edu you can use the mysql command at the Unix $ prompt after you loogin via PuTTy or SSH. First you need to setup the mysql alias on your account:

  1. Login to your bscacad3.buffalostate.edu account using Putty (on Windows) or ssh (on MacOS) using your BSC Username and Password. Your mysql-password is your Banner ID beginning with a capital B.
  2. Edit your .bashrc file using the vi editor (see the Washington University site for more information about Using VI) to setup the mysql alias:
    1. At the Unix $ prompt type: vi .bashrc
      1. Scroll to the end of the file
      2. Press SHIFT-A to 'append at the end of the this line'
        1. Press the ENTER key to go to the next line
        2. Type this line:

          alias mysql='mysql -uyour-bsc-userid -pyour-mysql-password -D your-bsc-userid'

        3. Press CONTROL-[ to exit the append mode
      3. To save your .bashrc file, press: :wq
  3. At the Unix $ prompt type: source .bashrc
Figure 8-9: Setting Up mysql alias
  1. At the Unix $ prompt type: mysql
  2. At the mysql> prompt type: show tables;
  3. There should not be any tables listed yet unless you used mysql for another course.
  4. To create a table, at the mysql> prompt type:

    CREATE TABLE testTable (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, firstName VARCHAR (25), lastName VARCHAR(25));

  5. At the mysql> prompt type: describe testTable;
  6. To add data to your table, type:

    INSERT INTO testTable (firstName, lastName) VALUES ("your first name", "your lastname");

  7. To view the data in your table type:

    SELECT * FROM testTable ORDER BY lastName, firstName;

  8. To exit mysql, at the mysql> prompt type: exit
  9. You should now be back at the Unix $ prompt.
Figure 8-10: Using mysql Command Line
mysql> CREATE TABLE booksTable (
bookID int(9) auto_increment primary key,
bookTitle varchar(100),
authorFname varchar(20),
authorLname varchar(20),
bookISBN varchar(14)
);
Query OK, 0 rows affected (0.02 sec)

Figure 8-11: MySQL CREATE TABLE Code
mysql> DESCRIBE booksTable;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| bookID      | int(9)       | NO   | PRI | NULL    | auto_increment |
| bookTitle   | varchar(100) | YES  |     | NULL    |                |
| authorFname | varchar(20)  | YES  |     | NULL    |                |
| authorLname | varchar(20)  | YES  |     | NULL    |                |
| bookISBN    | varchar(14)  | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
Figure 8-12: MySQL DESCRIBE Code
mysql> INSERT INTO booksTable (bookTitle, authorFname, authorLname, bookISBN)
VALUES ("Tarantula", "Bob", "Dylan", "978-0743230414");
mysql> INSERT INTO booksTable (bookTitle, authorFname, authorLname, bookISBN)
VALUES ("1984", "George", "Orwell", "978-0451524935");
mysql> INSERT INTO booksTable (bookTitle, authorFname, authorLname, bookISBN)
VALUES ("The Case-Book of Sherlock Holmes ", "Arthur Conan", "Doyle", "978-0199555642");

Figure 8-13: MySQL INSERT Code
mysql> SELECT * FROM booksTable;
+--------+-----------------------------------+--------------+-------------+----------------+
| bookID | bookTitle                         | authorFname  | authorLname | bookISBN       |
+--------+-----------------------------------+--------------+-------------+----------------+
|      1 | Tarantula                         | Bob          | Dylan       | 978-0743230414 |
|      2 | 1984                              | George       | Orwell      | 978-0451524935 |
|      3 | The Case-Book of Sherlock Holmes  | Arthur Conan | Doyle       | 978-0199555642 |
+--------+-----------------------------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
Figure 8-14: MySQL SELECT Code
mysql> SELECT * FROM booksTable WHERE authorLname = "Dylan";
+--------+-----------+-------------+-------------+----------------+
| bookID | bookTitle | authorFname | authorLname | bookISBN       |
+--------+-----------+-------------+-------------+----------------+
|      1 | Tarantula | Bob         | Dylan       | 978-0743230414 |
+--------+-----------+-------------+-------------+----------------+
1 row in set (0.00 sec)
Figure 8-15: MySQL SELECT ... WHERE Code

Task - Create a MySQL Table

For this Assignment you will create a well-designed HTML5/CSS page db.php on your local web server. This file may be based on your index.php file. This web page should:

  1. Create a form with a simple "Import Months" button.
  2. When that button is clicked, call a PHP function (added to your functions.php file) that:
    • Connects to your MySQL server.
    • Generates MySQL commands to CREATE a MySQL table, monthsTable, with these fields: monthsID (INT), monthName (CHAR, 10), monthDays (INT, 2). Since you will allow users to click the submit button multiple times, it would be a good idea to have your first SQL statement be: DROP TABLE IF EXISTS monthsTable;
    • If the table CREATE command is successful, call a PHP function (reuse the function from the PHP I/O assignment) that reads the text file cis475_io.txt which you downloaded for the I/O task.
    • This function should use PHP to generate MySQL commands to INSERT each line (record) in that file, AS it is read, INTO the monthsTable.
    • Displays a "Successful" or "Unsuccessful" message when complete.
  3. Modify your JavaScript in your index.html file that uses an array of the Assignments for this course to display an ordered list of these assignments to convert the entry for this assignment to a web link that allows me download and save your index.php, db.php, vars.php, functions.php, and db.png files so I can download, save, and grade your work.
  4. Use phpMyAdmin to Browse the monthsTable. Save a screen capture of this page and upload that image (db.png) to your bscacad3 Web space.

You *MUST* use the W3C Unicorn Validator to validate your HTML5/CSS3 code.

You will modify the PHP function for this item in your index.php page to add a link to your db.png that allows me to view your work.

Let's Get Started with a MySQL Database!
Creative Commons License This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License. Copyright © 2016-2019 Jim Gerland