Håkan Håkan - 6 months ago 7
SQL Question

What have I done wrong with my MySql query?

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

Answer
  • 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"');

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 (").

Comments