PHP MySQL:搜索字符串(数组中)缺少的结果

Question

我正在创建一个特性，用于搜索数据库中的字符串。PHP使用PHP验证器没有显示错误，如果搜索项不存在，则返回正确的错误。我的问题是，当在题为“配置”的数据库列中搜索“匆匆放弃”一词(这是当前数据库中唯一的条目)时，不返回结果。虽然我可以使用phpMyAdmin看到这个条目确实存在。

用户使用以下HTML将字符串输入到输入字段：

<form action='http://www.murkyfiles.esy.es/search.php' method='GET'>
  <center>
    <p><label for='search'>Please enter your question as accurately as possible:</label></p>
    <p><input type='search' size='90' name='search'></p>
    <p><input type='submit'  name='submit' value='Find answer'></p>
  </center>
</form>

在数据库中使用以下PHP搜索输入的术语：

<?php
$button = $_GET [ 'submit' ];
$search = $_GET [ 'search' ];
$host = "[HOST URL]";
$username = "[USERNAME]";
$password = "[PASSWORD]";
$database = "[DATABASE]";
$searchlength = strlen($search);

if( !$button )
  echo "You didn't submit a keyword";
else {
  if( strlen( $search ) <= 1 )
    echo "Search term too short";
  else {
    echo "You searched for <b> $search </b> <hr size='1' > </ br > ";

    // Connect to database

    $con = mysqli_connect ( $host, $username, $password );

    if(!$con) {
        die('Could not connect: ' .PDO::errorInfo());
    }

    mysqli_select_db ( $con, $database );

    $search = str_split($search, $searchlength);

    $construct = " SELECT * FROM 'coll_test' WHERE collocation LIKE '%$search%' ";

    $run = mysqli_query( $con, $construct );

    //Fetch and return search results.

    if ($foundnum == 0)
        echo "Sorry, there are no matching results for <b> $search[0] </b>.
        </ br >
        </ br > 1. Try presenting your Something is wrong in a more academic manner. Guidance can be found on the majority of University websites without need for registration.
        </ br > 2. Try more common words/phrases with similar meaning. This search focuses on colloquialisms - commonly used phrases within a language.
        </ br > 3. Please check your spelling";

    else {
      echo "$foundnum results found !<p>";

      while ( $runrows = mysqli_fetch_assoc($run) ) {
          $collocation = $runrows ['collocation'];
        echo "<a href='$url'> <b> $title </b> </a> <br> $desc <br> <a href='$url'> $url </a> <p>";
      }
    }
  }
}

我曾看过各种类似的问题，但没有一个能解决问题。

为了澄清，数据库表列标题如下：

google-results bing-results yahoo-results url-链接E 218E 119wiki>E 220{##**$$}>E 121日期E 222

到目前为止，我的数据库中只有一个条目：

collocation = abandon hastily
left = abandon
right = NULL
length = 2
google-results = 24000000
bing-results = 386000
yahoo-results = 385000
url-link = oxforddictionary.so8848.com/search1?word=abandon
wiki = 0
date = [TIMESTAMP]

Answer 1

一遍又一遍的重写，下面是注释：

$con = mysqli_connect ( $host, $username, $password, $database );

//$search = str_split($search, $searchlength); ///??? See below
$searchSafe = preg_replace("/[^0-9a-z-_ ]/i","",$search); //example only.

$construct = " SELECT COUNT(*) AS found FROM `coll_test` 
WHERE collocation LIKE '%".$searchSafe."%' ";

$run = mysqli_query($con, $construct);
$result = mysqli_fetch_array($run);

print $result['found']." number of results found!"; //for example.

1)可以在MySQLi连接函数中包含数据库引用。

2) str_split返回一个数组，但是您将结果作为字符串使用。这是混乱和不正确的，你打算用这个做什么？

• $_GET['search']将始终是字符串类型，因此不需要将其用作数组或任何基于数组的杂乱处理。

3)手动返回number_rows计数的外部函数可能是不准确的，COUNT在SELECT状态中。

4)忘记返回实际查询的结果！因此，上面我插入了一个mysql_fetch_array结果来查看结果的数量。您也没有为您的$foundnum变量定义值。

5)将PDO与MySQLi混合，这两种连接方法是互斥的。它们不混合。

6)您很容易受到SQL注入和数据库危害，您需要使用准备好的语句(以及Saty示例)并使用类似于preg_replace (或另一个REGEX解析器)的方法从字符串中删除无效字符，例如：

$searchSafe = preg_replace("/[^0-9a-z-%_ ]/i","",$search); //example only.

以上表示字符串中只允许0-9或are (大小写不敏感，/i)或-、%或_。

( 7)表名或列名('coll_test')不应用单引号括起来，而应放在后面(如果有的话)。在MySQL中，单引号用于包含数据字符串，只用于。

Answer 2

换掉引号、表单、表和列名，使用回勾，您的代码是为sql注入、用户准备和绑定语句打开的，以防止出现这种情况。

$search = $_GET ['search'];// get your value
$like = "%$search%";//
$stmt = $con->prepare("SELECT `collocation`,`left`,`right` FROM `coll_test` WHERE collocation LIKE ?");
$stmt->bind_param('s', $like);
$stmt->execute();
$stmt->bind_result($collocation,$left,$right);
$rows = $stmt->num_rows;// check your query return result of not
if ($rows > 0) {

    while ($stmt->fetch()) {// fetch data from query
       printf ("%s (%s)\n", $collocation,$left,$right);
       // fetch data form result set
    }
} else {
    echo "Sorry, there are no matching results for <b> $search </b>.";
}