Hello, in this problem I only need numbers 16 and 17 finished, and if you see anything else wrong feel free to change. (only change under "// Type your code here")

In PHP

3

-->

3. Problem Description

In this assignment you are to use PDO to access the p3_php_grades database and execute a WHERE JOIN query to retrieve some data. You will use an HTML form with a button to retrieve the data and build an HTML table to display it.

The provided file phpJoinThreeTables.php consists of a partially completed HTML section and an incomplete PHP block. You are to complete the PHP block as instructed below.

The HTML section uses the W3.CSS framework to style the page for improved readability. The page displays the results of the computations performed in the PHP block and which you are to code.

Notice the use of the PHP drop-ins, i.e. the use of the tags to execute PHP code from within the HTML elements.

The PHP block starts off by turning on error handling so debugging the code can become a bit easier.

9. Function definitions

You will implement a number of short functions to carry out each needed step. This modular approach will make it easier to write and ultimately debug the application.

10. getPostback() function definition

Define the function getPostback() that does not take any parameters. The function returns the sanitized key PHP_SELF from the $_SERVER superglobal array. The app is using a postback script to display and process the form.

11. getDSN() function defintion

Define the function getDSN() that does not take any parameters. The function creates and returns the Data Source Name string that will allow the app to connect to the server successfully. Return a DSN string consisting of the following components:

prefix: 'mysql' host: 'localhost' port: 8889 dbname: 'p3_php_grades'

12. getUsername() function defintion

Define the function getUsername() that does not take any parameters. The function returns 'root' as the username.

13. getPassword() function defintion

Define the function getPassword() that does not take any parameters. The function returns 'root' as the password.

14. getPDO() function defintion

Define the function getPDO() that does not take any parameters. The function returns a new PDO instance.

15. sqlJoinQuery() function definition

Define the function sqlSelectQuery() that does not take any parameters. The function returns a string containing the WHERE JOIN query used to access the database:

SELECT CONCAT(s.last, ',', s.first) AS 'Student', CONCAT(c.number, '-', c.title)

AS 'Course', CONCAT(t.term, t.year) AS 'Semester', t.grade AS 'Grade'

FROM student AS s, course AS c, transcript AS t

WHERE s.id=t.student$id

AND c.id=t.course$id

ORDER BY year, term, number;

16. buildTable() function definition

Define the function buildTable() that takes one parameter $pdoStatement holding the queried data. The function builds and returns an HTML table with a

, , and elements and the following requirements:

1. Use the string variable $table to hold the HTML table as you build it piece by piece.

2. Add the class w3-table-all to the

   tag.

3. Add the class w3-large to the

4. Add the content Student Transcripts to the

5. Add five column headers to the

element with values #, Student, Course, Semester, and Grade.

6. Add the count, Student, Course, Semester and Grade data values to the

element. Use a foreach statement to iterate the $podStatement set. The count represents the count of records, so create a counter variable which is incremented by one with each row displayed.

A sample output will look like:

Student Transcripts

# Student Course Semester Grade

1 Auburn, Allison CS111-Computer Concepts SP2018 A

2 Auburn, Allison ENG101-English Composition SP2018 B

3 Auburn, Allison MA150-Calculus I SU2018 A

17. displayRecords() function definition

1. Define the function displayRecords() that does not take any parameters. The function establishes a PDO connection with the database server, executes the above WHERE JOIN query and calls the buildTable() function that lists the results in an HTML table.

2. The query should not be executed unless the form is submitted. Thus check to make sure the form is posted first by checking the REQUEST_METHOD key of the $_SERVER superglobal array.

3. Use a try/catch statement and perform all steps below within it. If a PDOEXCEPTION is caught, simply echo the error message by calling the method getMessage() of the exception instance.

4. Call the getPDO() function to creaate a connection to the database and assign the PDO instance returned to the variable $pdo.

5. Use the setAttribute() method of the PDO instance to specify exceptions as the error handling mode.

6. Next call the query() method of the $pdoStatement instance, providing the query returned by the function sqlJoinQuery() as its argument, to retrieve the data from the database.

7. echo the string returned by the function buildTable() to render the HTML table.

8. Disconnect from the database by setting the $pdo instance to null.

-->

[Code Given]

// Turn on error reporting.

error_reporting(E_ALL);

ini_set('display_errors', '1');

// Type your code here

$year = date('Y');

function getPostback()

{

return $_SERVER['PHP_SELF'];

}

function getDSN()

{

return 'mysql:host=localhost:8889;dbname=p3_php_grades';

}

function getUsername()

{

return 'root';

}

function getPassword()

{

return 'root';

}

function getPDO()

{

$dsn=getDSN();

$username=getUsername();

$password=getPassword();

return new PDO($dsn, $username, $password);

}

function sqlJoinQuery()

{

return "SELECT CONCAT(s.last, ',', s.first) AS 'Student', CONCAT(c.number, '-', c.title) AS 'Course', CONCAT(t.term, t.year) AS 'Semester', t.grade AS 'Grade' FROM student AS s, course AS c, transcript AS t WHERE s.id=t.student$id AND c.id=t.course$id ORDER BY year, term, number;";

}

?>

p3 - PHP Join Three Tables

©

SQL DATA USED:





-- Table structure for table course -- DROP TABLE IF EXISTS `course; /* ! 40101 SET @saved_cs_client = @@character_set_client */; /*! 40101 SET character_set_client = utf8 */; CREATE TABLE `course ( 'id' int(10) unsigned NOT NULL AUTO_INCREMENT, number char(6) NOT NULL, 'title' varchar(50) NOT NULL, hours double(2,1) NOT NULL, PRIMARY KEY ( 'id") ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8; /* ! 40101 SET character_set_client = @saved_cs_client */; -- Dumping data for table course LOCK TABLES 'course WRITE; /*! 40000 ALTER TABLE `course' DISABLE KEYS */; INSERT INTO `course' VALUES (1, 'C5111', 'Computer Concepts',3.0), (2, 'ENG101', 'English Composition',3.0),(3, 'MA150', 'Calculus I',5.0),(4, '05140', 'Intro to Computing I',4.0),(5, 'C5150', 'Intro to Computing II',3.0),(6,'cs /*!40000 ALTER TABLE course' ENABLE KEYS */; UNLOCK TABLES; -- Table structure for table student DROP TABLE IF EXISTS student; /*!40101 SET @saved_cs_client = @@character_set_client */; /* !40101 SET character_set_client = utf8 */; CREATE TABLE `student ( 'id' int(10) unsigned NOT NULL AUTO_INCREMENT, eid' int(11) NOT NULL, 'first' varchar(20) NOT NULL, 'last' varchar(30) NOT NULL, PRIMARY KEY ('id") ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; /* !40101 SET character_set_client = @saved_cs_client */; -- Dumping data for table 'student LOCK TABLES student' WRITE; /*! 40000 ALTER TABLE student' DISABLE KEYS */; INSERT INTO `student" VALUES (1,800, 'Allison', 'Auburn'),(2,801, 'Barry', 'Brown'), (3,802, 'Cathy', 'Crimson'),(4,803, 'Frank', 'Fucia'),(5,804, 'Herald', 'Hibiscus'); /*! 40000 ALTER TABLE 'student ENABLE KEYS */; UNLOCK TABLES; -- Table structure for table 'transcript DROP TABLE IF EXISTS 'transcript'; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE 'transcript ( 'id' int(10) unsigned NOT NULL AUTO_INCREMENT, 'student$id" int(10) unsigned NOT NULL, course$id" int(10) unsigned NOT NULL, 'termenum('SP', 'SU', 'FA) NOT NULL, "year" int(4) NOT NULL, grade char(2) DEFAULT NULL, PRIMARY KEY (`id"), KEY 'student$id' (studentsid"), KEY 'course$id' ('course$id"), CONSTRAINT `transcript_ibfk_1" FOREIGN KEY ( studentsid") REFERENCES 'student('id'), CONSTRAINT `transcript_ibfk_2 FOREIGN KEY ( course$id") REFERENCES 'course (id") ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8; /*! 40101 SET character_set_client = @saved_cs_client */; - -- Dumping data for table 'transcript LOCK TABLES `transcript" WRITE; /*!40000 ALTER TABLE `transcript DISABLE KEYS */; INSERT INTO `transcript" VALUES (1,1,1, 'SP',2018, 'A'),(2,1,2, 'SP',2018, 'B'),(3,1,3,'SU',2018, 'A'),(4,1,4,'FA',2018, 'B'),(5,1,5, 'FA',2018,'C'),(6,2,1, 'FA',2019,'A'),(7,2,3, 'FA',2019, 'WP'), (8,2,4,'FA',2019, 'B'),(9,2,7, /*!40000 ALTER TABLE `transcript ENABLE KEYS */; UNLOCK TABLES; -- Table structure for table course -- DROP TABLE IF EXISTS `course; /* ! 40101 SET @saved_cs_client = @@character_set_client */; /*! 40101 SET character_set_client = utf8 */; CREATE TABLE `course ( 'id' int(10) unsigned NOT NULL AUTO_INCREMENT, number char(6) NOT NULL, 'title' varchar(50) NOT NULL, hours double(2,1) NOT NULL, PRIMARY KEY ( 'id") ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8; /* ! 40101 SET character_set_client = @saved_cs_client */; -- Dumping data for table course LOCK TABLES 'course WRITE; /*! 40000 ALTER TABLE `course' DISABLE KEYS */; INSERT INTO `course' VALUES (1, 'C5111', 'Computer Concepts',3.0), (2, 'ENG101', 'English Composition',3.0),(3, 'MA150', 'Calculus I',5.0),(4, '05140', 'Intro to Computing I',4.0),(5, 'C5150', 'Intro to Computing II',3.0),(6,'cs /*!40000 ALTER TABLE course' ENABLE KEYS */; UNLOCK TABLES; -- Table structure for table student DROP TABLE IF EXISTS student; /*!40101 SET @saved_cs_client = @@character_set_client */; /* !40101 SET character_set_client = utf8 */; CREATE TABLE `student ( 'id' int(10) unsigned NOT NULL AUTO_INCREMENT, eid' int(11) NOT NULL, 'first' varchar(20) NOT NULL, 'last' varchar(30) NOT NULL, PRIMARY KEY ('id") ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; /* !40101 SET character_set_client = @saved_cs_client */; -- Dumping data for table 'student LOCK TABLES student' WRITE; /*! 40000 ALTER TABLE student' DISABLE KEYS */; INSERT INTO `student" VALUES (1,800, 'Allison', 'Auburn'),(2,801, 'Barry', 'Brown'), (3,802, 'Cathy', 'Crimson'),(4,803, 'Frank', 'Fucia'),(5,804, 'Herald', 'Hibiscus'); /*! 40000 ALTER TABLE 'student ENABLE KEYS */; UNLOCK TABLES; -- Table structure for table 'transcript DROP TABLE IF EXISTS 'transcript'; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE 'transcript ( 'id' int(10) unsigned NOT NULL AUTO_INCREMENT, 'student$id" int(10) unsigned NOT NULL, course$id" int(10) unsigned NOT NULL, 'termenum('SP', 'SU', 'FA) NOT NULL, "year" int(4) NOT NULL, grade char(2) DEFAULT NULL, PRIMARY KEY (`id"), KEY 'student$id' (studentsid"), KEY 'course$id' ('course$id"), CONSTRAINT `transcript_ibfk_1" FOREIGN KEY ( studentsid") REFERENCES 'student('id'), CONSTRAINT `transcript_ibfk_2 FOREIGN KEY ( course$id") REFERENCES 'course (id") ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8; /*! 40101 SET character_set_client = @saved_cs_client */; - -- Dumping data for table 'transcript LOCK TABLES `transcript" WRITE; /*!40000 ALTER TABLE `transcript DISABLE KEYS */; INSERT INTO `transcript" VALUES (1,1,1, 'SP',2018, 'A'),(2,1,2, 'SP',2018, 'B'),(3,1,3,'SU',2018, 'A'),(4,1,4,'FA',2018, 'B'),(5,1,5, 'FA',2018,'C'),(6,2,1, 'FA',2019,'A'),(7,2,3, 'FA',2019, 'WP'), (8,2,4,'FA',2019, 'B'),(9,2,7, /*!40000 ALTER TABLE `transcript ENABLE KEYS */; UNLOCK TABLES

tag.

element.