MySQL CRUD - PHP Databases by Dino Cajic

MySQL CRUD

From Creation to Deletion: MySQL’s Complete Data Toolkit

What are CRUD operations? Create, Read, Update, Delete. That’s what we have created over the last few articles. The last piece of the puzzle is to combine them into one file that knows how to manage our authors table. If you’re jumping straight into this article, I recommend that you read the earlier articles leading up to this one first. You don’t need to, but it’ll help.

Recap

Review the code from the previous articles. We have most of the operations built but may need to tweak a couple of things here and there.

The Route File

In the previous articles, we used our route file to route to our various different files. We can do that here too. It’s going to be slightly messy with our existing code since we’re combining both object oriented and non-object oriented principles, but that’s sometimes what you’ll get. This would be a perfect time to create an Author Controller and then views, but we’ll pass on that for the sake of space. Instead, our route file (index.php) will use the header function to move the user around. We’ll cover the AuthorController in the next article.

<?php
require_once("./Author.php");

use Authors\Author;

if ( !isset($_GET['page']) ) {
    ?>
    <a href="index.php?page=authors">View All Authors</a>
    <?php
    die();
}

$page   = $_GET['page'];

if ( $page == "authors" ) {
    header("Location: ViewAll.php");
    die();
}

So far, all that our route file does is checks to see if the page url parameter exists. If it doesn’t, it’ll display the link to View All Authors. The link points to index.php and passes the page argument of authors. Once it hits index.php again, the page argument will be present and will direct the user to the ViewAll.php page.

This is just phase one. This file will continue to grow.

Author Class

The Author class now looks like a proper file. We’ve added all of our previous methods to create, read, update, and delete an author.

<?php
namespace Authors;

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

require_once("DB.php");

use Database\DB;

class Author
{
    public function insert( array $author )
    {
        $sql = "INSERT INTO authors (first_name, last_name, email) 
                VALUES (?, ?, ?)";

        $db = new DB;
        $mysqli = $db->_connection;

        $statement = $mysqli->prepare( $sql );
        $statement->bind_param("sss", $author['first_name'], $author['last_name'], $author['email']);
        $statement->execute();

        $error = $statement->error;

        $statement->close();
        $db->close();

        if ( $error ) {
            return false;
        }

        return true;
    }

    public function select_all()
    {
        $sql = "SELECT * FROM authors";

        $db = new DB;
        $mysqli = $db->_connection;

        $results = $mysqli->query( $sql );
        $db->close();

        return $results->fetch_all(MYSQLI_ASSOC);
    }

    public function select( int $id )
    {
        $sql = "SELECT * FROM authors WHERE id = " . $id;

        $db = new DB;
        $mysqli = $db->_connection;

        $results = $mysqli->query( $sql );
        $row = $results->fetch_assoc();

        $db->close();

        return $row;
    }

    public function update( array $author )
    {
        $sql = "UPDATE authors SET first_name = ?, last_name = ?, email = ? WHERE id = ?";

        $db = new DB;
        $mysqli = $db->_connection;

        $statement = $mysqli->prepare( $sql );
        $statement->bind_param(
            "sssi",
            $author['first_name'],
            $author['last_name'],
            $author['email'],
            $author['id']
        );
        $statement->execute();
        $error = $statement->error;

        $statement->close();
        $db->close();

        if ( $error ) {
            return false;
        }

        return true;
    }

    public function delete( array $author )
    {
        $sql = "DELETE FROM authors WHERE id = ?";

        $db = new DB;
        $mysqli = $db->_connection;

        $statement = $mysqli->prepare( $sql );
        $statement->bind_param(
            "i",
            $author['id']
        );
        $statement->execute();
        $error = $statement->error;

        $statement->close();
        $db->close();

        if ( $error ) {
            return false;
        }

        return true;
    }
}

View All

The view all page hasn’t been created yet. It’s entire purpose is to display all of the authors in the database.

<?php
require_once("./Author.php");

use Authors\Author;

$author = new Author;
$authors = $author->select_all();

foreach( $authors as $author ) {
    ?>
    <div>
        <a href="index.php?page=author&author_id=<?php echo $author['id']; ?>">
            <?php echo $author['id'] . ": " . $author['first_name'] . " " . $author['last_name']; ?>
        </a>
    </div>
    <?php
}

This will just display all the authors’ first and last name and wrap their name in a link. The link points to index.php and passes the author’s id and page arguments.

Show

View All displays all of the authors, but what if we needed to see an individual details? That’s where Show.php comes in hand. We’ll display the first name, last name, and email of the author. The author details will be added to a form so that we can also update them when necessary.

<?php
require_once("./Author.php");

use Authors\Author;

if ( !isset($_GET['author_id']) ) {
    die("You must pass an author");
}

$author = new Author;
$update_author = $author->select( $_GET['author_id'] );
?>

<form action="index.php?page=author" method="post">
    <input type="hidden" name="_method" value="put">
    <input type="hidden" name="id" value="<?php echo $update_author['id']; ?>">
    <div>
        First Name: <input type="text" name="first_name" value="<?php echo $update_author['first_name']; ?>">
    </div>
    <div>
        Last Name: <input type="text" name="last_name" value="<?php echo $update_author['last_name']; ?>">
    </div>
    <div>
        Email: <input type="text" name="email" value="<?php echo $update_author['email']; ?>">
    </div>
    <div>
        <input type="submit" value="Update">
    </div>
</form>

If the author_id argument is missing from Show.php, we’ll kill the application. If the user wants to update the author, they can click on the update button after making modifications. Before we get into the update, let’s see how our route needs to be updated from ViewAll to Show. In other words, how do we transfer that data from when a user clicks on the link in ViewAll.php to Show.php.

<?php
require_once("./Author.php");

use Authors\Author;

if ( !isset($_GET['page']) ) {
    ?>
    <a href="index.php?page=authors">View All Authors</a>
    <?php
    die();
}

$page   = $_GET['page'];

if ( $page == "authors" ) {
    header("Location: ViewAll.php");
    die();
}

if ( $page == "author" && isset($_GET['author_id']) ) {
    header("Location: Show.php?author_id=" . $_GET['author_id']);
    die();
}

We’re looking for the page argument set to author and for the url to also contain the author_id argument. If they do, the user is redirected to Show.php and the author_id argument is passed.

Update an Author

We have already defined the update functionality in our previous articles, so moving that over will be pretty simple. We’ll only move the put functionality in this instance.

The form in Show.php points to index.php?page=author and the _method hidden value of put is passed to our route.

<?php
require_once("./Author.php");

use Authors\Author;

if ( !isset($_GET['page']) ) {
    ?>
    <a href="index.php?page=authors">View All Authors</a>
    <?php
    die();
}

$page   = $_GET['page'];

if ( $page == "authors" ) {
    header("Location: ViewAll.php");
    die();
}

if ( $page == "author" && isset($_GET['author_id']) ) {
    header("Location: Show.php?author_id=" . $_GET['author_id']);
    die();
}

if ( $page == "author" ) {
    if ( isset($_POST['_method']) && $_POST['_method'] == "put" ) {
        $author = new Author;
        $update_success = $author->update($_POST);

        if ( $update_success ) {
            header("Location: index.php?page=author&author_id=" . $_POST['id']);
            die();
        } else {
            die("Could not update");
        }
    }
}

First we check if the page argument is an author. If it is, we check for the _method argument. Remember that it’s going to skip the first $page == author check since we did not pass an author_id in the url. This is a POST request.

Once we get into the meat of it, we’ll update the author and redirect the user back to the Show page.

Delete

The delete functionality is also built in. The question is where do we put the delete button? I’m going to place it on our Show page under the update button.

<?php
require_once("./Author.php");

use Authors\Author;

if ( !isset($_GET['author_id']) ) {
    die("You must pass an author");
}

$author = new Author;
$update_author = $author->select( $_GET['author_id'] );
?>

<form action="index.php?page=author" method="post">
    <input type="hidden" name="_method" value="put">
    <input type="hidden" name="id" value="<?php echo $update_author['id']; ?>">
    <div>
        First Name: <input type="text" name="first_name" value="<?php echo $update_author['first_name']; ?>">
    </div>
    <div>
        Last Name: <input type="text" name="last_name" value="<?php echo $update_author['last_name']; ?>">
    </div>
    <div>
        Email: <input type="text" name="email" value="<?php echo $update_author['email']; ?>">
    </div>
    <div>
        <input type="submit" value="Update">
    </div>
</form>

<form action="index.php?page=author" method="post">
    <input type="hidden" name="_method" value="delete">
    <input type="hidden" name="id" value="<?php echo $update_author['id']; ?>">
    <input type="submit" value="Delete">
    </div>
</form>

Let’s add our route so that we can complete the deletion process.

<?php
require_once("./Author.php");

use Authors\Author;

if ( !isset($_GET['page']) ) {
    ?>
    <a href="index.php?page=authors">View All Authors</a>
    <?php
    die();
}

$page   = $_GET['page'];

if ( $page == "authors" ) {
    header("Location: ViewAll.php");
    die();
}

if ( $page == "author" && isset($_GET['author_id']) ) {
    header("Location: Show.php?author_id=" . $_GET['author_id']);
    die();
}

if ( $page == "author" ) {
    if ( isset($_POST['_method']) && $_POST['_method'] == "put" ) {
        $author = new Author;
        $update_success = $author->update($_POST);

        if ( $update_success ) {
            header("Location: index.php?page=author&author_id=" . $_POST['id']);
            die();
        } else {
            die("Could not update");
        }
    }

    if ( isset($_POST['_method']) && $_POST['_method'] == "delete" ) {
        $author = new Author;
        $delete_success = $author->delete($_POST);

        if ( $delete_success ) {
            header("Location: index.php?page=authors");
            die();
        } else {
            die("Could not delete");
        }
    }
}

The route checks for the _method argument, similar to update and makes sure that it’s set to delete. Once the delete method is called in the Author class, the user is redirected back to view all authors.

Insert

Last but not least is the insert. We need two routes: one to create and one to actually insert the data. The link in our route file will point to a create page.

<?php
require_once("./Author.php");

use Authors\Author;

if ( !isset($_GET['page']) ) {
    ?>
    <a href="index.php?page=authors">View All Authors</a> |
    <a href="index.php?page=create">Create Author</a>
    <?php
    die();
}

$page   = $_GET['page'];

if ( $page == "create" ) {
    header("Location: Create.php");
    die();
}

//...

The Create.php file is just HTML that contains a blank form that’s identical to our Update.php form, but without any content in there.

<?php ?>
<form action="index.php?page=author" method="post">
    <div>
        First Name: <input type="text" name="first_name">
    </div>
    <div>
        Last Name: <input type="text" name="last_name">
    </div>
    <div>
        Email: <input type="text" name="email">
    </div>
    <div>
        <input type="submit" value="Create">
    </div>
</form>

We’re also not passing a hidden _method. This is just a pure post method. Let’s capture it and insert the data in our route file.

That’s it. We created all of our CRUD operations and added them to one file. We can:

view all authors
click on an author to view an individual author
update that author’s details
delete an author
and insert an author

There’s just a couple of things left to clean up and I’ll be pretty happy to send you along to Laravel.

https://github.com/dinocajic/php-youtube-tutorials?source=post_page—–1988f82072f5——————————–

ERASING DATA WITH SURGICAL PRECISION

PHP – P90: mysql delete

The last MySQL topic is delete. Just like with our update functionality, there is no delete method. We can submit a hidden method with delete as the value.

From Creation to Deletion: MySQL’s Complete Data Toolkit

PHP – P91: mysql crud

What are CRUD operations? Create, Read, Update, Delete. The last piece of the MySQL puzzle is to combine those operations into one file.

CONTROLLERS BRIDGE THE GAP BETWEEN USERS AND DATABASES

PHP – P92: controllers

Controller — The class that directs communication between the Model and the View. We’ll get data out of the model and send it to the View. Once the view mixes everything together, it will return

Chief Digital Officer & Author | Bridging IT, Marketing & Business

From Creation to Deletion: MySQL’s Complete Data Toolkit

Recap

The Route File

Author Class

View All

Show

Update an Author

Delete

Insert

Leave a Reply Cancel Reply

Dino Cajic

Chief Digital Officer & Author | Bridging IT, Marketing & Business