Searching using multiple fields from one column in php

Is it possible to search data using multiple fields from one column? For example column name is 'Job Title', and in the first field I key in 'HR' and second field I key in 'Human Resources'. After I click on search button it will search 'HR' and 'Human Resources' from column 'Job Title'. Below is my code,

<form action="process/searchprocess.php" method="GET">
<tr>
<th>
<br><br>Job Title<br><input type="text" name="Designation" />
</th>
<th>
<br><br>Business Nature / Company Name<br><input type="text" name="BusinessNature" />
</th>
<th valign="center">
<br><br>Email<select name="Email" >

<option value="" >All</option>
<option value="Yes">Yes</option>
<option value="Nil">No</option>
</select>

</th>
</tr>


And this is my php code,

$where = "";
$whereConditions = array();
if(strlen($query1) > 0) {
$whereConditions = "Designation LIKE '%" . $query1 . "%'";
}
if(strlen($query4) > 0) {
$whereConditions = "Designation LIKE '%" . $query4 . "%'";
}
if(strlen($query5) > 0) {
$whereConditions = "Designation LIKE '%" . $query5 . "%'";
}
if(strlen($query6) > 0) {
$whereConditions = "Designation LIKE '%" . $query6 . "%'";
}
if(strlen($query7) > 0) {
$whereConditions = "Designation LIKE '%" . $query7 . "%'";
}
if(strlen($query8) > 0) {
$whereConditions = "Designation LIKE '%" . $query8 . "%'";
}
if(strlen($query9) > 0) {
$whereConditions = "Designation LIKE '%" . $query9 . "%'";
}
if(strlen($query10) > 0) {
$whereConditions = "Designation LIKE '%" . $query10 . "%'";
}
if(strlen($query11) > 0) {
$whereConditions = "Designation LIKE '%" . $query11 . "%'";
}
if(strlen($query12) > 0) {
$whereConditions = "Designation LIKE '%" . $query12 . "%'";
}
if(count($whereConditions) > 0) {
$where = " (" . implode(" OR ", $whereConditions) . ")";
}

$where2 = "";
$whereConditions2 = array();
if(strlen($query2) > 0) {
$whereConditions2 = "BusinessNature LIKE '%" . $query2 . "%' or Company LIKE '%" . $query2 . "%'";
}
if(strlen($query13) > 0) {
$whereConditions2 = "BusinessNature LIKE '%" . $query13 . "%' or Company LIKE '%" . $query13 . "%'";
}
if(strlen($query14) > 0) {
$whereConditions2 = "BusinessNature LIKE '%" . $query14 . "%' or Company LIKE '%" . $query14 . "%'";
}
if(strlen($query15) > 0) {
$whereConditions2 = "BusinessNature LIKE '%" . $query15 . "%' or Company LIKE '%" . $query15 . "%'";
}
if(strlen($query16) > 0) {
$whereConditions2 = "BusinessNature LIKE '%" . $query16 . "%' or Company LIKE '%" . $query16 . "%'";
}
if(strlen($query17) > 0) {
$whereConditions2 = "BusinessNature LIKE '%" . $query17 . "%' or Company LIKE '%" . $query17 . "%'";
}
if(strlen($query18) > 0) {
$whereConditions2 = "BusinessNature LIKE '%" . $query18 . "%' or Company LIKE '%" . $query18 . "%'";
}
if(strlen($query19) > 0) {
$whereConditions2 = "BusinessNature LIKE '%" . $query19 . "%' or Company LIKE '%" . $query19 . "%'";
}
if(strlen($query20) > 0) {
$whereConditions2 = "BusinessNature LIKE '%" . $query20 . "%' or Company LIKE '%" . $query20 . "%'";
}
if(strlen($query21) > 0) {
$whereConditions2 = "BusinessNature LIKE '%" . $query21 . "%' or Company LIKE '%" . $query21 . "%'";
}
if(count($whereConditions2) > 0) {
$where2 = " (" . implode(" OR ", $whereConditions2) . ")";
}

$raw_results = mysqli_query($conn, "SELECT * FROM user_info WHERE " . $where . " and " . $where2 . " and (State LIKE '%".$query3."%') and (Email LIKE '%".$Email."%') order by ID") or die (mysqli_error());

if(mysqli_num_rows($raw_results) > null){ // if one or more rows are returned do following
?>
<table width="100%" >
<tr align="center">
<th>ID</th>
<th>Tag</th>
<th>Company</th>
<th>Designation</th>
<th>Email</th>
<th>State</th>
<th>Business Nature</th>
<th>Data code</th>
</tr>

<?php
while($results = mysqli_fetch_array($raw_results)){
?>

<tr align="center" style="margin-left: 100px;">
<td valign="top"><?php echo $results["ID"]; ?></td>
<td valign="middle"><?php echo $results["Tag"]; ?></td>
<td valign="middle"><?php echo $results["Company"]; ?></td>
<td valign="middle"><?php echo $results["Designation"]; ?></td>
<td valign="middle"><?php echo $results["Email"]; ?></td>
<td valign="middle"><?php echo $results["State"]; ?></td>
<td valign="middle"><?php echo $results["BusinessNature"]; ?></td>
<td valign="middle"><?php echo $results["Datacode"]; ?></td>
</tr>


<?php
}
?></table><?php
}


I have 1 job title text field, and 1 Company text field. When I only key in to Company text field, it shows this error

You have an error in your SQL syntax; check the manual that
corresponds to your MariaDB server version for the right syntax to use
near 'and (BusinessNature LIKE '%chemical%' or Company LIKE
'%chemical%')

It means Job title field is required. Can I know what is this error?

Check here,

Insert one field only http://spaceforemail.com/1.PNG

Result http://spaceforemail.com/2.PNG

Insert two fields http://spaceforemail.com/3.PNG

Result http://spaceforemail.com/4.PNG

1 Answer
1

A first approach: the two input fields should have distinguishable names, otherwise PHP won't be able to use the first value.

Additionally, you should only use the input fields that contain something, like the following:

$where = "";
$whereConditions = array();
if(strlen($query1) > 0) {
$whereConditions = "Designation LIKE '%" . $query1 . "%'";
}
if(strlen($query2) > 0) {
$whereConditions = "Designation LIKE '%" . $query2 . "%'";
}
if(count($whereConditions) > 0) {
$where = "WHERE (" . implode(" OR ", $whereConditions) . ")";
}
$query = "SELECT * FROM user_info " . $where . " order by ID";
$raw_results = mysqli_query($conn, $query) or die (mysqli_error());


Designation LIKE '%%'

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.