I have a database with only one table which has around 2 Million rows and 60 columns. I created a php application with multiple search filters like name, country, state, surname, domain etc which will filter and display the result in HTML table.

But the problem is, every text field is accepting only one input. I want to give multiple inputs in every text field separated by commas.

Eg: If I search for name: Harry Job: marketing I get the results . But i want a solution for City: Mumbai Job: marketing, Webdesigners, QA. (Multiple inputs)

Php code for sql query.

 $res = false;

    $Country =$_POST['Country'];
    $Website =$_POST['Website'];
    $sql=" SELECT * FROM hz WHERE FirstName like '%".$FirstName."%' AND LastName LIKE '%".$LastName."%' AND Function LIKE '%".$Function."%' AND State LIKE '%".$State."%' AND Country LIKE '%".$Country."%' AND Website LIKE '%".$Website."%'";
    $q=mysqli_query($con, $sql);
  $res = true;

Php Search Filter code

<form method="post" class="search">
<table width="200">
   <tr><input class="form__input" type="search" name="FirstName" placeholder="First Name" value="<?php if(isset($FirstName)) echo $FirstName;?>" /></tr>
   <tr><input class="form__input" type="search" name="LastName" placeholder="Last name" value="<?php if(isset($LastName)) echo $LastName;?>" /></tr><BR>
   <tr><input class="form__input" type="search" name="Function" placeholder="Function" value="<?php if(isset($Function)) echo $Function;?>" /></tr><BR>
   <tr><input class="form__input" type="search" name="State" placeholder="State" value="<?php if(isset($State)) echo $State;?>" /></tr><BR>
   <tr><input class="form__input" type="search" name="Country" placeholder="Country" value="<?php if(isset($Country)) echo $Country;?>" /></tr><BR>
   <tr><input class="form__input" type="search" name="Website" placeholder="Website" value="<?php if(isset($Website)) echo $Website;?>" /></tr><BR>    
   <tr><input type="submit" name="submit" value=" Search " class="button"/></tr>

Ps: $con = mysqli_connect("host","user","password", "databasename");

Your input form is only asking for ONE job field.
You can add to the form two or three more job fields ( or state fields as well I suspect). then process the input to concatenate these extra entries IF the extra fields are used, then alter your sql query to handle the extra values if they are used - this usually means building up the query a bit at a time as you check whether each input box is used.

RE multiple jobs - are these in separate columns, or do you have them all comma separated in the one column. Either of these is bad. Very bad. It makes searching tricky and the database is not normalised, which is very likely if you just have one table and 60 columns.

You should have several tables, depending on exactly how the data is structured
eg people (pID, first name, second name, etc)
job (pID,job) with a joint primary key of both fields.
probably others too.

Then your query joins the two tables and extracts the jobs related to the pID.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.