使用php搜索表单以匹配所有单词和特定单词。

Question

我有一个使用select标记的简单搜索表单，它允许您在数据库中搜索特定列中匹配的单词。然而，当所有的列输入任何单词时，我似乎无法搜索它们。例如，如果在任何地方选择“全部”并输入“动物”这个词，它就会抓住它。我觉得它应该是相当直接的，但似乎不能把我的头围绕着它。提前感谢您的帮助，非常感谢。

<form id="search_form" method="POST" name="searchTeachers">
        <fieldset>
            <div>
            <label for="mebook-category">Search teachers by:</label>
            <select name="teacher-category">
                <option value="">All</option>
              <option value="school_name">School</option>
              <option value="school_board">School Board</option>
              <option value="country">Country</option>
              <option value="state">Province/State</option>
              <option value="city">City</option>
            </select>
            </div>
            <input type="text" name="keyword-search" value="<?php echo $keyword_search ?>">
        </fieldset>
        <fieldset>
            <button type="submit" name="search" value="search"><span>Search</span></button>
        </fieldset>
    </form>

//PHP

SELECT * FROM members_teachers WHERE $teacher_category LIKE CONCAT('%',?,'%') AND approved = '2'
$stmt->bind_param('s', $keyword_search)  or die('Try Again');
/* Execute it */
$stmt->execute();
/*Store Results*/
$stmt->store_result();
/* Get Number of Rows */
$num_of_rows = $stmt->num_rows;
/* Bind the Results */
$stmt->bind_result($teacher_id, $username, $password1, $password2, $email, $first_name, $last_name, $school_name, $school_board, $city, $country, $state, $occupation, $expertise1, $expertise2, $expertise3, $focus, $description, $avatar, $website, $facebook, $linkedin, $skype, $twitter, $approved, $date);

Answer 1

在搜索“All”时，似乎需要一个单独的查询，因为您的查询只检查一个数据库字段。只需测试“all”，并使用“OR”将查询扩展到所有字段

SELECT * FROM members_teachers
WHERE (school_name LIKE CONCAT('%',?,'%')
       OR school_board LIKE CONCAT('%',?,'%')
       OR country LIKE CONCAT('%',?,'%')
       OR state LIKE CONCAT('%',?,'%')
       OR city LIKE CONCAT('%',?,'%')
)
AND approved = '2'

恐怕您还需要绑定额外的params，直到mysqli允许命名params为止(没有检查)。