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

Question

I have a simple search form using a select tag that allows you to search database for words that match in specific columns. However I can't seem to search by all the columns when they input any word. for example if there select all and input animals anywhere the word animals shows up it will grab it. I feel like it should be fairly straight forward but can't seem wrap my head around it. Thanks in advance for any help, greatly appreciated.

<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

It looks like you need a seperate query when searching for 'All' because your query only checks one database field. Just test for 'All' and extend the query to all fields using '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'

I'm afraid you also need to bind additional params until mysqli allows named params (didn't check).