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.

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
) CodeSetting 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:
- Login to your
bscacad3.buffalostate.edu
account usingPutty
(on Windows) orssh
(on MacOS) using your BSC Username and Password. Your mysql-password is your Banner ID beginning with a capital B. - Edit your
.bashrc
file using thevi
editor (see the Washington University site for more information about Using VI) to setup themysql
alias:- At the Unix $ prompt type:
vi .bashrc
- Scroll to the end of the file
- Press SHIFT-A to 'append at the end of the this line'
- Press the ENTER key to go to the next line
- Type this line:
alias mysql='mysql -uyour-bsc-userid -pyour-mysql-password -D your-bsc-userid'
- Press CONTROL-[ to exit the append mode
- To save your
.bashrc
file, press::wq
- At the Unix $ prompt type:
- At the Unix
$
prompt type:source .bashrc
mysql
alias- At the Unix
$
prompt type:mysql
- At the
mysql>
prompt type:show tables;
- There should not be any tables listed yet unless you used
mysql
for another course. - 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));
- At the
mysql>
prompt type:describe testTable;
- To add data to your table, type:
INSERT INTO testTable (firstName, lastName) VALUES ("your first name", "your lastname");
- To view the data in your table type:
SELECT * FROM testTable ORDER BY lastName, firstName;
- To exit mysql, at the
mysql>
prompt type:exit
- You should now be back at the Unix
$
prompt.
mysql
Command Linemysql>
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)
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)
DESCRIBE
Codemysql>
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");
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)
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)
SELECT ... WHERE
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.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 filecis475_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
themonthsTable
. - Displays a "Successful" or "Unsuccessful" message when complete.
- 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 yourindex.php
,db.php
,vars.php
,functions.php
, anddb.png
files so I can download, save, and grade your work. - Use
phpMyAdmin
toBrowse
themonthsTable
. 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.