I have a search box that I give the user to put in key words they want to search for. On submit, I split the words in the input box (if multiple) into an array and perform a query for each individual word. With SQL I can get results that have at least one of the values, the problem is that I want to only get rows that have at least all of the parameters in one column or another.
public function employeeSearchAction()
{
if ($this->user_permissions < 1) {
return 0;
}
// split search string into array based on spaces
if ($this->data != '') {
$searchParams = explode(' ', trim($this->data));
// get any rows with any one of these values in them
foreach ($searchParams as $s) {
$this->db->orWhere('user_firstname', '%'.$s.'%', 'like');
$this->db->orWhere('user_lastname', '%'.$s.'%', 'like');
$this->db->orWhere('user_email1', '%'.$s.'%', 'like');
$this->db->orWhere('user_phn_1', '%'.$s.'%', 'like');
$this->db->orWhere('user_phn_2', '%'.$s.'%', 'like');
$this->db->orWhere('user_phn_3', '%'.$s.'%', 'like');
// $this->db->orWhere('user_address1', '%'.$s.'%', 'like');
$this->db->orWhere('user_position', '%'.$s.'%', 'like');
$this->db->orWhere('user_type', '%'.$s.'%', 'like');
$this->db->orWhere('user_division', '%'.$s.'%', 'like');
// $this->db->orWhere('user_position', $s);
// $this->db->orWhere('user_type', $s);
// $this->db->orWhere('user_division', $s);
}
$this->db->having('owner_id', $this->owner_id);
$binaryArray = array_fill(0, sizeof($searchParams), 0);
$results = $this->db->get('tblusers');
$filtered = [];
// filter rows to ensure that all rows have every parameter
foreach ($results as $r) {
$binaryArray = array_fill(0, sizeof($searchParams), 0);
foreach ($binaryArray as $key=>$b) {
if (strpos(strtolower($r['user_firstname']), strtolower($searchParams[$key])) !== false ||
strpos(strtolower($r['user_lastname']), strtolower($searchParams[$key])) !== false ||
strpos($r['user_email1'], strtolower($searchParams[$key])) !== false ||
strpos($r['user_phn_1'], strtolower($searchParams[$key])) !== false ||
strpos($r['user_phn_2'], strtolower($searchParams[$key])) !== false ||
strpos($r['user_phn_3'], strtolower($searchParams[$key])) !== false ||
strpos(strtolower($r['user_position']), strtolower($searchParams[$key])) !== false ||
strpos(strtolower($r['user_type']), strtolower($searchParams[$key])) !== false ||
strpos(strtolower($r['user_division']), strtolower($searchParams[$key])) !== false
// strpos($r['user_address1'], strtolower($searchParams[$key])) !== false
) {
$binaryArray[$key] = 1;
}
}
if (!in_array(0, $binaryArray)) {
$filtered[] = $r;
}
}
} else {
$this->db->where('owner_id', $this->owner_id);
$filtered = $this->db->get('tblusers');
}
foreach ($filtered as $r) {
$user_phn_1 = $r['user_phn_1'] == '' ? 'N/A' : '<a href="tel:+1'.$r['user_phn_1'].'">'.preg_replace("/[^0-9]/", "", $r['user_phn_1']).'</a>';
$user_phn_2 = $r['user_phn_2'] == '' ? 'N/A' : '<a href="tel:+1'.$r['user_phn_2'].'">'.$r['user_phn_2'].'</a>';
$user_position = $r['user_position'] == '' ? 'N/A' : $r['user_position'];
$user_type = $r['user_type'] == '' ? 'N/A' : $r['user_type'];
$user_division = $r['user_division'] == '' ? 'N/A' : $r['user_division'];
$user_email1 = $r['user_email1'] == '' ? 'N/A' : '<a href="mailto:'.$r['user_email1'].'">'.$r['user_email1'].'</a>';
$user_address1 = $r['user_address1'] == '' ? 'N/A' : '<a href="https://maps.google.com/maps?q='.$r['user_address1'].'">'.$r['user_address1'].'</a>';
$user_firstname = $r['user_firstname'] == '' ? 'N/A' : $r['user_firstname'];
$user_lastname = $r['user_lastname'] == '' ? 'N/A' : $r['user_lastname'];
$r['is_ready'] != 1 ? $user_firstname .= ' <i title="Employee is not ready to work." class="orange fas fa-exclamation-triangle"></i>' : '';
echo "
<div class='search-form_row-container'>
<div class='col-xs-6 col-sm-6'>
<i class='fas fa-file-signature'>
<span class='results-text results-text--name'>$user_lastname $user_firstname</span>
</i>
</div>
<div class='col-xs-6 col-sm-6'>
<i class='fas fa-phone'>
<span class='results-text results-text--phone'>{$user_phn_1}</span>
</i>
</div>
<div class='col-xs-6 col-sm-6'>
<a href='/includes/sql/store/users/curr_user.php?id={$r['user_id']}&goto=/users/user_profile_main.php'>
<i class='fas fa-link'>
<span class='results-text results-text--edit'>View Profile</span>
</i>
</a>
</div>
<div class='col-xs-6 col-sm-6'>
<i class='fas fa-suitcase-rolling'>
<span class='results-text results-text--position'>$user_position</span>
</i>
</div>
<div class='col-xs-6 col-sm-6'>
<i class='fas fa-map-marker-alt'>
<span class='results-text results-text--type'>$user_type</span>
</i>
</div>
<div class='col-xs-6 col-sm-6'>
<i class='fas fa-i-cursor'></i>
<span class='results-text results-text--division'>$user_division</span>
</i>
</div>
</div>
";
}
}
What I have functions as it should but I can't help noticing how unbelievably ugly and inefficient it is. Looking at the code you'll know I'm not using raw queries, I'm using https://github.com/ThingEngineer/PHP-MySQLi-Database-Class Mysqlidb. If you'd like to forego phrasing your answer in that format, it's fine, this library can use raw queries. Any help is appreciated. I just would rather only retrieve records that hold all of the parameters instead of records that hold at least one and then filtering them out with PHP.
Edit: My intent is to be able to just do with this SQL
