MySQL PHP partial search

Question

I have a problem with partial world search.

The code below works, but only with full word search. If I put only part of the word in the search box it does not work. Please can you give me a hint what to change to be able to make partial word search?

<?php include "connection.php";


$sql = "SELECT * FROM Klienci ";

if (isset($_POST['search'])) {

  $search_term = mysql_real_escape_string($_POST['search_box']);

  $sql .= "WHERE nazwaKlienta = '{$search_term}' ";
  $sql .= " OR adresMail = '{$search_term}' ";
  $sql .= " OR adresMail2 = '{$search_term}' ";
}

$query = mysql_query($sql) or die(mysql_error());

?>


<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>BAZA  | Dashboard</title>
  <!-- Bootstrap core CSS -->
  <link href="css/bootstrap.min.css" rel="stylesheet">

  <link href="css/jumbotron-narrow.css" rel="stylesheet">
</head>
<body>

  <div class="container">
    <div class="header">
      <ul class="nav nav-pills pull-right">
        <li class="active"><a href="index.php">Home</a></li>
        <li><a href="add_customer.php">Dodaj Firmę</a></li>
      </ul>
      <h3 class="text-muted">ITalents</h3>
    </div>

    <div class="row marketing">
      <div class="col-lg-12">

        <h2>KLIENCI</h2>
        <table class="table table-striped">

          <form name="search_form" method="POST" action="display_data.php">
            Search: <input type="text" name="search_box" value ="" />
            <input type="submit" name="search" value="Search the table..." >
          </form>

          <tr>
            <th>id</th>
            <th>Klient</th>
            <th>Email</th>
            <th>Email 2</th>
            <th>Data dodania do bazy</th>
          </tr>

          <?php while ($row = mysql_fetch_array($query)) {

            //Display customer info
            $output ='<tr>';
            $output .='<td>'.$row['id'].'</td>';
            $output .='<td>'.$row['nazwaKlienta'].'</td>';
            $output .='<td>'.$row['adresMail'].'</td>';
            $output .='<td>'.$row['adresMail2'].'</td>';
            $output .='<td>'.$row['dataDodania'].'</td>';
            $output .='<td><a href="edit_customer.php?id='.$row['id'].'" class="btn btn-default btn-sm">Edit</a></td>';
            $output .='<td><a href="delete_customer.php?id='.$row['id'].'" class="btn btn-default btn-sm">Delete</a></td>';
            $output .='</tr>';

            //Echo output
            echo $output;
            ?>
            <?php } ?>

          </table>

        </div>
      </div>

    </div>

    <div class="footer">
      <p>&copy; Company 2014</p>
    </div>

  </div> <!-- /container -->


</body>
</html>

Use `like` and wildcards, or look into full text searching. Also don't use `mysql_*` anymore use `PDO` or `MySQLi`. https://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html — chris85, Sep 17 '17 at 20:43
It's beneficial to you and the SO community if you exhaust online searching first before asking here. If you simply search google with the title of this post, you'll find plenty of resources to help you solve it... that said, the LIKE keyword will be what you're looking for. — Chris J, Sep 18 '17 at 01:57
If you're writing new code, **_please_ don't use the `mysql_*` functions**. They are old and broken, were deprecated in PHP 5.5 (which is so old it no longer even receives security updates), and completely removed in PHP 7. Use [`PDO`](https://secure.php.net/manual/en/book.pdo.php) or [`mysqli_*`](https://secure.php.net/manual/en/book.mysqli.php) with _prepared statements_ and _parameter binding_ instead. See http://stackoverflow.com/q/12859942/354577 for details. — ChrisGPT was on strike, Sep 18 '17 at 02:26

score 0 · Accepted Answer · answered Sep 17 '17 at 20:50

The below code should work. You have to use LIKE instead of using equal. For example: WHERE something LIKE '%something2%'

<?php include "connection.php";


$sql = "SELECT * FROM Klienci ";

if (isset($_POST['search'])) {

  $search_term = mysql_real_escape_string($_POST['search_box']);

  $sql .= "WHERE nazwaKlienta LIKE '%{$search_term}%' ";
  $sql .= " OR adresMail LIKE '%{$search_term}%' ";
  $sql .= " OR adresMail2 LIKE '%{$search_term}%' ";
}

$query = mysql_query($sql) or die(mysql_error());

?>


<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>BAZA  | Dashboard</title>
  <!-- Bootstrap core CSS -->
  <link href="css/bootstrap.min.css" rel="stylesheet">

  <link href="css/jumbotron-narrow.css" rel="stylesheet">
</head>
<body>

  <div class="container">
    <div class="header">
      <ul class="nav nav-pills pull-right">
        <li class="active"><a href="index.php">Home</a></li>
        <li><a href="add_customer.php">Dodaj Firmę</a></li>
      </ul>
      <h3 class="text-muted">ITalents</h3>
    </div>

    <div class="row marketing">
      <div class="col-lg-12">

        <h2>KLIENCI</h2>
        <table class="table table-striped">

          <form name="search_form" method="POST" action="display_data.php">
            Search: <input type="text" name="search_box" value ="" />
            <input type="submit" name="search" value="Search the table..." >
          </form>

          <tr>
            <th>id</th>
            <th>Klient</th>
            <th>Email</th>
            <th>Email 2</th>
            <th>Data dodania do bazy</th>
          </tr>

          <?php while ($row = mysql_fetch_array($query)) {

            //Display customer info
            $output ='<tr>';
            $output .='<td>'.$row['id'].'</td>';
            $output .='<td>'.$row['nazwaKlienta'].'</td>';
            $output .='<td>'.$row['adresMail'].'</td>';
            $output .='<td>'.$row['adresMail2'].'</td>';
            $output .='<td>'.$row['dataDodania'].'</td>';
            $output .='<td><a href="edit_customer.php?id='.$row['id'].'" class="btn btn-default btn-sm">Edit</a></td>';
            $output .='<td><a href="delete_customer.php?id='.$row['id'].'" class="btn btn-default btn-sm">Delete</a></td>';
            $output .='</tr>';

            //Echo output
            echo $output;
            ?>
            <?php } ?>

          </table>

        </div>
      </div>

    </div>

    <div class="footer">
      <p>&copy; Company 2014</p>
    </div>

  </div> <!-- /container -->


</body>
</html>

anyway you should avoid using of mysql, i recommend you to learn mysqli or PDO. If you run this code on the newer version of PHP it will not works and also it will make your site vulnerable to SQL Injection. learning mysqli is as easy as mysql. — S M Jobayer Alam, Sep 17 '17 at 21:08
An update to @SMJobayerAlam, note, mysql as a DB is fine to use. The PHP `mysql_*` functions should not be used anymore though. — chris85, Sep 17 '17 at 21:22

MySQL PHP partial search

1 Answers1