1

I am having an issue where I can not search for the exact data I want. I can search the database fine and return all the values in the format I want. However i want to add in the ability to search by a company's name and also display those results in the same table. The companys Names are not stored in the databse but are in the Array session pulled from an API. We store the ID for each company in the database which is set as the Keys from the array.

I want to know if there is any way to get results given form the array as well as the database search. I know i can do it with a foreach loop around all my SQL SELECT Statement, but the issue with that is if there is no match in the array it will not run any search, making the rest of the search very limited as searching by IP Address is one of the main reasons for this. the only way I think it is possible is adding a foreach loop into the SQL statement but I don't see how that is possible nor can i find any documentation on it online. please help!

Here is my code that runs based on an input from a search box sent by 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>";
}

?>
Sam Hester
  • 23
  • 6
  • Your query `$view4` is wrong , Sam. – Niklesh Raut Jul 28 '16 at 10:07
  • Check this : http://stackoverflow.com/a/303770/2815635 – Niklesh Raut Jul 28 '16 at 10:09
  • I am aware the query is wrong, it worked fine before I added in the parts after like %search%. but i do not know how i am to combine the 2 search's, that is the question. that foreach php inclusion is what i thought is maybe possible but iI dont know if it is, thanks – Sam Hester Jul 28 '16 at 10:16
  • Did you try solution given in above link ? – Niklesh Raut Jul 28 '16 at 10:17
  • Or simply give a try for just learning, user conditions like this `where siteName LIKE '%$search%' OR owner LIKE '%$search%' OR hostName LIKE '%$search%'` And then follow as in given answer in above link – Niklesh Raut Jul 28 '16 at 10:19
  • I still need it to run through the array though, and i cant do that before the SQL as that will limit the search specifically to if the array gave results. how do i get the foreach loop to run within the mysql statement? – Sam Hester Jul 28 '16 at 10:24

1 Answers1

0

Solved the issue by adding in a matchingcompanies field before and setting it to an exceeding long string with lots of OR's, i will limit the search to only start after 3 characters inputed so the string return is not so large.

//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);
Sam Hester
  • 23
  • 6