I don't know how to ask it, so, I try to describe is as best I can. I want to search into a SQLite database for a single word, and for that (if I use correctly this term) I slug the input field. My code is working well slugging the input, but I don't know how to slug the database records before I apply my search. This is my code for the index.php:
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8" />
<script type='text/javascript' src="js/jquery.min.js"></script>
</head>
<body>
<form method="post" action="index.php" name="myform" id="f1">
<input type="text" name="p0" placeholder="Search..." autofocus >
<input type="submit" value="Search">
<br/><br/>
</form>
<?php
if (isset($_POST["p0"])) {
include "phpinclude/searchscript.php";
// displaying the results
if (count($results) > 0) {
echo "<b>".count($results)." results found</b><br/><br/>";
foreach ($results as $r) {
printf("<li>%s %s | %s</li>", $r["genus"], $r["species"], $r["commonEN"]);
}
} else {
echo "No results found";
}
}
?>
</body>
</html>
And this is my code for the searchscript.php:
<?php
$errors = array();
$search_text = $_POST['p0'];
if (!preg_match("/^[a-zA-Z'âÂăĂîÎșȘțȚöÖüÜóÓűŰőŐúÚéÉáÁ]{0,30}$/", $search_text)) {
$errors[] = 'Only 1 word allowed, with a lenght between 0 and 30 characters.</b>';
}
if(empty($errors ) == true) {
// replacing accented characters and all to lowercase
$accented_array = array('Á'=>'A', 'Â'=>'A', 'Ă'=>'A', 'É'=>'E', 'Í'=>'I', 'Î'=>'I', 'Ó'=>'O', 'Ö'=>'O', 'Ő'=>'O', 'Ú'=>'U', 'Ű'=>'U', 'Ü'=>'U', 'Ș'=>'S', 'Ț'=>'T',
'á'=>'a', 'â'=>'a', 'ă'=>'a', 'é'=>'e', 'í'=>'i', 'î'=>'i', 'ó'=>'o', 'ö'=>'o', 'ő'=>'o', 'ú'=>'u', 'ű'=>'u', 'ü'=>'u', 'ș'=>'s', 'ț'=>'t' );
$required_str = strtr($search_text, $accented_array);
$strict_search = strtolower($required_str);
$pdo = new PDO('sqlite:db/myDatabase.db');
$pdo -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo -> setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$sql = 'SELECT genus, species, commonEN FROM myTable WHERE genus LIKE ? OR species LIKE ? OR synonyms LIKE ? OR commonEN LIKE ? ORDER BY genus ASC, species ASC';
$stmt = $pdo->prepare($sql);
$stmt->execute(["%".$strict_search."%", "%".$strict_search."%", "%".$strict_search."%", "%".$strict_search."%"]);
$results = $stmt->fetchAll();
if (isset($_POST["ajax"])) {
echo json_encode($results);
}
} else {
foreach ($errors as $error) {
echo $error;
}
}
$pdo = null;
?>
For example: let's say I search for "Cortinarius moënne" and for that I use the search key Moënne, the output for $strict_search will be moenne which is so far so good, but I don't know how to apply the same rules for the columns (genus, species, synonyms ...) from my database before I actually search for moenne. Can you help me out?
Edit
@droopsnoot I borrowed the slug term from these posts: PHP to convert string to slug and PHP function to make slug (URL string), so, I suppose I don't misunderstood it very badly. And again, lets say I want to search for the very same "Cortinarius moënne" for which a user is allowed to put into the search (input type="text") only 1 word and he/she will use let say MoëNnE (using big and small charactres but also accented characters as well). After internationalization (slugifying), this keyword will be transformed into moenne and applied into the select:
SELECT genus, species, commonEN FROM myTable WHERE genus LIKE ? OR species LIKE ? OR synonyms LIKE ? OR commonEN LIKE ?
BUT and here is my pain, I internationalized my search keyword but not the values from my database. So, the column species in my database will still be with moënne and before I compare this 2 I would like to internatinalize the species column as well, because the values inside this column could be also with big and small characters and accented characters as well and I want to be sure that what I compare it has the same format moenne vs moenne.
what I'm looking for is a solution to slug these columns as well before I search, something like:
SELECT genus, species, commonEN FROM myTable WHERE strtolower(genus) LIKE ? OR strtolower(species) LIKE ? OR strtolower(synonyms) LIKE ? OR strtolower(commonEN) LIKE ?
But as long WHERE strtolower(genus) LIKE ? probably could work, I don't know how to filter out also the accented characters from this columns. Hope this clarify your question.
