filter array for select list

[adishardis]

Hi,

I have two diagrams and one unordered list( http://hardis.synology.me/portalen/filter.png ) that rely on select statements and I'm wondering what the best way to filter those. (one filter for all of them!)

$result = mysql_query("SELECT * FROM TABLE WHERE filterArray ORDER BY Datum_formatning ASC");

I have 5 fields that will contain the filteroptions (ever growing list) so I'm thinking a form containing all the filteroptions and a filter button as seen on the image. So the five filteroptions give 5 values going into the "filterarray"

The problem I'm having is
1. Is this really the best way to go about it?
2. The filterboxes are not very nice to look at, any theming possible?
3. I don't know to build the filterarray...

Any advice on these matters are appreciated!

Take care
/Adam

How many options in dropdown? If not huge, perhaps a set of checkboxes would be better (using name attribute array to build a WHERE clause string).

[adishardis]

Checkboxes would be nicer but the there are to many options so it won't fit... :-(

You can theme dropdowns with many front-end frameworks. Just search for them.

Anyway - again - how many options do you have?
Are these option items stored in a table in the DB?

Wrt. checkboxes - remember that these could be inline (stretch across the page, not just one per row).

[adishardis]

well, right now, all in all 22 options. maximum of 6 different options in a single dropdown.

The data is stored in a table along with the other information.

I am very thankful that you're helping me out!

Cheers!

well, right now, all in all 22 options. maximum of 6 different options in a single dropdown.

I don't get it, I thought each dropdown was the same - certainly the impression I got from your image (Andra benchmark).

OK, we need to know if the all 5 dropdowns are specific to one field to search? Or are your 5 dropdowns specific to 5 different fields?

If all dropdowns pertain to one search field, I would strongly suggest the checkbox solution, as with inline checkboxes, you could something like 4 rows x 6 items (24 items).
If this si too big, you could always have an accordion-type widget and place them inside that, so they could be hidden. It would be like an inline checkbox dropdown!

[adishardis]

I'm sorry for being unclear, they are 5 are specific to 5 different fields! I just copied an existing dropdown field for illustration purposes.

OK stick to dropdowns then. Few ways you can do this.
Probably the easiest would be to have 5 tables for each dropdown, with this structure:

drop1 table
type_id (PK) || type_label

drop2 table
type_id (PK) || type_label

(etc)

main table
main_id (PK) || field1 || ... || drop1_id || drop2_id || drop3_id || drop4_id || drop5_id

You can create the dropdowns dynamically with a loop, e.g (using mysql - but try mysqli or PDO instead).

$op1 = '<option value="0">Choose one...</option>';
$r = mysql_query("SELECT type_id, type_label FROM drop1");
if(mysql_num_rows($r)){
    while($d = mysql_fetch_assoc($r)){
        $op1 .= "\n\t<option value='{$d['type_id']}'">{$d['type_label']}</option>";
    }
}
//do this for each drop# table - so you get $op1, $op2, $op3 etc.

<!-- HTML within form-->
<select id="drop1">
    <?php echo $op1;?>
</select>
<!--(etc)-->

Your search string then could be built from the $_POST vars from the form:

if(isset($_POST['submit_search'])){
    for($x=1;$x<6;$x++){
        $dropint = intval($_POST['drop'.$x]);
        if($dropint != 0){
            $wherearray[] "drop$x_id = $dropint";
        }
    }
    $where = ($wherearray) ? ' WHERE ' . implode(' AND ', $wherearray) : '';
    //I'm assuming you need AND not OR
}

$result = mysql_query("SELECT * FROM TABLE$where ORDER BY Datum_formatning ASC");

This is just an example, not something I suggest you use as is.

An alternative would see a 'combined' types table:

type_id (PK) || tape_label || category_id (e.g. 1,2,3,4 or 5 - relating to dropdown)

[adishardis]

Hi, thank you for your advice! I've been looking into a few solutions and I've decided that what I really need is chained dropdowns so that the user won't get zero results on the selected combinations of dropdowns and that preferrably works without page reload until the a submit button is clicked.

I've got this somewhat working however it's not quite as I would like it.

Using the code below I get the first dropdownfilter option then onchange I get the next and so on.

I am now wondering if it's possible to change it so that all my dropdowns are visible from getgo and when one dropdown is choosen the rest would update accordingly?

On my main page I have

<?php include("select_list.php"); ?>
    <script src="js/ajax_select.js" type="text/javascript"></script>

    <form action="" method="post">
                            Select: <?php echo $re_html; ?>
                            </form>

Select_list.php:

<?php
if(!isset($_SESSION)) session_start();
// Here add your own data for connecting to MySQL database
$server = 'XXX';
$user = 'XXX';
$pass = 'XXX';
$dbase = 'XXX';
mysql_query( "SET NAMES utf8");
mysql_query( "SET CHARACTER SET utf8");

// Here add the name of the table and columns that will be used for select lists, in their order
// Add null for 'col_description' if you don`t want to display their data too
$table = 'MCI';
$ar_cols = array('Produktkategori', 'Kanal', 'Kampanjnamn');

$preid = 'slo_';        // a prefix used for element's ID, in which Ajax will add <select>
$col = $ar_cols[0];     // the variable used for the column that wil be selected
$re_html = '';          // will store the returned html code

// if there is data sent via POST, with index 'col' and 'wval'
if(isset($_POST['col']) && isset($_POST['wval'])) {
  // set the $col that will be selected and the value for WHERE (delete tags and external spaces in $_POST)
  $col = trim(strip_tags($_POST['col']));
  $wval = "'".trim(strip_tags($_POST['wval']))."'";
}

$key = array_search($col, $ar_cols);            // get the key associated with the value of $col in $ar_cols
$wcol = $key===0 ? $col : $ar_cols[$key-1];     // gets the column for the WHERE clause
$_SESSION['ar_cols'][$wcol] = isset($wval) ? $wval : $wcol;    // store in SESSION the column and its value for WHERE

// gets the next element in $ar_cols (needed in the onchange() function in <select> tag)
$last_key = count($ar_cols)-1;
$next_col = $key<$last_key ? $ar_cols[$key+1] : '';

$conn = new mysqli($server, $user, $pass, $dbase);     // connect to the MySQL database

if (mysqli_connect_errno()) { exit('Connect failed: '. mysqli_connect_error()); }     // check connection

// sets an array with data of the WHERE condition (column=value) for SELECT query
for($i=1; $i<=$key; $i++) {
  $ar_where[] = '`'.$ar_cols[$i-1].'`='.$_SESSION['ar_cols'][$ar_cols[$i-1]];
}

// define a string with the WHERE condition, and then the SELECT query
$where = isset($ar_where) ? ' WHERE '. implode($ar_where, ' AND ') : '';
$sql = "SELECT DISTINCT `$col` FROM `$table`".$where;

$result = $conn->query($sql);       // perform the query and store the result

// if the $result contains at least one row
if ($result->num_rows > 0) {
  // sets the "onchange" event, which is added in <select> tag
  $onchg = $next_col!==null ? " onchange=\"ajaxReq('$next_col', this.value);\"" : '';

  // sets the select tag list (and the first <option>), if it's not the last column
  if($col!=$ar_cols[$last_key]) $re_html = $col. ': <select name="'. $col. '"'. $onchg. '><option>- - -</option>';

  while($row = $result->fetch_assoc()) {
    // if its the last column, reurns its data, else, adds data in OPTION tags
    if($col==$ar_cols[$last_key]) $re_html .= '<br/>'. $row[$col];
    else $re_html .= '<option value="'. $row[$col]. '">'. $row[$col]. '</option>';
  }

  if($col!=$ar_cols[$last_key]) $re_html .= '</select> ';        // ends the Select list
}
else { $re_html = '0 results'; }

$conn->close();

// if the selected column, $col, is the first column in $ar_cols
if($col==$ar_cols[0]) {
  // adds html code with SPAN (or DIV for last item) where Ajax will add the select dropdown lists
  // with ID in each SPAN, according to the columns added in $ar_cols
  for($i=1; $i<count($ar_cols); $i++) {
    if($ar_cols[$i]===null) continue;
    if($i==$last_key) $re_html .= '<div id="'. $preid.$ar_cols[$i]. '"> </div>';
    else $re_html .= '<span id="'. $preid.$ar_cols[$i]. '"> </span>';
  }

  // adds the columns in JS (used in removeLists() to remove the next displayed lists when makes other selects)
  $re_html .= '<script type="text/javascript">var ar_cols = '.json_encode($ar_cols).'; var preid = "'. $preid. '";</script>';
}
else echo $re_html;
?>

And finally the ajax_select.js:

// Multiple select lists - http://coursesweb.net/ajax/

// function used to remove the next lists already displayed when it chooses other options
function removeLists(colid) {
  var z = 0;
  // removes data in elements with the id stored in the "ar_cols" variable
  // starting with the element with the id value passed in colid
  for(var i=1; i<ar_cols.length; i++) {
    if(ar_cols[i]==null) continue;
    if(ar_cols[i]==colid) z = 1;
    if(z==1) document.getElementById(preid+ar_cols[i]).innerHTML = '';
  }
}

// create the XMLHttpRequest object, according browser
function get_XmlHttp() {
  // create the variable that will contain the instance of the XMLHttpRequest object (initially with null value)
  var xmlHttp = null;

  if(window.XMLHttpRequest) { xmlHttp = new XMLHttpRequest(); }     // for Forefox, IE7+, Opera, Safari
  else if(window.ActiveXObject) { xmlHttp = new ActiveXObject("Microsoft.XMLHTTP"); }      // IE5 or 6

  return xmlHttp;
}

// sends data to a php file, via POST, and displays the received answer
function ajaxReq(col, wval) {
  removeLists(col);           // removes the already next selects displayed

  // if the value of wval is not '- - -' and '' (the first option)
  if(wval!='- - -' && wval!='') {
    var request =  get_XmlHttp();             // call the function with the XMLHttpRequest instance
    var php_file = 'select_list.php';     // path and name of the php file

    // create pairs index=value with data that must be sent to server
    var  data_send = 'col='+col+'&wval='+wval;

    request.open("POST", php_file, true);           // set the request

    document.getElementById(preid+col).innerHTML = 'Loadding...';   // display a loading notification

    // adds a header to tell the PHP script to recognize the data as is sent via POST
    request.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
    request.send(data_send);            // calls the send() method with data_send

    // Check request status
    // If the response is received completely, will be added into the tag with id value of "col"
    request.onreadystatechange = function() {
      if (request.readyState==4) {
        document.getElementById(preid+col).innerHTML = request.responseText;
      }
    }
  }
}

I understand if you don't have time to look through it all but if you understand what I'm after maybe you have know of a easier approach or some tips on how to progress.

Anyway thank you for all your help and sparking my interest in webdevelopement I'm actually thinking of taking an evening course learning pdo or mysqli after christmas!

Cheers