Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

PHP

James Barrett
James Barrett
13,253 Points
Posted by James Barrett
James Barrett
13,253 Points

Struggling with maintainability on this MySQLi/PHP code segment

Hi there,

I have a piece of code that works, however it's pretty messy. The objective is to retrieve all the relevant rows from a user search. For example: If the user searches 'Jo', it will retrieve John and Josh:

The code that works:

if($_SERVER["REQUEST_METHOD"] == "POST") {
  $search = trim(filter_input(INPUT_POST,"user_search",FILTER_SANITIZE_SPECIAL_CHARS));
  $search = preg_replace("#[^0-9a-z]#i", "", $search);

  if(!isset($error_message)) {
    $query = $db->query("SELECT * FROM User WHERE U_Forename LIKE '%$search%'
                        OR U_Surname LIKE '%$search%' OR U_Team LIKE '%$search'
                        OR U_City LIKE '%$search%'");
  }
}

  <?php
          if ($query->num_rows == 0) {
            $output = "No results found!";
          } else {
            echo "<h2>Search results for: " . $search . "</h2>";
            while($row = $query->fetch_assoc()) {
              $id = $row['U_ID'];
              $firstname = $row['U_Forename'];
              $lastname = $row['U_Surname'];
              $team = $row['U_Team'];
              $city = $row['U_City'];
              echo "<div class='searchresults'>";
              echo "<p> Name: " . ucfirst($firstname) . " " . ucfirst($lastname) . "</p>";
               echo "<p> Favourite Team: " . ucfirst($team) . "</p>";
              echo "<p> Location: " . ucfirst($city) . "</p>";
              echo "<a class='lift' href='profile.php?=[$id]'>View Profile</a>";
              echo "<a class='lift' href='#'>Follow Driver</a>";
              echo "</div>";
            }
          }
          ?>

What I have tried:

function getSearchResults($search) {
  global $db;
  $searchArray = array();
  $query = $db->query("SELECT * FROM User WHERE U_Forename LIKE '%$search%'
                      OR U_Surname LIKE '%$search%' OR U_Team LIKE '%$search'
                      OR U_City LIKE '%$search%'");
  if ($query->num_rows == 0) {
    echo "No results found!";
  } else {
    while($row = $query->fetch_assoc()) {
      $searchArray['U_ID'] = $row['U_ID'];
      $searchArray['U_Forename'] = $row['U_Forename'];
      $searchArray['U_Surname'] = $row['U_Surname'];
      $searchArray['U_Team'] = $row['U_Team'];
      $searchArray['U_City'] = $row['U_City'];
    }
  }
  return $searchArray;
}

However this will not work as only the first result from the fetch_assoc() is stored in the $searchArray and nothing else! Does anyone have any suggestions? I have stuck on this for so long! :(

James.

1 Answer

Chris Shaw
Chris Shaw
26,676 Points
Chris Shaw
Chris Shaw
26,676 Points

Hi James,

You can solve the issue you're having by using the U_ID key you're setting as an array index and then assigning the additional key/value pairs as values in a nested array. See the below.

while ($row = $query->fetch_assoc()) {
  $searchArray[$row['U_ID']] = array(
    'U_Forename'  => $row['U_Forename'],
    'U_Surname'   => $row['U_Surname'],
    'U_Team'      => $row['U_Team'],
    'U_City'      => $row['U_City']
  );
}

What is going on? We're reusing U_ID so its now our array index for this iteration of the while loop, and then assigning the existing key/value pairs to a nested array that is assigned as the value of our new array key.

How do we access U_ID now?

Very simply, since arrays are most commonly key/value pairs, we can use a foreach loop for iterate over each value.

foreach ($searchArray as $key => $attrs) {
  // $key   = U_ID
  // $attrs = U_Forename, U_Surname, U_Team, U_City
}

You can further optimise your code by restricting the columns you return in your query too as shown below.

$query = $db->query("SELECT U_ID as ID, U_Forename as Firstname, U_Surname as Lastname, U_Team as Team, U_City as City
  FROM User
  WHERE U_Forename LIKE '%$search%'
  OR U_Surname LIKE '%$search%' OR U_Team LIKE '%$search'
  OR U_City LIKE '%$search%'");

while ($row = $query->fetch_assoc()) {
  array_push($searchArray, $row);
}

foreach ($searchArray as $attrs) {
  // $attrs == ID, Firstname, Lastname, Team, City
}

Either way, the same result is achieved but personal preference is usually the best way to go.

Hope that helps.

James Barrett
James Barrett
13,253 Points
James Barrett
James Barrett
13,253 Points

Hi! Thanks for your detailed answer. Im a little stuck on how to display the information to the screen using the foreach. I have called the getSearchResults() function in another file and stored the array result in a variable named $searchResult. I understand that the U_ID is being indexed and the corresponding values are being displayed (using var_dump() helped me understand this). I am just unsure on whats happening in the variable assignment on the body of the foreach - how would I echo the values to the screen? Thanks.

Chris Shaw
Chris Shaw
26,676 Points
Chris Shaw
Chris Shaw
26,676 Points

You can echo your results out using your existing code in a similar way, as you have said you use a function which you can use to return the $searchArray from and then use the result of the call to getSearchResults to render your results.

$results = getSearchResults();

foreach ($results as $key => $attrs) {
  echo "<div class='searchresults'>
    <p>Name: " . ucfirst($attrs['U_Forename']) . " " . ucfirst($attrs['U_Surname']) . "</p>
    <p>Favourite Team: " . ucfirst($attrs['U_Team']) . "</p>
    <p>Location: " . ucfirst($attrs['U_City']) . "</p>
    <a class='lift' href='profile.php?=[$key]'>View Profile</a>
    <a class='lift' href='#'>Follow Driver</a>
  </div>";
}

This is for the first example I gave, if you wanted to use the second example you would simply need to remove $key as a variable and change it in the HTML to $attrs['U_ID'].

$results = getSearchResults();

foreach ($results as $attrs) {
  echo "<div class='searchresults'>
    <p>Name: " . ucfirst($attrs['U_Forename']) . " " . ucfirst($attrs['U_Surname']) . "</p>
    <p>Favourite Team: " . ucfirst($attrs['U_Team']) . "</p>
    <p>Location: " . ucfirst($attrs['U_City']) . "</p>
    <a class='lift' href='profile.php?=[{$attrs['U_ID']}]'>View Profile</a>
    <a class='lift' href='#'>Follow Driver</a>
  </div>";
}

Hope that helps.