How do I output information from different search fields?

Question

I have the following form with three search fields. Two text inputs (keyword and distance) and a dropdown list (transportation).

My search function works with the keyword text input, but not the other two fields. I can see that data is being output when I "echo", but it won't actually search for those terms.

    <form action="searchresults.php" method="POST">
  <h3> Keyword </h3>
    <input type="text" name="keyword-search">

  <h3> Primary Function </h3>
  <?php
  $sql = "SELECT pf_id, primary_function FROM primary_function ORDER BY pf_id;";
  $result = mysqli_query($conn, $sql);

  echo "<select name='function-search' id = 'function-search'>";
  echo '<option value=""></option>';

  while ($row = mysqli_fetch_assoc($result)) {
                unset($id, $name);
                $id = $row['pf_id'];
                $name = $row['primary_function'];
                echo '<option value="'.$name.'">'.$name.'</option>';

              }
  echo "</select>";
  ?>

  <h3> Distance </h3>
    within <input type="text" name="distance-search"> miles of PCC
    <br>
    <button type="submit" name="submit-search">Search</button>
</form>

The following is where the data is being pushed to.

    <?php
if (isset($_POST['submit-search'])) {

$functionSearch = mysqli_real_escape_string($conn, $_POST['function-search']);
$keySearch = mysqli_real_escape_string($conn, $_POST['keyword-search']);
$distanceSearch = mysqli_real_escape_string($conn, $_POST['distance-search']);
$sql = "SELECT * FROM resource WHERE resource_name LIKE '%$keySearch%'
OR  description LIKE '%$keySearch%' OR username LIKE '%$keySearch%'
OR primary_function LIKE '%$keySearch%'
OR distance_in_miles LIKE '%$keySearch%'
ORDER BY distance_in_miles";
$result = mysqli_query($conn, $sql);
$queryResult = mysqli_num_rows($result);
// Checking for search result page errors
//echo $result;
echo $queryResult;
// echo $functionSearch;

if ($queryResult > 0){
    while ($row = mysqli_fetch_assoc($result)) {
      echo "<tr><td>". $row['resource_id'] ."</td><td>". $row['resource_name'] ."</td><td>". $row['username'] ."</td><td>". "$" .$row['cost_in_usd'] ."/". $row['cost_per'] ."</td><td>". $row['distance_in_miles'] ."</td></tr>";
    } echo "</table>";
  } else {
    echo "<br>No results matching your search";
  }
}
?>

Sorry for the jargon. It won't search for those terms even though the information is being output. — howthegodschill, Dec 13 '18 at 21:51
You do not have squiggly brackets in your foreach loop so it just loops the sql assignment. — Nawed Khan, Dec 13 '18 at 22:04
There appears to be code missing here. Mostly for what appears to be a form. — Funk Forty Niner, Dec 13 '18 at 22:40
@funkfortyniner I didn't even realize that my entire was missing. Thank you. — howthegodschill, Dec 13 '18 at 22:58
Thanks. See if there's anything with enabling error reporting and using `mysqli_error($conn)` on the query/queries, looking at the HTML source also which will reveal what's in the form being used with the query in there. You can also echo your query to see what's being passed in there (or not). — Funk Forty Niner, Dec 13 '18 at 23:02
@FunkFortyNiner So, I've narrowed the issue down to my array. Whichever value is last, that's the data that is searched. — howthegodschill, Dec 13 '18 at 23:51
Ok, and that's what I thought would be at fault I'd try and remove the array you've passed the variables into and by assigning different variables instead. — Funk Forty Niner, Dec 13 '18 at 23:54
@howthegodschill I posted something for you to look at below in the answers area. It was too long for a comment. I hope this helps. — Funk Forty Niner, Dec 14 '18 at 00:46

score 2 · Answer 1 · answered Dec 14 '18 at 00:45

Side note: This is too long for a comment.

I would remove the foreach loop and use separate variables instead.

Right now, your foreach would show something like search1search2search3 in each of the LIKE queries, which won't thrown an error per se, it just won't (probably) match anything.

Here's an example, and replace the $search_x's with the criterias/POSTs of your choice:

$sql = "SELECT * FROM resource WHERE resource_name LIKE '%$search_1%'
        OR description LIKE '%$search_2%' OR username LIKE '%$search_3%'
        OR primary_function LIKE '%$search_4%'
        OR distance_in_miles LIKE '%$search_5%'
        ORDER BY distance_in_miles";

$result = mysqli_query($conn, $sql);
if(!$result){
   // handle no results/failure here
}

You may also have to play around with the %'s by either using one at both ends, at the beginning and/or at the end.

Side note: As stated in comments, always use proper bracing with any type of looping function.

By the way, I suggest that you use a prepared statement for this instead. real_escape_string() can be bypassed and here's a Q&A on the subject:

SQL injection that gets around mysql_real_escape_string()

I appreciate the help. So I've adjusted the code. At this point I can only get '%keySearch%' to work properly for all five of the variables within the $sql function. If I add $functionSearch or $distanceSearch, the function still doesn't recognize them in the search. — howthegodschill, Dec 14 '18 at 05:43
@howthegodschill (welcome) - *Hmm....*, it's hard for me to figure out why that would be. There could be space somewhere that's introduced in the query (or input) or in the value(s) of the record(s), or it's case-sensitive or a combination maybe. If it's a space introduced during the input, try using `trim()`. If none of those are the issue, then I'd be baffled (lol). Also try playing around with the `%`'s that mentioned in the answer also. — Funk Forty Niner, Dec 14 '18 at 12:58
@howthegodschill another thing would probably be that some of the values don't match (some of) the column types. One of your columns appears to have distances that suggests integers and not a string. — Funk Forty Niner, Dec 14 '18 at 13:15

How do I output information from different search fields?

1 Answers1