0

I am building a CRUD application using PHP and PDO.

I have a table with various foods and want to return results based on the first letter of user input. As in "return all foods starting with 'a'".

I have this query:

$sql = "SELECT * 
                    FROM food
                    WHERE FoodName LIKE 'a%';
    $userInput = $_POST['userInput'];

This obviously works and returns all food starting with an a.

But how can I query based on the user input, instead of writing the above for every alphabetical letter.

I cannot post the whole application, I have it on localhost, but I don't think this is necessary. What is the most efficient way to implement this? JS, PHP or a PDO command?

ptts
  • 1,022
  • 8
  • 18
  • this might help you [https://stackoverflow.com/questions/1457131/php-pdo-prepared-statements](https://stackoverflow.com/questions/1457131/php-pdo-prepared-statements) – vher2 Aug 30 '17 at 12:58
  • Your query appears to be missing a closing double quote; was that a bad paste? – Funk Forty Niner Aug 30 '17 at 12:59
  • @Fred-ii-, it's just not the whole function, just a part of the try/catch bit for brevity purposes. – ptts Aug 30 '17 at 13:06

1 Answers1

3

Based on what you have there is a form submitting user input which results in a variable in the $_POST array:

$_POST['userInput']

In order to use that in the query you just have to replace the search term with the posted variable. Here is an example in PDO using a prepared statement to prevent potential SQL injection attacks:

$sql = $conn->prepare("SELECT * FROM `food` WHERE `FoodName` LIKE :user_input"); // $conn being the database connection
$parameters = array(':user_input' => $_POST['userInput'] . '%'); // note that I have concatenated the wildcard here
$sql->execute($parameters); 
$results = $sql->fetchAll(PDO::FETCH_ASSOC); // fetch everything into an associative array

Here I have executed an array of parameters, you can also bind each parameter individually (if you have more than one) using bindParam()

Jay Blanchard
  • 34,243
  • 16
  • 77
  • 119
  • 1
    Thank, you so much, so this wildcard is all there is needed and needs to be concatenated like that? Fantastic stuff. – ptts Aug 30 '17 at 17:32
  • That's correct *if you expect the first letter of the query*. If you want your `LIKE` to be more encompassing a wildcard should be concatenated to the from of the variable too. – Jay Blanchard Aug 30 '17 at 18:38
  • Is there a way of recognizing not just the first user char input, but every consecutive one? Sorry if this sounds like a stupid question, I am all new(first db project ever) to SQL and realize even the "regex" is quite different. The way to do this in JS is clear to me, I am just wondering how an expert would go on about this is DBO/SQL. Thanks for these very valuable initial lessons. – ptts Aug 30 '17 at 23:02
  • There is, the wildcard would stay in place and you'd allow for a longer user input. – Jay Blanchard Aug 31 '17 at 02:02