使用复选框从MySQL中的多个表中选择数据，并根据复选框显示表字段

I have two tables in database of my users.

我的用户数据库中有两个表。

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

第一个表包含用户唯一ID、名称、联系方式和其他个人信息。

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.

第二个表包含来自第一个表和设备信息的唯一用户id，如他的第一个机器号、第二个机器号和其他许多信息。

My table no 2 structure is..

我的表2结构是。

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.

它将正常工作。但我想展示一份定制报告。这意味着我要为tables字段的用户提供复选框/单选按钮。如果他选择字段使用复选框，那么它只显示选中复选框/单选按钮的那些值。它喜欢用户选择三个复选框/单选按钮，比如Uid, name, m_atm。它只显示来自两个表的三列的详细信息，并相应地显示表视图。

5 个解决方案

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

如果我解除你的身份，你需要在d.uid=e上添加。uid=e。uid和uid =$id和name=$name和m_atm=$atm，或者将其添加到where(在我认为不太好的地方)

For example

例如

HTML:

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)

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:

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

这个Php代码很简单，但是您可以使用循环和键值来使它看起来更好

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

例如，您可以对所有ckeckbox元素使用并执行此操作

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

#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

当你想用动态显示表列可以使用如果....如果您确切地知道列的数量，则执行else循环查询。与您的问题相似，我创建了两个表。首先是员工

eId,Name,Address

开斋节,姓名、地址

And Second Is job

其次是工作

jobId,eId,postName,Skill

开斋节,jobId postName,技能

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

查询美元= "选择e.Name、e.Address j.postName,j。作为e级内部员工的技能，在e.eId=j.eId上作为j;

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.

GET值将与相应的列进行比较。

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

#3

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

希望这将帮助。创建一个变量，例如:$condition并分配值

$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.

并在查询中追加$condition变量。你会得到动态值。要显示结果，简单的方法是在复选框中使用AJAX。

#4

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

您可以在页面中以以下方式使用复选框

mypage.html

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

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

您可以使用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.

我希望这对你有帮助。

#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.

发布这些字段并运行query以只获取已发布的这些字段的数据。

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

生成关于已发布字段和数据的html代码/表之后

<?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 } ?>