Searching a Database for and input aswell as array values in one search PHP, MYSQL, AJAX

Question

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>";
}

?>

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
– Sam Hester, Commented Jul 28, 2016 at 10:16
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
– Niklesh Raut, Commented Jul 28, 2016 at 10:19

Sam Hester · Accepted Answer · 2016-07-28 12:25:44Z

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);

answered Jul 28, 2016 at 12:25

Sam Hester

236 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Searching a Database for and input aswell as array values in one search PHP, MYSQL, AJAX

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related