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

Introduction

This tutorial will explain how create web app using PHP and CockroachDB. Steps will demonstrate how to set up the PHP project folder structure, how to set up the cluster and how to connect PHP to the CockroachDB database. A basic knowledge of HTML, CSS and PHP scripting language is required.

Prerequisites

  • CockroachDB must be properly installed and configured before beginning. Consult the following link for instructions on how to install CockroachDB: How to Install CockroachDB on Mac OSX

  • A basic knowledge of HTML and CSS.

  • A basic knowledge of the PHP scripting language and a properly functioning PHP installation. Execute the following command in the terminal to determine the installed version of PHP: PHP --version

The results should resemble the following:

PHP 7.3.6-1+ubuntu18.04.1+deb.sury.org+1 (cli) (built: May 31 2019 11:06:48) ( NTS )  
Copyright (c) 1997-2018 The PHP Group  
Zend Engine v3.3.6, Copyright (c) 1998-2018 Zend Technologies  
with Zend OPcache v7.3.6-1+ubuntu18.04.1+deb.sury.org+1, Copyright (c) 1999-2018, by Zend Technologies

How to Setup the PHP Project Folder Structure

The PHP files used in this tutorial are as follows:

privateprivate/sharedpublic/resto
database.phpstaff_footer.phpindex.php
functions.phpstaff_header.phpnew.php
initialize.phpedit.php
query_functions.phpdelete.php

the project structure of the web app

How to Setup the CockroachDB

First, create a database and user access for the new web app via CockroachDB shell.

How to set up the cluster

Open a terminal and execute the following command to start a CockroachDB node. For this function, use a single node only in the local cluster, as follows:

cockroach start --insecure --listen-addr=localhost:26257 --http-addr=localhost:8081

The results should resemble the following:

* WARNING: RUNNING IN INSECURE MODE!  
* - Your cluster is open for any client that can access localhost.  
* - Any user, even root, can log in without providing a password.  
* - Any user, connecting as root, can read or write any data in your cluster.  
* - There is no network encryption nor authentication, and thus no confidentiality.  
* Check out how to secure your cluster: https://www.cockroachlabs.com/docs/v19.1/secure-a-cluster.html  
CockroachDB node starting at 2019-06-21 07:10:14.094720125 +0000 UTC (took 1.7s)  
build: CCL v19.1.1 @ 2019/05/15 20:27:00 (go1.11.6)  
webui: http://localhost:8081  
sql: postgresql://root@localhost:26257?sslmode=disable  
client flags: cockroach -client cmd="cmd"- --host=localhost:26257 --insecure  
logs: /home/500rocket-user/cockroach-data/logs  
temp dir: /home/500rocket-user/cockroach-data/cockroach-temp140499851  
external I/O path: /home/500rocket-user/cockroach-data/extern  
store[0]: path=/home/500rocket-user/cockroach-data  
status: restarted pre-existing node  
clusterID: f21dc71c-88dc-497b-84b9-2264a0d2e497  
nodeID: 1

NOTE: It is normal to encounter the above warning when creating a cluster via insecure mode (–insecure).

Now open up another terminal and connect the CockroachDB Built in SQL client with the following command:

cockroach sql --insecure --host=localhost:26257

The results should resemble the following:

# Welcome to the cockroach SQL interface.  
# All statements must be terminated by a semicolon.  
# To exit: CTRL + D.  
#  
# Server version: CockroachDB CCL v19.1.1 (x86_64-unknown-linux-gnu, built 2019/05/15 20:27:00, go1.11.6) (same version as client)  
# Cluster ID: f21dc71c-88dc-497b-84b9-2264a0d2e497  
#  
# Enter \? for a brief introduction.  
#  
root@localhost:26257/defaultdb-

How to setup the CockroachDB database and its user(s)

Now create the database that will be used in this tutorial with the following SQL command:

  • Creating the database restaurants.
-- This will create a database named restaurants  
CREATE DATABASE restaurants;

Now select the restaurants database by executing the following command:

SET DATABASE = restaurants;
  • Now create a table within the restaurants database named tblrestaurants.
CREATE TABLE tblrestaurants (id INT PRIMARY KEY,  
-- name VARCHAR,  
-- phone VARCHAR,  
-- email VARCHAR,  
-- stars INT,  
-- category VARCHAR);

Next, execute the following command to create the first user account that will be used to interact with the database:

CREATE USER IF NOT EXISTS yeshua;

Use the following command to grant the necessary permissions to prevent encountering any permission-related issues later in the process:

GRANT ALL ON DATABASE restaurants TO yeshua;

While this step is optional, a key and certificate can be created for the user yeshua as follows:

cockroach cert create-client yeshua --certs-dir=certs --ca-key=my-safe-directory/ca.key

How to setup The CockroachDB database table

Now insert some data in the database table using the following sql command format:

INSERT INTO TABLE_NAME column1,column2...  
VALUES  
Value1,Value2...

Now use the above format to insert data into the tblrestaurants.

INSERT INTO tblrestaurants (id,name,phone,email,stars,category) VALUES  
(1,'Pure Coffee','847-585-0172','purebeaty2@example.net',5,'coffee') ,  
(2,'yumster delicacy','225-256-0102','yumsterD@example.com',1,'Italian');

The result should resemble the following:

idnamephoneemailstarscategory
1Pure’s Coffee847-585-0172purebeauty2@example.net5Coffee
2yumster delicacy225-456-0102yumsterD@example.com1Italian
Refer to the article How To Perform an Insert in CockroachDB
to learn more about the INSERT command.

How to Connect PHP to the CockroachDB Database

Now that the database and its table has been set up, connect to the database by opening the database.php file and create a connection statement using the below code:

NOTE: This is just to confirm the database was successfully created and to ensure the PHP-pgsql driver can connect to the database. There is a separate article dedicated to database connection.

$dbconn = pg_connect("host=localhost port=26257 dbname=restaurants user=yeshua")  
or die('Could not connect: ' . pg_last_error());  
if($dbconn){  
echo "-script-";  
echo "alert('Successfully connected to the database')";  
echo "-/script-";  
}

Now test the basic web app by going to http://localhost/crdb_php/public/staff/resto/index.php. The message “Successfully connected to the database” should be visible, as shown here:
A popup window alert saying that the connection to the database is successful

Conclusion

Part one of this tutorial demonstrated how to create web app using PHP and CockroachDB. The tutorial explained how to set up and connect to CockroachDB, setup the PHP project folder structure, set up a local cluster and single node and the corresponding users, create a table and perform a simple insert sql command and then test the basic web app. Remember that it is normal to encounter a warning when creating a cluster insecure (–insecure) mode.

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.