Home>

search system that can perform partial search from multiple items in mysql and php

I have been using PDO and bindValue to update the search system from multiple items in MySQL and PHP that I used before
If implemented, it will not work. I would appreciate your help.
Since the column item contains multiple characters, LIKE partial search is incorporated.

Old PHP&HTML code 
// search.php code
function getUserData ($params) {
  // DB connection information
  include_once ('database.php');
  // Create DB connector
  $Mysqli = new mysqli ($dsn, $usr, $passwd);
  $Mysqli->set_charset ('utf8');
  $query = "SELECT * FROM TABLE1";
  // Check connection status
  if ($Mysqli->connect_error) {
    error_log ($Mysqli->connect_error);
    exit;
  }
  // Generate SQl statement from entered search conditions
  $where = [];
  if (! empty ($params ['search_name'])) {
    $where [] = "search_name like '% {$params [' search_name ']}%'";
  }
  if (! empty ($params ['color'])) {
    $where [] = "color like '% {$params [' color ']}%'";
  }
  if (! empty ($params ['category'])) {
    $where [] = "category like '% {$params [' category ']}%'";
  }
  if ($where) {
    $whereSql = implode ("AND", $where);
    $sql = "select * from TABLE1 where". $whereSql;
  } else {
    $sql = "select * from TABLE1";
  }
  $query. = $where;
  // Execute SQL statement
  $UserDataSet = $Mysqli->query ($sql);
  // Change to a manageable form
  $result = [];
  while ($row = $UserDataSet->fetch_assoc ()) {
    $result [] = $row;
  }
  return $result;
}// ー ー ー HTML side ー ー ー
<! doctype html>
<? php
include_once ('search.php');
$userData = getUserData ($_POST);
?>
<html>
<head>
</head>
<body>
// Search by 3 types: keyword search, check box, selection
<Input name = "search_name" value = "<? Php echo isset ($_ POST ['search_name'])? Htmlspecialchars ($_ POST ['search_name']): ''?>">
   <input type = "checkbox" name = "color" value = "white"<? php echo isset ($_ POST ['color'])&&$_POST ['color'] == 'white'? 'selected': ' '? >><label for = "color">white</label>// other omitted
<Select name = "category">
   <option value = "0"<? php echo empty ($_ POST ['category'])? 'selected': ''? >>Category A</option>// Other omitted
   </select>
   <button type = "submit" name = "search">Search</button>
*
// Display results as data other than above
   <? php if (isset ($userData)):?>
   <? php foreach ($userData as $row):?>
     ">
     <? php echo htmlspecialchars ($row ['original_name'])?>
   <? php endforeach;?>
   <? php endif;?>
</body>
</html>
After revision 
// search.php code
function getUserData () {
  include_once ('database.php');
   try {
    $db = new PDO ($dsn, $usr, $passwd);
    $db->setAttribute (PDO :: ATTR_ERRMODE, PDO :: ERRMODE_EXCEPTION);
    $db->setAttribute (PDO :: ATTR_EMULATE_PREPARES, false);
    $q = (string) filter_input (INPUT_GET, 'q');
    $maxKeywords = -1;
    $keywords = preg_split ('/ (?: \ p {Z} | \ p {Cc}) ++/u', $q, $maxKeywords, PREG_SPLIT_NO_EMPTY);
    if ($keywords) {
      foreach ($keywords as $keyword) {
        $holders [] = "((search_name LIKE? ESCAPE '!') OR (color LIKE? ESCAPE '!') OR (category LIKE? ESCAPE '!'))";
        $values ​​[] = $values ​​[] = '%'. preg_replace ('/ (? = [! _%]) /', '!', $keyword). '%';
      }
      $sql = "SELECT * FROM TABLE1 WHERE ('. implode (' AND ', $holders).')";
      $UserDataSet = $bd->prepare ($sql);$UserDataSet->execute ($values);
      $userData = $UserDataSet->fetchAll (PDO :: FETCH_ASSOC);
    }
    print 'Connection succeeded';
  } catch (PDOException $e) {
    header ('Content-Type: text/plain;charset = UTF-8', true, 500);
    exit ('Could not connect'. $e->getMessage ());
  }
  function h ($str) {
    return htmlspecialchars ($str, ENT_QUOTES, 'UTF-8');
  }
}
 // ー ー ー HTML side ー ー ー
<! doctype html>
<? php
include_once ('search.php');
$userData = getUserData ($_GET);
?>
<html>
<head>
</head>
<body>
// Search by 3 types: keyword search, check box, selection
<Input name = "search_name" value = "<? Php echo isset ($search_name)? = H ($search_name): ''?>">
   <input type = "checkbox" name = "color" value = "white"<? php echo isset ($color)&&$color == 'white'? 'selected': ''? >><label for = "color ">White</label>// other omitted
<Select name = "category">
   <option value = "0"<? php echo empty ($category)? 'selected': ''? >>Category A</option>// Other omitted
   </select>
   <button type = "submit" name = "search">Search</button>
*
≪Result display with data other than the column specified in // $holders []
   <? php if (isset ($err)):?>
      <p><? = h ($err->getMessage ());?></p>
    <? php endif;?>
    <? php if (0<count ($userData)):?>
      <? php foreach ($userData as $row):?>
        
           ">
          <? = h ($row ['original_name']);?>
      
      <? php endforeach;?>
    <? php endif;?>
</body>
</html>
php html mysql pdo search
  • Answer # 1

    Addition and correction request field

      

    Thank you very much. Although it does not work well, the upper part of the revision, the database can be read, but the data below "// query data" cannot be implemented, and the search result is written as 1.
      We look forward to advice.

    I'm still not sure to say

    , but

    addcslasheswas misunderstood asaddslashes, so removed

    In the first place,

    // Change to a manageable form
  $result = [];
  while ($row = $UserDataSet->fetch (PDO :: FETCH_ASSOC)) {
    $result [] = $row;
  }
  return $result;
  $UserDataSet->execute ();

    and
    SQL is executed
    $UserDataSet->execute ();
    However, the SELECT statement is not executed because it is written afterreturn.

    We recommend that you first understand the PDO behavior with the minimum functionality and code, referring to the PHP manual.

    $qPart = array_fill (0, count ($userData), "(?,?,?,?,?)");

    ↑$userDatais undefined

    foreach ($userData as $row) {// bind the values ​​one by one

    ↑ The processing in foreach is probably not executed

    $stmt->bindValue ($i ++, $row ['%'. preg_replace ('/ (? = [! _%]) /', '!', $search_name). '%'], PDO :: PARAM_STR);

    ↑$stmtis also undefined, so if the foreach process is executed, an error will occur

  • Answer # 2


    regexp is easier to write
    (However, escape processing is required when including regexp wildcards.)

  • Answer # 3

    For now, there is not enough data or information to check the operation, so only the points of interest

    setAttribute (PDO :: ATTR_ERRMODE, PDO :: ERRMODE_EXCEPTION);
        $db->setAttribute (PDO :: ATTR_EMULATE_PREPARES, false);
        $q = filter_input (INPUT_GET, 'q');
        $maxKeywords = -1;
        $keywords = preg_split ('/ (?: \ p {Z} | \ p {Cc}) ++/u', $q, $maxKeywords, PREG_SPLIT_NO_EMPTY);
        if (count ($keywords)>0) {
            $values ​​= [];// add
            foreach ($keywords as $keyword) {
                $holders [] = "((search_name LIKE? ESCAPE '!') OR (color LIKE? ESCAPE '!') OR (category LIKE? ESCAPE '!'))";
                // $values ​​[] = $values ​​[] = '%'. preg_replace ('/ (? = [! _%]) /', '!', $keyword). '%';
                $values ​​[] = '%'. preg_replace ('/ (? = [! _%]) /', '!', $keyword). '%';
            }
            // $sql = "SELECT * FROM TABLE1 WHERE ('. implode (' AND ', $holders).')";
            $sql = 'SELECT * FROM TABLE1 WHERE ('. implode ('AND', $holders). ')';
            // $UserDataSet = $bd->prepare ($sql);
            $UserDataSet = $db->prepare ($sql);
            $UserDataSet->execute ($values);
            // $userData = $UserDataSet->fetchAll (PDO :: FETCH_ASSOC);
            return $UserDataSet->fetchAll (PDO :: FETCH_ASSOC);
        }
        // print 'Connection succeeded';
    } catch (PDOException $e) {
        header ('Content-Type: text/plain;charset = UTF-8', true, 500);
        exit ('Could not connect'. $e->getMessage ());
    }
// function h ($str)
// {
// return htmlspecialchars ($str, ENT_QUOTES, 'UTF-8');
//}
}
function h ($str)
{
    return htmlspecialchars ($str, ENT_QUOTES, 'UTF-8');
}

Related articles