PHP在mysql中搜索多个关键字

Question

嗨，我一直在尝试创建一个像搜索引擎这样的程序。这是我到目前为止所得到的:

https://imgur.com/a/4Cnut

我需要在选择选项上选择一种症状，然后添加它。
我添加的这些选项将开始搜索 Mysql 数据库上的每一行。我需要输出我添加的每个匹配关键字。这真的很痛苦，所以我想知道你将如何解决这个问题。

这是我的代码:

$x = 0;
for($x; $x < 10;$x++) {

  $selx = "SELECT * FROM sicks WHERE tags LIKE '%{$_POST['a0']}%' AND id = $x ";
  $sel1 = mysqli_query($connect,$selx);

  $selx = "SELECT * FROM sicks WHERE tags LIKE '%{$_POST['a1']}%' AND id = $x ";
  $sel2 = mysqli_query($connect,$selx);

  $selx = "SELECT * FROM sicks WHERE tags LIKE '%{$_POST['a2']}%' AND id = $x ";
  $sel3 = mysqli_query($connect,$selx);

  $selx = "SELECT * FROM sicks WHERE tags LIKE '%{$_POST['a3']}%' AND id = $x ";
  $sel4 = mysqli_query($connect,$selx);

  $selx = "SELECT * FROM sicks WHERE tags LIKE '%{$_POST['a4']}%' AND id = $x ";
  $sel5 = mysqli_query($connect,$selx);

  $selx = "SELECT * FROM sicks WHERE tags LIKE '%{$_POST['a5']}%' AND id = $x ";
  $sel6 = mysqli_query($connect,$selx);

  $selx = "SELECT * FROM sicks WHERE tags LIKE '%{$_POST['a6']}%' AND id = $x ";
  $sel7 = mysqli_query($connect,$selx);

  $selx = "SELECT * FROM sicks WHERE tags LIKE '%{$_POST['a7']}%' AND id = $x ";
  $sel8 = mysqli_query($connect,$selx);

  $selx = "SELECT * FROM sicks WHERE tags LIKE '%{$_POST['a8']}%' AND id = $x ";
  $sel9 = mysqli_query($connect,$selx);

  $selx = "SELECT * FROM sicks WHERE tags LIKE '%{$_POST['a9']}%' AND id = $x ";
  $sel10 = mysqli_query($connect,$selx);

  $c1 = mysqli_num_rows($sel1);
  $c2 = mysqli_num_rows($sel2);
  $c3 = mysqli_num_rows($sel3);
  $c4 = mysqli_num_rows($sel4);
  $c5 = mysqli_num_rows($sel5);
  $c6 = mysqli_num_rows($sel6);
  $c7 = mysqli_num_rows($sel7);
  $c8 = mysqli_num_rows($sel8);
  $c9 = mysqli_num_rows($sel9);
  $c10 = mysqli_num_rows($sel10);

  $q2 = mysqli_query($sel2);
  $q3 = mysqli_query($sel3);
  $q4 = mysqli_query($sel4);
  $q5 = mysqli_query($sel5);
  $q6 = mysqli_query($sel6);
  $q7 = mysqli_query($sel7);
  $q8 = mysqli_query($sel8);
  $q9 = mysqli_query($sel9);
  $q10 = mysqli_query($sel10);
  $q1 = mysqli_query($sel1);

  $row = mysqli_fetch_array($q2);
  $_SESSION['news'] = $row['tags'];

  $every = $c1 + $c2 + $c3 + $c4 + $c5 + $c6 + $c7 + $c8 + $c9 ;

  echo $every;
}

{$_POST['a0']}, {$_POST['a1']}, ... 是来自 select 选项的关键字。

我的 JavaScript:

$('.sendit').click(function() {
  a0 = $('.a0').val();
  a1 = $('.a1').val();
  a2 = $('.a2').val();
  a3 = $('.a3').val();
  a4 = $('.a4').val();
  a5 = $('.a5').val();
  a6 = $('.a6').val();
  a7 = $('.a7').val();
  a8 = $('.a8').val();
  a9 = $('.a9').val();
  a10 = $('.a10').val();

  $.ajax({
    url:"function.php?sendit=true",
    type:"post",
    data:{a0:a0,
    a1:a1,
    a2:a2,
    a3:a3,
    a4:a4,
    a5:a5,
    a6:a6,
    a7:a7,
    a8:a8,
    a9:a9,
    a10:a10},
    success:function(data) {
      $('.texta').html(data);
    }
  });

  return false;
});

Answer 1

我不知道你为什么要调用 mysqli_query 两次，也许是你代码的上下文。

我建议您始终让 SQL 服务器承担这些工作。接口(interface)语言(尤其是 php)可能不如 SQL 服务器高效。

我的建议是在 WHERE 子句中使用 OR 运算符，这样您就只发送一个查询。

$selx = "SELECT * FROM sicks WHERE
(tags LIKE '%{$_POST['a0']}%' OR 
tags LIKE '%{$_POST['a1']}%' OR 
tags LIKE '%{$_POST['a2']}%' OR 
) AND id = $x";

注意:此代码可通过 SQL 注入(inject)。我建议您对 $_POST 值使用 str_replace:

$_POST['a0'] = str_replace("'", "'", $_POST['a0']);

这将使所有单引号加倍，这些单引号已用于分隔字符串。

关于SQL injection .