0

I am making a search function for a page that loads data from an sql db. The page loads all rows upon loading and I need it to update the rows loaded when a search is made. When I search "abyss" I expect abyss1.9.0 to be returned but instead nothing is returned.

<?php
 $currentPage = 'software';
   include("head.php");

 $collapseVal=1;
 $db = new SQLite3('softwareinfo2.db');
 $res = $db->query("SELECT * FROM software ORDER BY Name COLLATE NOCASE ASC");

 $previous = '';
 $count = '1';
 ?>

    <h3>Packages</h3>
     <form name="search_form" action="" method="POST">
         <input type = "text" name="_input" placeholder = "Search...">
         <input type="submit" value="Submit">
     </form>
   <div class = "panel-group" id="accordion">


<?php
    if($_SERVER["REQUEST_METHOD"] == "POST") {
        $res = $db->prepare("SELECT * FROM software WHERE Name LIKE :filter ORDER BY Name COLLATE NOCASE ASC");
        $filter = "%".trim($_POST["_input"])."%";
        $res-> bindValue(':filter', $filter, SQLITE3_TEXT);
        $res->execute();
    } else {
        $res = $db->query("SELECT * FROM software ORDER BY Name COLLATE NOCASE ASC");
    }
?>
GP45
  • 29
  • 3
  • You need to add some whitelisting of your column names urgently. Letting the user specify the value for $filter and not validating it all leaves your code wide, wide open for SQL injection attacks. – ADyson Aug 10 '21 at 20:14
  • 1
    P.S. This code doesn't look much like a search. Instead the "filter" seems to try to select a specific column in the table, rather than filtering by rows. It's not entirely clear what the intention was? And we can't see your search form so we can't be sure what you're sending to the server or what it's called. Have you done any debugging? – ADyson Aug 10 '21 at 20:15
  • 1
    `if($_SERVER["POST"] == "POST")` I don't think this does what you think it does either. – miken32 Aug 10 '21 at 20:16
  • That's also a good point. Go to https://www.php.net/manual/en/reserved.variables.server.php - "POST" isn't one of the fields. Perhaps you meant `$_SERVER["REQUEST_METHOD"]`. But you really need to run that test _before_ trying to read a value from the $_POST array! – ADyson Aug 10 '21 at 20:18
  • When you say `The page refreshes but nothing new loads`...do you mean it just shows the exact same results again? Or you see no results at all? Or what? It's a bit ambiguous. – ADyson Aug 10 '21 at 20:39
  • Have you done any basic debugging when you submit the form such as checking the contents of $_POST contain what you expected? – ADyson Aug 10 '21 at 20:40
  • The page refreshes but nothing new loads: Currently after the updated $_SERVER it reloads the page with a list of numbers..... and yes I have checked to make sure the $_POST has the correct contents. – GP45 Aug 10 '21 at 20:41
  • @ADyson I think this boils down to "how to SQL" but it's far too unfocused and unclear, so I VTC. There are doubtless many errors popping up in the logs due to invalid SQL syntax. – miken32 Aug 10 '21 at 20:43
  • `Currently after the updated $_SERVER it reloads the page with a list of numbers`...is that different to before? And what did you expect instead? And what did you put into the search box? Please...give us some proper clarity, remember we cannot see your screen, and cannot read your mind about what you want / expect to happen. – ADyson Aug 10 '21 at 20:50
  • So I submit "abyss" to the search and i expect it return abyss1.9.0. It reloads the page with a list of numerical values, this is different than before as before it simply reloaded the page with the same rows returned. I am hoping to get it to return the correct row. The numbers returned are 01-01-1970 01-01-1970 01-01-1970 repeated about 100 times. – GP45 Aug 10 '21 at 20:54
  • `i expect it return abyss1.9.0` ...why? Is that in your source data somewhere? And it's not an exact match anyway. As I explained to you at the start, your "filter" isn't really a filter, it just tries to select a specific _column_ from the table, or a hard-coded string. Does your table have a column called "abyss"? You'll end up with a query looking like this: `SELECT 'abyss' FROM software ORDER BY Name COLLATE NOCASE ASC`. This isn't useful and probably isn't what you wanted. – ADyson Aug 10 '21 at 20:57
  • Are you trying to make it search within a particular column or columns within your table? – ADyson Aug 10 '21 at 20:57
  • `but instead 01-01-1970 01-01-1970 01-01-1970`...those are dates, not numerical values. – ADyson Aug 10 '21 at 20:58
  • I want to search the Name column – GP45 Aug 10 '21 at 20:59
  • So then you need a WHERE clause. How much SQL have you studied? Because you seem to be misunderstanding the basics. You'd need something like `SELECT * FROM software WHERE name LIKE :filter ORDER BY Name` and then pass the $filter value as a parameter, with `%` appended at the start and end to allow partial matching. You also need to use a prepared statement for this, to protect against SQL injection - see https://www.php.net/manual/en/sqlite3.prepare.php for examples. – ADyson Aug 10 '21 at 21:01
  • Yes they are dates but they are from the date column and I am unsure why that is being returned. I have used "abyss", "bib", and "" all three return the same date values – GP45 Aug 10 '21 at 21:02
  • https://stackoverflow.com/a/28624594/5947043 also contains a good example – ADyson Aug 10 '21 at 21:02
  • `I am unsure why that is being returned`..me too, perhaps it's the fault of some of your display code, which I can't see. Anyway it's probably nothing to worry about - once you fix the query it will go back to returning useful results and I expect that issue would disappear. – ADyson Aug 10 '21 at 21:03
  • I am just a student for now so only the basics are known to me. – GP45 Aug 10 '21 at 21:03
  • 1
    It seems not even the basics, to be perfectly honest. WHERE clauses are something you learn _very_ early on in a SQL course. – ADyson Aug 10 '21 at 21:04
  • Thanks for the update. Good attempt, you seem to be a quick learner. But I don't know what the `value(':filter')` bit at the end is supposed to be? You should remove that. – ADyson Aug 10 '21 at 21:11
  • And `$db->query` needs to be `$db->prepare` - look again a bit more closely at the examples. You also then need to `execute()` the query. – ADyson Aug 10 '21 at 21:11
  • You also missed what I said about % wildcards. See https://www.sqlitetutorial.net/sqlite-like/ for details. You need to amend $filter to add % at the start and end, before you pass it to bindValue. – ADyson Aug 10 '21 at 21:28
  • 1
    So basically `$filter = "%".trim($_POST["_input"])."%";` and then `$res-> bindValue(':filter', $filter, SQLITE3_TEXT);`. If you don't do that, then (per your example) it will search only for the exact word "abyss" , not for names which _contain_ "abyss" somewhere in them. – ADyson Aug 10 '21 at 21:28
  • Thanks for the last update, but you forgot the second change I showed in my previous comment. – ADyson Aug 10 '21 at 21:36
  • sorry I didn't see that my apologies. – GP45 Aug 10 '21 at 21:37
  • It's important, otherwise you're not making use of the $filter value you've just created :-) – ADyson Aug 10 '21 at 21:39
  • 1
    Not sure where `WHERE Name (filter) LIKE` came from in the SQL either?? It should just be `WHERE Name LIKE`. You seem to be inventing random bits of syntax on the fly. Again it's unclear what you think the purpose of the `(filter)` bit is...and it wasn't in my example. – ADyson Aug 10 '21 at 21:40
  • 1
    Now you're running `$res->execute();` but you're not collecting the result from it. In fact I suggest renaming the variable to `$stmt` (short for "statement") so you don't confuse it with your result set, and then collecting the result from execute() into $res. e.g. `$stmt = $db->prepare("SELECT * FROM software WHERE Name LIKE :filter ORDER BY Name COLLATE NOCASE ASC"); $filter = "%".trim($_POST["_input"])."%"; $stmt-> bindValue(':filter', $filter, SQLITE3_TEXT); $res = $stmt->execute();` – ADyson Aug 10 '21 at 22:10
  • No problem. Hope you learnt some stuff – ADyson Aug 11 '21 at 00:06

0 Answers0