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.
CREATE TABLE booksTable (
bookID int(9) auto_increment primary key,
bookTitle varchar(100),
authorFname varchar(20),
authorLname varchar(20),
bookISBN varchar(14)
);
CREATE TABLE CodeThe MySQL describe table Statement
You can use the describe table statement to view the structure of your table on your localhost server.
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 |
DESCRIBE CodeThe MySQL insert Statement
You can use the insert statement to add data record (row) to your table on your localhost server.
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");
INSERT CodeThe MySQL select ... where Statement
You can use the select statement to retreive a data record (or records) from your table on your localhost server.
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 |
SELECT CodeThe MySQL where Statement
You can use the where keyword on your select statement to addd criteria that limits the retreived records(results set).
SELECT * FROM booksTable WHERE authorLname = "Dylan";
| bookID | bookTitle | authorFname | authorLname | bookISBN |
|---|---|---|---|---|
| 1 | Tarantula | Bob | Dylan | 978-0743230414 |
SELECT ... WHERE CodeWorking 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.
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 |
join CodeMySQL 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.
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 |
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.
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 |
as) CodeTask - 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:
- Create a form with a simple "Import Months" button.
- When that button is clicked, call a PHP function (added to your
functions.phpfile) that:- Connects to your MySQL server.
- Generates MySQL commands to
CREATEa 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
CREATEcommand is successful, call a PHP function (reuse the function from the PHP I/O assignment) that reads the text filecda216_io.txtwhich you downloaded for the I/O task. - This function should use PHP to generate MySQL commands to
INSERTeach line (record) in that file, AS it is read,INTOthemonthsTable. - Displays a "Successful" or "Unsuccessful" message when complete.
- Modify your JavaScript in your
index.htmlfile 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 yourindex.php,db.php,vars.php,functions.php, anddb.pngfiles so I can download, save, and grade your work. - Use
phpMyAdmintoBrowsethemonthsTable. Save a screen capture of this page and upload that image (db.png) to your ubunix 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.
