Geek Answers Handbook

Select data from multiple tables in MySQL using checkboxes and show table field according to checkbox

I have two tables in my users database.

The first table contains a unique user ID, name, contact information and other personal information.

The second table contains a unique user identifier from the first table and device information, such as his first car number, second car number and many others.

My table number 2 structure ..

table2

On the reports page, I show all the information in 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 great. But I want to show an individual report. This means that I want to set a checkbox / radio button for the user field for tables. If he selects a field that uses a check box, then only those values ​​are displayed that have the check box / radio button checked. He likes it if the user selects three flags / radio buttons of type Uid, name, m_atm. It displays only three columns from both tables and displays the table according to these columns.

+6
source share
5 answers

If you do not need me, for this you need to add ON d.uid=e.uid" something like this ON d.uid=e.uid" AND Uid=$id AND name=$name And m_atm=$atm or add this to where (to where I feel bad)

for instance

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>

This php code is simple, but you can use the loop and key value to make it look better

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);
+4
source

If you want to show a table with a dynamic column, you can use the if....else loop if....else you know exactly the number of queries for column queries. Like your problem, I created 2 tables. employee first

Id, name, address

And the second job

JobId, Eid, postName, skill

Request for data:

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

There is a check box for each column.

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

GET values ​​will be compared with the corresponding columns.

To dynamically display columns using checkbox for the following 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>"; }
0
source

Hope this helps. Create an eg: $condition variable and assign a value like

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

and add this $ condition variable to your request. you will get dynamic values. To show the result, a simple method is to use the AJAX flag on the flag.

0
source

You can simply use the checkboxes on 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 with ajax like

 $('.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); } }); });

Hope this helps you.

0
source

I think I get it. The following code can be used in a single file, but for obvious reasons, it is better to separate them.

what i did is i have a list of checkboxes based on the fields you have in the database.

Publish these fields and run the query to return data for published fields only.

After I create the html code / table regarding published fields and 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 } ?>
0
source

More articles:


All Articles