What have I done wrong with my MySql query?

Question

I'm trying to create a simple search function in PHP for my MySQL database. I've tried different tutuorials but I end up with the same result. From testing a bit with print_r I think the problem is the query, but I don't understand why it doesn't work. Does anyone have an idea? My code is following:

html

<form action="search.php" method="get">
     <div class="input-field">
          <input id="search" name="search" type="search" placeholder="Search lesson plans" required>
              <label for="search"><i class="material-icons">search</i></label>
              <i class="material-icons" id="closesearch">close</i>
     </div>
</form>

php

<?php

require_once("db_link.inc.php");

if(isset($_GET['search'])) {
    $search = $link->escape_string($_GET['search']);
    $query = $link->query('SELECT * FROM LessonPlans WHERE Subject LIKE "%{$search}%" OR Level LIKE "%{$search}%" OR Aim LIKE "%{$search}%" AND Language="English"');

if($query->num_rows){
    while($r = $query->fetch_object()){

    echo '<div>
        <p>'; $r->Subject; echo '</p>
    </div>';
        }
    } 
}
?>

Table

LessonPlans
Id | Subject | Level | Aim | Text | Language

Can anyone see what I've done wrong?

/Håkan

Hmm.. You did not use echo on `$r->Subject;`? And use double tick for your query? Please state also the error you are getting or problems you are encountering. — Logan Wayne, May 11 '16 at 05:22
your echoing statement is wrong it should be echo $r->Subject; — siddhesh, May 11 '16 at 05:23
I get nothing when searching. I used `print_r($query)` and get this: `mysqli_result Object ( [current_field] => 0 [field_count] => 14 [lengths] => [num_rows] => 0 [type] => 0 )`. Does it make any difference in what order I use `'` and `"`? — Håkan, May 11 '16 at 05:36

score 1 · Accepted Answer · edited May 23 '17 at 11:52

1

You did not print the $r->Subject inside your loop.
You did not properly concatenate the $search variable to your query

Your query should look like (if you insist on using single tick '):

$query = $link->query('SELECT * FROM LessonPlans WHERE Subject LIKE "%'.$search.'%" OR Level LIKE "%'.$search.'%" OR Aim LIKE "%'.$search.'%" AND Language="English"');

but if you want to keep on using the curly brackets:

$query = $link->query("SELECT * FROM LessonPlans WHERE Subject LIKE '%{$search}%' OR Level LIKE '%{$search}%' OR Aim LIKE '%{$search}%' AND Language='English'");

and for displaying/echoing the data:

echo '<div>
        <p>'.$r->Subject.'</p>
      </div>';

You can refer here for the difference of single tick (') and double tick (").

edited May 23 '17 at 11:52

Community

1
1

answered May 11 '16 at 05:36

Logan Wayne

6,001
16
31
49

Thanks! Works now! But How come it didn't work doing the query the other way? In the video tutorial it came out with the curly brackets. Becasue of the single ticks? – Håkan May 11 '16 at 05:52
`$foo = hello; echo '$foo earth';` Displays: $foo earth. If surrounded by double quotes it would display: hello earth. Look at your query log. – Progrock May 11 '16 at 06:28
@Håkan - Yes, it does. You can check the tutorial you're looking at again. I bet the tutorial uses double tick when it uses curly brackets. – Logan Wayne May 11 '16 at 06:30
Ah, Ok, Thanks guys. I'm new to this and I had no idea there were any difference. @Logan Wayne - Yes, the tutorial use double ticks! – Håkan May 11 '16 at 06:37

What have I done wrong with my MySql query?

1 Answers1