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

Introduction

This lesson is Part 4 of a multi-part tutorial about how to create web app using PHP and CockroachDB. Today, we’ll go over how records are counted in a database, as well as explain the way to create a form so that you can add records to it. Let’s begin.

If you’re familiar with how to accomplish the steps in this lesson but want to review the sample code only, you can skip to Just the Code.

Prerequisites

  • Before continuing, if you haven’t already, please complete Parts 1 to 3 of the multi-part tutorial before proceeding here with Part 4.

Use the PHP-pgsql driver to create a record in a CockroachDB database

  • Create a new record and then insert it into a CockroachDB database. Here’s a recap of our SQL query and the correct format for creating a new record:
INSERT INTO TABLE (column1,column2)  
VALUES (val1, val2);
  • The pg_query shown above is what will be sent to our database.

  • Access the new.php file if you want to put the query in your web app, and then add the code below:

>NOTE: As we continue with this tutorial, for clarification, we’ll discuss the different sections of code and what they represent.

  • You’ll load every required dependency with this code.

  • The count_record_cr() function counts the number of database records so that it can assign new record ID numbers in numerical order.

$rec_count = count_record_cr();  
$newId = $rec_count;  
$name = '';  
$phone = '';  
$email = '';  
$stars = '';  
$category = '';
  • The is_post_request() saves values in the $record variable after it handles them.

  • Next, add_record_cr() receives the $record variable as a parameter to add the record to the database in CockroachDB.

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'] ?? '';  
add_record_cr($record);  
}else{  
$record = [];  
// automatically provide an ID for the new record  
$record['id'] = $newId;  
$record['name'] = '';  
$record['phone'] = '';  
$record['email'] = '';  
$record['stars'] = '';  
$record['category'] = '';  
}  
?>
  • The code above represents an example of “single page processing.” The form’s value submits it back to itself, then it on the same page, it handles the requested operation for the database.

  • Open a tab in your browser and paste in the link below to get a closer look at our sample app: http://localhost/crdb_php/public/staff/resto/index.php

>NOTE: If the link fails to load, don’t be alarmed. It’s nobody’s fault. Glitches may appear due to different OS environments, browser types, etc. These situations are mostly beyond your or our control. Please continue with the lesson to learn these important steps. The result set from CockroachDB displayed in table format via PHP

  • Next, click “Create New Restaurant” to begin to add a record.
    The new.php page, with details to be added to the CockroachDB database

>NOTE: The lower left corner has a “Create Restaurants” button. If you want to have values sent to the database and inserted as new records, click it.

  • The code below shows how to redirect a page. The $sqlAddResto function states if the request is successful, then redirect the page. The parameters indicate the page it should go to is the index.php page.
if($sqlAddResto){  
redirect_to(the_url('/staff/resto/index.php'));  
}
  • For more detailed code and information, see the end of this tutorial in “The PHP Code” section under functions.php . There, we’ve supplemented additional code to the function for add_record_cr(). It will allow a more complete way for you to handle a request for redirecting the page after a new document is inserted.

  • Now, back to the completion of inserted a record into a CockroachDB. This is an example of a successful result:
    The newly added record has an id of 6

>NOTE: The ID number of the record is “6” (six). There were 5 (five) existing records and they were counted, so the count_record_cr() function is at work and takes the next available number in succession.

Conclusion

As you have learned, creating a simple web app using CockroachDB and PHP, plus adding a new record to a CockroachDB database using the PHP-pgsql driver can be a smooth process. In part 4 of this tutorial, we went over the specifics of the count_record_cr(), is_post_request(), and add_record_cr() functions. We talked about their important roles in adding records and consecutively assigning ID numbers to records, handling values and saving them, and using a form to insert records into an existing database in CockroachDB. You learned how to construct code for a redirect page request after a record was added successfully too. Use the steps in this lesson to build a basic web app and add records quickly anytime you feel the need.

Just the Code

Here’s some sample code for this lesson on how to create web app using PHP and CockroachDB.

Initialize.php sample script

  • This script defines the URL locations for the various paths. It loads them when called inside the 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();

Database.php sample script

  • The sample script code below is used to make a connection to the database.
function db_connect(){  
$dbconn = pg_connect("host=localhost port=26257 dbname=restaurants user=yeshua")  
or die('Could not connect: ' . pg_last_error());  
}  
// ======= END OF TEST CODE ====  
return $dbconn;  
}  
function db_disconnect($dbconn){  
if(isset($dbconn)){  
pg_close($dbconn);  
}  
}

Functions.php sample script

  • Here’s a script for a file that contains the URL for encoding, Escape HTML special characters, and redirect functions.
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;  
}

Query_functions.php sample script

  • The sample shown here is for creating the file that accommodates reusable functions you used that were once SQL statements.
// 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;  
}

Pilot the ObjectRocket platform free for 30 Days

It's easy to get started. Imagine the time you'll save by not worrying about database management. Let's do this!

PILOT FREE FOR 30 DAYS

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.