0

Hi there,

May I know if i want to filter 3 column, for example

Column A, Column B and Column C

When user select Column A, the mysql query will select the data based on the Column A value.
When user continue select Column B, the msql query will select the data based on the Column B value from query Column A
When user continue select Column C, the msql query will select the data based on the Column C value from query Column A and Column B

More details..
(i) Alex select "Apple" in Column A
so, the table show the details all about "Apple"
(ii) Next, Alex select "China" in Column B,
the table show the details of Apple which from China
(iii) Then, Alex select "Small" in Column C,
the table show the details of Apple which from China and the size is small.

Thanks
tzewin

3
Contributors
19
Replies
56
Views
3 Years
Discussion Span
Last Post by AleMonteiro
0

May I know what means of you? 3 table?

3 table each table have their own sql query?

0

I think you should use at least 4 tables:

Fruits (Id, Name)
Countries (Id, Name)
Sizes(Id, Name)
Defailts(DetaildId, FruidId, CountryId, SizeId, Value)

It all depends in how much info you have, how organized you want to be and how much do you care for the quality of the information, of course this is all spoken based on the notion that it would be used for an actual system or application, not just some test or small presentation.

At least, if you are going to use just one table and sink it all in there, it's a simple query:

SELECT *Values* WHERE Fruit = 'Apple' AND Country ='China' AND Size = 'Small'
1

I think 3 tables

SELECT
    (SELECT value FROM fruits WHERE value='Apple' LIMIT 1) as fruit ,
    (SELECT value FROM countries WHERE value='China' LIMIT 1) as countrie ,
    (SELECT value FROM sizes WHERE value='Small' LIMIT 1) as size;

Edited by AndrisP

0

AndrisP, but what about the relation of the values and entities? With only 3 tables you could not know what apples are from china and small.

0

Hi all,

My data all store into same table. It cannot be seperate into 3 table.

Edited by Koh_1

0

Koh, if already have the table ready, post the schema and some data if possible.

Edited by AleMonteiro

0

Ok, I would to paste a code here.. but it can't may I know how to do?

0
function doQuery() {
 var idx = getField("Quarter").selectedIndex;
 var selectedValue = getField("Quarter").options[idx].value;
 var selectedLabel = getField("Quarter").options[idx].text;
 var adx=getField("Department").selectedIndex;
 var selectedValue1=getField("Department").options[adx].value;
 var selectedLabel1=getField("Department").options[adx].value;
 var bdx=getField("subdept").selectedIndex;
 var selectedValue2=getField("subdept").options[bdx].value;
 var selectedLabel2=getField("subdept").options[bdx].value;
 var p = selectedValue;
 var q = selectedValue1;
 var r = selectedValue2;
   if(p=="") {
      //clearGrid();
      return;
   }
  else if(q==""){
    return;
  }
  else if(r==""){
    return;
  }

   if (window.XMLHttpRequest) // code for IE7+, Firefox, Chrome, Opera, Safari
      xmlhttp=new XMLHttpRequest();
   else // code for IE5, IE6
      xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");

   xmlhttp.onreadystatechange=function() {
      if (xmlhttp.readyState==4 && xmlhttp.status==200) {
          var rows = eval( '(' + xmlhttp.responseText + ')' );
         //rows.style.fontSize="xx-large";        
        //repopulate the grid
          for (var i = 0; i < rows.length; i++) {
          var table=document.getElementById("yesTable");
          var row=table.insertRow(1);
          var cell1=row.insertCell(0);
          var cell2=row.insertCell(1);
          var cell3=row.insertCell(2);
          var cell4=row.insertCell(3);
          var cell5=row.insertCell(4);
          var cell6=row.insertCell(5);

            cell1.style.fontSize="12px";
            cell2.style.fontSize="12px";
            cell3.style.fontSize="12px";
            cell4.style.fontSize="12px";
            cell5.style.fontSize="12px";
            cell6.style.fontSize="12px";
            cell1.style.color="black";
            cell2.style.color="black";
            cell3.style.color="black";
            cell4.style.color="black";
            cell5.style.color="black";
            cell6.style.color="black";


          cell1.innerHTML=rows[i].APP_NUMBER;
          cell2.innerHTML=rows[i].DRIVERNAME;
          cell3.innerHTML=rows[i].INSP_NAME_LABEL;
          cell4.innerHTML=rows[i].INSP_DATE;
          cell5.innerHTML=rows[i].DEPARTMENT;
          cell6.innerHTML=rows[i].SUBDEPT;

         }
       }
   }
   xmlhttp.open("GET", "http://d123456/yes.php?p="+p+"&q="+q+"&r="+r, true);   
   xmlhttp.send();
}
getField("Quarter").onchange=doQuery;
getField("Department").onchange=doQuery;
getField("subdept").onchange=doQuery;

java script

Edited by Koh_1

0
<?php
if (isset($_GET['p'])) //quarter year
   $p = $_GET['p'];
else
    die('[b]'); //return an empty array;

if (isset($_GET['q'])) //department
   $q = $_GET['q'];
else
    die('[a]'); //return an empty array;

if (isset($_GET['r'])) //subdepartment
   $r = $_GET['r'];
else
    die('[c]'); //return an empty array;

$con = mysql_connect("127.0.0.1:3306","admin","","wf_workflow") or die('[c]');

/*if (!$con) {
    die('Could not connect: ' . mysql_error());
}*/


$a = "SELECT * FROM wf_workflow.pmt_detailreport WHERE LBLYEAR = '".$p."'  AND DEPARTMENT = '".$q."' AND SUBDEPT = '".$r."'";

$result = mysql_query($a,$con) or die(mysql_error());
$ret = '';
while ($row = mysql_fetch_array($result))
    { 
    $ret .= (empty($ret) ? '' : ', ') .   "{'APP_NUMBER' : '{$row['APP_NUMBER']}', 
        'DRIVERNAME' : '{$row['DRIVERNAME']}','INSP_NAME_LABEL' : '{$row['INSP_NAME_LABEL']}','INSP_DATE' : '{$row['INSP_DATE']}','DEPARTMENT' : '{$row['DEPARTMENT']}','SUBDEPT' : '{$row['SUBDEPT']}'}";
    }

mysql_close($con);
die('[' . $ret . ']');

?>

PHP Code

0

(3) Database data

APP_UID    15194702153c769c17f7638044635280 
APP_NUMBER 241 
APP_STATUS COMPLETED 
DRIVERNAME KOH TZE WIN 
LBLYEAR    Q4 F14 
DRIVERNAME Koh Tze Win 
INSP_DATE  2014-07-17 
DEPARTMENT IS 
SUVDEPT    NULL 
0

Something like this:

<label for="ch_p">use BLYEAR</label>
<input id="ch_p" type="checkbox" onchange="this.nextSibling.disabled=(this.checked?false:true)" 
        <?php if(isset($_GET['p'])){ echo " checked"; } ?> /><select name="p" <?php if(!isset($_GET['p'])){ echo " disabled"; } ?> >
    <option value=".....">.....</option><!-- All values from DB for this opt if selected or not selected other params -->
</select>
<label for="ch_q">use DEPARTMENT</label>
<input id="ch_q" type="checkbox" onchange="this.nextSibling.disabled=(this.checked?false:true) 
        <?php if(isset($_GET['q'])){ echo " checked"; } ?> />" /><select name="q" <?php if(!isset($_GET['q'])){ echo " disabled"; } ?> >
    <option value=".....">.....</option><!-- All values from DB for this opt if selected or not selected other params -->
</select>
<label for="ch_r">use SUBDEPT</label>
<input id="ch_r" type="checkbox" onchange="this.nextSibling.disabled=(this.checked?false:true) 
        <?php if(isset($_GET['r'])){ echo " checked"; } ?> />" /><select name="r" <?php if(!isset($_GET['r'])){ echo " disabled"; } ?> >
    <option value=".....">.....</option><!-- All values from DB for this opt if selected or not selected other params -->
</select>

and php like this:

$params = array();
    if(isset($_GET['p'])){ $params[] = "BLYEAR='".$_GET['p']."'"; }
    if(isset($_GET['q'])){ $params[] = "DEPARTMENT='".$_GET['q']."'"; }
    if(isset($_GET['r'])){ $params[] = "SUBDEPT='".$_GET['r']."'"; }
if(count($params)>0){
    $a = "SELECT * FROM wf_workflow.pmt_detailreport WHERE ".@implode(" AND ", $params)."; ";

    $result = mysql_query($a,$con) or die(mysql_error());
    $ret = '';
    while ($row = mysql_fetch_array($result)) { 
        $ret .= (empty($ret) ? '' : ', ') .   "{'APP_NUMBER' : '{$row['APP_NUMBER']}', 
        'DRIVERNAME' : '{$row['DRIVERNAME']}','INSP_NAME_LABEL' : '{$row['INSP_NAME_LABEL']}','INSP_DATE' : '{$row['INSP_DATE']}','DEPARTMENT' : '{$row['DEPARTMENT']}','SUBDEPT' : '{$row['SUBDEPT']}'}";
        }
    mysql_close($con);
    die('[' . $ret . ']');
    }

Edited by AndrisP

0

How about if my selection is using dropdown list?

0

I sepearate the php code into another file name call yes.php.. how do i call back in javascript? (using xml)

0

Hi AleMonteiro,

Thanks, I will follow it first. If any problem, I will repost here.

Thanks

0

Go get it man!

You'll probable have some problems, but there's always a way.
Use developer console to debug the ajax request params and return.

Good luck =)

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.