DES471: Dynamic Web 1

Week # 10

Upload your Challenge and Assignment to D2L. This will be located in Content -> Week 10 -> Graded Activities

CMS

• Last week, we went over using SQL to update our database in the form of a Content Management System (CMS).

Here are some highlights about CMS:

• What is a CMS? According to Wikipedia, a CMS is "A content management system (CMS)[1][2][3] is a computer application that allows publishing, editing, modifying, organizing, deleting, and maintaining content from a central interface." (https://en.wikipedia.org/wiki/Content_management_system)
• Basically, a CMS is a program that allows you to manage content using an interface. Examples of some common CMS are WordPress, SquareSpace, Joomla!, and Drupal. 
• We'll cover the usage of existing CMS in detail in Dynamic Web 2, but for now we're going to learn how to create some of the concepts behind the technology using PHP.

CMS

Here is the final result of our update.php from last week:

https://goo.gl/ON4iQn

Note: You might run into some issues with the page giving warnings related about a header issue. This can be resolved by putting the "require require/login_check.php" line right above the HTML below all the rest of the code. 

CMS

This week, we're going to finish our usage of CRUD and CMS by implementing the deletion of elements in our system.

We are also going to upload our projects to a live server, including the information from our local database.

CMS

Alright, so let's get started with deleting our information.

Let's say a user wants to delete their information. This is a typical use case for when a user wants to delete their profile. In this case, the profile is the user row for that particular user. 

The steps we are going to take in order to perform this action is as follows:

1. Create a form for the username and password of the user.

2. Have a simple check to make sure the user wants to delete their user from the database.

3. If the username and password match that in the database, then delete the user. 

CMS

Our Delete form is going to be very simple. We only need fields for username and password. The submit button should have a name and a value of "delete" and the method of the form should remain as "post". I suggest doing a copy/paste from the login.php form. 

Try and do this on your own and then take a look at the solution on the next page.

CMS

<div id="container">
	<?php include "includes/header.php" ?>
	<?php include "includes/nav.php" ?>
<h1>Delete User</h1>
<form id="form_delete" method="post" action="">
	<ul>
		<li>
			<label for="username">Username</label>
			<input id="username" type="text" name="username" value="" />
		</li>
		<li>
			<label for="password">Password</label>
			<input id="password" type="password" name="password" value="" />
		</li>
		<li>
			<input id="submit_delete" type="submit" name="delete" value="Delete">
		</li>
	</ul>
</form>
	<?php include 'includes/footer.php' ?>
</div>

Delete Form

CMS

Delete Form

Next, be sure to delete any php code (aside from the require statements) from the top of the page. We're just going to copy and paste the code from our login.php file and then make some adjustments. 

Changes:

• Change the "login" to "delete"

if (isset($_POST['delete']) && trim($_POST['delete']) != '') {
	if (isset($_POST['username']) && isset($_POST['password']) 
		&& trim($_POST['username']) != '' && trim($_POST['password']) != '') {

		$username = escape_quotes($_POST['username']);
		$password = escape_quotes(hash("sha512", $_POST['password']));

CMS

Delete Form

Next, we're going to just copy more code from login.php. Seeing a trend here? This one is going to be similar to the code from login.php, except that we're going to redirect the urls in the "die" functions to "delete.php". We're just checking to see if the login information from the form matches that of the database.

$password = escape_quotes(hash("sha512", $_POST['password']));

$user = get_all_info("SELECT * FROM users WHERE username='$username'");

// Get the first instance of the user and store it into an array
$userArray = $user->fetch_assoc();

if(count($userArray) <= 0) {
	die("<h2>That username doesn't exist! Please type in the correct username. 
		<a href='delete.php'>Back</a></h2>");
}
if ($userArray['password'] != $password) {
	die("<h2>Incorrect password! <a href='delete.php'>Back</a></h2>");
}

CMS

Delete Form

We will now go ahead and delete the information from our database.

• First do a query to the database to delete the information using the "insert_or_update_info" function. (continued on next page)

		if ($userArray['Password'] != $password) {
			die("<h2>Incorrect password! <a href='delete.php'>Back</a></h2>");
		}

		insert_or_update_info("DELETE FROM users WHERE username='$username'");	

		setcookie("c_user" , '' , time()-50000, '/');

	        $logged = false;

		echo "<h2>User has been deleted. <a href='index.php'>Home</a> </h2><br>";

	    exit;
	}
	else {
		echo "<h2>Please enter a username and password.</h2>";
	}
}

CMS

Delete Form

• Next, we're going to set the cookie to expire, just like we did in the logout.php page. 
• Then, set the $logged variable to false so that the user is logged out. 
• Print out a statement that says the user is logged out and then exit the script using the keyword "exit"

		if ($userArray['Password'] != $password) {
			die("<h2>Incorrect password! <a href='delete.php'>Back</a></h2>");
		}

		insert_or_update_info("DELETE FROM users WHERE username='$username'");	

		setcookie("c_user" , '' , time()-50000, '/');

	        $logged = false;

		echo "<h2>User has been deleted. <a href='index.php'>Home</a> </h2><br>";

	    exit;
	}
	else {
		echo "<h2>Please enter a username and password.</h2>";
	}
}

CMS

JavaScript Check

• In case you wanted to use JavaScript to put a check on the delete button, the code is listed above. Before you implement this, make sure you have an id of "form_delete" on the form element. Also, be sure to include the file, along with jQuery. 

$( document ).ready(function() {
    $("#form_delete").submit(function( event ) {
		var answer = confirm("Are you sure you want to delete the user?");

		if (answer == true) {
		    return;
		}

		event.preventDefault();
	});
});

CMS

Delete Form

That's it! Your delete form should now be working. Test it out!

You now have a complete CRUD system that you can copy and use across any of your future sites :)

CMS

Putting your app on a live server

Now that we have a completed CRUD application, along with a contact form and a variety of other functionality, we should make it live for everyone to see. 

If you're in this class, I'm sure you have experience with uploading your sites to a live page. This may have been done using FTP or Git with Github pages (if you've taken my classes).

I've found a free solution that works pretty well, so I'm gonna go with that for everyone. We're going to use a combination of Heroku for hosting, cleardb for our production database, and Sequel Pro for our database management system (just like phpmyadmin, but way nicer). 

CMS

Putting your app on a live server

Here is the basis for the next series of steps: https://www.youtube.com/watch?v=iRgSQTmA-N4

Just like before, though, the video is going to be different in some regards from what I do, but the concepts are the same. 

CMS

Hosting

Heroku is a cloud application platform and a service that we'll be using to host our web application. 

• Let's start using Heroku by going to heroku.com and signing up for a free account. Be sure to save your login and password, as we'll need it. 
• Next, download the heroku toolbelt from https://toolbelt.heroku.com/ (be sure to download the one for your operating system) and install the toolbelt. This toolbelt will allow us to access Heroku from the terminal. 
• Open your terminal and cd into your application. 
• Log in to Heroku using the command "heroku login" in your terminal (again, be sure you've cd'ed into your project). Enter your username and password for Heroku.
• After hitting enter, you should see a prompt that says you should generate a public key (if not, that's fine too). Type "Y" (for "yes") and hit enter.

CMS

Hosting

• Once that part is done, you'll be logged into Heroku from the command line.
• Next, heroku requires a "composer.json" file in order for us to deploy to heroku. Simply create this file from the command line using "touch composer.json". In this file, add "{}" and save the file. This is creating an empty object. 
• In order to use Heroku, we need to use git. We're definitely not going to go into git in this class, so I'll just go over the commands to use (don't include the double quotes):
  • Type/enter "git init"
  • Type/enter "git add ."
  • type/enter "git commit -m 'committing for Heroku'"
• Next, let's create our Heroku application on Heroku by typing in "heroku create" on the command line. 

CMS

Hosting

• Next, we're going to deploy our application to Heroku. This just means we're uploading our application from our local machine to Heroku. Type in "git push heroku master". You should see a bunch of information being printed onto the screen; this is just the process of the application being deployed. Heroku automatically detects that our application is built in php and sets up the app for us. We are then given a live link to the project if you look at the command line. Take a look at the next page to see where the link to your live page is.

CMS

Hosting

CMS

Hosting

• There is a problem, though. If you tried going to the application, you would see an error. That's because we've deployed our app, but not our database!
• In order to deploy our database, we're first going to download a database management system similar to phpmyadmin. If you're using a Mac, please download Sequel Pro: http://www.sequelpro.com/. If you're using a PC, please download MySQLWorkbench: https://www.mysql.com/products/workbench/
  • Again, these applications are just a way to visualize your database and make changes to them, just like we did in phpmyadmin, except that now we can use it live. phpmyadmin does work live, but doesn't seem to work well with heroku.

CMS

Hosting

• Once the applications are installed, we are going to add a database called "cleardb" to our Heroku application. Be sure that you're in your application in the terminal and type "heroku addons:create cleardb:ignite". 
• Next, we're going to connect to our local database using either Sequel Pro (if using Mac) or MySQLWorkbench (if on PC). To connect to the local database on Sequel Pro, click on the button on the bottom left to create a new connection. Click on the "socket" tab and then name your database "development" (this is just for us). Then, type in the username, password, and database name that we gave our existing database. Most of you are using "root" for your username and an empty password. My database name is "monkey_butt". 

CMS

Hosting

• Once the applications are installed, we are going to add a database called "cleardb" to our Heroku application. Be sure that you're in your application in the terminal and type "heroku addons:create cleardb:ignite". Note: You will need to enter a credit card into Heroku if you plan on exporting a database. They will not charge you, but they do need it for verification for some reason. You can still get a full grade without doing this, but it will require you commenting out a lot of code to make your app work on the live server. 
• Next, we're going to connect to our local database using either Sequel Pro (if using Mac) or MySQLWorkbench (if on PC). To connect to the local database on Sequel Pro, click on the button on the bottom left to create a new connection. Click on the "socket" tab and then name your database "development" (this is just for us). Then, type in the username, password, and database name that we gave our existing database. Most of you are using "root" for your username and an empty password. My database name is "monkey_butt". For the socket, type "/Applications/MAMP/tmp/mysql/mysql.sock"
• See next page for a visual.

CMS

Hosting

CMS

Hosting

• Next, we're going to get the database name, database username, database password, and database host by typing in "heroku config --app HEROKUAPPNAME" into your terminal. Note that HEROKUAPPNAME is a placeholder and can be found from the url of your application. For example, if your url is https://intense-oasis-99775.herokuapp.com/, then your app name is "intense-oasis-99775". 
• Once we type the above in, it should give us something back that looks like the following: 
  • https://goo.gl/W542d7
  • Let's break it down on the next page

CMS

Hosting

Now, that just means we have the following information:

• username: sfsdf9287hasdgu2
• Password: asdsd23234
• Host: us-cdbr-iron-east-03.cleardb.net
• Database name: heroku_6c46f8852casd334

Of course, your credentials are going to look different than mine. We're going to use these credentials to log into our database from Sequel Pro or MySQLWorkbench. Before we do that, though, we're going to do something called a SQL dump. That sounds gross, but all it means is that we're going to get all of the info needed to get the information from our database and store it into a file.

CMS

Importing and Exporting

Let's log into our local database using Sequel Pro or MySQLWorkbench. As a side note, you can save your credentials so you don't have to keep type it in over and over when you want to log in.

• If you're using Sequel Pro, go to File -> Export
• See the next page for a diagram of what to click next

CMS

Importing and Exporting

CMS

Importing and Exporting

• Once you've downloaded the file, you're going to place it in your project directory. The file should end with .sql. 
• Again, what we just did is download a file with all of our contents from our local database. Our goal is to then import that information into our production database.
• To do this, go to your terminal and be sure that you are in your project and that your .sql file is in there.
• Then, connect to your production database from Sequel Pro (create a new connection and name it) using the database information that we got earlier from typing "heroku config --app HEROKUAPPNAME".  See next page for a diagram of what it looks like to connect via Sequel Pro.
• In Sequel Pro, go to File -> Import and then import the sql file you exported earlier. 

CMS

Importing and Exporting

 Log into the production database by using the credentials you got earlier form the terminal. It should look something like this:

CMS

Connecting

Once you log in, you should see all the information from your local database! 

Ok, so our final step is going to be connecting our application to the production database. This part is going to be fairly short. 

Go to your constants.php page and then go to the next slide for how the constants.php page should now look like.

CMS

Connecting

<?php 

//DB Constants for local
// define("DB_SERVER", "localhost");
// define("DB_USER", "potatoMike");
// define("DB_PASS", "meowmix");
// define("DB_NAME", "monkey_butt");

//DB Constants for production
$cleardb_url = parse_url(getenv("CLEARDB_DATABASE_URL"));
$cleardb_server = $cleardb_url["host"];
$cleardb_username = $cleardb_url["user"];
$cleardb_password = $cleardb_url["pass"];
$cleardb_db = substr($cleardb_url["path"], 1);

define("DB_SERVER", $cleardb_server);
define("DB_USER", $cleardb_username );
define("DB_PASS", $cleardb_password);
define("DB_NAME", $cleardb_db);

?>

CMS

Connecting

Notice that I commented out my connection constants for my local machine. That's because we now want to connect to our production database instead of our local database. 

The second chunk of code is actually asking Heroku for the credentials. This is a secure way of using the information, since it prevents our file system from storing sensitive information. I've then sent the information from these variables into the define functions. 

Once you've done this, please be sure to perform the following in the command line (minus the double quotes):

"git add ."

"git commit -m 'updated the constants file'"

"git push heroku master"

CMS

Connecting

That's it. If you refresh your heroku app, you should see your project up and running :)

Here is the final code for the files we went over today:

constants.php: https://goo.gl/wOpi6k

delete.php: https://goo.gl/y7cVX2

composer.json: Just has empty brackets inside.

Optional script.js file for form validation (making sure a user wants to delete their user): https://goo.gl/kil3QP

{}

Assignment 10

Be sure to add what we did today to your final project. I'll be checking to make sure it exists. Also, be sure that your app is hosted live and that the database is also on a live server. Basically, this assignment is a freebie if you've followed along with class today. 

Final Project

Design and develop a 4-page PHP and MySQL driven website.

• PHP Require Demonstrates shared global variables and functionality. Connection script, global variables, and at least one shared function.
• PHP Includes Demonstrates shared html assets
• 4 Pages At least 4 pages
• DB Driven Gallery Your solution must feed a lightbox or slideshow. Even if you didn't export a database, you need to show the code that would make this work and then comment it out. 
• CMS Login, Log Out User login and log out
• CMS Insert, Update, and Delete The ability to add records to a DB table of your choosing.
• Exported DB and Link to Live Site Exported SQL database. A link to the live, functioning version of your website (Published to your webhost, database synched, and db connection made). Note: If you don't have an exported db due to not wanting to put your credit card info on Heroku, you must comment out the info on your project that has any references to the database. Be sure not to delete any information, as I'll be checking to see if it works. Once you do this, push your changes up to heroku.
• PHP Mail Contact Form One instance of a contact us form like we did in class.