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

Introduction

This is part three of the tutorial “How to Build Simple Web App Using CockroachDB and PHP. This series demonstrates how to create web app using PHP and CockroachDB. Part two of this series showed how to set up the working environment for the project. This part will demonstrate how to interact with the CockroachDB database while using the functions created in part two and show how to add additional operations. Be sure to become familiar with parts one and two of this tutorial before preceding.

How to Connect to CockroachDB Database with the PHP-pgsql Driver

This tutorial will demonstrate different ways of interacting with the database. The following steps may vary slightly between users, but these are the ones used in this tutorial:

  1. Create a database connection.
  2. Perform a query to the database.
  3. Use and process the returned data from the query results.
  4. Releasing the data.
  5. Closing the database connection.

NOTE: Steps One and Five, above, should only be used once for each PHP script, whereas Steps Two and Four may require repeating several times throught a PHP script, as needed.

How to open a connection to CockroachDB

The following format of the pg_connect() function will open a connection in CockroachDB via PHP-pgsql driver:

pg_connect ( string $connection_string )

The connection string can contain one or more parameter settings separated by whitespace. The following are the parameter keyword currently recognized: host, hostaddr, port, dbname (database name), user. password, connect_timeout, options, sslmode and service. The pg_connect() returns a connection resource that allows the user to assign and track a variable. The user can then utilize the variable to make queries across the open connection.

Next, open the database.php file and add the below code that will show how to connect the PHP-pgsql driver to CockroachDB.

Example One

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

Example Two

// connect to a database named "restaurants" on the localhost with user yeshua
$dbconn = pg_connect("host=localhost port=26257 dbname=restaurants user=yeshua") or die('Could not connect: ' . pg_last_error());

Using the second example, expand on this by putting the previous code into a function. It is wise to close the connection before proceeding to prevent any unforeseen issues. Now execute the following command:

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;
}
// the function that will close the connection.
function db_disconnect($dbconn){
// this will check if the connection is open or not,
// there is no sense of closing a connection that is not open.
if(isset($dbconn)){
pg_close($dbconn);
}
}

To ensure a PHP file loads the initialize.php whenever the database connection is established, create a database variable that will be readily available by opening initialize.php and entering the following code:

require_once('database.php');
// $dbconn is the one we will be using to connect to our database
$dbconn = db_connect();

To close the database connection, open up the __staff_footer.php__ and insert the following code to close the database connection:

db_disconnect($dbconn);

This informs PHP that the rendering is completed and to now close the connection, even if no action was actually taken, just be certian the database connection was closed properly.

Now test the database connection by going to http://localhost/crdb_php/public/staff/resto/index.php. A “Successfully connected to the database” message should appear, as shown here:

A popup window alert saying that the connection to the database is successful

How to perform a query to the database

Now that the database is set up, this section will demonstrate how to interact with the database using PHP.

Query the database using the pg_query() function. This function is composed of the following two parameters:

  1. $connection — which is the connection resource.

  2. $query — which will be the SQL statement.

To execute this function, open up the __query_functions.php__ and add the following code:

// This command creates a database connection:
global $dbconn;
// Performs a database query:
$sqlSelect = "SELECT * FROM tblrestaurants ";
$sqlSelect .= "ORDER BY id ASC";
$result = pg_query($dbconn, $sqlSelect);
// Places the returned resource in the $result variable for later use:
return $result;
// This commnd will free up the result set to free up memory:
pg_free_result($dbconn);
// Closes the database connection:
pg_close($dbconn);

The above code will produce results that can be processed.

How to process of the returned result

This section of the tutorial will demonstrate how to work with the result set obtained from the previous section. There are multiple ways to work with the result set, including:

  1. pg_fetch_row() — This returns the result as an enumerated array format.

['1','Pure Coffee','847-585-0172','purebeauty@example.net', '5', 'Coffee']

  • Integers will be used to access the result. For example, to access the Pure Coffee the command would be echo $result[1];
  1. pg_fetch_assoc() — This returns the result in an associative array.

[ 'id' =- '1', 'name' =- 'Pure Coffee', 'phone' =- '847-585-0172','email' =- 'purebeauty@example.net', 'stars' =- '5', 'category' =- 'Coffee']

  • This gives a much clearer and easier-to-understand presentation of the results.

  • Accessing the results will be much easier using column names, such as: echo $result['name'];.

  1. pg_fetch_array() — This command returns one or both types of the array.

Given the above details, option two is the better choice.

To use the pg_fetch_assoc() method to process the result set, open up the index.php and add the following code:

-?php require_once('../../../private/initialize.php'); ?-
-?php
// Add the result set in the variable $restaurants as follows:
$restaurants = show_records_cr();
?-
-?php $page_title = 'Restaurants'; ?-
-?php
// The following command will include the footer named staff_footer.php
include(SHARED_PATH . '/staff_header.php');
// Replaces the "../../priate" with just "SHARED_PATH", as discussed in Part Two of this tutorial.
// It simply says to find the "staff_header.php" look for the "SHARED_PATH" file path.
// Include('../../private/shared/staff_header.php');
?-
Restaurant Listing
"-Create New Restaurant
id
name
phone
email
stars
category
 
-?php echo $i; ?-
-?php echo h($row['name']);?-
-?php echo h($row['phone']);?-
-?php echo h($row['email']);?-
-?php echo h($row['stars']);?-
-?php echo h($row['category']);?-
"
-View
"-Edit
"
-Delete
-?php
pg_free_result($restaurants);
?-
// This will include the footer named staff_footer.php:
-?php include(SHARED_PATH . '/staff_footer.php'); ?-

Test the database connection by going to http://localhost/crdb_php/public/staff/resto/index.php.

The result set from CockroachDB displayed in table format via PHP

The PHP Code

The following section will show the PHP files with their corresponding code as explained 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 the 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 use in 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());
// The following code is to create an alert if a
// connection to the database is successful.
// This can be delete once the testing is completed.
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

The following file holds all the functions used to redirect pages, encode URLs and escapes 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);
}

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);
}

The index.php

The following code is just 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');
?-
Restaurant Listing
"-Create New Restaurant
Use the following code to enter some type of data in the input field to search the table for a name, email, or category:
-input class="
form-control"="form-control"" id=" type=" text"="text"" placeholder=" myinput"="myInput"" search.."="Search..""-
id
name
phone
email
stars
category
 
-?php echo $i; ?-
-body-
-header-
Admin Area
-/header-
-nav class="
nav="nav" nav-pills="nav-pills" nav-justified"="nav-justified""-
"-Menu
-/nav-

Conclusion

This was Part Three in a series demonstrating how to create web app using PHP and CockroachDB. Building on parts one and two, this tutorial demonstrated how to connect PHP to the CockroachDB database, different ways of interacting with the database, how to create a database variable and process the result set by displaying the results in a table format. Remember to always close the database connection after creating a variable.

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.