Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am using PHP and MYSQL to make a search field and it is working fine. But when I type % and click search, I am getting all rows of the column description from the database. Here is the code:
  <!-- Main Content -->
  <form action="test.php" method="GET">
  <div class="container mb-5">
    <div class="row">
      <div class="col-lg-6">
        <label for="search" class="mb-2 mt-4 fw-bold d-flex justify-content-center" style="font-size: 20px; color: #6f42c1;">Search</label>
        <div class="input-group mb-4">
        <input type="search" class="form-control" name="search" value="<?=(isset($_GET['search'])?$_GET['search']:'');?>">
        <button type="submit" class="btn btn-outline-secondary"></button>
      </div>
      <div class="d-flex justify-content-center mb-4">
      <canvas class="bg-light" id="myCanvas" style="border:1px solid #dee2e6;"></canvas>
    </div>

      <?php
      if(isset($_GET['search']) && !empty(trim($_GET['search']))){
          $condition = '';
          $param = explode(" ", sanitize($_GET['search']));
          foreach($param as $text){
            $condition .= "description LIKE '%" . mysqli_real_escape_string($db, $text) . "%' AND ";
          }
          $condition = substr($condition, 0, -4);
          $sql = "SELECT * FROM customers WHERE " . $condition;
          $result = $db->query($sql);
          if(mysqli_num_rows($result) > 0){
            while($row = mysqli_fetch_assoc($result)){ ?>

              <div>
              Description: <?=html_entity_decode(str_replace("p", "span", $row['hadeeth']));?>
              <hr>
            </div>

            <?php }
          }else{
            echo 'No results';
          }
      }
    ?>
</div>
</div>


What I have tried:

If I search normally, I am getting the correct results. Also, if search field is empty and I click on search I am not getting any results, which is also correct. But if I type % and then I click on search button, I am getting all rows from the column description which is not what I need. Why is that happening and what's the solution?
Posted
Updated 18-Mar-23 20:18pm
v3

The '%' character in an SQL LIKE clause is a wildcard: "match any number of any characters including none" - it's like "*" in a file name under Windows: "*.*" matches every file name with every extension.

If you want to allow the user to search for a literal percent character, it needs to be escaped: MySQL 8.0 Reference Manual: 9.1.1 String Literals[^]
But ... do remember that your code almost certainly adds '%' to the front and end of the search string the user enters! Escape those and all searches will return no results.
 
Share this answer
 
Comments
FRS4002 19-Mar-23 14:40pm    
Ok thanks for explaining the issue. I think you are right ... But I didn't understand the solution ... I checked the link you sent, there is a table 9.1, that it says I should use \%, I don't know if I understand or no. So, I changed the code to this:
$condition .= "description LIKE '\%" . mysqli_real_escape_string($db, $text) . "\%' AND ";
But the search stopped working ... I am not getting any results even if I type an existing word ... Could you please help me what to do ??
OriginalGriff 19-Mar-23 14:45pm    
If you escape *all* the '%' characters, there are no wild cards left...
You need to leave the ones you add alone and escape all the ones in the data the user enters only.
FRS4002 19-Mar-23 14:55pm    
Ok, what I understand from you is to leave % on both sides and add \%. I tried that here:
$condition .= "description LIKE '%\%" . mysqli_real_escape_string($db, $text) . "%' AND ";
It didn't work too ... Sorry, I am facing difficulties on how to apply the escape on the code ... Is it even doing \% right ?? Could you please help me apply the escape on the code ??

OriginalGriff 19-Mar-23 15:29pm    
Oh, come on ... read what I typed ...

If the user entered "100% dairy free" then the search string you want to end up with is "%100\% dairy free%" so that it matches "Soya Milk 100% dairy free 500ml", "100% dairy free soya milk 500ml", and "500ml Soya Milk 100% dairy free" - but not "Soya Milk 100 percent dairy free 500ml"
So you need to modify what the user typed to escape the '%' character ...
FRS4002 19-Mar-23 21:28pm    
Done!! I just added:
$param = str_replace('%', '\\%', $param);
above foreach loop. Thanks alot!
Have a look at: SQL Wildcard Characters[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900