CL5_TEK

1

IT 423 Project One Database Interface Instructions This tutorial assumes that you have already completed the steps necessary to have a working WordPress website to use and that you have constructed the required ERD diagram from earlier in this project. In this tutorial, you will use the PhpMyAdmin plug-in, create new pages, add PHP code to your website, and perform form submissions to complete a fully functional database integration within WordPress. IMPORTANT NOTE: In the AWS Educate site, and in the AWS Console, you are instructed to shut down your EC2 instance when you’re not using it to preserve credits. DON’T DO IT! Leave it running until you are finished with the assignment and have submitted your screenshots. If you do stop your EC2 instance, when you restart it, the IP address and URL associated with the WordPress site will change, and require you to jump through hoops to change it in the MySQL database associated with your site. While this is possible to do in an emergency, it’s not advised. In the event that you forget this advice, here’s a link to an article on how to do an emergency fix: Change the WordPress Site URL Using MySQL Command Line. https://kunwardharmesh.wordpress.com/2013/08/05/how-to-change-the-wordpress-site-url-using-mysql-command-line/

2

Step 1: Download and install the phpMyAdmin plug-in After you sign in to your WordPress admin panel, go to the main plug-ins page by selecting Plugins from the left side menu. The page should look like the following screenshot on a fresh install.

3

Click the Add New button at the top of the page. In the top right search bar, enter WP phpMyAdmin to get to the Add Plugins page, as shown in the following screenshot.

Click Install Now on the first result. Once the plug-in is installed, the button will turn to Activate. Click on it to continue. Make sure that Use HTTPS (so, auto-login will work) is unchecked before you try to access phpMyAdmin.

4

The default setting for the plug-in will work. Click Enter phpMyAdmin in the top right to view the current database setup. This plug-in will automatically grab the username and password from the configuration file stored with your WordPress setup.

Once you have reached the main page shown in the screenshot above, continue by inserting the entities and attributes you created earlier in this project in the ERD section. For further information on how to create these new tables, you can review this online phpMyAdmin documentation.

5

The entities and attributes given in the sample ERD must be included to fully complete this project. The code in the next section relies on having at least the following, but the YODAE Business Case suggests that you need additional tables as shown in the following diagram:

Hint: Make sure that you set the primary keys to INT NOT NULL AUTOINCREMENT, so that when you add new data/records, the ID will be automatically created. Also, notice the many-to-many relationship. This suggests that a table like Students-Courses might be useful.

6

Clicking on the Students table in the left side menu in phpMyAdmin, then clicking on the Structure tab (as shown in the screenshot) should result in something very similar to the following display, if the table was constructed correctly.

Continue to create all the components from your ERD diagram and then progress to the next step.

7

Step 2: Create Add Student form This section will start at the main admin dashboard of your WordPress install. Click Pages from the left side menu to continue, as shown in the following screenshot.

From this page, click Add New at the top of the page. Add the page title “Add Student”.

8

Click the + icon next to the block in the center of the page. Then choose Formatting and Custom HTML, as shown in the following screenshot.

9

Next copy and paste the code below to create the Add Student form. Make sure to change the URL to match your site URL. <form id="addStudentForm" name="addStudentForm" action="/wordpress/wp-admin/admin-post.php" method="POST">

<input type="hidden" name="action" value="add_student">

First Name <input id="first_name" type="text" name="first_name" required="">

Last Name <input id="last_name" type="text" name="last_name" required="">

DOB <input id="dob" type="date" name="dob" required="">

Entry Year <input id="entry_year" name="entry_year" type="number" min="1900" max="2099" step="1"

value="2016" required="">

<button type="submit">Add Student</button>

</form>

10

Click Preview to see the form that was created, shown in the following screenshot.

Last, publish the page using the Publish… button at the top right of the page.

11

Step 3: Add PHP code snippet plug-in Next you will need to install another plug-in. Go to Plugins and choose Add New. In the search, enter “Insert PHP Code Snippet”. Select the first plug-in, install, and then activate, as shown in the following screenshot.

This will create a new menu item in the left side menu that we will load a few snippets into for use later.

12

After clicking on XYZ PHP Code, select Create New from the top of the page. Here you will enter the following information: Tracking Name: ViewStudents PHP code: <?php

global $wpdb;

$students = $wpdb->get_results("SELECT * FROM Students;");

echo "<table><tr><th>Firstname</th><th>Lastname</th><th>Birth Date</th><th>Entry

Year</th></tr>";

foreach($students as $student){

echo "<tr>";

echo "<td>".$student->first_name."</td>";

echo "<td>".$student->last_name."</td>";

echo "<td>".$student->dob."</td>";

echo "<td>".$student->entry_year."</td>";

echo "</tr>";

}

echo "</table>";

?>

13

After clicking Create, you will now be able to use this code using a simple short code. This is found under the section Snippet Short Code for your new entry. This will be used in the creation of the View Students page.

14

Step 4: Add View page Follow a process similar to the one in Step 2, this time to create a View Students page. Add a page title of “View Students”. For this page, choose the Widgets and Shortcode block. Paste the short code you created in the previous step into the Shortcode bar, as shown in the following screenshot:

Once you are ready, click Publish… to continue.

15

When you return to select Pages and All Pages, you should now see your two new published pages for the website, as shown in the following screenshot.

16

Step 5: Form submission To continue, you will need to add a few lines of code to the core functions.php page of WordPress. This allows you to grab data that is submitted through your newly created Add Student form and store that information into the proper tables.

From the main admin screen, go to Appearance then Theme Editor on the left side menu.

On the right side Theme Files menu, choose Theme Functions (functions.php) to edit and scroll to the bottom of this file, as shown in the following screenshot.

17

Here you will copy and paste the code below: function add_student_form() {

global $wpdb;

$first_name = $_POST['first_name'];

$last_name = $_POST['last_name'];

$dob = date("Y-m-d", strtotime($_POST['dob']));

$entry_year = $_POST['entry_year'];

$wpdb->insert ('students', array(

'first_name' => $first_name,

'last_name' => $last_name,

'dob' => $dob,

'entry_year' => $entry_year

));

wp_redirect( site_url('/index.php/view-students') );

}

add_action( 'admin_post_nopriv_add_student', 'add_student_form' );

add_action( 'admin_post_add_student', 'add_student_form' );

Once you are finished, click Update File at the bottom of the page. Note: This form lacks validation that normally would be found here or done before the form is submitted on the client side of the form page.

18

Step 6: Testing For the last step, you will be testing your newly created tables and forms. To start, go to yoursiteurl.com/view-students, which will bring up a table of all students you have within your Students table. Unless you added test data to your new tables, the content of this page should be blank.

Go to yoursiteurl.com/add-student and enter some student information to store into the Students table. Upon successful completion, you should be redirected to the View Students page showing your newest entry.

19

Add your student information and take a screenshot of your View Students page after it is successfully added.

Student Name: <Insert your first and last name here> Date of Birth: <Insert your birth month, birth date, and birth year> Entry Year: <Insert the year that you started taking courses>