Mysql not returning expected results

Question

I've tried so many different variations of this, I can't figure out why this isn't returning my expected results. Here's my code as it is now. I've tried many variations of the variable in the SQL statement after the LIKE and nothing has returned an array as expected. I've also verified that the $search variable does have the correct string in it.

index.php action switch (this works correctly):

case 'search_result':
    $search = filter_input(INPUT_POST, 'searchBar');
    $results = search_books($search);
    if($results == NULL){
        include('/view/search_null.php');
    }else {
        include('/view/search_results.php');
    }
    break;

My db search function:

function search_books($search_word){
global $db;
$query = ' SELECT * FROM books
           WHERE bookTitle LIKE :search_word  ';
$statement = $db->prepare($query);
$statement->bindValue(":search_word", $search_word);
$statement->execute();
$results_search = $statement->fetchAll();
$statement->closeCursor();
return ($results_search);
}

Every time $results is NULL and kicks me to my page that returns no results, even if it should return results. On that page I verify that $search has the correct word in it and that the array size of $results and $results_search is 0.

(Yes I know I should be cleansing the $search, this isn't going live it's just for an assignment)

Try removing `'` (quotes) from the query and replacing `$search_word` with `:search_word` from it. Take a look an [example with placeholders](http://php.net/manual/en/pdostatement.bindvalue.php#refsect1-pdostatement.bindvalue-examples) — FirstOne, Apr 21 '17 at 13:43
`Like` without wildcards is the same as an =. do you need to add/concatenate the wildcards to your search string? — xQbert, Apr 21 '17 at 14:08
Yeah, I noticed that after I posted. That was one of the many iterations (:search_word) that hasn't worked. Changed it and I still have the same issue. — Alex, Apr 21 '17 at 14:22
In terms of using LIKE, I have to use that because I may search for a single keyword out of a bookTitle without it being an exact match. Am I correct in that thinking? — Alex, Apr 21 '17 at 14:23
Have you tried adding `%` before and after your `search_word`? Something like `$search_word = '%' . $search_word . '%';`. — FirstOne, Apr 21 '17 at 14:33
I did, but I just tried this and it worked!!!!! '%$search_word%' — Alex, Apr 21 '17 at 14:38
Haha, this is my first question. How do I mark it solved? Just edit the title or something? — Alex, Apr 21 '17 at 14:40

score 1 · Accepted Answer · edited Apr 24 '17 at 13:00

1

What you are looking for is to surround the search term with wildcards by adding % to the search word, like so:

$search_word = '%' . $search_word . '%';

And then just prepare / bind / execute as you already are. This is your code after the change:

function search_books($search_word){
    global $db;
    $search_word = '%' . $search_word . '%'; // added
    $query = ' SELECT * FROM books
           WHERE bookTitle LIKE :search_word  ';
    $statement = $db->prepare($query);
    $statement->bindValue(":search_word", $search_word);
    $statement->execute();
    $results_search = $statement->fetchAll();
    $statement->closeCursor();
    return ($results_search);
}

The query would look something like this after the parameter get its place:

SELECT * FROM books WHERE bookTitle LIKE '%something here%'

That will look for anything that contains $search_word. Example:

For $search_word as Rings:
The Lord of the Rings: The Return of the King would be a match.

You can read more about that in here: Pattern Matching.

edited Apr 24 '17 at 13:00

Jay Blanchard

34,243
16
77
119

answered Apr 21 '17 at 14:55

FirstOne

6,033
7
26
45

@JayBlanchard This has nothing to do with prepared statements at all! The op IS already using prepared statments, this will only add `%` **before** the statment usage. My answer doesn't even have php code related to database, just string concatenation. – FirstOne Apr 24 '17 at 11:54
@JayBlanchard There is no _sloppy_ nor _dangerous coding practices_ at place here. I never suggested the op to do it. Read my answer again, I just showed how the query **would look like**. Look at the variable name: `$search_word`, that will be used a a parameter in the prepared statement. I never even suggested anything you are implying. BTW, I never used input directly in the query. – FirstOne Apr 24 '17 at 11:59
@JayBlanchard I advise you to read the question and answer again paying very close attention to variable names and usage... – FirstOne Apr 24 '17 at 11:59
@JayBlanchard _Suggesting concatenation with user input data is dangerous_ interesting, that's exactly what's being done in the dup: a variable concat with `%`. The concatenation is not done in the query, but in the variable used as parameter. I really don't know to to explain it better. **Edit:** I'll edit my answer real quick – FirstOne Apr 24 '17 at 12:02
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/142491/discussion-between-firstone-and-jay-blanchard). – FirstOne Apr 24 '17 at 12:04

Shahzad Barkati · Answer 2 · 2017-04-25T10:16:12.593

0

UPDATED:

You bind the variable with query but did not put the bind value in query, so use the following code: ( Hope it works for you! )

function search_books($search_word){
  global $db;
  $query = " SELECT * FROM books
           WHERE bookTitle LIKE % :search_word %";
  $statement = $db->prepare($query);
  $statement->bindValue(":search_word", $search_word);
  $statement->execute();
  $results_search = $statement->fetchAll();
  $statement->closeCursor();
  return ($results_search);
}

edited Apr 25 '17 at 10:16

answered Apr 21 '17 at 13:48

Shahzad Barkati

2,532
6
25
33

Let's not teach/propagate sloppy and dangerous coding practices. If you post an answer without prepared statements [you may want to consider this before posting](http://meta.stackoverflow.com/q/344703/). Additionally [a more valuable answer comes from showing the OP the right method](https://meta.stackoverflow.com/a/290789/1011527). – Jay Blanchard Apr 21 '17 at 14:16
Yeah, I noticed that after I posted. That was one of the many iterations that hasn't worked. Changed it and I still have the same issue. – Alex Apr 21 '17 at 14:20
Jay, sorry I'm just in class and working within the framework of the assignment. I will look into mysqli once I'm done with the assignment, but as it is I am just working on what I was assigned. – Alex Apr 21 '17 at 14:31
It isn't about MySQLi @Alex, it is about not using prepared statements. This answer ignores prepared statements. – Jay Blanchard Apr 21 '17 at 15:14
@JayBlanchard ok, I looked those up. So those are more secure than the way I was doing it? I am sorry for the questions, I'm certainly not a pro and just doing this the way we are being shown to do them. I saw a way of doing it that cleans the statement before you send it into the function, but I hadn't seen anything regarding prepared statements. Also, I very much appreciate your insight! – Alex Apr 21 '17 at 18:55
You're already using them @Alex when you use placeholders like `:search_word`. The answer under which we are commenting here is wrong because it fails to keep using the prepared statements. – Jay Blanchard Apr 21 '17 at 18:57
I gotcha. I couldn't get it to work though using the placeholder and then binding it. I had :search_word in the SQL statement and bound it using $statement->bindValue(":search_word", $search_word); Do you see any errors in this? I couldn't get it to work for the life of me. – Alex Apr 21 '17 at 19:06

score 0 · Answer 3 · answered Apr 21 '17 at 13:57

0

$query = " SELECT * FROM books
       WHERE bookTitle LIKE :search_word ";

That`s all what you need

answered Apr 21 '17 at 13:57

Александр Резниченко

64
4

Yeah, I noticed that after I posted. That was one of the many iterations that hasn't worked. Changed it and I still have the same issue. – Alex Apr 21 '17 at 14:20

Mysql not returning expected results

3 Answers3