PHP

Create your own CRUD app with PHP & MySQL (Part 2)

By Leighton Rodney

Picking up from where we left off, we need to start by creating some HTML to display the data we’ve stored in the $results variable. You can always modify this to suite your project. I’ll be using a table structure.

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8"/>
<link href="styles/theme.css" rel="stylesheet"/>
<title>PHP & MySQL</title>\
</head>
<body>

    <div id="wrapper">
    <table>
    	<thead>
            <th>Title</th>
            <th>Release Date</th>
            <th>Publisher</th>
            <th>System</th>
            <th>Rating</th>
            <th>Number Of Players</th>
            <th>Tools</th>
        </thead>
        <tbody>
        	<?php foreach($results as $entry): ?>
            <tr>
            	<td><?php echo $entry->title; ?></td>
                <td><?php echo $entry->release_date; ?></td>
                <td><?php echo $entry->publisher; ?></td>
                <td><?php echo $entry->system; ?></td>
                <td><?php echo $entry->rating; ?></td>
                <td><?php echo $entry->num_players; ?></td>
                <td><a href="edit.php?id=<?php echo $entry->id; ?>">Edit</a> <a href="delete.php?id=<?php echo $entry->id; ?>">Delete</a></td>
            </tr>
            <?php endforeach; ?>
            <tr>
            	<td class="create"><a href="add.php">Add New Game</a></td>
            </tr>
        </tbody>
    </table>
    </div>

</body>
</html>

Let’s go through what’s happening here. Right after the opening tbody tag I create a for loop.

Essentially, a loop is a piece of code you want to use repeatedly; hence the term loop. For each result from the database I store it in a variable called $entry. Then to go a step further I can target specific fields by using the namespace separator or sometimes called the “arrow operator”(->).

The namespace separator allows you to target objects within objects. By using the loop we can “loop” through all the results from the database and they will have the own rows in the table.  Ensure that the loop is closed after the closing tr tag to prevent data spilling out incorrectly.

You’ll also notice that there are two links (Edit & Delete) that handle editing and deleting specific entries. The syntax is simple and if you understand how one works, the other one makes perfect sense. Let’s look at the Edit link:

<a href=”edit.php?id=<?php echo $entry->id; ?>”>Edit</a>

This link will go to edit page and display the editable data related to the id that we echo out. Delete link simply deletes an entry by it’s id. Please note that these two links are inside the for loop, that way we get a edit and delete button for each entry in the database. The last link, which is out side of the loop is an add link that goes to an add.php page where you can input new data instead of going to the actual database manager to insert data.

First we’ll take a look at the delete.php page:

<?php
require_once 'includes/db.php';

if( isset($_GET['id']))
{
	$sql = $db->prepare('DELETE FROM games WHERE id = :id');
	$sql->bindValue(':id', $_GET['id'], PDO::PARAM_INT);
	$sql->execute();
}

	header('Location: index.php');
	exit;

The first line includes our db.php file which establishes a connection to our database. Next we create an if statement to see if an id was also passed in the link and if it exists in our database, then we continue.

Next we create a prepare statement. A prepare statement “prepares” a SQL statement to be “executed” by the PDO:execute method. Prepare statements help to prevent against hacks and injection attacks because we never clearly quote values, parameters etc. we utilize user-input and bind them to our statements.

So in our case our prepare statement is: ‘DELETE FROM games WHERE id = :id’. The id with a colon is our user-input, in our case the id we pass along in the link. In the next line we bind id to the id we passed in the link, so if the id we passed was 3, we will be deleting the entry in our database that has an id of 3.

While we bind our values we also specify what kind of data it is. In our case it’s an integer so we use PDO::PARAM_INT. Next we need to execute our SQL statement, using the execute method. Once it has been executed, we want to redirect the user to the index.php page the best and fastest way is by using header(‘Location: index.php’)exit;. This process happens so quickly you barely even notice it. Now we’ll move on to the edit.php page.

<?php
require_once 'includes/filter-wrapper.php';
require_once 'includes/db.php';

$errors = array();

$id = filter_input(INPUT_GET, 'id', FILTER_SANITIZE_NUMBER_INT);
	//if there's no id redriect to the homepage
	if(empty($id))
	{
		header('location: index.php');
		exit;
	}

//sanitize all the fields
$title = filter_input(INPUT_POST, 'title', FILTER_SANITIZE_STRING);

$release_date = filter_input(INPUT_POST, 'release_date', FILTER_SANITIZE_STRING);

$publisher = filter_input(INPUT_POST, 'publisher', FILTER_SANITIZE_STRING);

$system = filter_input(INPUT_POST, 'system', FILTER_SANITIZE_STRING);

$rating = filter_input(INPUT_POST, 'rating', FILTER_SANITIZE_NUMBER_INT);

$num_players = filter_input(INPUT_POST, 'num_players', FILTER_SANITIZE_NUMBER_INT);

if($_SERVER['REQUEST_METHOD'] == 'POST')
{
	//validate the form
	if(empty($title))
		$errors['title']=true;

	if(empty($release_date))
		$errors['release_date']=true;

	if(empty($publisher))
		$errors['publisher']=true;

	if(empty($system))
		$errors['system']=true;

	if(empty($rating))
		$errors['rating']=true;

	if(empty($num_players))
		$errors['num_players']=true;	

	//if there are no errors put data into database
	if(empty($errors))
	{
		$sql = $db->prepare('UPDATE games SET title = :title, release_date = :release_date, publisher = :publisher, system = :system, rating = :rating, num_players = :num_players WHERE id = :id');
		$sql->bindValue(':id', $id, PDO::PARAM_INT);
		$sql->bindValue(':title', $title, PDO::PARAM_STR);
		$sql->bindValue(':release_date', $release_date, PDO::PARAM_STR);
		$sql->bindValue(':publisher', $publisher, PDO::PARAM_STR);
		$sql->bindValue(':system', $system, PDO::PARAM_STR);
		$sql->bindValue(':rating', $rating, PDO::PARAM_INT);
		$sql->bindValue(':num_players', $num_players, PDO::PARAM_INT);

		$sql->execute();
		header('location: index.php');
		exit;
	}

}
else
{
	//display database information
	//shows the title in the value part
	$sql = $db->prepare('SELECT id, title, release_date, publisher, system, rating, num_players FROM games WHERE id = :id');
	$sql->bindValue(':id', $id, PDO::PARAM_INT);
	$sql->execute();
	$results = $sql->fetch(PDO::FETCH_OBJ);

	$title = $results->title;
	$release_date = $results->release_date;
	$publisher = $results->publisher;
	$system = $results->system;
	$rating = $results->rating;
	$num_players = $results->num_players;
}

?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<link href="styles/theme.css" rel="stylesheet"/>
<title>Edit</title>
</head>
<body>

	<div id="wrapper">

    <form action="edit.php?id=<?php echo $id; ?>" method="post">

        <div>
        	<label for="title">Title</label>
            <?php if(isset($errors['title'])): ?>
            <label for "title"><p class="error">Error! Enter Valid Title</p></label>
            <?php endif; ?>
            <input id="title" name="title" value="<?php echo $title; ?>">
        </div>

        <div>
        	<label for="release_date">Release Date</label>
            <?php if(isset($errors['release_date'])): ?>
            <label for "release_date"><p class="error">Error! Enter Valid Date (YYYY-DD-MM)</p></label>
            <?php endif; ?>
            <input id="release_date" name="release_date" value="<?php echo $release_date; ?>">
        </div>

        <div>
        	<label for="publisher">Publisher</label>
            <?php if(isset($errors['publisher'])): ?>
            <label for "publisher"><p class="error">Error! Enter Publisher</p></label>
            <?php endif; ?>
            <input id="publisher" name="publisher" value="<?php echo $publisher; ?>">
        </div>

        <div>
        	<label for="system">System</label>
            <?php if(isset($errors['system'])): ?>
            <label for "system"><p class="error">Error! Enter System</p></label>
            <?php endif; ?>
            <input id="system" name="system" value="<?php echo $system; ?>">
        </div>

        <div>
        	<label for="rating">Rating</label>
            <?php if(isset($errors['rating'])): ?>
            <label for "rating"><p class="error">Error! Enter Rating (1-10)</p></label>
            <?php endif; ?>
            <input id="rating" name="rating" value="<?php echo $rating; ?>">
        </div>

        <div>
        	<label for="num_players">Number Of Players</label>
            <?php if(isset($errors['num_players'])): ?>
            <label for "num_players"><p class="error">Error! Enter Number Of Players</p></label>
            <?php endif; ?>
            <input id="num_players" name="num_players" value="<?php echo $num_players; ?>">
        </div>

        <div>
            <button type="submit">Save</button>
        </div>

    </form>
  </div>

</body>
</html>

The first line includes our db.php file and our filter-wrapper.php file. As you already know, the db.php file establishes a connection with the database, the filter-wrapper on the other hand does something totally different. It checks to see what wrappers your version of php has, if it’s missing some it creates and defines them. Depending what version you have newer or older I think it best always to keep a copy handy, as may not need it when creating the code but your client or wherever it may be hosted might need it if they’re running an older version of php. In the next line we create an array and store it in a variable called $errors; this will help us with our validation. Next we check if an id has been passed along with the link; if not we redirect them to the index page using header(‘Location: index.php’)exit;.

If an id is passed along we need to make sure that validate that the user and inputted data we can store in our database, and that the input is not harmful to our database and will neither compromise it’s integrity. We need to sanitize our inputs. This is where our filter wrapper shines as it holds all the filters we need to check if the user’s input is valid. We’ll use filters that appropriately match our desired input; eg.

If we expect a string our filter would be FILTER_SANITIZE_STRING, for a integer, FILTER_SANITIZE_INT and so on. Once we’ve taken care of filtering our input fields, we need to do some basic validation. Remember that empty $errors array we created? The next thing we need to get some basic validation going is to create an if statement. If a particular field is empty we add that to the errors array. Once we have that done for all fields and we check if the data is valid we can update the database by using the prepare statement again like we did for the delete.php file. We bind all our inputs to the fields in the database then execute our SQL statement.

Now we need to ensure that when the page is visited the data that was previously stored in the database shows up in their specific fields; this helps if the user just wants to make a slight edit like a name change or correct a spelling error etc. The first part is done in this remaining code block; the rest is done within the form. What comes next in our code is another prepare statement; we will select all fields by id. Next, we bind the id we passed along in the edit.php link and execute the query. Since we only need to display one database entry, we don’t need a for loop; so variables for each field is fine in this case.

Now we can move to the little bits of php in our html. For our form element, ensure that you echo the id when you are typing the link for the action, and set the method to post. Next, the input fields; This is very easy just echo the different variables in their appropriate values. For the basic valid you can use whatever element you like to display your error message; a strong tag, p tag, whatever. I used a label tag. To get the validating going you need to wrap that label or strong or p tag in an if statement and check if it’s appropriate error is in the $errors array. If it is we display that error message, and that’s it for the edit.php page. Now let’s take a look at the add.php page.

<?php
require_once 'includes/filter-wrapper.php';
require_once 'includes/db.php';

$errors = array();

//sanitize all the fields
$title = filter_input(INPUT_POST, 'title', FILTER_SANITIZE_STRING);

$release_date = filter_input(INPUT_POST, 'release_date', FILTER_SANITIZE_STRING);

$publisher = filter_input(INPUT_POST, 'publisher', FILTER_SANITIZE_STRING);

$system = filter_input(INPUT_POST, 'system', FILTER_SANITIZE_STRING);

$rating = filter_input(INPUT_POST, 'rating', FILTER_SANITIZE_NUMBER_INT);

$num_players = filter_input(INPUT_POST, 'num_players', FILTER_SANITIZE_NUMBER_INT);

if($_SERVER['REQUEST_METHOD'] == 'POST')
{
	//validate the form
	if(empty($title))
		$errors['title']=true;

	if(empty($release_date))
		$errors['release_date']=true;

	if(empty($publisher))
		$errors['publisher']=true;

	if(empty($system))
		$errors['system']=true;

	if(empty($rating))
		$errors['rating']=true;

	if(empty($num_players))
		$errors['num_players']=true;	

	//if there are no errors put data into database
	if(empty($errors))
	{
		$sql = $db->prepare('INSERT games SET title = :title, release_date = :release_date, publisher = :publisher, system = :system, rating = :rating, num_players = :num_players');
		$sql->bindValue(':title', $title, PDO::PARAM_STR);
		$sql->bindValue(':release_date', $release_date, PDO::PARAM_STR);
		$sql->bindValue(':publisher', $publisher, PDO::PARAM_STR);
		$sql->bindValue(':system', $system, PDO::PARAM_STR);
		$sql->bindValue(':rating', $rating, PDO::PARAM_INT);
		$sql->bindValue(':num_players', $num_players, PDO::PARAM_INT);

		$sql->execute();
		header('location: index.php');
		exit;
	}

}

?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<link href="styles/theme.css" rel="stylesheet"/>
<title>Add New Game</title>
</head>
<body>

    <div id="wrapper">
    <form action="add.php" method="post">

        <div>
        	<label for="title">Title</label>
            <?php if(isset($errors['title'])): ?>
            <label for "title"><p class="error">Error! Enter Valid Title</p></label>
            <?php endif; ?>
            <input id="title" name="title" value="<?php echo $title; ?>">
        </div>

        <div>
        	<label for="release_date">Release Date</label>
            <?php if(isset($errors['release_date'])): ?>
            <label for "release_date"><p class="error">Error! Enter Valid Date (YYYY-DD-MM)</p></label>
            <?php endif; ?>
            <input id="release_date" name="release_date" value="<?php echo $release_date; ?>">
        </div>

        <div>
        	<label for="publisher">Publisher</label>
            <?php if(isset($errors['publisher'])): ?>
            <label for "publisher"><p class="error">Error! Enter Publisher</p></label>
            <?php endif; ?>
            <input id="publisher" name="publisher" value="<?php echo $publisher; ?>">
        </div>

        <div>
        	<label for="system">System</label>
            <?php if(isset($errors['system'])): ?>
            <label for "system"><p class="error">Error! Enter System</p></label>
            <?php endif; ?>
            <input id="system" name="system" value="<?php echo $system; ?>">
        </div>

        <div>
        	<label for="rating">Rating</label>
            <?php if(isset($errors['rating'])): ?>
            <label for "rating"><p class="error">Error! Enter Rating (1-10)</p></label>
            <?php endif; ?>
            <input id="rating" name="rating" value="<?php echo $rating; ?>">
        </div>

        <div>
        	<label for="num_players">Number Of Players</label>
            <?php if(isset($errors['num_players'])): ?>
            <label for "num_players"><p class="error">Error! Enter Number Of Players</p></label>
            <?php endif; ?>
            <input id="num_players" name="num_players" value="<?php echo $num_players; ?>">
        </div>

        <div>
            <button type="submit">Save</button>
        </div>

    </form>
    </div>

</body>
</html>

You can see it’s very similar to the edit.php page, with regards to validation and such. Where it becomes different is the SQL statement; instead of updating or deleting data we are inserting data into the database hence we use INSERT. We also do not insert a new id as we want that to auto-increment as we have set in our database. The rest looks similar; we bind our inputs and execute our SQL statement. Our html is the same from the edit.php page as we are doing basic validation. That is how to create a CRUD app in php; the principles can be expanded to how large or how small your desired project is, and it is very secure from rainbow table hacks and SQL injection attacks.

By Leighton Rodney
Leighton is a Graphic Designer & Front-End Developer currently based in the Ottawa, Canada. Check out his Website: www.leightonrodney.com Twitter:@leightonrodney More articles by Leighton Rodney
Web Development

Why building a gift card option is essential for ecommerce

By Natalie Pavlovskaya
Gift cards possess huge potential for merchants who want to increase revenue, acquire new customers and get their brand noticed. It’s generally believed that a gift card program is a marketing tool perfect only for the biggest holiday season or that it can be applied merely by big brands. However, gift cards can be a great tool to drive customers and increase sales, regardless of the season or the brand size. According to the National Retail Federation 2014 survey, 60% (or 6 in 10) of shoppers say...
C#

A basic database SCRUD class using abstract and generic modifiers

By DeveloperDrive Staff
Introduction ... SCRUD is short for Select, Create, Read, Update and Delete. These are the general actions that any program will perform when working with databases. Yes, there are plenty of ORM applications ( http://en.wikipedia.org/wiki/Object-relational_mapping ) out there that can replace this, but as a general introduction to abstract or generic classes and general development I still think this is worth a read. I should also explain why I have used an abstract class instead of an interface....
DesignBombs

How to Create a Landing Page with Bootstrap 4

By DeveloperDrive Staff
Bootstrap 4 is currently the most popular front-end framework in the world. You can use it to build any kind of user interfaces, from admin templates to full-featured websites. In this step-by-step tutorial, we will have a look at how to build a responsive landing page with Bootstrap 4. You can even use it as a homepage or connect it to a specific marketing campaign. The landing page we will build will have five sections: a fixed navigation bar, a hero section, a three-column "Services" section, a full-width...
WordPress

How to build an auto-ranking Twitter list with WordPress

By James Montgomery
My team and I recently built an awesome list template on WordPress that ranks a set of Twitter users based on follower count. It allows a content writer to easily add a list of Twitter handles, and generate a well designed post. The first piece of content we created with it generated a 1,212% increase in traffic to our site. Check out the tutorial below if you're interested in building it as well. Requirements ... Input Twitter Handle (required) Name (optional) Tagline (optional) Image (optional) Bio...
DesignBombs

How to build your own theme for Grav CMS

By Angie Vella
Grav is an open source flat-file CMS platform, built by the RocketTheme Team. While there are plenty of great CMS platforms available, they are all mostly database-driven, which can be overkill for smaller websites. Instead of a database, Grav uses folders and a basic file structure, it is focused on speed, simplicity, and flexibility. After reading all of the documentation and spending some time trying Grav out, I’m definitely sold and will be using the platform. I hope you will too. What We’ll...
JavaScript

Developing With The HTML5 Element

By Scott Stanton
Videos have been a great way to attract viewers to a website long before YouTube launched back in 2005.  But it wasn’t until the release of HTML5 that web developers have had a lightweight solution to playing the video.  In the past, displaying a video on your site meant your viewer was required to have a Flash or Java-based player installed on their system in order to watch the video.  This was one more thing that would weigh down your site, causing pages to load slower, and be one more thing you’d...
CSS

HTML5 and CSS3 Form Validation

By DeveloperDrive Staff
In HTML5 forms got a major upgrade with the addition of some simple, yet flexible validation attributes. To support these added attributes CSS3 also added several new pseudo selectors styling controls based on their validation state. Adding validation ... To illustrate the new attributes and some of the new input types, we'll be building up a simple sign up form. Every sign up form is essentially the same, you fill in your details and click submit. How many times has the form been reset on you after you've...
HTML

OAuth2 and the LinkedIn API – A WordPress CV/Résumé plugin

By DeveloperDrive Staff
Continuing on from last weeks introduction to OAuth 2.0 using the Twitter API, this week we're going to focus on an alternate implementation of OAuth 2.0 used in the LinkedIn API. This implementation requires that you manually authorize the application before you can get a token. This implementation is a more complex as it requires you to be re-directed to the authentication server, which then re-directs you back to your own site with the approved token. This allows LinkedIn to add additional permissions...
Home CSS Deals DesignBombs HTML HTML5 JavaScript jQuery Miscellaneous Mobile MySQL News PHP Resources Security Snippet Tools Tutorial Web Development Web Services WordPress