Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    Apr 2014
    Posts
    20
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Dynamic dropdown assistance needed

    I am a noob with sql statements and tying through php so please excuse my ignorance. I really appreciate any assistance.

    I have a 4 level dynamic dropdown connected to a sql db that I am trying to implement. Once all of the selections have been made, pressing the go button will call up a datatables query pushing results to a results page.

    I connected the dropdowns to the sql db via this tutorial and it wasnt very difficult. Multiple Select Dropdown List with AJAX That tutorial only is set up for 3 dropdowns and I need to add the forth and am not sure how to do so.

    I need to accomplish several things here
    1. need to add a forth dropdown for Engine populated by the 'E' column in the table
    2. need to sort the dropdowns in ascending order
    3. need to change the labels of the search boxes from Y, MA, MO, E to Year, Make, Model, Engine
    4. need to tie the complete selections to the go button so pressing the go button puts the 4 selections into a string that I can then pass on to datatables

    See this link to see where I am at so far

    my php file:
    Code:
    if(!isset($_SESSION)) session_start();
    
    
    $server = 'hostedresource.com';
    $user = '';
    $pass = '';
    $dbase = '';
    
    $table = 'data';
    $ar_cols = array('Y', 'MA', 'MO', 'E');
    
    $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;
    ajax_select.js
    Code:
    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
    my html:
    Code:
    <form action="" name="ymm" method="post">
    Select: <?php echo $re_html; ?>
    </form>
    <form action="search.aspx" name="ymm" target="_parent">
    <input type="hidden" name="find" id="find" />
    <input name="Submit" type="submit" 
    onClick="setupForm()" value="Go">

  • #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,132
    Thanks
    2
    Thanked 328 Times in 320 Posts
    Answers to your queries -

    1) The code looks to be general purpose. To add a forth dropdown, you would add it as the forth entry in the array AND add a fifth entry that's either a column you want to display a list of values from or a null for no display.

    2) You would modify the SELECT DISTINCT query, at about line 46, to add ORDER BY `$col` to the end of the query.

    3) Your table should have meaningful column names in the first place, but if you want to make the code even more generic, with arbitrary display labels for each dropdown, you would make a second array like $ar_cols, with the $ar_cols values as the keys and the actual display label as the value. Then in the code that is buidling the dropdown menus, you would use the $col value to get the actual legend out of the new array and display it instead of the $col value.

    4) The tutorial site tells you how to put the select menus into a form so that their values get submitted when that form is submitted. Your GO button would need to be in the same form that you are outputing the select menus in.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • Users who have thanked CFMaBiSmAd for this post:

    jagsweb (07-11-2014)

  • #3
    New Coder
    Join Date
    Apr 2014
    Posts
    20
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Thanks, that was a big help. I got some of it fixed

    1. Fixed

    2
    Code:
    $sql = "SELECT DISTINCT `$col` FROM `$table` ORDER BY `$col`".$where;
    This did not work, breaks dropdowns to only the year category. Tried a couple of variations but couldnt get it to work

    3. Fixed, renamed the columns

    4. Partially fixed. I moved the go button into the form but am stuck on tying the selections to the go button. The tutorial says to use $_POST['Year'+'Make'+'Model'+'Engine'] and to put it in the php script, but I am not sure where to put it or how to call it. See below for where I put it in the php in the form. Obviously wrong as it accomplished nothing
    Code:
    <form action="search.aspx"  name="ymm" method="post" target="_parent">
    <?php echo $re_html; 
     $_POST['Year'+'Make'+'Model'+'Engine']
    ?>
    <input type="hidden" name="find" id="find" />
    <input name="Submit" type="submit" 
    onClick="setupForm()" value="Go">
    </form>

  • #4
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,132
    Thanks
    2
    Thanked 328 Times in 320 Posts
    Re:

    2) My reply stated to add that onto the end of the query. The syntax definition (what you can do and where the parts go) is all covered in the mysql documentation.

    4) If you were submitting this to a .php page, you would use $_POST variables, one for each named form field - <select name='Year'> ($_POST['Year']), <select name='Make'> ($_POST['Make']). Since you show submitting this to a .aspx page, you would need to use whatever method aspx provides for accessing submitted form field data.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,206
    Thanks
    80
    Thanked 4,566 Times in 4,530 Posts
    It's really strange to see a mixture of PHP and ASP.NET pages in the same site like that, but it's not illegal or impossible.

    Assuming that the searching is indeed supposed to be done by that "search.aspx" page, then the code KIND of makes sense.

    But you would need to get the $_POST[one_field_at_a_time] and put them into <form> fields and submit the form. Pretty complex. Why are you trying to mix PHP and ASP.NET???
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    jagsweb (07-12-2014)

  • #6
    New Coder
    Join Date
    Apr 2014
    Posts
    20
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Sorry, I tried putting it at the end just as you said and it did not work either

    Code:
    $sql = "SELECT DISTINCT `$col` FROM `$table`".$where; ORDER BY `$col`
    I also tried it before the ; and checked the sql documentation and could not get it to work in any variation.... Not sure what I'm still doing wrong.

    It is all php and not aspx I was messing around with it on another site of mine seeing if it would work.

  • #7
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,132
    Thanks
    2
    Thanked 328 Times in 320 Posts
    The php code to build the sql query statement needs to be -

    PHP Code:
    $sql "SELECT DISTINCT `$col` FROM `$table` $where ORDER BY `$col`"
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • Users who have thanked CFMaBiSmAd for this post:

    jagsweb (07-21-2014)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •