Tt
Click this widget to change the font size.
CC
Click this widget to change contrast.

Home Page 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | Links | Search | Bio |

Guide to PHP and MySQL (University at Buffalo 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

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 cda215_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 ub_homepages 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!

Help contribute to my OER Resources. Donate with PayPal button via my PayPal account.
Creative Commons License This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License. Copyright © 2016-2024 Jim Gerland