Here's a rough single-file implementation. It uses prepared statements to mitigate against injection attacks, it implements the ability to add/remove from the database, all db transactions are done via an ajax request to itself.
I've used functions to handle Ajax work, so you can easily fire-off several one after the other, getting notified when the request completes or a message if it fails (you could easily change this to call you if an error occured instead) Since the ajax object passed to the callbacks is the same object that was created when making the request, you've got access to the responseText, responseXML etc, etc members. In this example, I dont do anything with the result - though you can see a commented-out line that prints it.
Notes:
■ it will create a file test.db.sqlite in the same folder as the script.
■ you'd need make minimal changes to work with mysql. This code is adapted from some running on a free host, that doesn't include a SQlite3 PDO driver, and has a low limit on the number of MySql dbs (Basically, # of databases is limited only by diskspace, rather than their artificially low number, designed to tempt you into buyinga primo plan).
The changes that come to mind are (1) the way that you make the connection to the db in the first couple of lines, and (2) The code that gets the result set from a query. PDO allows the retrieval of rows as objects - I've returned them as arrays with named keys. Given the task that you're aiming to achieve, I guess #2 is largely irrelevant to you, but still worth mentioning.
Hope it helps! :)
oneFile.php (name is important, since it makes ajax requests to itself)
<?php
/*
ensure database and table exist
*/
$dbFilename = "test.db.sqlite3";
$sqlDb = new SQLite3($dbFilename);
$queryStr = "CREATE TABLE if not exists "
. "`sampleTable` "
. "(`id` INTEGER PRIMARY KEY AUTOINCREMENT, `comment` TEXT ); ";
$sqlDb->query($queryStr);
/*
do the db insert/delete and then exit (without producing any output)
*/
if ( isset($_POST['submit']) == true )
{
$rawData = $_POST['submit'];
$parsedData = json_decode( $rawData );
$newComment = $parsedData->comment;
$query = $sqlDb->prepare("insert into `sampleTable` (comment) values (:newComment)");
$query->bindParam(":newComment", $newComment);
$query->execute();
printf("comment successfully added");
exit;
}
else if (isset($_POST['delete']) == true)
{
$rawData = $_POST['delete'];
$parsedData = json_decode( $rawData );
$removeId = $parsedData->id;
$query = $sqlDb->prepare("delete from `sampleTable` where id = :idToRemove");
$query->bindParam(":idToRemove", $removeId);
$query->execute();
printf("comment successfully deleted");
exit;
}
/*
Otherwise, just display the html - the remaining code in _this_ php block
only contains utility functions.
*/
function getResultArray( $sqlResult )
{
$resultArray = array();
while ($curRow = $sqlResult->fetchArray(SQLITE3_ASSOC))
{
$j = $sqlResult->numColumns();
$resultRow = array();
for ($i=0; $i<$j; $i++)
{
$resultRow[ $sqlResult->columnName($i) ] = $curRow[$sqlResult->columnName($i)];
}
$resultArray[] = $resultRow;
}
return $resultArray;
}
function addCommentSelectorOrMessage($stringSelectorId)
{
global $sqlDb; // declared as global so we access the variable defined on line 6 of this file.
$queryStr = "select * from sampleTable order by id asc";
$sqlResult = $sqlDb->query($queryStr);
$resultArray = getResultArray( $sqlResult );
$nResults = count($resultArray);
if ($nResults != 0)
{
printf("<select id='%s'>\n", $stringSelectorId);
for ($i=0; $i<$nResults; $i++)
{
printf("<option value='%d'>%s</option>\n", $resultArray[$i]['id'], $resultArray[$i]['comment'] );
}
printf("</select>\n");
}
else
printf("No database entries yet..<br>\n");
}
?>
<!DOCTYPE html>
<html>
<head>
<script>
"use strict";
function byId(e){return document.getElementById(e);}
function allByClass(className){return document.getElementsByClassName(className);}
function newEl(tag){return document.createElement(tag);}
function newTxt(txt){return document.createTextNode(txt);}
window.addEventListener('load', onDocLoaded, false);
function onDocLoaded()
{
byId('addBtn').addEventListener('click', onAddBtn, false);
byId('delBtn').addEventListener('click', onDelBtn, false);
}
function onAddBtn()
{
var newComment = byId('commentInput').value;
var params = { comment: newComment };
myAjaxPost("oneFile.php", "submit", JSON.stringify(params), onCommentAddedOrDeleted);
}
function onDelBtn()
{
var commentId = byId('commentSelList').value;
var params = { id: commentId }; //new Object();
myAjaxPost("oneFile.php", "delete", JSON.stringify(params), onCommentAddedOrDeleted);
}
function onCommentAddedOrDeleted(ajax)
{
//alert(ajax.responseText);
if (ajax.responseText.length != 0)
alert(ajax.responseText);
window.location.reload();
}
function myAjaxGet(url, callback)
{
var ajax = new XMLHttpRequest();
ajax.onreadystatechange = function()
{
if (this.readyState==4 && this.status==200)
callback(this);
}
ajax.onerror = function()
{
console.log("AJAX request failed to: " + url);
}
ajax.open("GET", url, true);
ajax.send();
}
function myAjaxPost(url, phpPostVarName, data, callback)
{
var ajax = new XMLHttpRequest();
ajax.onreadystatechange = function()
{
if (this.readyState==4 && this.status==200)
callback(this);
}
ajax.onerror = function()
{
console.log("AJAX request failed to: " + url);
}
ajax.open("POST", url, true);
ajax.setRequestHeader("Content-type","application/x-www-form-urlencoded");
ajax.send(phpPostVarName+"=" + encodeURI(data) );
}
</script>
</head>
<body>
<h3>Add new comment</h3>
<input id='commentInput'/> <button id='addBtn'>Add comment to DB</button>
<br>
<h3>Saved comments</h3>
<?php addCommentSelectorOrMessage('commentSelList'); ?><button id='delBtn'>Delete</button>
<div id='ajaxResponseTgt'></div>
</body>
</html>
