MySQL Queries with PHP
CSCI 2910-001 In-Class Exercise

You should be able to...

This lab merely supports the material from the lecture on interfacing to MySQL through PHP. The result of your work will be a homework grade based on the components of your scripts that you get to work.

MySQL Database Table

The script below will create a table called "timetable" with the following fields and data types.

Field Name	Data Type
DEPT	CHAR(4)
COURSE	CHAR(4)
SECTION	CHAR(3)
TITLE	VARCHAR(30)
CALL_NUM	SMALLINT
SEATS	TINYINT
DAYS	VARCHAR(7)
TIME	CHAR(11)
HOURS	TINYINT
INSTRUCTOR	VARCHAR(10)
CAPACITY	TINYINT
ENROLLED	TINYINT
BUILDING	VARCHAR(10)
ROOM_NUM	SMALLINT

As we did before, copy the code shown below into your favorite text editor, then save it as an SQL script with the file extension ".SQL". Using FTP, upload it to the Einstein server, then from the MySQL command line, execute the script using the command:


\. mysqlscript.sql

where "mysqlscript.sql" is the filename you used to save your script under.


DROP TABLE timetable;

CREATE TABLE timetable(DEPT CHAR(4), COURSE CHAR(4), SECTION CHAR(3), TITLE VARCHAR(30), CALL_NUM SMALLINT, SEATS TINYINT, DAYS VARCHAR(7), TIME CHAR(11), HOURS TINYINT, INSTRUCTOR VARCHAR(10), CAPACITY TINYINT, ENROLLED TINYINT, BUILDING VARCHAR(10), ROOM_NUM SMALLINT);

INSERT INTO timetable VALUES ('CSCI', '1200', '001','Essential of CSCI', 14340, 15,'TR','1215-0235pm', 3,'Laws', 30, 15,'Gilbreath', 314 );

INSERT INTO timetable VALUES ('CSCI', '1250', '001','Intro Computer Sci I', 10666, 1,'TR','1205-0205pm', 4,'Price', 25, 24,'Gilbreath', 313 );

INSERT INTO timetable VALUES ('CSCI', '1250', '201','Intro Computer Sci I', 10667, 10,'MW','0500-0700pm', 4,'Bennett', 25, 15,'Gilbreath', 212 );

INSERT INTO timetable VALUES ('CSCI', '1260', '001','Intro Computer Sci II', 10668, 10,'MW','1235-0235pm', 4,'Montagnari', 25, 15,'Gilbreath', 313 );

INSERT INTO timetable VALUES ('CSCI', '1260', '201','Intro Computer Sci II', 10669, 16,'TR','0500-0700pm', 4,'Riser', 25, 9,'Gilbreath', 314 );

INSERT INTO timetable VALUES ('CSCI', '1510', '001','Student in University', 13513, 7,'TR','0215-0335pm', 3,'Loyd', 20, 13,'Gilbreath', 313 );

INSERT INTO timetable VALUES ('CSCI', '1710', '001','World Wide Web-Design', 10670, 1,'TR','0905-1105am', 3,'Wallace', 25, 24,'Gilbreath', 212 );

INSERT INTO timetable VALUES ('CSCI', '1710', '002','World Wide Web-Design', 10671, 7,'MW','1235-0235pm', 3,'Price K', 25, 18,'Gilbreath', 314 );

INSERT INTO timetable VALUES ('CSCI', '1710', '003','World Wide Web-Design', 10672, 8,'MW','1025-1225pm', 3,'Smith', 25, 17,'Gilbreath', 313 );

INSERT INTO timetable VALUES ('CSCI', '1710', '201','World Wide Web-Design', 10674, 12,'R','0500-0900pm', 3,'Laws', 25, 13,'Gilbreath', 115 );

INSERT INTO timetable VALUES ('CSCI', '1720', '001','World Wide Web-Advanced', 13748, 5,'MW','0245-0445pm', 3,'Laws', 25, 20,'Gilbreath', 212 );

INSERT INTO timetable VALUES ('CSCI', '1800', '001','Visual Programming I', 10675, 0,'TR','0215-0415pm', 4,'Montagnari', 25, 26,'Gilbreath', 115 );

INSERT INTO timetable VALUES ('CSCI', '1800', '201','Visual Programming I', 10677, 4,'MW','0500-0700pm', 4,'Bailey', 25, 21,'Gilbreath', 313 );

INSERT INTO timetable VALUES ('CSCI', '1800', '301','Visual Programming I', 10678, 15,'T','0530-0930pm', 4,'Britt', 25, 10,'Kingsport', 336 );

INSERT INTO timetable VALUES ('CSCI', '1900', '001','Math for Computer Science', 14341, 0,'MW','1025-1225pm', 3,'Wallace', 25, 29,'Gilbreath', 212 );

INSERT INTO timetable VALUES ('CSCI', '1900', '201','Math for Computer Science', 14599, 0,'MW','0500-0650pm', 3,'Wallace', 25, 25,'Burleson', 203 );

INSERT INTO timetable VALUES ('CSCI', '2150', '001','Computer Organization', 10679, 4,'MW','1235-0235pm', 4,'Tarnoff', 25, 21,'Burleson', 304 );

INSERT INTO timetable VALUES ('CSCI', '2150', '201','Computer Organization', 10680, 7,'MW','0450-0650pm', 4,'Tarnoff', 25, 18,'Sam Wilson', 228 );

INSERT INTO timetable VALUES ('CSCI', '2160', '001','Assembly Languag', 10681, 13,'TR','0215-0415pm', 4,'Bailey', 25, 12,'Gilbreath', 212 );

INSERT INTO timetable VALUES ('CSCI', '2200', '001','Unix Fundamentals', 14342, 0,'MW','0245-0445pm', 3,'Jenkins', 20, 23,'Gilbreath', 313 );

INSERT INTO timetable VALUES ('CSCI', '2200', '201','Unix Fundamentals', 14343, 5,'TR','0500-0700pm', 3,'Franklin', 20, 15,'Sam Wilson', 341 );

INSERT INTO timetable VALUES ('CSCI', '2210', '001','Data Structures', 10682, 10,'MW','1235-0235pm', 4,'Bailes', 25, 15,'Gilbreath', 212 );

INSERT INTO timetable VALUES ('CSCI', '2230', '001','File Processing', 10683, 10,'TR','0500-0700pm', 4,'Pine', 25, 15,'Sam Wilson', 230 );

HTML Forms

The following is the XHTML code for a page that contains five forms. Each form consists of a submit button along with a possible input. Pressing the submit button calls a different PHP script for each form that will access the timetable table in your database to search for courses. The first form calls search_01.php, the second search_02.php and so on. You will be writing each of these PHP scripts. Copy the HTML text below into the body of an XHTML document, and save it to your Einstein public_html folder. Don't forget to change the privileges!


<h1 align="center">Search Options</h1>



<hr />

<p>List all courses displaying the fields DEPT, COURSE, SECTION, TITLE, and INSTRUCTOR sorted by INSTRUCTOR.</p>

<form name="search_01" method="get" action="search_01.php">

    <div align="center">

        <input type="submit" value="List all by instructor">

    </div>

</form>



<hr />



<p>List courses with at least 8 free seats, (SEATS >= 8) displaying the fields DEPT, COURSE, SECTION, TITLE, DAYS, TIME, and SEATS sorted by COURSE.</p>

<form name="search_02" method="get" action="search_02.php">

    <div align="center">

        <input type="submit" value="List with at least 8 seats">

    </div>

</form>



<hr />

<p>List courses matching selected course number displaying the fields CALL_NUM, DEPT, COURSE, SECTION, DAYS, TIME, ROOM_NUM, and BUILDING sorted by SECTION.</p>

<form name="search_03" method="get" action="search_03.php">

    <div align="center">

        <select size="1" name="course">

            <option value="1200">CSCI 1200</option>

            <option value="1250">CSCI 1250</option>

            <option value="1260">CSCI 1260</option>

            <option value="1510">CSCI 1510</option>

            <option value="1710">CSCI 1710</option>

            <option value="1720">CSCI 1720</option>

            <option value="1800">CSCI 1800</option>

            <option value="1900">CSCI 1900</option>

            <option value="2150">CSCI 2150</option>

            <option value="2160">CSCI 2160</option>

            <option value="2200">CSCI 2200</option>

            <option value="2210">CSCI 2210</option>

            <option value="2230">CSCI 2230</option>

        </select>

    <input type="submit" value="List all sections of">

    </div>

</form>



<hr />

<p>List courses with the entered text in their TITLE displaying the fields DEPT, COURSE, SECTION, TITLE, HOURS, INSTRUCTOR, and SEATS sorted by COURSE.</p>

<form name="search_04" method="get" action="search_04.php">

    <div align="center">

        <input type="text" name="text_to_find" value="Type search text here.">

        <input type="submit" value="List with text in title">

    </div>

</form>



<hr />

<p>List all 2xxx courses (sophomore) displaying the fields DEPT, COURSE, SECTION, TITLE, and HOURS sorted by COURSE.</p>

<form name="search_05" method="get" action="search_05.php">

    <div align="center">

        <input type="submit" value="List 2xxx courses">

    </div>

</form>



<hr />

You can also find this form at http://einstein.etsu.edu/~tarnoff/search.htm Just right-click on the link and select "Save target as."

For the forms that pass data to your PHP script, the method is "get". This is meant to aid in your development since you will be able to see in the URL the actual data that your PHP script is receiving.

The Process

Your assignment is to create all five scripts, one for each form. Remember that since our PHP development environment is anything but helpful regarding debugging, take small steps. I would recommend first developing a generic PHP script to output the results of a database search. As a guide, you can use the template presented on slide 11 of the "MySQL in PHP" lecture, but it is going to have ugly output. Clean it up so that it looks like a nice table when run on the data.

Remember that to access a MySQL database from PHP, you must do the following steps:

Connect to MySQL: $connection = mysql_connect ("localhost", "zxyx999", "12345");
Select a database: mysql_select_db("zxyx999", $connection);
Perform a query on table(s): $result = mysql_query("MySQL query command goes here.", $connection);
Extract records from query result: $record = mysql_fetch_array($result, MYSQL_ASSOC);
Disconnect from MySQL: mysql_close ($connection);

These steps will be included in each of the five PHP scripts you write.

Last of all, we will be including this as a homework score, so as soon as you have finished your five scripts, call me over for verification.

Developed by David Tarnoff for his students in CSCI 2910 at ETSU

MySQL Queries with PHP CSCI 2910-001 In-Class Exercise

You should be able to...

MySQL Database Table

HTML Forms

The Process

MySQL Queries with PHP
CSCI 2910-001 In-Class Exercise