我遇到了一个问题,无法搜索到我想要的确切数据。我可以很好地搜索数据库,并以我想要的格式返回所有值。然而,我想添加按公司名称搜索的功能,并在同一个表中显示这些结果。companyNames不存储在数据库中,而是存储在从API获取的Array会话中。我们将每个公司的ID存储在数据库中,该数据库设置为数组中的密钥。
我想知道是否有任何方法可以从数组和数据库搜索中获得给定的结果。我知道我可以用foreach循环来完成所有SQL SELECT语句,但问题是,如果数组中没有匹配项,它将不会运行任何搜索,这使得搜索的其余部分非常有限,因为按IP地址搜索是造成这种情况的主要原因之一。我认为唯一可行的方法是在SQL语句中添加foreach循环,但我看不出这是怎么可能的,也无法在网上找到任何文档。请帮忙!
这是我的代码,它基于AJAX发送的搜索框中的输入运行。
// Start sessions, Connected to DB set customer array
session_start();
$conn = connect();
$customers = $_SESSION['array'];
// if ajax posted search input
if($_POST){
$search = mysqli_real_escape_string($conn, $_POST['search']);
//set a second input for checking against customers array and format to give results
$input = $search
$input = strtolower($input);
$input = "'$input'";
$result = preg_grep($input, $customers);
$arrayk = array_keys($result);
//SELECT Search mysql Query
$matchingcompanies = "";
foreach($arrayk as $id)
{
$matchingcompanies .= "'" . $id . "' OR ";
}
$matchingcompanies = substr($matchingcompanies,0,strlen($matchingcompanies)-4);
$query1 = "SELECT * FROM IPV4_linked WHERE `companyId` = (" . $matchingcompanies . ") OR concat(ipv4Address, ipv4Subnet, hostName, owner, siteName) like '%" . $search . "%'";
$view4 = mysqli_query($conn, $query1);
//Table Title row
echo "<div><table name ='ipv4' class='tableparent'><td class='tabled'>IP Address</td><td class='table'>Subnet</td><td class='table'>PC Name</td><td class='table'>Owner</td><td class='table'>Customer</td><td class='table'>Customer Site</td></tr>";
//loops through search results and echo table
while ($row = mysqli_fetch_array($view4)){
$id = $row['companyId'];
$company = $customers[$id];
echo "<tr><td class='tabled'>". $row['ipv4Address']."</td><td class='table'>". $row['ipv4Subnet']."</td><td class='table'>".$row['hostName']."</td><td class='table'>". $row['owner']."</td><td class='table'>".$company."</td><td class='table'>".$row['siteName']."</td></tr>";
}
echo"</table></div>";
}
?>
通过在之前添加matchingcompanies字段并将其设置为具有大量OR的超长字符串来解决问题,我将限制搜索仅在输入3个字符后开始,这样字符串返回就不会那么大。
//SELECT Search mysql Query
$matchingcompanies = "";
foreach($arrayk as $id)
{
$matchingcompanies .= "'" . $id . "' OR ";
}
$matchingcompanies = substr($matchingcompanies,0,strlen($matchingcompanies)-4);
$query1 = "SELECT * FROM IPV4_linked WHERE `companyId` = (" . $matchingcompanies . ") OR concat(ipv4Address, ipv4Subnet, hostName, owner, siteName) like '%" . $search . "%'";
$view4 = mysqli_query($conn, $query1);