Select data from multiple tables in MySQL using check boxes and show the table field according to checkbox

Question

I have two tables in database of my users.

First table contains user unique ID, Name, Contact No and other personal information.

Second table contains unique id of user from first table and device information like his first machine number, second machine number and many others also.

My table no 2 structure is..

On the reports page, I am showing all the information in a table form using this

$sql = "SELECT e.* ,d.* FROM emitra_basic As e INNER JOIN emitra_device as d  ON d.uid=e.uid";
$result = $conn->query($sql);

if ($result->num_rows>0) {?>
            <table ><tr><td> Uid</td><td> Name</td>
             <td> Micro Atm</td>.......and all column of both tables </tr>                            
 <?php while($row = $result->fetch_array()) {
   echo "<td>".  $row['uid']. "</td>";
     echo "<td>".  wordwrap($row['name'],15,"\n",1). "</td>"; ....and all  
    } echo "</table>";  

It works fine. But I want to show a customised report. It means I want to give check box/radio button for user of tables field. If he select field uses check box then its show only those value which check box/radio button are selected. It likes if user select three check box/radio button like Uid, name, m_atm. It shows only details of three columns from both tables and display table view accordingly these columns.

Donoho

Do you need to SELECT this fields via SQL-query or you need to show selected fields only? .. You can use your query as is, but analyze POST/GET query with selected columns and show only that fields, i.e. don't touch the sql, but change view. – Cumulative

Answer 1

If I undestand you, to do that you need add to ON d.uid=e.uid" something like this ON d.uid=e.uid" AND Uid=$id AND name=$name And m_atm=$atm, or to add this to where (to where I thinght is not good)

For example

HTML:

 <form method="get" action="/a.php">
 <input type="checkbox" name="check1" value="text1"/>
 <input type="checkbox" name="check2" value="text2"/>
 <input id="submit" onclick="f();return false;" type="button" value="ok"/>
 </form>

PHP (test.php)

     if(isset($_GET['check1'])) $id=" AND Uid='$_GET[check1]'"; //if is checked first
if(isset($_GET['check2'])) $name=" AND name='$_GET[check2]'"; //if is checked second

/* . . . */

$sql = "SELECT e.* ,d.* FROM emitra_basic As e INNER JOIN emitra_device as d  ON (d.uid=e.uid $id $name )";

var_dump($sql);

JS:

 <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.0/jquery.min.js"></script>

 <script>
 function f() {
 var url;
 var xmlhttp,

 url="/text.php?"+$('form').serialize(); //change text.php

 if (window.XMLHttpRequest) {
        xmlhttp = new XMLHttpRequest();
    } else if (window.ActiveXObject) {
        xmlhttp = new ActiveXObject('Microsoft.XMLHTTP');
    }
    xmlhttp.open('GET', url, true);
    xmlhttp.onreadystatechange = function() {
        if (xmlhttp.readyState == 4) {
            myfunction(xmlhttp.responseText);
        }
    }
    xmlhttp.send(null); 


  function myfunction(response) { alert(url+'   '+response);
 //do something
  }
  }

 </script>

That Php code is simply, but you can use loop and key value to make it look more good

For example you can use <input name=text[]> for all ckeckboxes elements and do this

 foreach ($_GET['text'] as $key => $value) {
 if($key==0) $key='uid'; else
 if($key==1) $key='name'; else
 if($key==2) $key='m_atm'; 

 $q.="$key='$value' AND ";
 }

 $q=substr($q,0,strlen($q)-5);

 $sql2 = "SELECT e.* ,d.* FROM emitra_basic As e INNER JOIN emitra_device as d  ON (d.uid=e.uid $q )";

 var_dump($sql2);

Siegfried

i want to show only these field which checkbox select by user in table format .how can i show this – Donoho

I write you, put chechboxex on form and subbmit button and after get checkboxes bay it aname with $_GET or $POST and see if is ckecked(if isset thei is checked if not iiset the not checked), and if is ckecked put condition to ON like I write. Whe you print table put for example if($row['uid'])!='' echo "<td>$row['uid']</td>"; – Siegfried

If you wont ajax then add to checkbox onclick="f();return false;" and make in js function f() that will submit data using ajax and not refresh all page – Siegfried

The general idea is correct, but this code has syntax errors. Also, there's no value="text" attribute for the checkbox examples, so $_GET['check1'] wouldn't contain anything useful for MySQL. – Palstave

Answer 2

When you want to show table with dynamic column you can use if....else loop if you know exactly the number of columns query retrieve. Similar to your problem I have created 2 tables.First is employee

eId,Name,Address

And Second Is job

jobId,eId,postName,Skill

Query for retrieving data is:

$query="SELECT e.Name,e.Address,j.postName,j.Skill FROM employee AS e INNER JOIN job AS j ON e.eId=j.eId";

For each column there is checkbox

<input type="checkbox" name="chkName"/>
<input type="checkbox" name="chkAddress"/>......for all columns.

GET values will be compared with the respective columns.

To show columns dynamically using checkbox for above query:

        $checkArray=array();

                    if(isset($_GET['chkName']))
                         $checkArray[0]=1; 
                    else 
                         $checkArray[0]=0;
                    if(isset($_GET['chkAddress']))
                         $checkArray[1]=1; 
                    else 
                         $checkArray[1]=0;
                    if(isset($_GET['chkPost']))
                         $checkArray[2]=1; 
                    else 
                         $checkArray[2]=0;
                    if(isset($_GET['chkSkill']))
                         $checkArray[3]=1; 
                    else 
                        $checkArray[3]=0;

                $query="SELECT e.Name,e.Address,j.postName,j.Skill FROM
 employee AS e INNER JOIN job AS j ON e.eId=j.eId";

            $result = $con->query($sql);

        if ($result->num_rows>0) {
        ?>
             <table >
                <tr>
                  <?php
                  if($checkArray[0]==1){
                  ?>
                        <td> Name</td>
                  <?php
                    }
                      if($checkArray[1]==1){
                      ?>
                         <td> Address</td>
                          <?php
                      }
                      if($checkArray[2]==1){
                      ?>
                         <td> Post</td>
                          <?php
                      }
                      if($checkArray[3]==1){
                      ?>
                         <td>Skioll</td>
                         <?php
                      }
                 ?>
                 </tr>                            
     <?php

      while($row = $result->fetch_array()) {

                echo "<tr>";

                if($checkArray[0]==1)
                {
                echo "<td>".  $row[0]. "</td>";
                }
                if($checkArray[1]==1)
                {
                echo "<td>".  $row[1]. "</td>";
                }
                if($checkArray[2]==1)
                {
                echo "<td>".  $row[2]. "</td>";
                 }
                if($checkArray[3]==1)
                {
                echo "<td>".  $row[3]. "</td>";
                }
                echo "</tr>";           
          } 

Cobweb

Answer 3

Hope this will help. Create a variable eg: $condition and assign value like

$condition = "";
if($check1) {
    $condition = " AND Uid=$id";
}
if($check2) {
   $condition = " AND name=$name";
}

and append this $condition variable in your query. you will get dynamic values. To show the result simple method is to use AJAX on checkbox checked.

Katonah

Answer 4

You can simply use the checkboxes in the following manner in your page

mypage.html

 <form>
   <input class="form-check" type="checkbox" name="checkName"><label class="form-label">Name</label>
   <input class="form-check" type="checkbox" name="checkMicroATM"><label class="form-label">MicroATM</label>
   <!--and so on for each field-->
</form>
<div id="dataTable">
   <!--Here your table will be displayed-->
</div>

myquerypage.php

if($_POST){

 $sql = "SELECT e.* ,d.* FROM emitra_basic As e INNER JOIN emitra_device as d  ON d.uid=e.uid";
 $result = $conn->query($sql);

 $displayColCount=0;   // maintain the count of columns to be displayed
 $displayCol=array();  // contains the database column names to be displayed on the page

 echo '<div id="dataTable">';   // very useful for replacing the content using ajax call if required

 echo '<table>';
 echo '<tr>';
 if(isset($_POST['checkName'])){
   $displayCol[$displayColCount++]='name';
   echo '<th>Name</th>';
 }
 if(isset($_POST['checkMicroATM'])){
   $displayCol[$displayColCount++]='m_atm';
   echo '<th>MicroATM</th>';
 }
  .
  .
  // and so on for each column
 echo '</tr>';

 while($row = $result->fetch_array()) {
   echo '<tr>';
   $i=0;
   while($i< $displayColCount){
     echo "<td>".  $row[displayCol[i++]]. "</td>";  
   }
   echo '</tr>';
 }
echo '</table>';
echo '</div>';
}

you can call the above page using ajax as

$('.form-check').change(function (e) {
   var form=this.form;
   var formData = $('form').serialize();
   $.ajax({
        type: 'POST',
        url: 'myquerypage.php',
        data: formData,
        cache: false,
        success: function (html)
        {

            $("#dataTable").html(html);

        },
        error: function (xhr, ajaxOptions, thrownError) {
            alert(xhr.status);
            alert(thrownError);
        }
    });   
});

I hope this helps you.

Overhear

Answer 5

I think I get your point. The following code can be used in the same file but for obvious reasons it's better to separate them.

what I have done, I have got a list of the checkboxes based on the fields that you've got in the database.

Post these fields and run query to get back data for only these fields that are posted.

After I generate the html code/table regarding the posted fields and the data

<?php

$result = [];
$fields = [];
$dbFields = [];
$sql = "";

$fieldsNameMapping = [
    'e.uid' => 'Uid',
    'd.block' => 'Blocked',
    'd.m_atm' => 'Atm',
    'd.uid_name' => 'Name'
];

if (isset($_POST)) {

    // build query based on the posted fields
    if (isset($_POST['fields']) && !empty($_POST['fields'])) {
        $sql = "SELECT ";

        foreach ($_POST['fields'] as $fieldValue) {
            $sql .= $fieldValue . ", ";
            // get field names
            $fields[] = $fieldsNameMapping[$fieldValue];
            // mapping db field names, remove first two characters
            $dbFields[] = substr($fieldValue, 2);
        }

        // remove last comma
        $sql = substr($sql, 0, -2);
        $sql .= " FROM emitra_basic As e INNER JOIN emitra_device as d  ON d.uid=e.uid";
        // get result
        $result = $conn->query($sql);

    }

}

?>


<form method="post">
     <input type="checkbox" name="fields[]" value="e.uid" />&nbsp;Uid<br />
     <input type="checkbox" name="fields[]" value="d.block" />&nbsp;Blocked<br />
     <input type="checkbox" name="fields[]" value="d.m_atm" />&nbsp;Atm<br />
     <input type="checkbox" name="fields[]" value="d.uid_name" />&nbsp;Name<br />
     <input type="submit" value="Show Result" />
</form>

<?php if (!empty($fields)) { ?>
<table>
    <thead>
        <tr>
        <?php foreach($fields as $fieldName) { ?>
            <th><?php echo $fieldName ?></th>
        <?php } ?>
        </tr>
    </thead>
    <?php if (!empty($result) && $result->num_rows > 0) { ?>
    <tbody>
    <?php while($row = $result->fetch_array()) { ?>
        <tr>
            <?php foreach($dbFields as $fieldDbName) { ?>
            <td><?php echo $row[$fieldDbName] ?></td>
            <?php } ?>
        </tr>
    <?php } ?>
    </tbody>
    <?php } ?>
</table>
<?php } ?>

Anaerobic