I am writing a code to make a query builder. but unfortunately, the filter table text box is not working. This might be a problem of javascript confliction. but I could not get the hang of it. Can anybody help me out resolving this issue. The only problem with this code is with the filter box appears above the data table.
<?php
ini_set( "display_errors", 0);
include "include.php";
include "connect_to_mysql.php";
$table='Genomics_data_ncbi';
$display = array();
$qu_mul="";
echo <<<HTML
<head>
<title>TODO supply a title</title>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<script type="text/javascript">
function formValidation(form)
{
var ccc = document.getElementById("first_box").value;
if(ccc == "") {
alert("Please enter a keyword !!!");
return false;
}
}
var intTextBox=0;
//FUNCTION TO ADD TEXT BOX ELEMENT
function addElement()
{
intTextBox = intTextBox + 1;
var contentID = document.getElementById("content");
var newTBDiv = document.createElement("div");
newTBDiv.setAttribute("id","strText"+intTextBox);
newTBDiv.innerHTML = "<select name='op[]'><option value='and'>AND</option> <option value='or'>OR</option> </select><select name='fieldsi[]'><option value='all'>All Fields</option><option value='' align='center' style='background-color: #FFCCCC'>Genome Information</option><option value='Gene_id'>Gene_id</option><option value='Gene_name'>Gene_name</option><option value='Gene_product'>Gene_product</option><option value='Function_of_proteins'>Function_of_proteins</option><option value='Protein_id'>Protein_id</option><option value='Localization_of_proteins'>Localization_of_proteins</option><option value='Strain_id'>Strain_id</option></select><select name='fieldsi_op[]'><option value='like'>LIKE</option><option value='not like'>Not LIKE</option><option value='equal'>Equal to</option><option value='not_equal'>Not equal to</option><option value='='> = </option><option value='!='> != </option><option value='<='> <= </option><option value='>='> >= </option><option value='<'> < </option><option value='>'> > </option></select> <input type='text' id='" + intTextBox + "' name='keyword[]'>";
contentID.appendChild(newTBDiv);
}
//FUNCTION TO REMOVE TEXT BOX ELEMENT
function removeElement()
{
if(intTextBox != 0)
{
var contentID = document.getElementById("content");
contentID.removeChild(document.getElementById("strText"+intTextBox));
intTextBox = intTextBox-1;
}
}
</script>
<script src="j-jquery-3.3.1.js" type="text/javascript"></script>
<script src="j-jquery.dataTables.min.js" type="text/javascript"></script>
<script src="j-dataTables.bootstrap.min.js" type="text/javascript"></script>
<link href="bootstrap.min1.css" rel="stylesheet" type="text/css"/>
<link href="dataTables.bootstrap.min1.css" rel="stylesheet" type="text/css"/>
<script>
$(document).ready(function() {
$('#example').DataTable();
} );
</script>
</head>
<br>
<br>
<br>
<br>
<font color="#3d4983" face="Verdana" size="3"><b>Query Builder</b></font>
<br>
<p align='justify'>Users may build complex queries using the logical operators 'AND' and 'OR'. Each sub-query can be built using other operators such as LIKE, NOT LIKE, EQUAL and NOT EQUAL TO while dealing with strings like
words or letters and =, !=, <=, >=, < and > while dealing with numerical values. The Query builder aids to the flexibility of performing search on a number of fields simultaneously.</p>
<br>
<form name='form1' action="<?=$_SERVER[PHP_SELF]; ?>" method='post' onsubmit="return formValidation(this);">
<select name='fieldsi[]'>
<option value='all'>All Fields</option>
<option value='' style="background-color: #FFCCCC">Gene Information</option>
<option value='Gene_id'>Gene id</option>
<option value='Gene_name'>Locus tag</option>
<option value='Gene_product'> Gene product</option>
<option value='Function_of_proteins'> Protein function</option>
<option value='Protein_id'> Protein ID</option>
<option value='Localization_of_proteins'> Localization of protein</option>
<option value='Strain_id'> Strain ID</option>
</select>
<select name='fieldsi_op[]'>
<option value='like'>LIKE</option>
<option value='not like'>Not LIKE</option>
<option value='equal'>Equal to</option>
<option value='not_equal'>Not equal to</option>
<option value='='> = </option>
<option value='!='> != </option>
<option value='<='> <= </option>
<option value='>='> >= </option>
<option value='<'> < </option>
<option value='>'> > </option>
</select>
<input type='text' name='keyword[]' id='first_box'>
<input type="button" onclick="addElement()" value="+"> <input type="button" onclick="removeElement()" value="-">
<div id="content" ></div>
<br>
<input type='submit' value='Search'>
</form>
<br>
<br>
<body>
HTML;
$dbServerName = "abc";
$dbUsername = "abc";
$dbPassword = "abc";
$dbName = "abc";
// create connection
$conn = new mysqli($dbServerName, $dbUsername, $dbPassword, $dbName);
// check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SHOW COLUMNS FROM $table";
if(isset($_POST[keyword])){
$key = $_POST[keyword];
$op = $_POST[op];
$field = $_POST[fieldsi];
$field_op = $_POST[fieldsi_op];
for($i=0;$i<count($key);$i++){
$keyw = $key[$i];
$field_val = $field[$i];
$field_op_val = $field_op[$i];
if($i>0){
$j = $i-1;
$operate = $op[$j];
if($operate != 'not'){
if($field_val == 'all'){
$result = mysqli_query($conn,$sql);
$result1 = mysqli_query("SHOW COLUMNS FROM $table");
echo "$result1";
if (mysqli_num_rows($result) > 0)
{
$i=0;
while ($row = mysqli_fetch_array($result))
{
if($i==0)
{
$fi_name = $row[0];
$display[$fi_name] = 0;
$qu_mul = $qu_mul.' '.$operate.' '."$fi_name $field_op_val '%$keyw%'";
}
else
{
$fi_name = $row[0];
$display[$fi_name] = 0;
$qu_mul.=" or "."$fi_name $field_op_val '%$keyw%'";
}
$i++;
}
}
}
else{
$display[$field_val] = 0;
if(!preg_match('/[like]/i', $field_op_val)){
$qu_mul = $qu_mul.' '.$operate.' '."$field_val $field_op_val $keyw";
}
else if(preg_match('/equal/i', $field_op_val)){
$qu_mul = $qu_mul.' '.$operate.' '."$field_val ='$keyw'";
}
else if(preg_match('/not_equal/i', $field_op_val)){
$qu_mul = $qu_mul.' '.$operate.' '."$field_val !='$keyw'";
}
else{
$qu_mul = $qu_mul.' '.$operate.' '."$field_val $field_op_val '%$keyw%'";
}
}
}
else{
$qu_mul = $qu_mul.' '."and $field_val not like '%$keyw%'";
}
}
else{
if($field_val == 'all'){
$result = mysqli_query("SHOW COLUMNS FROM $table");
if (mysqli_num_rows($result) > 0)
{
$i=0;
while ($row = mysqli_fetch_array($result))
{
if($i==0)
{
$fi_name = $row[0];
$display[$fi_name] = 0;
if(!preg_match('/[like]/i', $field_op_val)){
$qu = "$fi_name $field_op_val $keyw";
}
else if(preg_match('/equal/i', $field_op_val)){
$qu = "$fi_name ='$keyw'";
}
else if(preg_match('/not_equal/i', $field_op_val)){
$qu = "$fi_name !='$keyw'";
}
else{
$qu = "$fi_name $field_op_val '%$keyw%'";
}
}
else
{
$fi_name = $row[0];
$display[$fi_name] = 0;
if(!preg_match('/[like]/i', $field_op_val)){
$qu.=" or "."$fi_name $field_op_val $keyw";
}
else if(preg_match('/equal/i', $field_op_val)){
$qu.=" or "."$fi_name ='$keyw'";
}
else if(preg_match('/not_equal/i', $field_op_val)){
$qu.=" or "."$fi_name !='$keyw'";
}
else{
$qu.=" or "."$fi_name $field_op_val '%$keyw%'";
}
}
$i++;
}
}
}
else{
$display[$field_val] = 0;
if(!preg_match('/[like]/i', $field_op_val)){
$qu = "$field_val $field_op_val $keyw";
}
else if(preg_match('/equal/i', $field_op_val)){
$qu = "$field_val ='$keyw'";
}
else if(preg_match('/not_equal/i', $field_op_val)){
$qu = "$field_val !='$keyw'";
}
else{
$qu = "$field_val $field_op_val '%$keyw%'";
}
}
}
}
$dis="";
$display[Gene_id] = 0;
#echo "$display";
$display[Gene_name] = 0;
$display[Gene_product] = 0;
$display[Function_of_proteins] = 0;
$display[Protein_id] = 0;
#$display[Protein_sequence] = 0;
#$display[Nucleotide_sequence] = 0;
$display[Protein_length] = 0;
$display[Localization_of_proteins] = 0;
$display[Strain_id] = 0;
$display[Source] = 0;
foreach($display as $key => $value){
$dis = "$dis"."$key,";
#echo "$dis";
}
$dis = preg_replace("/,$/",'',$dis);
$qu_pre = "select $dis from $table where ";
$sql = "$qu_pre"."$qu"."$qu_mul";
$res = $conn->query("$sql") or die($conn->error);
#echo"<table border=1>";
$res_no = mysqli_num_rows($res);
if($res_no == 0){
"<font color='brown' size='4'><b>No Record Found in CRAB-DB coressponding to your keyword !!!</b></font><br><br><input type='button' value='Go Back' onClick='history.go(-1)'><br><br><br>";
}
else{
$tot_res = mysqli_num_rows($res);
echo "Total number of records = $tot_res";
echo '<br>';
#<!-- back working properly-->
echo "<input type='button' onClick='history.go(-1)' value='Back'><br><br>";
echo '<div style="padding:0px 0px 0px 0px;">';
$query = mysqli_query($conn, $sql);
if (mysqli_num_rows($query)){
$headerRow = true;
while ($row = mysqli_fetch_assoc($query)){
if ($headerRow){
$headerRow = false;
echo '<table id="example" class="table table-striped table-bordered" style="width:100%">';
echo "<thead>";
echo "<tr>";
foreach (array_keys($row) as $header)
{
echo "<th align = left>$header</a></th>";
}
echo "</tr>";
echo "</thead>";
}
echo '<tbody>';
#echo "<tbody>";
echo "<tr>";
#echo "<td>";
foreach ($row as $value){
echo "<td>$value</td>";
}
echo "</td>";
echo "</tr>";
#echo '</tr>';
echo '</tbody>';
}
}
}
while($row=mysqli_fetch_array($res)){
for($i=0;$i<mysqli_num_fields($res);$i++){
$f_name = mysqli_field_name($res, $i);
$f_val = $row[$f_name];
if($f_name == 'Gene_name'){
print"<td>$f_val</td>";
print "<td><a href='#####.php?Gene_name=$f_val&type=Gene_name' title='Click to See Details' style='text-decoration: none;'>$f_val</a></td>";
}
else if($f_name == 'Gene_id'){
$c_id = $row[Gene_name];
$res_pub = mysqli_query("select * from $table where Gene_name='$c_id'");
}
print "</th>";
}
}
}
echo "</table>";
echo "</body>";
?>
