Before I provide you an example solution, I would like to share a few words of comment to the code you pasted, so you be able to understand why I wrote the code in different way.
You have the line:
$SQLITEPDO_PRST->execute() or die($SQLITEPDO->errorInfo());
But the PDO:errorInfo() method returns an array, not a string that is accepted by die()—an equivalent of exit(). Therefore, in case of any error, you will not see any other information that just the literal text: Array.
You may convert an array to string by implode:
$SQLITEPDO_PRST->execute() or die("sqlite err: " . implode(", ", $SQLITEPDO->errorInfo()));
Before that $SQLITEPDO_PRST->execute() you have a plenty of commands that may fail as well, but there isn't any testing if their execution was successful.
$SQLITEPDO_PRST = $SQLITEPDO->prepare("SELECT ...");
$SQLITEPDO_PRST->bindValue(":WordPol", $wordlc);
$SQLITEPDO_PRST->bindValue(":WordPol2", $wordlcclean);
There should be separate test for each of them, because it is common that preparation or binding of values fails.
As you can see, there is a lot of testing if everything went right/wrong when working with PDO.
Therefore I would like to encourage you to use Exceptions instead. You may configure PDO by passing options with PDO::setAttribute() or by passing an array like below so you don't need to test anything and in case of any error the PDO will throw an exception immediately.
An example PDO configuration:
$options = [
\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
\PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
\PDO::ATTR_EMULATE_PREPARES => false,
];
$SQLITEPDO = new \PDO('sqlite:'. $sqliteFilePath, null, null, $options);
Here is great PDO Tutorial if you want to know more and W3 Tutorial on Exceptions.
Play with the examples I provided you and make invalid SQL query to see how Exceptions are handled.
It is possible to optimize your query so no matter the quantity of user provided words you could query database with all of them at once.
For doing that you may use:
IN keyword (it works with prepared statements) or
JOIN keyword with a TEMPORARY TABLE
Retrieving results in the same order as words provided by user can be done in two ways.
When using IN keyword you need to use the array of user provided words as the source of the order and sort the retrieved data in PHP. That is because sqlite does not support ORDER BY FIELD (...), that is supported by MySQL.
When using TEMPORARY TABLE you may use its column that has incremental integer and sort as any other table by using simple ORDER BY.
Using IN and PHP sorting example.
Using prepared statements with IN in case you don't know quantity of words upfront is possible by using binding with ? instead of :bindName labels.
An example of binding by using question mark placeholders (Example #2 from PHP manual)
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
FROM fruit
WHERE calories < ? AND colour = ?');
$sth->bindParam(1, $calories, PDO::PARAM_INT);
$sth->bindParam(2, $colour, PDO::PARAM_STR);
$sth->execute();
Note that first argument of bindParam() is an integer and it starts from 1 not from 0. It is also important to have numbers incrementing by 1 continuously = 1,2,3 an not 1,3,4 (2 skipped) .
In your application if you receives array of unknown number elements then you need to count the number of elements and make exactly the same amount of ? inside the IN ().
For two words sent by the user, your query is:
SELECT `WordPol`, `IsFirstCap` FROM tWordsTest WHERE `WordPol` IN (?, ?) ORDER BY (WordPol);
And if the user sends 100 words then there should be 100x ? separated by comma , and so on.
You may use count($userSentWordsParsed) to get the number of words passed by user and str_repeat() to get exact amount of ? that equal to the number of words passed by the user:
$userSentWordsParsedCount = count($userSentWordsParsed);
$additionalQuestionMarksCount = $userSentWordsParsedCount - 1;
$in = str_repeat('?,', $additionalQuestionMarksCount) . '?';
$query = "SELECT `WordPol`, `IsFirstCap` FROM tWordsTest WHERE `WordPol` IN (" . $in . ") ORDER BY (WordPol);";
Having that query you need to make prepare and binding to PDO parametres like in the example from PHP manual above.
You can do it in the foreach loop that iterates over the data.
Simple, but handy, is this function that prepares, binds data and executes a statement:
function pdoPrepareAndExecute(
\PDO $connection,
string $query,
array $bindData = []
) : \PDOStatement
{
$stmt = $connection->prepare($query);
if (!empty($bindData)) {
foreach ($bindData as $entry) {
$stmt->bindParam(
$entry['bindName'],
$entry['bindValue'],
$entry['bindType']
);
}
}
$stmt->execute();
return $stmt;
}
So you can use it this way:
#---------- build query ------------
$userSentWordsParsedCount = count($userSentWordsParsed);
$additionalQuestionMarksCount = $userSentWordsParsedCount - 1;
$in = str_repeat('?,', $additionalQuestionMarksCount) . '?';
$query = "SELECT `WordPol`, `IsFirstCap` FROM tWordsTest WHERE `WordPol` IN (" . $in . ") ORDER BY (WordPol);";
#---------- build query ------------
# --------- create data with binding arr -------
$data = [];
$placeholderPosition = 1;
foreach ($userSentWordsParsed as $word) {
$data[] = [
'bindName' => $placeholderPosition,
'bindValue' => $word,
'bindType' => \PDO::PARAM_STR
];
$placeholderPosition++;
}
# --------- create data with binding arr -------
#---------- execute query ----------
$stmt = pdoPrepareAndExecute($SQLITEPDO, $query, $data);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
in many places of your code.
Once you have results, there is a need to use the $userSentWordsParsed as a source of the order for sorting the $results.
The example generates output:
----------------------------------------
userSentWordsParsed:
Array
(
[0] => cherry
[1] => lemon
[2] => potato
[3] => banana
[4] => carrot
[5] => raspberry
)
----------------------------------------
Display results:
cherry (in database)
lemon (in database)
potato
banana (in database)
carrot
raspberry (in database)
The example PHP Code:
<?php
declare(strict_types=1);
error_reporting(E_ALL);
try {
header('Content-Type: text/plain');
# change directory to any temporary
$saveDir = '/usr/share/nginx/tmp';
$separatorCount = 40; // just for printing msg
// create directory
if (!file_exists($saveDir)) {
mkdir($saveDir);
}
// -------- connect to db
$sqliteFilePath = $saveDir . '/sqlite.db';
$options = [
\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
\PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
\PDO::ATTR_EMULATE_PREPARES => false,
];
$SQLITEPDO = new \PDO('sqlite:'. $sqliteFilePath, null, null, $options);
// -------- connect to db
function pdoPrepareAndExecute(
\PDO $connection,
string $query,
array $bindData = []
) : \PDOStatement
{
$stmt = $connection->prepare($query);
if (!empty($bindData)) {
foreach ($bindData as $entry) {
$stmt->bindParam(
$entry['bindName'],
$entry['bindValue'],
$entry['bindType']
);
}
}
$stmt->execute();
return $stmt;
}
#------ Create table ---------
// an example table of tags in a database
# CAUTION: It may delete your database table
# if you change tWordsTest to tWords
# remove that DROP TABLE if you want use that
# with your target database
// drop any previous table with its content
$query = "DROP TABLE IF EXISTS tWordsTest;";
pdoPrepareAndExecute($SQLITEPDO, $query);
// create new table for tags
$query = "CREATE TABLE IF NOT EXISTS tWordsTest (
id INTEGER PRIMARY KEY AUTOINCREMENT,
WordPol TEXT,
IsFirstCap INTEGER
);";
pdoPrepareAndExecute($SQLITEPDO, $query);
#------ Create table ----------
// content of the example table
$wordInTable = [
'apple',
'banana',
'orange',
'lemon',
'raspberry',
'cherry',
'grapefruit',
'kiwi',
];
#----------- insert data into table -----------
$query = "INSERT INTO tWordsTest (WordPol, IsFirstCap) VALUES (:WordPol, :IsFirstCap);";
$firstCap = 0;
foreach($wordInTable as $WordPol) {
$data = [
[
'bindName' => ':WordPol',
'bindValue' => $WordPol,
'bindType' => \PDO::PARAM_STR
],
[
'bindName' => ':IsFirstCap',
'bindValue' => $firstCap,
'bindType' => \PDO::PARAM_INT
],
];
pdoPrepareAndExecute($SQLITEPDO, $query, $data);
}
#----------- insert data into table -----------
#---------- Test insert -------------
$query = "SELECT * FROM tWordsTest ";
$stmt = pdoPrepareAndExecute($SQLITEPDO, $query);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo str_repeat('-', $separatorCount) . \PHP_EOL;
echo "Data in table:" . \PHP_EOL;
print_r($results);
#---------- Test insert -------------
# example tags sent by user
$userSentWords = [
'cherry',
'lemon',
'potato',
'banana',
'carrot',
'raspberry'
];
#---------- parse words ------------
function parseWords(array $inputWordsTest) : array
{
$userSentWordsParsed = [];
foreach ($inputWordsTest as $key => $value) {
$word = $value;
if ($word != "") {
$word = str_replace("’", "'", $word);
$word = str_replace("᾿", "'", $word);
$wordlc = mb_strtolower($word, "UTF-8");
$wordlcclean = str_replace("'", "", $wordlc);
$wordlcclean = str_replace("‘", "", $wordlcclean);
$userSentWordsParsed[] = $word;
$userSentWordsParsed[] = $wordlcclean;
}
}
// remove possible duplicates
$userSentWordsParsed = array_unique($userSentWordsParsed);
// make keys ascending and incremental by 1 without missing integers (PDO binding Requirement)
$userSentWordsParsed = array_values($userSentWordsParsed);
return $userSentWordsParsed;
}
$userSentWordsParsed = parseWords($userSentWords);
echo str_repeat('-', $separatorCount) . \PHP_EOL;
echo "userSentWordsParsed:" . \PHP_EOL;
print_r($userSentWordsParsed);
#---------- build query ------------
$userSentWordsParsedCount = count($userSentWordsParsed);
$additionalQuestionMarksCount = $userSentWordsParsedCount - 1;
$in = str_repeat('?,', $additionalQuestionMarksCount) . '?';
$query = "SELECT `WordPol`, `IsFirstCap` FROM tWordsTest WHERE `WordPol` IN (" . $in . ") ORDER BY (WordPol);";
#---------- build query ------------
# --------- create data with binding arr -------
$data = [];
$placeholderPosition = 1;
foreach ($userSentWordsParsed as $word) {
$data[] = [
'bindName' => $placeholderPosition,
'bindValue' => $word,
'bindType' => \PDO::PARAM_STR
];
$placeholderPosition++;
}
# --------- create data with binding arr -------
#---------- execute query ----------
$stmt = pdoPrepareAndExecute($SQLITEPDO, $query, $data);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo str_repeat('-', $separatorCount) . \PHP_EOL;
echo "Results unsorted:" . \PHP_EOL;
print_r($results);
#---------- execute query ----------
// name of the key that holds words in returned rows from DB
$sortKey = 'WordPol';
# ------------ get not matched words ----
// get a flat array having only words from results rows.
$foundWordsInDb = array_column($results, $sortKey);
// substract from user wordList these words that were found in the db
$notFoundWords = array_diff($userSentWordsParsed, $foundWordsInDb);
if (!empty($notFoundWords)) {
// make keys ascending without missing integers
$notFoundWords = array_values($notFoundWords);
}
# ------------ get not matched words ----
# ------------ sort results -------------
$resultsSorted = [];
// for each word that user sent (in the order it was sent)
foreach ($userSentWordsParsed as $userWord) {
// iterate over all rows of database returned results
foreach($results as $row) {
$wordInDatabase = $row[$sortKey];
// if the word in db is same as user's word
if ($wordInDatabase === $userWord) {
// put it on sorted results
$resultsSorted[] = $row;
}
}
}
# ------------ sort results -------------
echo str_repeat('-', $separatorCount) . \PHP_EOL;
echo "resultsSorted:" . \PHP_EOL;
print_r($resultsSorted);
echo str_repeat('-', $separatorCount) . \PHP_EOL;
echo "notFoundWords:\n";
print_r($notFoundWords);
# -------- combine results ------------
$combinedResults = [];
// get result as [ rowIndex => 'tagName' ]
$foundWords = array_column($resultsSorted, $sortKey);
// switch places of key and value to [ 'tagName' => rowIndex ]
$foundWords = array_flip($foundWords);
// create combined results from found in the db tags
// and not found (notFoundWords)
foreach ($userSentWordsParsed as $word) {
$row = [];
// if the tag was not found in the DB
if (in_array($word, $notFoundWords)) {
// create row with word name and present = 0
$row = [$sortKey => $word, 'present' => 0];
// tag was found in the DB
} else {
$key = $foundWords[$word]; // get index of row for that word
$row = $resultsSorted[$key]; // get row of of found word
// add key present with value 1 to row
$row = array_merge($row, ['present' => 1]);
}
$combinedResults[] = $row;
}
echo str_repeat('-', $separatorCount) . \PHP_EOL;
echo "Combinded restults:\n";
print_r($combinedResults);
# -------- combine results ------------
# -------- display results ------------
echo str_repeat('-', $separatorCount) . \PHP_EOL;
echo "Display results:\n";
foreach ($combinedResults as $result) {
if ($result['present'] === 1) {
echo $result['WordPol'] . ' (in database)' . \PHP_EOL;
} else {
echo $result['WordPol'] . \PHP_EOL;
}
}
# -------- display results ------------
} catch (Throwable $e) {
$msg = 'There was an error. ';
$msg .= 'Code: ' . $e->getCode() . '. ';
$msg .= 'Msg: ' . $e->getMessage() . '. ';
$msg .= 'Location: ' . $e->getFile() . ':' . $e->getLine() . PHP_EOL;
echo $msg;
// get only file and line locations from the trace
$callback = function ($entry) {
return $entry['file'] . ':' . $entry['line'];
};
$traceArr = array_map($callback, $trace = $e->getTrace());
echo "Execution flow:" . PHP_EOL;
print_r($traceArr);
exit;
}
Example using TEMPORARY TABLE and JOIN
Using a temporary table has the advantage that you don't need to sort anything in the PHP application, so it is much simpler and less prone to any errors. Also if you create a temporary table then it is possible to create it with an additional column that has incremental integer of order similarly to keys of the array with user's words. That incremental column later can be used for receiving results in the same order that is in the temporary table that was populated with the values of user's words.
$query =
"CREATE TEMPORARY TABLE IF NOT EXISTS temp_word_list (
`input_order` INTEGER PRIMARY KEY AUTOINCREMENT,
`user_word` TEXT
);";
Then if you make LEFT JOIN
$query =
"SELECT
`temp_word_list`.`input_order`,
`user_word`,
`WordPol`,
`IsFirstCap`
FROM `temp_word_list` LEFT JOIN `tWordsTest`
ON `temp_word_list`.`user_word` = `tWordsTest`.`WordPol`
ORDER BY `temp_word_list`.`input_order`
You will get all the user's words as results (even if they are not in the database) plus you may have results sorted in the order of the user's words by column input_order from the temp_word_list.
x - temp_word_list
y - tWordsTest
See more examples of JOINs visualization
The result output of the code:
userSentWordsParsed:
Array
(
[0] => cherry
[1] => lemon
[2] => potato
[3] => banana
[4] => carrot
[5] => raspberry
)
----------------------------------------
Display results:
cherry (in database)
lemon (in database)
potato
banana (in database)
carrot
raspberry (in database)
PHP Code:
<?php
declare(strict_types=1);
error_reporting(E_ALL);
try {
header('Content-Type: text/plain');
# change directory to any temporary
$saveDir = '/usr/share/nginx/tmp';
$separatorCount = 40; // just for printing msg
// create directory
if (!file_exists($saveDir)) {
mkdir($saveDir);
}
// -------- connect to db
$sqliteFilePath = $saveDir . '/sqlite.db';
$options = [
\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
\PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
\PDO::ATTR_EMULATE_PREPARES => false,
];
$SQLITEPDO = new \PDO('sqlite:'. $sqliteFilePath, null, null, $options);
// -------- connect to db
function pdoPrepareAndExecute(
\PDO $connection,
string $query,
array &$bindData = []
) : \PDOStatement
{
$stmt = $connection->prepare($query);
if (!empty($bindData)) {
foreach ($bindData as &$entry) {
$stmt->bindParam(
$entry['bindName'],
$entry['bindValue'],
$entry['bindType']
);
}
}
$stat = $stmt->execute();
return $stmt;
}
#------ Create table ---------
// an example table of tags in a database
# CAUTION: It may delete your database table
# if you change tWordsTest to tWords
# remove that DROP TABLE if you want use that
# with your target database
// drop any previous table with its content
$query = "DROP TABLE IF EXISTS tWordsTest;";
pdoPrepareAndExecute($SQLITEPDO, $query);
// create new table for tags
$query = "CREATE TABLE IF NOT EXISTS tWordsTest (
id INTEGER PRIMARY KEY AUTOINCREMENT,
WordPol TEXT,
IsFirstCap INTEGER
);";
pdoPrepareAndExecute($SQLITEPDO, $query);
#------ Create table ----------
// content of the example table
$wordInTable = [
'apple',
'banana',
'orange',
'lemon',
'raspberry',
'cherry',
'grapefruit',
'kiwi',
];
#----------- insert data into table -----------
$query = "INSERT INTO tWordsTest (WordPol, IsFirstCap) VALUES (:WordPol, :IsFirstCap);";
$firstCap = 0;
foreach($wordInTable as $WordPol) {
$data = [
[
'bindName' => ':WordPol',
'bindValue' => $WordPol,
'bindType' => \PDO::PARAM_STR
],
[
'bindName' => ':IsFirstCap',
'bindValue' => $firstCap,
'bindType' => \PDO::PARAM_INT
],
];
pdoPrepareAndExecute($SQLITEPDO, $query, $data);
}
#----------- insert data into table -----------
#---------- Test insert -------------
$query = "SELECT * FROM tWordsTest ";
$stmt = pdoPrepareAndExecute($SQLITEPDO, $query);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo str_repeat('-', $separatorCount) . \PHP_EOL;
echo "Data in table:" . \PHP_EOL;
print_r($results);
#---------- Test insert -------------
# example tags sent by user
$userSentWords = [
'cherry',
'lemon',
'potato',
'banana',
'carrot',
'raspberry'
];
#---------- parse words ------------
function parseWords(array $inputWordsTest) : array
{
$userSentWordsParsed = [];
foreach ($inputWordsTest as $key => $value) {
$word = $value;
if ($word != "") {
$word = str_replace("’", "'", $word);
$word = str_replace("᾿", "'", $word);
$wordlc = mb_strtolower($word, "UTF-8");
$wordlcclean = str_replace("'", "", $wordlc);
$wordlcclean = str_replace("‘", "", $wordlcclean);
$userSentWordsParsed[] = $word;
$userSentWordsParsed[] = $wordlcclean;
}
}
// remove possible duplicates
$userSentWordsParsed = array_unique($userSentWordsParsed);
// make keys ascending and incremental by 1 without missing integers (PDO binding Requirement)
$userSentWordsParsed = array_values($userSentWordsParsed);
return $userSentWordsParsed;
}
$userSentWordsParsed = parseWords($userSentWords);
echo str_repeat('-', $separatorCount) . \PHP_EOL;
echo "userSentWordsParsed:" . \PHP_EOL;
print_r($userSentWordsParsed);
########## DIFFERENT CODE BELOW ############
#------ Create table ---------
// an example table of tags in a database
$query = "DROP TABLE IF EXISTS temp_word_list;";
pdoPrepareAndExecute($SQLITEPDO, $query);
$query =
"CREATE TEMPORARY TABLE IF NOT EXISTS temp_word_list (
`input_order` INTEGER PRIMARY KEY AUTOINCREMENT,
`user_word` TEXT
);";
pdoPrepareAndExecute($SQLITEPDO, $query);
#------ Create table ----------
#----------- insert data into table -----------
// Prepare INSERT statement.
$query = "INSERT INTO temp_word_list (user_word) VALUES (:user_word);";
foreach($userSentWordsParsed as &$word) {
$data =
[
[
'bindName' => ':user_word',
'bindValue' => $word,
'bindType' => \PDO::PARAM_STR,
]
];
pdoPrepareAndExecute($SQLITEPDO, $query, $data);
}
#----------- insert data into table -----------
#---------- build query ------------
# query for all user words
$query =
"SELECT
`temp_word_list`.`input_order`,
`user_word`,
`WordPol`,
`IsFirstCap`
FROM `temp_word_list` LEFT JOIN `tWordsTest`
ON `temp_word_list`.`user_word` = `tWordsTest`.`WordPol`
ORDER BY `temp_word_list`.`input_order`
;";
#---------- build query ------------
# query for user words not present in the DB
// $query =
// "SELECT
// `temp_word_list`.`input_order`,
// `user_word`,
// `WordPol`,
// `IsFirstCap`
// FROM `temp_word_list` LEFT JOIN `tWordsTest`
// ON `temp_word_list`.`user_word` = `tWordsTest`.`WordPol`
// WHERE `WordPol` IS NULL
// ORDER BY `temp_word_list`.`input_order`
// ;";
#----------- prepare query ---------
#---------- execute query ----------
$stmt = pdoPrepareAndExecute($SQLITEPDO, $query);
$combinedResults = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo str_repeat('-', $separatorCount) . \PHP_EOL;
echo "Results combined:" . \PHP_EOL;
print_r($combinedResults);
#---------- execute query ----------
# -------- display results ------------
echo str_repeat('-', $separatorCount) . \PHP_EOL;
echo "Display results:\n";
foreach ($combinedResults as $result) {
if (!empty($result['WordPol'])) {
echo $result['user_word'] . ' (in database)' . \PHP_EOL;
} else {
echo $result['user_word'] . \PHP_EOL;
}
}
# -------- display results ------------
} catch (Throwable $e) {
$msg = 'There was an error. ';
$msg .= 'Code: ' . $e->getCode() . '. ';
$msg .= 'Msg: ' . $e->getMessage() . '. ';
$msg .= 'Location: ' . $e->getFile() . ':' . $e->getLine() . PHP_EOL;
echo $msg;
// get only file and line locations from the trace
$callback = function ($entry) {
return $entry['file'] . ':' . $entry['line'];
};
$traceArr = array_map($callback, $trace = $e->getTrace());
echo "Execution flow:" . PHP_EOL;
print_r($traceArr);
exit;
}
