How to display the data by using dropdown list

Question

Koh_1 0 Newbie Poster

10 Years Ago

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

javascript

3 Contributors
19 Replies
203 Views
2 Weeks Discussion Span
Latest Post 10 Years Ago Latest Post by AleMonteiro

AndrisP 193 Posting Pro in Training

10 Years Ago

I think you need 3 tables instead of 3 columns e.g.
Fruits, Countries, Sizes

AleMonteiro 238 Can I pick my title?

10 Years Ago

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'

AndrisP 193 Posting Pro in Training

10 Years Ago

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 10 Years Ago by AndrisP

AleMonteiro 238 Can I pick my title?

10 Years Ago

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.

AleMonteiro 238 Can I pick my title?

10 Years Ago

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

Edited 10 Years Ago by AleMonteiro

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Koh_1 0 Newbie Poster · Answer 1 · 2014-07-25T08:30:51+00:00

May I know what means of you? 3 table?

3 table each table have their own sql query?

Koh_1 0 Newbie Poster · Answer 2 · 2014-07-25T09:15:15+00:00

Hi all,

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

Koh_1 0 Newbie Poster · Answer 3 · 2014-07-25T09:32:37+00:00

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

AleMonteiro 238 Can I pick my title? · Answer 4 · 2014-07-25T09:34:05+00:00

AleMonteiro 238 Can I pick my title?

10 Years Ago

Use the </> Code button

Koh_1 0 Newbie Poster · Answer 5 · 2014-07-25T09:36:25+00:00

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

Koh_1 0 Newbie Poster · Answer 6 · 2014-07-25T09:37:16+00:00

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

Koh_1 0 Newbie Poster · Answer 7 · 2014-07-25T09:37:48+00:00

(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

AndrisP 193 Posting Pro in Training · Answer 8 · 2014-07-25T11:01:05+00:00

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

Koh_1 0 Newbie Poster · Answer 9 · 2014-07-30T00:25:21+00:00

How about if my selection is using dropdown list?

Koh_1 0 Newbie Poster · Answer 10 · 2014-07-30T00:49:09+00:00

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

Koh_1 0 Newbie Poster · Answer 11 · 2014-08-06T02:01:49+00:00

Koh_1 0 Newbie Poster

10 Years Ago

May I Know how?

AleMonteiro 238 Can I pick my title? · Answer 12 · 2014-08-07T05:19:31+00:00

Oh, sorry for the delay.

I suggest you use jQuery and to do it nicely.

This guide should get you throught it:
http://blog.teamtreehouse.com/beginners-guide-to-ajax-development-with-php

Koh_1 0 Newbie Poster · Answer 13 · 2014-08-11T03:04:35+00:00

Hi AleMonteiro,

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

Thanks

AleMonteiro 238 Can I pick my title? · Answer 14 · 2014-08-11T03:20:17+00:00

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