How to Build Simple Web App Using CockroachDB and PHP Part 5

Introduction

This is the last of part of the five-part tutorial, How to Build Simple Web App Using CockroachDB and PHP. Part five will explain how to retrieve a single record and update and delete in CRUD operations with the PHP-pgsql driver. After completing this final part of the tutorial the user will be able to perform all of operations necessary to create web app using PHP and CockroachDB.

How to Edit and Update a Record in the CockroachDB Database with PHP-pgsql Driver

Part four of this series demonstrated how to create a record, or perform insert operation, in the CockroachDB database using PHP. This part will demonstate how to edit and update the operation of record(s) in the database using a submitted form data.

Here is a review of what the sql statement looks like for updating a record:

UPDATE TABLE  
SET col1 = 'val1' , col2 = 'val2'  
WHERE id = 1;

The above command is the SQL query that this part of the tutorial will use to send the pg_query to the database.

The “UPDATE” query will update everything in the database table if no specific condition is specified.

This situation can be avoided by using the primary ID in the “WHERE” clause to specify what particular record is to be updated.

Before an update can be performed, a form is typically used to execute the three following steps:

  1. Retrieve data from the database.

  2. Display the data in the form.

  3. Change or edit the details and then submit the form.

Notice, on the table shown below, the three buttons on the far right side named “View,” “Edit” and “Delete:”

The result set from CockroachDB displayed in table format via PHP

Click on the “Edit” button in a row to edit that specific record. The application will then go to the page where the details can be edited. Next, open the edit.php file and enter the below code:

NOTE: The code for this file will be broken down to better explain the important individual part(s) of the code.

function edit_record_cr($details){  

<?php  
// This command will load initialize.php file:  
require_once('../../../private/initialize.php');

The below piece of code will check the id. The page will redirected to index.php if it is not set, as shown here:

if(!isset($_GET['id'])){  
redirect_to(the_url('/staff/resto/index.php'));  
}

If a post request is made the system will gather the details from the submitted data and put them in a variable that will be used in updating the selected record(s), as shown here:

$id = $_GET['id'];  

if(is_post_request()){  

$details = [];  
$details['id'] = $id;  
$details['name'] = $_POST['resto_name'] ?? '';  
$details['phone'] = $_POST['phone'] ?? '';  
$details['email'] = $_POST['email'] ?? '';  
$details['stars'] = $_POST['stars'] ?? '';  
$details['category'] = $_POST['category'] ?? '';  

edit_record_cr($details);

The below code is for the above function edit_record_cr(). Creating this function inside the __query_functions.php__ allows the code to be reused whenever necessary.

    // PostgreSQL provides a function that will escape special character
    $escaped = pg_escape_string($name);
    $sqlUpdateRecord = "UPDATE tblrestaurants SET name = '$escaped',
    phone = '$phone', email = '$email',stars = $stars,category = '$category' WHERE id = '$id' "
;
    $result = pg_query($dbconn,$sqlUpdateRecord);
    // if the update is successful this code will redirect the page to
    //show.php
    if($sqlUpdateRecord){
        redirect_to(the_url('/staff/resto/show.php?id=' . $id));
    }

The following code will located the records from the ID and display the results in the form so the details can be changed or updated:

}else{  
    $result = find_by_id($id);

The below code is for the above function find_by_id(). Creating this function inside the __query_functions.php__ allows the code to be reused whenever necessary.

    global $dbconn;
    $sqlSelectById = "SELECT * FROM tblrestaurants WHERE id = $id";
    $result = pg_query($dbconn, $sqlSelectById);
   
}

?>

The remainder of the code makes up the form where the details are edited, as shown here:

<?php $page_title = 'Edit Subject'; ?>
<?php include(SHARED_PATH . '/staff_header.php'); ?>

<div id="content">
    <a class="back-link" href="<?php echo the_url('/staff/resto/index.php'); ?>"> « Back to the List</a>

    <div class="subject edit">

       
       
        <h2> Edit Restaurant's Details</h2>
        <?php

        while($row = pg_fetch_assoc ($result)){ ?>


        <form action="<?php echo the_url('
/staff/resto/edit.php?id=' . h(u($id))); ?>" method="post">
            <div class="form-group col-md-3" >
                <Label>Restaurant'
s Name</label>
                <input class="form-control"type="text" name="resto_name" value="<?php echo h($row['name']); ?>" />
            </div>
            <div class="form-group col-md-3">
                <label>Phone:</label>
                <input class="form-control"type="text" name="phone" value="<?php echo h($row['phone']); ?>" />
            </div>
            <div class="form-group col-md-3">
                <label  >Email Address</label>
                <input  class="form-control" type="text" name="email" value="<?php echo h($row['email']); ?>"/>
            </div>  
            <div class="form-group col-md-3">
                <label  >Stars:</label>
                <input  class="form-control" type="text" name="stars" value="<?php echo h($row['stars']); ?>"/>
            </div>  
            <div class="form-group col-md-3">
                <label  >Category:</label>
                <input  class="form-control" type="text" name="category" value="<?php echo h($row['category']); ?>"/>
            </div>  
         
         
            <input type="submit" value="Edit Details" class="btn btn-primary"/>
        </form>
      <?php } ?>
    </div>
</div>

<?php include(SHARED_PATH . '/staff_footer.php'); ?>

NOTE: Provide screen shot

How to Delete a Record in CockroachDB Database with the PHP-pgsql Driver

The previous section demonstated how to edit and update records and how to display or retrieve the records that were update. This section will explain how to delete a record using its ID.
Below is a review of what the “DELETE” statement looks like:

DELETE FROM TABLE  
WHERE id = 1;

As with the “UPDATE” operation, the “DELETE” operation will remove all of the records in a table if no condition is specified in the query. Remember to execute the “WHERE” clause to avoid deleting the entire record in the table.

The below image shows the three buttons, “View,” “Edit” and “Delete,” at the right side of the table:

The result set from CockroachDB displayed in table format via PHP

Click The “Delete” button to remove the desired row or the record.

Open the delete.php file then add the below code to see how the “DELETE” operation is performed.

NOTE: The codes for this file will be broken up to better explain the important components.

The following code tells PHP to gather the details from the submitted data and put the details in a variable used to delete the selected record(s) if it is a post request. This command will use the id, as follows:

<?php

require_once('../../../private/initialize.php');

if(!isset($_GET['id'])) {
  redirect_to(url_for('/staff/resto/index.php'));
}
$id = $_GET['id'];
$record = find_by_id($id);

if(is_post_request()) {
  delete_record_by_id_cr($id);
}

?>

The above delete_record_by_id_cr() function can be found in the __query_functions.php__.

To better explain the operation, the below code obtains the value of the $id and then passes it in the $sqlDelete SQL statement for the “DELETE” operation to delete a specific record. After the delete operation is performed the function will redirect to index.php page, as shown here:

function delete_record_by_id_cr($id){  
global $dbconn;  
$sqlDelete = "DELETE FROM tblrestaurants WHERE id = $id";  
$result = pg_query($dbconn,$sqlDelete);  
if($sqlDelete){  
redirect_to(the_url('/staff/resto/index.php'));  
}  
}

The following code serves as the confirmation page that shows the details of the record that is being deleted:

<?php $page_title = 'Delete Subject'; ?>
<?php include(SHARED_PATH . '/staff_header.php'); ?>

<div id="content">

  <a class="back-link" href="<?php echo the_url('/staff/resto/index.php'); ?>">« Back to List</a>

Notice the ph_fetch_assoc() function is used for easy access to the items in the result set that were retrieved with the find function $record = find_by_id($id);. The returned results are then passed into the variable $record as shown here:

<?php
    while($row =pg_fetch_assoc ($record)){ ?>
  <div class="record delete">
    <h1>Delete Restaurant</h1>
    <p>Are you sure you want to delete this restaurant?</p>
    <p class="item"><?php echo h($row['name']); ?></p>
    <form action="<?php echo the_url('/staff/resto/delete.php?id=' . h(u($row['id']))); ?>" method="post">
      <div id="operations">
        <input type="submit" name="commit" value="Delete Restaurant" />
      </div>
    </form>
  </div>
    <?php }?>    
</div>

<?php include(SHARED_PATH . '/staff_footer.php'); ?>

To sum up this action, the delete.php page is single page processing that also facilitates the “DELETE” operation and “RETRIEVE” record operation for confirmation purposes.

How to Show a Single Record in the CockroachDB Database with PHP-pgsql Driver

The previous section explained how to delete a single record. This section will demonstrate how to show a record using its ID.

This operation is normally used to audit or review the details of a particular record.

Following is an image of the “SELECT” statement that will be used in PHP code:

SELECT column1, column2 FROM TABLE  
WHERE id;

Now open show.php and add the below code:

NOTE: The codes for this file will be broken up to better explain the important components.

This code obtains the id and then passes it in the find_by_id() function with the results being stored in the variable $result, as shown here:

<?php

require_once('../../../private/initialize.php');

if(!isset($_GET['id'])){
    redirect_to(url_for('/staff/pages/index.php'));
}

$id = $_GET['id'];
$result=find_by_id($id);
?>

The code find_by_id() in the above function can be found in the __query_functions.php__. To better understand this part of the code, this function uses the “SELECT” operation and “WHERE” clause to limit the retrieval of records that only matches this condition, as shown here:

function find_by_id($id){  
global $dbconn;  
$sqlSelectById = "SELECT * FROM tblrestaurants WHERE id = $id";  
$result = pg_query($dbconn, $sqlSelectById);  
return $result;  
}

The following code will display the results returned by the find_by_id() function, again using the pg_feth_assoc() function to access the items in the returned results:

<?php $page_title = 'Show Restaurants'; ?>
<?php include(SHARED_PATH . '/staff_header.php'); ?>

<div id="content">
    <a class="back-link" href="<?php echo the_url('/staff/resto/index.php'); ?>"> « Back to the List</a>

    <div class="subject show">
        <?php while($row = pg_fetch_assoc ($result)){ ?>
        <h2>Restaurant's Name: <?php echo h($row['name']);?> </h2>
       
        <div class="attributes">
            <dl>
                <dt>Phone Number:</dt>
                <dd><?php echo h($row['phone']);?></dd>
            </dl>
            <dl>
                <dt>Email Address:</dt>
                <dd><?php echo h($row['email']);?></dd>
            </dl>

            <dl>
                <dt>Stars:</dt>
                <dd><?php echo h($row['stars']);?></dd>
            </dl>
           
            <dl>
                <dt>Category:</dt>
                <dd><?php echo h($row['category']);?></dd>
            </dl>
        </div>
        <?php } ?>
    </div>
</div>

<?php include(SHARED_PATH . '/staff_footer.php'); ?>

This next section of this tutorial will explain the basic way of displaying certain records for review or audit purposes via PHP.

The PHP Code

This section will show the PHP files with the corresponding code as stated in the above sections of this tutorial.

The initialize.php

The following code will set up the path and URL location and will load both accordingly when this file is called within a PHP file:

    define("PRIVATE_PATH", dirname(__FILE__));
    define("PROJECT_PATH", dirname(PRIVATE_PATH));
    define("PUBLIC_PATH", PROJECT_PATH . '/public');
    define("SHARED_PATH", PRIVATE_PATH . '/shared');
   
    $public_end = strpos($_SERVER['SCRIPT_NAME'], '/public') + 7;
    $doc_root = substr($_SERVER['SCRIPT_NAME'], 0, $public_end);
    define("WWW_ROOT", $doc_root);

    require_once('database.php');
    $dbconn = db_connect();

The database.php

The following code is for database connection purposes:

function db_connect(){
    $dbconn = pg_connect("host=localhost port=26257 dbname=restaurants user=yeshua")
or die('Could not connect: ' . pg_last_error());    
   

// this following code is to create an alert if we
// successfully connect to our database
// you can delete this once done with the testing
    if($dbconn){
    echo "<script>";
    echo "alert('Successfully connected to the database')";
    echo "</script>";

    }
// ======= END OF TEST CODE ====

    return $dbconn;
}      

function db_disconnect($dbconn){
    if(isset($dbconn)){
        pg_close($dbconn);
    }
}

The functions.php

This file contains all of the commands used to redirect pages, encode URL and escape HTML special characters.

function the_url($script_path){
    if($script_path[0] != '/'){
        $script_path = "/" . $script_path;
    }
    return WWW_ROOT . $script_path;
}


function u($string=""){
    return urlencode($string);
}

function h($string=""){
    return htmlspecialchars($string);
}

function redirect_to($location){
    header("Location: " . $location);
    exit;
}

The query_functions.php

The following file holds all the SQL statements turned into a function for reusability:

// RETRIEVE ALL RECORDS
function show_records_cr(){
    global $dbconn;
    $sqlSelect = "SELECT * FROM tblrestaurants ";
    $sqlSelect .= "ORDER BY id ASC";

    $result = pg_query($dbconn, $sqlSelect);
    return $result;
    pg_close($dbconn);
}

// COUNTING THE RECORDS
function count_record_cr(){
    global $dbconn;

    $sqlCount = "SELECT * FROM tblrestaurants";
    $result = pg_query($dbconn,$sqlCount);
    $rows = pg_num_rows($result);
    $countResult = $rows + 1;
    return $countResult;

}

// UPDATE/EDIT OPERATION
function edit_record_cr($details){

    global $dbconn;
    $id = $details['id'];
    $name = $details['name'];
    $phone = $details ['phone'];
    $email = $details ['email'];
    $stars = $details ['stars'];
    $category = $details ['category'];

    // this will escaped any special characters in the text.
    $escaped = pg_escape_string($name);

   
    $sqlUpdateRecord = "UPDATE tblrestaurants SET name = '$escaped',
    phone = '$phone', email = '$email',stars = $stars,category = '$category' WHERE id = '$id' "
;

    $result = pg_query($dbconn,$sqlUpdateRecord);
   
    if($sqlUpdateRecord){
        redirect_to(the_url('/staff/resto/show.php?id=' . $id));
    }

}

// RETRIEVE OPERATION VIA ID
function find_by_id($id){
    global $dbconn;
    $sqlSelectById = "SELECT * FROM tblrestaurants WHERE id = $id";
    $result = pg_query($dbconn, $sqlSelectById);
    return $result;
   
}

// DELETE OPERATION
function delete_record_by_id_cr($id){
        global $dbconn;
        $sqlDelete = "DELETE FROM tblrestaurants WHERE id = $id";
        $result = pg_query($dbconn,$sqlDelete);

        if($sqlDelete){
            redirect_to(the_url('/staff/resto/index.php'));
        }
    }

The index.php

The following code is the index page of the web app:

<?php require_once('../../../private/initialize.php'); ?>

<?php
$restaurants = show_records_cr();

?>

<?php $page_title = 'Restaurants'; ?>
<?php
    include(SHARED_PATH . '/staff_header.php');
?>

<br>
<div id="content">
    <div class="restaurant listing">
        <h1>Restaurant Listing</h1>
        <div class="actions">
            <a class="action" href="<?php echo the_url('/staff/resto/new.php'); ?>">Create New Restaurant</a>
        </div>
        <br>
        <div>
            <p>Type something in the input field to search the table for name, email, or category:</p>  
            <input class="form-control" id="myInput" type="text" placeholder="Search..">
        </div>
        <br>
        <table class="table table-sm table-bordered ">
            <thead class="thead-dark">
            <tr>
                <th>id</th>
                <th>name</th>
                <th>phone</th>
                <th>email</th>
                <th>stars</th>
                <th scope="col" colspan="3">category</th>
                <th> </th>
            </tr>
            </thead>
            <tbody id="myTable">
            <?php
                $i = 1;
            while ($row = pg_fetch_assoc ($restaurants)){ ?>

                <tr>
                    <td><?php echo $i; ?></td>
                    <!--
                    <td><?php echo h($row['name']);?></td>
                    <td><?php echo h($row['phone']);?></td>
                    <td><?php echo h($row['email']);?></td>
                    <td><?php echo h($row['stars']);?></td>
                    <td><?php echo h($row['category']);?></td>
                    <td><a class="action" href="<?php echo the_url('/staff/resto/show.php?id=' . h(u($row['id'])));?>">View</a></td>
                    <td><a class="action" href="<?php echo the_url('/staff/resto/edit.php?id='. h(u($row['id']))); ?>">Edit</a></td>
                    <td><a class="action" href="<?php echo the_url('/staff/resto/delete.php?id='. h(u($row['id'])));?>">Delete</a></td>
                </tr>
            <?php $i++;} ?>
            </tbody>
        </table>
            <?php
                pg_free_result($restaurants);
            ?>
    </div>
</div>

<?php include(SHARED_PATH . '/staff_footer.php'); ?>

The show.php

This file is responsible for showing the selected record by id.

<?php

require_once('../../../private/initialize.php');

if(!isset($_GET['id'])){
    redirect_to(url_for('/staff/pages/index.php'));
}

$id = $_GET['id'];

$result=find_by_id($id);


?>

<?php $page_title = 'Show Restaurants'; ?>
<?php include(SHARED_PATH . '/staff_header.php'); ?>

<div id="content">
    <a class="back-link" href="<?php echo the_url('/staff/resto/index.php'); ?>"> « Back to the List</a>

    <div class="subject show">
        <?php while($row = pg_fetch_row ($result)){ ?>
        <h2>Restaurant's Name: <?php echo h("$row[1]");?> </h2>
       
        <div class="attributes">
            <dl>
                <dt>Phone Number:</dt>
                <dd><?php echo h("$row[2]");?></dd>
            </dl>
            <dl>
                <dt>Email Address:</dt>
                <dd><?php echo h("$row[3]");?></dd>
            </dl>

            <dl>
                <dt>Stars:</dt>
                <dd><?php echo h("$row[4]");?></dd>
            </dl>
           
            <dl>
                <dt>Category:</dt>
                <dd><?php echo h("$row[5]");?></dd>
            </dl>
        </div>
        <?php } ?>
    </div>
</div>

<?php include(SHARED_PATH . '
/staff_footer.php'); ?>

The new.php

This page is used for creating/inserting new record.

<?php

require_once('../../../private/initialize.php');

$rec_count = count_record_cr();
$newId = $rec_count;

$name = '';
$phone = '';
$email = '';
$stars = '';
$category = '';

   
$id = $_POST['id'] ?? '';
if(is_post_request()){
    $record = [];
    $record['id'] = $newId;
    $record['name'] = $_POST['resto_name'] ?? '';
    $record['phone'] = $_POST['phone'] ?? '';
    $record['email'] = $_POST['email'] ?? '';
    $record['stars'] = $_POST['stars'] ?? '';
    $record['category'] = $_POST['category'] ?? '';
   
    echo $name;

    add_record_cr($record);
 
}else{
   
    $record = [];
    $record['id'] = $newId;
    $record['name'] = '';
    $record['phone'] = '';
    $record['email'] = '';
    $record['stars'] = '';
    $record['category'] = '';
}



?>

<?php $page_title = 'Create Record'; ?>
<?php include(SHARED_PATH . '/staff_header.php'); ?>

<div id="content">
    <a class="back-link" href="<?php echo the_url('/staff/resto/index.php'); ?>"> « Back to the List</a>

    <div class="subject new">
        <h2> Create Restaurant</h2>

        <form action="<?php echo the_url('/staff/resto/new.php'); ?>" method="post">
            <div class="form-group col-md-3" >
                <Label>Restaurant's ID :</label>
                <input type="text" class="form-control" name="resto_id" value="<?php echo h("$newId");?>" maxlength="4" size="4" readonly />
            </div>
            <div class="form-group col-md-3" >
                <Label>Restaurant'
s Name :</label>
                <input class="form-control" type="text" name="resto_name" value="<?php echo h("$name");?>" />
            </div>
            <div class="form-group col-md-3">
                <label>Phone :</label>
                <input class="form-control" type="text" name="phone" value="<?php echo h("$phone"); ?>" />
            </div>
            <div class="form-group col-md-3">
                <label  >Email Address :</label>
                <input  class="form-control" type="text" name="email" value="<?php echo h("$email"); ?>"/>
            </div>  
            <div class="form-group col-md-3">
                <label  >Stars :</label>
                <input  class="form-control" type="text" name="stars" value="<?php echo h("$stars"); ?>"/>
            </div>  
            <div class="form-group col-md-3">
                <label  >Category :</label>
                <input  class="form-control" type="text" name="category" value="<?php echo h("$category"); ?>"/>
            </div>  
         
         
            <input type="submit" value="Create Restaurants" class="btn btn-primary"/>
        </form>
    </div>
</div>

<?php include(SHARED_PATH . '/staff_footer.php'); ?>

The delete.php

This PHP file is where we delete records.

<?php

require_once('../../../private/initialize.php');

if(!isset($_GET['id'])) {
  redirect_to(url_for('/staff/resto/index.php'));
}
$id = $_GET['id'];

$record = find_by_id($id);

if(is_post_request()) {
  delete_record_by_id_cr($id);
}

?>

<?php $page_title = 'Delete Subject'; ?>
<?php include(SHARED_PATH . '/staff_header.php'); ?>

<div id="content">

  <a class="back-link" href="<?php echo the_url('/staff/resto/index.php'); ?>">« Back to List</a>
<?php
    while($row =pg_fetch_assoc ($record)){ ?>
  <div class="subject delete">
    <h1>Delete Restaurant</h1>
    <p>Are you sure you want to delete this restaurant?</p>
    <p class="item"><?php echo h($row['name']); ?></p>
    <form action="<?php echo the_url('/staff/resto/delete.php?id=' . h(u($row['id']))); ?>" method="post">
      <div id="operations">
        <input type="submit" name="commit" value="Delete Subject" />
      </div>
    </form>
  </div>
    <?php }?>    
</div>

<?php include(SHARED_PATH . '/staff_footer.php'); ?>

The staff_footer.php

A normal footer file.

<footer>
    © <?php echo date('Y'); ?> 500OR.io
</footer>

<script src="" async defer></script>

    </body>
</div>
   
</html>

<?php
    db_disconnect($dbconn);
?>

The staff_header.php

A normal header file.

<?php
    if(!isset($page_title)){ $page_title = 'Staff Area';}
?>


<!DOCTYPE html>

    <head>
        <meta charset="utf-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <title>Team Solo — <?php echo h($page_title); ?></title>
        <meta name="description" content="">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <!-- <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css"> -->
        <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
        <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/js/bootstrap.min.js"></script>
    </head>
    <div class="container-fluid">
    <body>
       
        <header>
            <h2>Admin Area</h2>
        </header>

        <nav class="nav nav-pills nav-justified">
           
            <a class="nav-item nav-link active" href="<?php echo the_url('/staff/index.php'); ?>">Menu</a>
           
        </nav>

Conclusion

This was the final part in the five-part tutorial “How to Build Simple Web App Using CockroachDB and PHP,” explaining the basic ways to create web app using PHP and CockroachDB. Part five built on the techniques demonstrated in the first four parts of this tutorial, showing the basic ways to perform CRUD operations to edit, update and delete a record in the CockroachDB database with the PHP-pgsql Driver.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed Redis,
MongoDB & Elasticsearch

Get Started

OR

Try CockroachDB
in Beta

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.