omnath omnath - 2 months ago 9
MySQL Question

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

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

table2

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.

Answer

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