I have two files I'm working with here. query.php is a script I wrote to handle all MySQL queries for this particular website. query_test.js is a series of AJAX calls to test query.php. query_test.js, as you will see below, uses Promises to sequence one test after another, as I have made the tests dependent upon one another. By writing to the console, the AJAX calls appear to always be finishing up in the correct order. The problem is the output is inconsistent and the output of individual tests clearly are not always reflecting the results of the tests before them. Since individual tests seem to work perfectly fine, I strongly believe that something is a sequencing issue. I just can't figure out why and how this is happening.
For clarity, the tests are as follows:
- Test 1: Retrieve entire table and display
- Test 2: Add new row, then retrieve and display the table again
- Test 3: Select the just-added row, display results
- Test 4: Update the just-added row, retrieve and display table
- Test 5: Remove the just-added row, retrieve and display table
In the example output below, notice that the table retrieved after Test 2 does not reflect that a row was added and Test 3's query returns nothing. It isn't until Test 4 that the previously-added row seems to show up. Each execution yields a different output; sometimes completely normal, and other times really wrong like the above. The console output always shows that each call is in the order it's supposed to be. That makes me think something in the backend is going wrong, but I'm clueless as to what that is.
query.php
<?php
/**
The purpose of this script is to query the database in a number of ways.
This script should be called via AJAX. See below for supported actions and
required parameters for each.
-------- ACTIONS ($_POST["action"])-----------
1. GET_TABLE: retrieve an entire table
- Parameters:
"table_name": [String] name of the table
- Returns: [JSON] the entire table
2. UPDATE_TABLE: update a row in a table
- Parameters:
"table_name": [String] name of the table
"queries": [array] a list of queries, like so: <column>[<relational_operator]<value> to find rows to update
"values": [array] key=>value pairs for each column to be updated.
3. SELECT_TABLE: select specified columns from specified rows
- Parameters:
"table_name": [String] name of the table
"queries": [array] a list of queries, like so: <column>[<relational_operator]<value>
"columns": (optional) [array] a list of column names to be returned. default value is '*', or all columns
- Returns: [JSON] the rows returned from the query
4. ADD_ROW: add a row to a table
- Parameters:
"table_name": [String] name of the table
"values": [array] key=>value pairs of columns names and corresponding values for the new row
5. REMOVE_ROW: remove a row or rows from a table
- Parameters:
"table_name": [String] name of the table
"queries": [array] a list of queries, like so: <column>[<relational_operator]<value>
**/
// Constants
// Actions
define("GET_TABLE", 100);
define("UPDATE_TABLE", 101);
define("SELECT_TABLE", 102);
define("ADD_ROW", 103);
define("REMOVE_ROW", 104);
$server = "localhost";
$username = "root";
$password = "password";
$db_name = "test";
$conn = new mysqli($server, $username, $password, $db_name);
if ($conn->connect_error) die("Connection failed: " . $conn->connect_error);
// Action must be set
checkPOST("action") or die("Error: POST variable 'action' must be setand not empty.");
$action = $_POST["action"];
// Table name must be given for all actions
checkPOST("table_name") or die("Error: POST variable 'table_name' must be set and not empty.");
$table = $_POST["table_name"];
// Generic error message
$param_err = "Error: ensure all required params are set and not empty.";
// See which action needs to be done
switch ($action) {
case GET_TABLE:
// Simply run the query
$q = "SELECT * FROM $table";
$result = $conn->query($q);
$result or die("Query '" . $q . "' failed: " . $conn->error);
if ($result->num_rows > 0) {
$output = array();
while ($row = $result->fetch_assoc()) {
$output[] = $row;
}
echo json_encode($output);
}
break;
case UPDATE_TABLE:
// Check for additional required params
(checkPOST("values") && checkPOST("queries"))
or die($param_err);
$values = $_POST["values"];
$queries = $_POST["queries"];
$q = "UPDATE $table";
// Add the values to be set to the query
$q .= " SET ";
addQuotesToStrings($values);
addItemsToQuery($q, $values, true);
// Add the WHERE clause at the end of the query
$q .= " WHERE ";
addItemsToQuery($q, $queries, false);
// Now ready to send off the query to the db and report success or failure
$conn->query($q) or die("Query '" . $q . "' failed: " . $conn->error);
echo "Successfully updated " . $conn->affected_rows . " rows.";
break;
case SELECT_TABLE:
// Check for additional required params
checkPOST("queries") or die($param_err);
$queries = $_POST["queries"];
$q = "SELECT ";
// Add columns if specified
if (checkPOST("columns")) {
$columns = $_POST["columns"];
addItemsToQuery($q, $columns, false);
}
else $q .= "* "; // No columns specified. Select all
// Add table name
$q .= "FROM $table ";
// Add queries
$q .= "WHERE ";
addItemsToQuery($q, $queries, false);
// Now, send off query
$result = $conn->query($q);
$result or die("Query '" . $q . "' failed: " . $conn->error);
if ($result->num_rows > 0) {
$output = array();
while ($row = $result->fetch_assoc()) {
$output[] = $row;
}
echo json_encode($output);
}
break;
case ADD_ROW:
// Check for POST var "values"
checkPOST("values") or die($param_err);
$values = $_POST["values"];
$q = "INSERT INTO $table";
// First, add column names
$q .= " (";
addItemsToQuery($q, array_keys($values), false);
$q .= ") ";
// Add the values
$q .= "VALUES (";
addQuotesToStrings($values);
addItemsToQuery($q, $values, false);
$q .= ")";
// Run the query
$conn->query($q) or die("Query '" . $q . "' failed: " . $conn->error);
echo "Query was successful.";
break;
case REMOVE_ROW:
// Check for queries
checkPOST("queries") or die($param_err);
$queries = $_POST["queries"];
$q = "DELETE FROM $table";
// Add queries
$q .= " WHERE ";
addItemsToQuery($q, $queries, false);
// Run query
$conn->query($q) or die("Query '" . $q . "' failed: " . $conn->error);
echo "Query affected " . $conn->affected_rows . " rows.";
break;
default:
die("Error: POST variable 'action' has an unknown value.");
}
/**
Adds items from an array to an SQL query string
Assumes a space is present before the last keyword of the existing query.
@param string &$q A reference to an SQL query string
@param array $items An array containing strings that need to be added to a query in a list format (e.g. item1,item2,item3)
$param boolean $pairs A boolean that indicated whether the items are key=>value pairs or not
**/
function addItemsToQuery(&$q, $items, $pairs) {
$first = true;
foreach ($items as $name => $item) {
if (!$first) $q .= ", ";
else $first = false;
$q .= $pairs ? $name . "=" . $item : $item;
}
}
/**
Adds single quotes to each string in a array of items for the purpose of being added to a MySQL query
@param array $values A reference to an array of items
**/
function addQuotesToStrings(&$values) {
foreach ($values as &$value) {
if (strcmp(gettype($value), "string") == 0) $value = "'" . $value . "'";
}
unset($value);
}
/**
Simple helper function to check if a POST var is set and not empty
@param string $name The name of the POST variable
**/
function checkPOST($name) {
return isset($_POST[$name]) && !empty($_POST[$name]);
}
?>
query_test.js
// This script is a series of AJAX calls to test query.php
const GET_TABLE = 100;
const UPDATE_TABLE = 101;
const SELECT_TABLE = 102;
const ADD_ROW = 103;
const REMOVE_ROW = 104;
/**
Runs an AJAX request to query.php and displays the result
@param {jQuery} div A jQuery object div to place results
@param {array} params An assoc. array of POST variables for the AJAX call
@param {boolean} displayAsTable Determines where the result will be displayed as a table or as it is
@return {Promise} A Promise object
**/
function runTest(div, params, displayAsTable) {
return Promise.resolve($.post("../php/query.php", params, function(data) {
// Display result
if (displayAsTable) {
let tableData = JSON.parse(data);
let table = $('<table></table>');
for (let rowKey in tableData) {
let row = $('<tr></tr>');
for (let colKey in tableData[rowKey]) {
let col = $('<td></td>');
col.html(tableData[rowKey][colKey]);
row.append(col);
}
table.append(row);
}
div.append(table);
}
else div.append($('<p>' + data + '</p>'));
}));
}
var num = 0;
// Temporary function to test if Promise order holds up
function logOrder(test) {
num++;
console.log(test + " finished: " + num);
}
// ----------- Test 1: Retrieve table ----------------
let div = $('<div></div><br>');
let header = $('<h2>Test 1: Retrieve table</h2>');
div.append(header);
$('body').append(div);
let params = {action: GET_TABLE, table_name: "projects"};
var promise = runTest(div, params, true);
// ------------ Test 2: Add Row ----------------------
promise = promise.then(function(value) {
logOrder("Test 1");
let div = $('<div></div><br>');
let header = $('<h2>Test 2: Add Row</h2>');
div.append(header);
$('body').append(div);
let values = {title: "test_proj", pic: "none.jpg", brief: "testing", description: "this is a test"};
let params = {action: ADD_ROW, values: values, table_name: "projects"};
runTest(div, params, false);
}, function(error) {
alert(error);
});
// Get table to ensure row has been added
promise = promise.then(function(value) {
logOrder("Test 2");
let div = $('<div></div><br>');
$('body').append(div);
let params = {action: GET_TABLE, table_name: "projects"};
runTest(div, params, true);
}, function(error) {
alert(error);
});
// -------------- Test 3: Select table -------------------
promise = promise.then(function(value) {
logOrder("Test 2 Check");
let div = $('<div></div><br>');
let header = $('<h2>Test 3: Select Table</h2>');
div.append(header);
$('body').append(div);
let queries = ["title='test_proj'"];
let params = {action: SELECT_TABLE, table_name: "projects", queries: queries};
runTest(div, params, true);
}, function(error) {
alert(error);
});
// -------------- Test 4: Update table -------------------
promise = promise.then(function(value) {
logOrder("Test 3");
let div = $('<div></div><br>');
let header = $('<h2>Test 4: Update Table</h2>');
div.append(header);
$('body').append(div);
let queries = ["title='test_proj'"];
let values = {brief: "This was updated", description: "This was also updated"};
let params = {action: UPDATE_TABLE, table_name: "projects", queries: queries, values: values};
runTest(div, params, false);
}, function(error) {
alert(error);
});
// Get table to ensure table has been updated
promise = promise.then(function(value) {
logOrder("Test 4");
let div = $('<div></div><br>');
$('body').append(div);
let params = {action: GET_TABLE, table_name: "projects"};
runTest(div, params, true);
}, function(error) {
alert(error);
});
// -------------- Test 5: Remove Row(s) ------------------
promise = promise.then(function(value) {
logOrder("Test 4 Check");
let div = $('<div></div><br>');
let header = $('<h2>Test 5: Remove Row(s)</h2>');
div.append(header);
$('body').append(div);
let queries = ["title='test_proj'"];
let params = {action: REMOVE_ROW, table_name: "projects", queries: queries};
runTest(div, params, false);
}, function(error) {
alert(error);
});
// Get table to ensure row has been removed
promise = promise.then(function(value) {
logOrder("Test 5");
let div = $('<div></div>');
$('body').append(div);
let params = {action: GET_TABLE, table_name: "projects"};
runTest(div, params, true);
}, function(error) {
alert(error);
}).then(function(value) {
logOrder("Test 5 Check");
}, function(error) {
alert(error);
});
Example output:
Test 1: Retrieve table
11 placeholder none.jpg placeholder placeholder description
Test 2: Add Row
Query was successful.
11 placeholder none.jpg placeholder placeholder description
Test 3: Select Table
Test 4: Update Table
Successfully updated 1 rows.
11 placeholder none.jpg placeholder placeholder description
17 test_proj none.jpg This was updated This was also updated
Test 5: Remove Row(s)
Query affected 1 rows.
11 placeholder none.jpg placeholder placeholder description
