Build simple To-Do list with Cloud and PHP


Purpose of this tutorial is to give a guide of Clusterpoint Cloud basics, to teach how to use primary functions (create, read, update, delete, aggregate...).

An example shows how to create simple To-Do list with PHP. Each item will have creation time, priority and text.

This is how it should look in the end:

Preconditions:
We have a Windows machine running PHP 5.4 on Apache (XAMPP). But any OS with webserver and PHP installed should be good to go.

Creating a simple To-Do view in PHP file


Let's start with creating a simple To-Do view in our index.php file.

HTML:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
 
    <title>Simple To-Do list</title>
    <style type="text/css">
        body {
            width: 600px;
            margin: 0 auto;
        }
 
        ul {
            list-style: none;
            padding: 0;
        }
 
        ul li {
            border: 1px solid gray;
            padding: 5px
        }
 
        input[type="number"] {
            width: 70px;
        }
    </style>
</head>
<body>
 
<h1>To-Do list:</h1>
 
<ul>
    <li><input type="number" name="priority[]" value="5">
        <input type="text" name="text[]" value="Lorem Ipsum">
        <button>update</button>
        <button>delete</button>
    </li>
</ul>
 
<ul>
    <li>
        <p>Add new item:</p>
        <input type="number" name="priority" value="" id="new_priority" placeholder="Priority"/>
        <input type="text" name="todo" value="" id="new_text" placeholder="Text"/>
        <button onclick="createItem()">Add</button>
    </li>
</ul>
 
</body>
</html>

Clusterpoint Cloud Database and Collection creation


We need to create Clusterpoint Cloud account (tutorial here: Cloud Account Setup) to get access to the Clusterpoint Console. Through Console we will be able to create database and collection where we will store our to-do items.

After signing in to the Clusterpoint Cloud, create a new database and collection. Let's give a name "todo" for both - database and collection.


Insert a new to-do item in to the cloud database


We will use AJAX requests in this example to communicate with backend. Let's add JavaScript at the bottom of index.php file to handle the creation of new items. And add this function to the "create" button.

JavaScript:

function createItem() {
    $.post('', {
          new_priority: $('#new_priority').val(),
          new_text    : $('#new_text').val()
      },
      function () {
          alert('Item created!');
          location.reload();
      });
}

 

Add the PHP code to actually insert the item in to the database.

PHP:

// INSERT: https://www.clusterpoint.com/docs/api/4/php/347/insert-single
if (isset($_POST['new_priority'])) {
	// insert new to-do item
	$doc = array(
		'priority' => $_POST['new_priority'],
		'text' => $_POST['new_text'],
		'timestamp' => time(),
	);

	$ch = curl_init();
	// https://api-eu.clusterpoint.com/v4/ACCOUNT_ID/DATABASE_NAME[ID]     -  Insert single document with explicit ID
	curl_setopt($ch, CURLOPT_URL, $url); //  In this case document ID is automatically generated by system.
	curl_setopt($ch, CURLOPT_USERPWD, $userPassword);
	curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
	curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
	curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 0);
	curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($doc));
	$response = curl_exec($ch);
	$errorMsg = curl_error($ch);
	if ($errorMsg) {
		var_dump($errorMsg);
	}
	curl_close($ch);

	die(); // AJAX request, just kill it.
}

 

You can now see in Clusterpoint Cloud Console newly added items! Go to Console Database view and click on database named "todo". Then click "Run query".

Select items from database


More info about JS/SQL query syntax.

PHP:

// More about QUERY: https://www.clusterpoint.com/docs/4.0/32/querying
$ch = curl_init();
$query = 'SELECT * FROM todo ORDER BY \'timestamp\' DESC';
// search
if (isset($_GET['search']) && $_GET['search'] !== '') {
    // We are storing item text in field named "text"
	$query = 'SELECT * FROM todo WHERE text.CONTAINS("*'.addslashes($_GET['search']).'*") ORDER BY \'timestamp\' DESC';
	// More info: https://www.clusterpoint.com/docs/?page=Matching_Documents_with_WHERE
}
curl_setopt($ch, CURLOPT_URL, $url . '/_query'); // We added _query here!
curl_setopt($ch, CURLOPT_USERPWD, $userPassword);
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 0);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, $query);
$response = json_decode(curl_exec($ch));
$errorMsg = curl_error($ch);
if ($errorMsg) {
	var_dump($errorMsg);
}
curl_close($ch);
$todos = array();
$inc = 0;
foreach ($response->results as $document) {
	$inc++;
	$btnUpdate = '<button onclick="updateItem(\'' . $document->_id . '\',' . $inc . ')">update</button> ';
	$btnDelete = '<button onclick="deleteItem(\'' . $document->_id . '\',' . $inc . ')">delete</button> ';

	$priorityInput = date('Y-m-d H:i:s', (string)$document->timestamp) . '
        <input type="number" name="priority[]" value="' . (string)$document->priority . '" id="priority_' . $inc . '"> ';

	$textInput = '<input type="text" name="text[]" value="' . (string)$document->text . '" id="text_' . $inc . '"> ';
	$todos[] = '<li>  ' . $priorityInput . $textInput . $btnUpdate . $btnDelete . '</li>';
}
?>

<ul>
    <? echo implode('', $todos) ?>
</ul>

 

Let's add some items until the list looks something like this:

Update item


JavaScript:

function updateItem(docId, fieldsId) {
	$.post('', {
			update_id: docId,
			text:      $('#text_' + fieldsId).val(),
			priority:  $('#priority_' + fieldsId).val()
		},
		function () {
			alert('Item updated!');
			location.reload();
		});
}

 

PHP:

// More about UPDATE: https://www.clusterpoint.com/docs/api/4/php/355/updatereplace
if (isset($_POST['update_id'])) {

	$query = 'UPDATE todo["' . $_POST['update_id'] . '"] SET text = "' . $_POST['text'] . '", priority = "' . $_POST['priority'] . '"';

	$ch = curl_init();
	curl_setopt($ch, CURLOPT_URL, $url . '_query');
	curl_setopt($ch, CURLOPT_USERPWD, $userPassword);
	curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST"); // or use PATCH request  https://www.clusterpoint.com/docs/?page=Update
	curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
	curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 0);
	curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
	curl_setopt($ch, CURLOPT_POSTFIELDS, $query);
	$response = curl_exec($ch);
	$errorMsg = curl_error($ch);
	if ($errorMsg) {
		var_dump($errorMsg);
	}
	curl_close($ch);

	die(); // AJAX request, just kill it.
}

 

Delete item


JavaScript:

function deleteItem(id) {
    $.post('', { delete_id: id },
      function () {
          alert('Item deleted!');
          location.reload();
      });
}

 

PHP:

if (isset($_POST['delete_id'])) {
	$ch = curl_init();
	curl_setopt($ch, CURLOPT_URL, $url . '['.$_POST['delete_id'].']');
	curl_setopt($ch, CURLOPT_USERPWD, $userPassword);
	curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "DELETE");
	curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
	curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 0);
	curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
	$response = curl_exec($ch);
	$errorMsg = curl_error($ch);
	if ($errorMsg) {
		var_dump($errorMsg);
	}
	curl_close($ch);
	die(); // AJAX request, just kill it.
}

 

Search in items


More info about JS/SQL query syntax.

Simple HTML form for search:

<form action="" method="get">
    Search: <input type="text" value="<? echo @$_GET['search'] ?>" name="search"/>
    <button type="submit">Search</button>
</form>

 

PHP:

// search
if (isset($_GET['search']) && $_GET['search'] !== '') {
    // We are storing item text in field named "text"
	$query = 'SELECT * FROM todo WHERE text.CONTAINS("*'.addslashes($_GET['search']).'*") ORDER BY \'timestamp\' DESC';
	// More info: https://www.clusterpoint.com/docs/?page=Matching_Documents_with_WHERE
}

 

Aggregate - count how many items we have per day


More info about aggregation with GROUP BY.

Advanced API provides more functionality and features over Simple API.

PHP:

$ch = curl_init();
$query = 'SELECT priority, COUNT(priority) AS count FROM todo GROUP BY priority ORDER BY priority DESC';
curl_setopt($ch, CURLOPT_URL, $url . '/_query'); // We added _query here!
curl_setopt($ch, CURLOPT_USERPWD, $userPassword);
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 0);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, $query);
$response = json_decode(curl_exec($ch));
$errorMsg = curl_error($ch);
if ($errorMsg) {
	var_dump($errorMsg);
}
curl_close($ch);

$stats = array();
foreach ($response->results as $document) {
	$stats[] = '<li>' . $document->count . ' tasks with priority: ' . $document->priority . ' </li>';
}

 

HTML:

<ul>
    <? echo implode('', $stats) ?>
</ul>

 

Final source code looks like this:

<?php
// config
$accountId = 0;
$database = 'todo';
// Note: Replace 'api-eu' with 'api-us', if you are connecting to US cloud!
$url = 'https://api-eu.clusterpoint.com/v4/' . $accountId . '/' . $database;
$userPassword = 'LOGIN:PASSWORD';


if (isset($_POST['new_priority'])) {
	// insert new to-do item
	$doc = array(
		'priority' => $_POST['new_priority'],
		'text' => $_POST['new_text'],
		'timestamp' => time(),
	);

	$ch = curl_init();
	// https://api-eu.clusterpoint.com/v4/ACCOUNT_ID/DATABASE_NAME[ID]     -  Insert single document with explicit ID
	curl_setopt($ch, CURLOPT_URL, $url); //  In this case document ID is automatically generated by system.
	curl_setopt($ch, CURLOPT_USERPWD, $userPassword);
	curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
	curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
	curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 0);
	curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
	curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($doc));
	$response = curl_exec($ch);
	$errorMsg = curl_error($ch);
	if ($errorMsg) {
		var_dump($errorMsg);
	}
	curl_close($ch);

	die(); // AJAX request, just kill it.
}


if (isset($_POST['update_id'])) {

	$query = 'UPDATE todo["' . addslashes($_POST['update_id']) . '"] SET text = "' . addslashes($_POST['text']) . '", priority = "' . addslashes($_POST['priority']) . '"';

	$ch = curl_init();
	curl_setopt($ch, CURLOPT_URL, $url . '/_query');
	curl_setopt($ch, CURLOPT_USERPWD, $userPassword);
	curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST"); // or use PATCH request  https://www.clusterpoint.com/docs/?page=Update
	curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
	curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 0);
	curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
	curl_setopt($ch, CURLOPT_POSTFIELDS, $query);
	$response = curl_exec($ch);
	$errorMsg = curl_error($ch);
	if ($errorMsg) {
		var_dump($errorMsg);
	}
	curl_close($ch);

	die(); // AJAX request, just kill it.
}


if (isset($_POST['delete_id'])) {
	$ch = curl_init();
	curl_setopt($ch, CURLOPT_URL, $url . '['.$_POST['delete_id'].']');
	curl_setopt($ch, CURLOPT_USERPWD, $userPassword);
	curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "DELETE");
	curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
	curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 0);
	curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
	$response = curl_exec($ch);
	$errorMsg = curl_error($ch);
	if ($errorMsg) {
		var_dump($errorMsg);
	}
	curl_close($ch);
	die(); // AJAX request, just kill it.
}


$ch = curl_init();
$query = 'SELECT * FROM todo ORDER BY \'timestamp\' DESC';
// search
if (isset($_GET['search']) && $_GET['search'] !== '') {
    // We are storing item text in field named "text"
	$query = 'SELECT * FROM todo WHERE text.CONTAINS("*'.addslashes($_GET['search']).'*") ORDER BY \'timestamp\' DESC';
	// More info: https://www.clusterpoint.com/docs/?page=Matching_Documents_with_WHERE
}
curl_setopt($ch, CURLOPT_URL, $url . '/_query'); // We added _query here!
curl_setopt($ch, CURLOPT_USERPWD, $userPassword);
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 0);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, $query);
$response = json_decode(curl_exec($ch));
$errorMsg = curl_error($ch);
if ($errorMsg) {
	var_dump($errorMsg);
}
curl_close($ch);
$todos = array();
$inc = 0;
foreach ($response->results as $document) {
	$inc++;
	$btnUpdate = '<button onclick="updateItem(\'' . $document->_id . '\',' . $inc . ')">update</button> ';
	$btnDelete = '<button onclick="deleteItem(\'' . $document->_id . '\',' . $inc . ')">delete</button> ';

	$priorityInput = date('Y-m-d H:i:s', (string)$document->timestamp) . '
        <input type="number" name="priority[]" value="' . (string)$document->priority . '" id="priority_' . $inc . '"> ';

	$textInput = '<input type="text" name="text[]" value="' . (string)$document->text . '" id="text_' . $inc . '"> ';
	$todos[] = '<li>  ' . $priorityInput . $textInput . $btnUpdate . $btnDelete . '</li>';
}


$ch = curl_init();
$query = 'SELECT priority, COUNT(priority) AS count FROM todo GROUP BY priority ORDER BY priority DESC';
curl_setopt($ch, CURLOPT_URL, $url . '/_query'); // We added _query here!
curl_setopt($ch, CURLOPT_USERPWD, $userPassword);
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 0);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, $query);
$response = json_decode(curl_exec($ch));
$errorMsg = curl_error($ch);
if ($errorMsg) {
	var_dump($errorMsg);
}
curl_close($ch);

$stats = array();
foreach ($response->results as $document) {
	$stats[] = '<li>' . $document->count . ' tasks with priority: ' . $document->priority . ' </li>';
}

?>
	<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
	<html>
	<head>
		<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>

		<title>Simple To-Do list</title>
		<style type="text/css">
			body {
				width: 600px;
				margin: 0 auto;
			}

			ul {
				list-style: none;
				padding: 0;
			}

			ul li {
				border: 1px solid gray;
				padding: 5px
			}

			input[type="number"] {
				width: 70px;
			}
		</style>
	</head>
	<body>


	<h1>To-Do list:</h1>
	<form action="" method="get">
	    Search: <input type="text" value="<? echo @$_GET['search'] ?>" name="search"/>
	    <button type="submit">Search</button>
	</form>
	<ul>
		<?php echo implode('', $todos) ?>
	</ul>
	<ul>
	    <? echo implode('', $stats) ?>
	</ul>
	<ul>
		<li>
			<p>Add new item:</p>
			<input type="number" name="priority" value="" id="new_priority" placeholder="Priority"/>
			<input type="text" name="todo" value="" id="new_text" placeholder="Text"/>
			<button onclick="createItem()">Add</button>
		</li>
	</ul>

	</body>
	</html>
	<script type="text/javascript">
		function createItem() {
			$.post('', {
					new_priority: $('#new_priority').val(),
					new_text:     $('#new_text').val()
				},
				function () {
					alert('Item created!');
					location.reload();
				});
		}

		function updateItem(docId, fieldsId) {
			$.post('', {
					update_id: docId,
					text:      $('#text_' + fieldsId).val(),
					priority:  $('#priority_' + fieldsId).val()
				},
				function () {
					alert('Item updated!');
					location.reload();
				});
		}

		function deleteItem(id) {
		    $.post('', { delete_id: id },
		      function () {
		          alert('Item deleted!');
		          location.reload();
		      });
		}
	</script>