Start Free Trial

asked on

Select boxe and MySQL

Hi,

I have data in three columns in the database that represents three levels.
Column 'Amt' is the top level
Column 'Herred' is the middle level
Column 'Sogn' it the bottom level

I have made this page: http://kroweb.dk/gfdev/dropdown/ where I have the three levels represented in three select boxes.

What I need is this:
The 'Amt' box should reflect a dropdown box with the content of the column 'Amt' in the database
The 'Herred' box should reflect a dropdown box with the content of the column 'Herred' within the chosen 'Amt'
The 'Sogn' box should reflect a dropdown box with the content of the column 'Sogn' within the chosen 'Amt' and 'Herred'.

I have this HTML (commented where I believe something more is needed :) :

                    <!-- Søge felter -->
        <div class="container" style="width:100%;padding-top:10px;background-color:#f6f6f6;">

            <!-- HEADER START -->
            <div class="row" id="header">
                <form method="POST" style="margin-left:30px;"> 
   

                     Amt                        
                        <select id="amt" name="amt">
                        <option value=""></option>
                        <option value="Content of column 'Amt'">Content of column 'Amt'</option> <!-- something else needed -->
                
                    </select>

                   Herred                        
                        <select id="herred" name="herred">
                        <option value=""></option>
                        <option value="Content of column 'Herred'">Content of column 'Herred'</option> <!-- something else needed -->
                     </select>                      

                   Sogn                        
                        <select id="sogn" name="sogn">
                        <option value=""></option>
                        <option value="Content of column 'Sogn'">Content of column 'Sogn'</option> <!-- something else needed -->

                    </select>

                   <input type="submit" title="Klik her, eller tast Enter, for at aktivere søgningen." style="margin-left:5px;padding-right:10px;height:25px;font-family:arial; font-size: 12px; text-align:left; background-color:#cccccc; border: 2px solid #grey;border-radius:2px; color:black; background-color:#cccccc; box-shadow: 0 8px 16px 0 rgba(0,0,0,0.2), 0 6px 20px 0 rgba(0,0,0,0.19);" value="Søg">

                    <input type="submit" style="margin-left:5px;height:25px;font-family:arial; font-size: 12px; text-align:left; background-color:#cccccc; border: 2px solid grey;border-radius:2px; color:black; background-color:#cccccc; box-shadow: 0 8px 16px 0 rgba(0,0,0,0.2), 0 6px 20px 0 rgba(0,0,0,0.19);;" value="Nulstil">                    

                </form>
            </div>

Open in new window

And I have this php:

<?php
// Turn on error reporting
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Do we need the database
if (!empty($_POST)):

    // Connect to the database is we have a POST
    $mysqli = mysqli_connect("db credentials") or die("Error connecting to database!");
    $mysqli->set_charset("utf8");

    // Trim the input values
    $sogn = trim($_POST['sogn']);
    $amt = trim($_POST['amt']);
    $amt = trim($_POST['herred']);




    if (!empty($amt) && empty($herred) && empty($sogn)):

        // We have a 'mmt' value and no 'herred' value no 'sogn' value


        $query = $mysqli->prepare("SELECT * FROM sogne WHERE Amt = ?");
        $query->bind_param("s", $amt);

    else:

        if (!empty($amt) && !empty($herred) && empty($sogn)):
            // We have a 'amt' value a 'herred' value and no 'sogn' value
            $query = $mysqli->prepare("SELECT * FROM sogne WHERE Amt = ? AND Herred = ?");
            $query->bind_param("ss", $amt, $herred);

        elseif (!empty($amt) && !empty($herred) && !empty($sogn)):
            // We have a 'amt' and a 'herred' and a sogn' value
            $query = $mysqli->prepare("SELECT * FROM sogne WHERE Amt = ? AND Herred = ? AND Sogn = ?");
            $query->bind_param("sss", $amt, $herred, $sogn);

        endif;

    endif;

    // Do we have a query to run
    if (isset($query) && $query):
        $query->execute();
        $results = $query->get_result();
    endif;

endif;
?>

Open in new window

I hope that somebody can support me with a little guidance on this.

There is this PAQ that deals with this issue (https://www.experts-exchange.com/questions/28980758/selecting-a-category-and-selecting-subcategories.html)

You can find a sample with source code here (http://www.marcorpsa.com/ee/t1496.html)
This sample demonstrates both in page linking and using an AJAX call to get the next round of data
Code replicated here

  <form class="form form-horizontal">
    Get from server <input type="checkbox" name="server" id="server" /><br/>
    <div class="form-group">
      <label for="company" class="col-sm-2 control-label">Company</label>
      <div class="col-sm-10">
        <select id="company" name="company" class="form-control linked-dropdown" data-linked="make">
          <option value="">-- Select Product --</option>
          <option value="1">Product1</option>
          <option value="2">Product2</option>
          <option value="3">Product3</option>
        </select>
      </div>
    </div>
    <div class="form-group">
      <label for="make" class="col-sm-2 control-label">Make</label>
      <div class="col-sm-10">
        <select id="make" name="make" class="form-control linked-dropdown" data-linked="model"></select>
      </div>
    </div>
    <div class="form-group">
      <label for="model" class="col-sm-2 control-label">Model</label>
      <div class="col-sm-10">
        <select id="model" name="model" class="form-control"></select>
      </div>
    </div>
  </form>

Open in new window

Data for demonstrating in page linking

<script>
var options = {
  make: {
    1:{
      1: 'Prod1Make1',
      2: 'Prod1Make2',
      3: 'Prod1Make3'
    },
    2:{
      4: 'Prod2Make1',
      5: 'Prod2Make2',
      6: 'Prod2Make3'
    },
    3 :{
      7: 'Prod3Make1',
      8: 'Prod3Make2',
      9: 'Prod3Make3'
    }
  },
  model: {
    1: {
      1: 'Prod1Make1Model1',
      2: 'Prod1Make1Model2',
      3: 'Prod1Make1Model3'
    },
    2: {
      4: 'Prod1Make2Model1',
      5: 'Prod1Make2Model2',
      6: 'Prod1Make2Model3'
    },
    3: {
      7: 'Prod1Make3Model1',
      8: 'Prod1Make3Model2',
      9: 'Prod1Make3Model3'
    },
    4: {
      10: 'Prod2Make1Model1',
      11: 'Prod2Make1Model2',
      12: 'Prod2Make1Model3'
    },
    5: {
      13: 'Prod2Make2Model1',
      14: 'Prod2Make2Model2',
      15: 'Prod2Make2Model3'
    },
    6: {
      16: 'Prod2Make3Model1',
      17: 'Prod2Make3Model2',
      18: 'Prod2Make3Model3'
    },
    7: {
      19: 'Prod3Make1Model1',
      20: 'Prod3Make1Model2',
      21: 'Prod3Make1Model3'
    },
    8: {
      22: 'Prod3Make2Model1',
      23: 'Prod3Make2Model2',
      24: 'Prod3Make2Model3'
    },
    9: {
      25: 'Prod3Make3Model1',
      26: 'Prod3Make3Model2',
      27: 'Prod3Make3Model3'
    }
  }
};
</script>

Open in new window

jQuery

<script>
$(function() {
  $('.linked-dropdown').change(function() {
    // GET THE id OF THE NEXT DROPDOWN
    // IN THE CHAIN
    var linked = $(this).data('linked');
    
    // CREATE THE SELECTOR
    var target = '#' + linked;
  
    // EMPTY THE TARGET
    $(target).empty();
    
    // EMPTY ALL THE CHILDREN
    var child = $(target).data('linked');
    while (child) {
      $('#' + child).empty();
      child = $('#' + child).data('linked');
    }
    
    // ARE WE GETTING THIS FROM THE SERVER
    // OR USING LOCAL ARRAY
    if ($('#server').is(':checked')) {
    
      // SERVER SEND AJAX CALL AND 
      // POPULATE WITH RESPONSE
      $.ajax({
        url: 't1496.php',
        data: {
          linked: linked,
          value: $(this).val()
        },
        type: "POST",
        dataType: "html"
      }).then(function(resp) {
      
        // SERVER BUILDS HTML RETURN
        // WE JUST INSERT IT
        $(target).html(resp);
      });
    }
    else {
      // GET THE SELECTED VALUE
      var val = $(this).val();
      // AND USE THIS TO GET THE CORRESPONDING
      // ARRAY OF OPTIONS
      opts = options[linked][val];
    
      // INSERT THE INSTRUCTION OPTION
      $(target).append($('<option/>').val('').html('-- Select --'));
      
      // LOOP THROUGH THE TARGET OPTIONS AND ADD THEM
      $.each(opts, function(v,o) {
        $(target).append($('<option/>').html(o).val(v))
      });
    }
  });
});
</script>

Open in new window

PHP (server code demonstrating how to return data for linked select's asynchronously)

<?php
 
$options = array (
  'make' => array (
    1 => array (
      1 => 'Server-Prod1Make1',
      2 => 'Server-Prod1Make2',
      3 => 'Server-Prod1Make3'
    ),
    2=> array (
      4=> 'Server-Prod2Make1',
      5=> 'Server-Prod2Make2',
      6=> 'Server-Prod2Make3'
    ),
    3 => array (
      7=> 'Server-Prod3Make1',
      8=> 'Server-Prod3Make2',
      9=> 'Server-Prod3Make3'
    )
  ),
  'model' => array (
    1=> array (
      1=> 'Server-Prod1Make1Model1',
      2=> 'Server-Prod1Make1Model2',
      3=> 'Server-Prod1Make1Model3'
    ),
    2=> array (
      4=> 'Server-Prod1Make2Model1',
      5=> 'Server-Prod1Make2Model2',
      6=> 'Server-Prod1Make2Model3'
    ),
    3=> array (
      7=> 'Server-Prod1Make3Model1',
      8=> 'Server-Prod1Make3Model2',
      9=> 'Server-Prod1Make3Model3'
    ),
    4=> array (
      10=> 'Server-Prod2Make1Model1',
      11=> 'Server-Prod2Make1Model2',
      12=> 'Server-Prod2Make1Model3'
    ),
    5=> array (
      13=> 'Server-Prod2Make2Model1',
      14=> 'Server-Prod2Make2Model2',
      15=> 'Server-Prod2Make2Model3'
    ),
    6=> array (
      16=> 'Server-Prod2Make3Model1',
      17=> 'Server-Prod2Make3Model2',
      18=> 'Server-Prod2Make3Model3'
    ),
    7=> array (
      19=> 'Server-Prod3Make1Model1',
      20=> 'Server-Prod3Make1Model2',
      21=> 'Server-Prod3Make1Model3'
    ),
    8=> array (
      22=> 'Server-Prod3Make2Model1',
      23=> 'Server-Prod3Make2Model2',
      24=> 'Server-Prod3Make2Model3'
    ),
    9=> array (
      25=> 'Server-Prod3Make3Model1',
      26=> 'Server-Prod3Make3Model2',
      27=> 'Server-Prod3Make3Model3'
    )
  )
);
 
$val = isset($_POST['value']) ? intval($_POST['value']) : false;
$linked = isset($_POST['linked']) ? $_POST['linked'] : false;
if ($val && $linked) {
  $opt = $options[$linked][$val];
  $return = <<< OPTION
      <option value="">-- Select --</option>
 
OPTION;
  foreach($opt as $v => $o) {
    $return .= <<< OPTION
      <option value="{$v}">{$o}</option>
OPTION;
  }
  
  echo $return;
}

Open in new window

ASKER

Thanks Julian,

This is very helpful, but it does not tell me anything about how I fetch the data from a database, in stead of hardcoding them in the code, like

<option value="">-- Select Product --</option>
<option value="1">Product1</option>
<option value="2">Product2</option>
<option value="3">Product3</option>

Understood. You can do like this
HTML - as you have it above
jQuery

<script>
$(function() {
  $('#amt').change(function() {
     $.get('options.php',{amt: $(this).val()}, function(options) {
        $('#herred').html(options)
     }) 
  });
  
  $('#herred').change(function() {
     $.get('options.php',{amt: $('#amt').val(), herred: $(this).val()}, function(options) {
        $('#sogn').html(options)
     })
  });
})
</script>

Open in new window

On the server side - this is just an example
options.php

<?php

require_once('connection.php');

$amt = isset($_GET['amt']) ? $_GET['amt'] : false;
$herred = isset($_GET['herred']) ? $_GET['herred'] : false;

if ($amt) {
  // CHANGE AS REQUIRED
  if ($herred) {
    $query = "SELECT sogn AS field FROM t2834 WHERE amt='{$amt}' and herred='{$herred}'";
  }
  else {
    $query = "SELECT herred AS field  FROM t2834 WHERE amt='{$amt}'";
  }

  $result = $mysqli->query($query);
  if ($result) {
    while($row = $result->fetch_object()) {
      echo <<< OPTION
        <option value="{$row->field}">{$row->field}</option>
OPTION;
    }
  }
}

Open in new window

Working sample here

ASKER

Thanks Julian,

I will work with this and get back to you :)

ASKER

Hi Julian,

Sorry for the late response - but I have been tied a little up in other projects.

Thanks a lot for your very nice and fine explanation and the very easy understandable working sample.

But as I read it, you are actually building a database in your working sample. What I need is acces a database.

Let me explain the actual project in a little more detail.

I have a MySql table with appr. 90.000 lines.
I acces this table through three select boxes and a text search field.
The three select boxes are labeled according to the columns in the MySql table as "Amt" "Type" "År" (Aar in the MySQL table).

What I would like to do, is to limit the results in the "År" box according to the choices made in the "Amt" box and/or in the "Type" box. The values for the "Amt" and the "Type" box I keep "hard-coded" but I would like the "År" box to look at the "Aar" column in the table and return the values that correspond to the choices made in the two other boxes - BUT since an actual "Aar" value is very likely to be represented several times in the results I only want it to show once in the select list. If no choices are made in those two boxes the "År" box should show the content od the "Aar" column - still showing only one instance per value.

You can take a look at the page here http://kroweb.dk/gfdev/ft_raw2/ and if you need any code I will of course post it here.

I hope you can guide me in the right direction to get this done :)

I am not building a database I am making selections from the database based on choices already made. It should be a fairly trivial process to adapt to your requirements

You need to change the query to

SELECT
   DISTINCT `AAR`
FROM
   `YOURTABLE`
WHERE
  `AMT`='VALUE_FROM_AMT_DROPDOWN' AND 
  `TYPE` ='VALUE_FROM_TYPE_DROPDOWN'

Open in new window

Return the data as per my sample and populate the drop down.

ASKER

Thanks Julian,

I will go to work on that.

I just thought you were building a database here :) ?

CREATE TABLE `t2834` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `amt` int(11) DEFAULT NULL,
  `herred` varchar(50) DEFAULT NULL,
  `sogn` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

Open in new window

That was so I could create a working demo - I don't have access to your database so I needed somewhere to get the data from.
My samples always provide everything someone might need to replicate.

Just pretend that my database is your database and that I am getting data from yours instead of mine.

ASKER

OK - I'll move on from here :)

ASKER

Hi Julian,

I have tried to work your suggestion into my project, but I am not getting eny values in the "År" drop-down field. What am I doing wrong - think it might be around line 76 in the main file?

This is the page: http://kroweb.dk/gfdev/dropdown/dropdown.php

I have this main file:

<!doctype html>
<html>
<head>
<meta charset="UTF-8"/>
<meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=2.0, user-scalable=yes" />
<title>Linked dropdowns using AJAX</title>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css" rel="stylesheet" />
<link href="css/custom.css" rel="stylesheet" />
<style type="text/css">
</style>
</head>
<body>
<div class="wrapper">
  <header>
    <!--<div class="container">
    <h1 class="col-lg-9">Linked dropdowns using AJAX</h1>
    
    </div> -->
  </header>
  <div class="container">
    <h5>Testpilot: Peter Kroman, December 2017</h5>
  <!--<p class="alert alert-success"></p>-->
<!-- Søge felter -->
        <div class="container" style="width:100%;padding-top:10px;background-color:#f6f6f6;">
 
            <!-- HEADER START -->
            <div class="row" id="header">
                <form method="POST" id="search" style="margin-left:30px; margin-right: 30px;"> 
                <label for="amt">Amt</label> 
                <select id="amt" name="amt" style="width: 125px; margin-right: 20px; font-weight:normal; ">
                    <option value=""></option>
                    <option value="Bornholm">Bornholm</option>
                    <option value="Frederiksborg">Frederiksborg</option>
                    <option value="Færøerne">Færøerne</option>
                    <option value="Grønland">Grønland</option>
                    <option value="Haderslev">Haderslev</option>
                    <option value="Hjørring">Hjørring</option>
                    <option value="Holbæk">Holbæk</option>
                    <option value="København">København</option>
                    <option value="Køøge">Køge</option>               
                    <option value="Maribo">Maribo</option>
                    <option value="Odense">Odense</option>
                    <option value="Præstø">Præstø</option>
                    <option value="Randers">Randers</option>
                    <option value="Ribe">Ribe</option>
                    <option value="Ringkøbing">Ringkøbing</option>
                    <option value="Roskilde">Roskilde</option>
                    <option value="Skanderborg">Skanderborg</option>
                    <option value="Sorø">Sorø</option>
                    <option value="Svendborg">Svendborg</option>
                    <option value="Sønderborg">Sønderborg</option>
                    <option value="Thisted">Thisted</option>
                    <option value="Tønder">Tønder</option> 
                    <option value="Vejle">Vejle</option>
                    <option value="Viborg">Viborg</option>
                    <option value="Aabenraa">Aabenraa</option>
                    <option value="Aalborg">Aalborg</option>
                    <option value="Aarhus">Aarhus</option>
                    <option value="Åbenrå-Sønderborg">Åbenrå-Sønderborg</option>
                </select>

                <label for="type">Type</label>
                <select id="type" name="type"style="width: 140px; margin-right: 20px; font-weight:normal;">
                    <option value=""></option>
                    <option value="Landdistrikt">Søg i Landdistrikter</option>
                    <option value="Købstad">Søg i Købstæder</option>
                    <option value="Hovedstaden">Søg i Hovedstaden</option>
                    <option value="Slesvig">Søg i Selsvig</option>
                    <option value="Særtælling">Søg i Særtællinger (alle Typer)</option>
                    <option value="Diverse">Søg i Diverse</option>
                </select>


                <label for="aar">År</label>                
                <select id="aar" name="aar" style=" width: 100px; margin-right: 20px; font-weight:normal; ">
                    <option value= "<?php echo $row->Aar?>" ><?php echo $row->Aar?></option>
                    
                </select>

                <label for="sogn">Sted, By, Sogn, Gade</label>
                <input type="text" name="sogn" id="sogn" placeholder="Fritekst" style="color: #e08a94;">
                <input type="submit" value="Søg" name="Sog" id="Sog">  
                <input type="reset" value="Nulstil">
                <script>
                $(':reset', '#search').click(function(e) {
                    $('#ft_items').html('');
                });
                </script>

            </form>
            </div>
    </div>
 
</div>
</div>
<footer>
  <div class="container">
    Testing Peter Kroman &copy; 2017
  </div>
</footer>
<!-- INCLUDE "limit.php:PHP" -->
<!-- INCLUDE "SQL-connection" -->
<script src="http://code.jquery.com/jquery.js"></script>
<!-- Latest compiled and minified JavaScript -->
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>
<script>
$(function() {
  $('#amt').change(function() {
     $.get('limit.php',{amt: $(this).val()}, function(options) {
        $('#aar').html(options)
     }) 
  });
  $('#type').change(function() {
     $.get('limit.php',{amt: $('#amt').val(), type: $(this).val()}, function(options) {
        $('#aar').html(options)
     })
  }); 

})
</script>
</body>
</html>

Open in new window

And this php file:

<?php

// Turn on error reporting
error_reporting(E_ALL);
ini_set('display_errors', 1);
 
require_once('connection.php');
    $mysqli = mysqli_connect("db credentisla) or die("Error connecting to database!");
$mysqli->set_charset("utf8");
 
$amt = isset($_GET['amt']) ? $_GET['amt'] : false;
$type = isset($_GET['type']) ? $_GET['type'] : false;
$aar = isset($_GET['aar']) ? $_GET['aar'] : false;
 
if ($amt) :
  if ($type) :
    $query = "SELECT DISTINCT Aar AS field FROM ftu WHERE Amt='{$amt}' and Type='{$type}'";
  
  elseif ($type):
    $query = "SELECT DISTINCT Aar AS field FROM ftu WHERE Type='{$type}'"; 

  else: 
     if (!empty ($amt) && !empty ($type)) :
     $query = "SELECT DISTINCT Aar AS field FROM ftu ";   

  endif;
endif;
 
  $result = $mysqli->query($query);
  if ($result) :
	echo "<option>Select a value </option>";
    while($row = $result->fetch_object()) {
      echo <<< OPTION
        <option value="{$row->field}">{$row->field}</option>
OPTION;
    }
  endif;
  
}

Open in new window

This does not make sense

if ($type) :
    $query = "SELECT DISTINCT Aar AS field FROM ftu WHERE Amt='{$amt}' and Type='{$type}'";
  
  elseif ($type):
    $query = "SELECT DISTINCT Aar AS field FROM ftu WHERE Type='{$type}'"; 

  else:

Open in new window

You are checking $type twice

ASKER

OK - can I edit it like this;

if (!empty($amt) && !empty($type)) :

    $query = "SELECT DISTINCT Aar AS field FROM ftu WHERE Amt='{$amt}' and Type='{$type}'";
  
  elseif (!empty($type)):
    $query = "SELECT DISTINCT Aar AS field FROM ftu WHERE Type='{$type}'"; 

  else: 
     if (empty ($amt) && empty ($type)) :
     $query = "SELECT DISTINCT Aar AS field FROM ftu ";   

  endif;
endif;

Open in new window

Does that produce the correct results?

ASKER

No - still no result :)

Add this

echo $query . "<br/>";
echo "Error: " . $mysqli->error;

Open in new window

Call the script directly from the browser adding the parameters to the url

http://yourserver/scriptname.php?amt=VALUE-HERE&aar=VALUE-HERE&type=VALUE-HERE

Open in new window

Check for DB errors and verify the dumped query by copying it and running it directly against the database.

ASKER

When running this http://kroweb.dk/gfdev/dropdown/limit.php?amt=VALUE-HERE&aar=VALUE-HERE&type=VALUE-HERE from the browser, I get this error:
Parse error: syntax error, unexpected '$result' (T_VARIABLE) in /var/www/kroweb.dk/public_html/gfdev/dropdown/limit.php on line 31

where line 31 is the same line here:

<?php

// Turn on error reporting
error_reporting(E_ALL);
ini_set('display_errors', 1);
 
require_once('connection.php');
    $mysqli = mysqli_connect("db credentials") or die("Error connecting to database!");
$mysqli->set_charset("utf8");
 
$amt = isset($_POST['amt']) ? $_POST['amt'] : false;
$type = isset($_POST['type']) ? $_POST['type'] : false;
$start = isset($_POST['aar']) ? $_POST['aar'] : false;
 
if (!empty($amt) && !empty($type)) :

    $query = "SELECT DISTINCT Aar AS field FROM ftu WHERE Amt='{$amt}' and Type='{$type}'";
  
  elseif (!empty($type) && empty($amt)) :
    $query = "SELECT DISTINCT Aar AS field FROM ftu WHERE Type='{$type}'"; 

  else : 
     if (empty($amt) && empty($type)) :
     $query = "SELECT DISTINCT Aar AS field FROM ftu ";   

  endif;
endif;

var_dump($query)
 
  $result = $mysqli->query($query);
  if ($result) :
	echo "<option>Select a value </option>";
    while($row = $result->fetch_object()) {
      echo <<< OPTION
        <option value="{$row->field}">{$row->field}</option>
OPTION;
    }
  endif;
  
}

Open in new window

ASKER

I believe that we driving down a wrong track here :)

As I see your setup the choise in the drill-down boxes have to be made in a specific sequence in order to get to final selection.

That's not what I'm after. What I'm after is the possibility to make a choice in one, or in two drilldown boxes ("Amt"and/or "Type"), and that the choise i might make there will limit the displayed data in the third drill-down box ("År"). AND - if no choices are made in the first two drill down boxes the third one should just show the full content.

I am not sure that it is convenient to do this running it through the database, so what I think we should aim at is to do this with the hard coded values, like the ones below.

                <label for="amt">Amt</label> 
                <select id="amt" name="amt" style="width: 125px; margin-right: 20px; font-weight:normal; ">
                    <option value=""></option>
                    <option value="Bornholm">Bornholm</option>
                    <option value="Frederiksborg">Frederiksborg</option>
                    <option value="Færøerne">Færøerne</option>
                    <option value="Grønland">Grønland</option>
                    <option value="Haderslev">Haderslev</option>
                    <option value="Hjørring">Hjørring</option>
                    <option value="Holbæk">Holbæk</option>
                    <option value="København">København</option>
                    <option value="Køøge">Køge</option>               
                    <option value="Maribo">Maribo</option>
                    <option value="Odense">Odense</option>
                    <option value="Præstø">Præstø</option>
                    <option value="Randers">Randers</option>
                    <option value="Ribe">Ribe</option>
                    <option value="Ringkøbing">Ringkøbing</option>
                    <option value="Roskilde">Roskilde</option>
                    <option value="Skanderborg">Skanderborg</option>
                    <option value="Sorø">Sorø</option>
                    <option value="Svendborg">Svendborg</option>
                    <option value="Sønderborg">Sønderborg</option>
                    <option value="Thisted">Thisted</option>
                    <option value="Tønder">Tønder</option> 
                    <option value="Vejle">Vejle</option>
                    <option value="Viborg">Viborg</option>
                    <option value="Aabenraa">Aabenraa</option>
                    <option value="Aalborg">Aalborg</option>
                    <option value="Aarhus">Aarhus</option>
                    <option value="Åbenrå-Sønderborg">Åbenrå-Sønderborg</option>
                </select>

                <label for="type">Type</label>
                <select id="type" name="type"style="width: 140px; margin-right: 20px; font-weight:normal;">
                    <option value=""></option>
                    <option value="Landdistrikt">Søg i Landdistrikter</option>
                    <option value="Købstad">Søg i Købstæder</option>
                    <option value="Hovedstaden">Søg i Hovedstaden</option>
                    <option value="Slesvig">Søg i Selsvig</option>
                    <option value="Særtælling">Søg i Særtællinger (alle Typer)</option>
                    <option value="Diverse">Søg i Diverse</option>
                </select>


                <label for="aar">År</label>                
                <select id="aar" name="query_start" style=" width: 100px; margin-right: 20px; font-weight:normal; ">
                    <option value=""></option>
     
                    <option value="1787"style=" color: black; ">1787</option> 
                    <option value="1801"style=" color: black; ">1801</option> 
                    <option value="1834"style=" color: black; ">1834</option>
                    <option value="1835"style=" color: black; ">1835</option>
                    <option value="1840"style=" color: black; ">1840</option>
                    <option value="1845"style=" color: black; ">1845</option>
                    <option value="1850"style=" color: black; ">1850</option>
                    <option value="1855"style=" color: black; ">1855</option>
                    <option value="1860"style=" color: black; ">1860</option>  
                    <option value="1870"style=" color: black; ">1870</option>
                    <option value="1880"style=" color: black; ">1880</option> 
                    <option value="1890"style=" color: black; ">1890</option> 
                    <option value="1901"style=" color: black; ">1901</option>
                    <option value="1906"style=" color: black; ">1906</option>
                    <option value="1911"style=" color: black; ">1911</option>
                    <option value="1916"style=" color: black; ">1916</option>
                    <option value="1921"style=" color: black; ">1921</option>
                    <option value="1925"style=" color: black; ">1925</option>
                    <option value="1930"style=" color: black; ">1930</option>
                    <option value="1940"style=" color: black; ">1940</option>                             
                    <option value="1645"style="color:rgba 255,0,0;">1645</option>
                    <option value="1664-1833"style="color:red;">1664-1833</option>
                    <option value="1702-1741"style="color:red;">1702-1741</option>
                    <option value="1709-1831"style="color:red;">1709-1831</option>
                    <option value="1717"style="color:red;">1717</option>
                    <option value="1721-1833"style="color:red;">1721-1833</option>
                    <option value="1728"style="color:red;">1728</option>
                    <option value="1731"style="color:red;">1731</option>
                    <option value="1732-1761"style="color:red;">1732-1761</option>
                    <option value="1764-1819"style="color:red;">1764-1819</option>
                    <option value="1764-1848"style="color:red;">1764-1848</option>
                    <option value="1769"style="color:red;">1769</option>
                    <option value="1772-1846"style="color:red;">1772-1846</option>
                    <option value="1773-1846"style="color:red;">1773-1846</option>
                    <option value="1787-1851"style="color:red;">1787-1851</option>
                    <option value="1790"style="color:red;">1790</option>
                    <option value="1803"style="color:red;">1803</option>
                    <option value="1805"style="color:red;">1805</option>
                    <option value="1805-1814"style="color:red;">1805-1814</option>
                    <option value="1810"style="color:red;">1810</option>
                    <option value="1811"style="color:red;">1811</option>
                    <option value="1812-1823"style="color:red;">1812-1823</option>
                    <option value="1812-1871"style="color:red;">1812-1871</option>
                    <option value="1814"style="color:red;">1814</option>
                    <option value="1814-1841"style="color:red;">1814-1841</option>
                    <option value="1815-1867"style="color:red;">1815-1867</option>
                    <option value="1818"style="color:red;">1818</option>
                    <option value="1820"style="color:red;">1820</option>
                    <option value="1822"style="color:red;">1822</option>
                    <option value="1823"style="color:red;">1823</option>
                    <option value="1824"style="color:red;">1824</option>
                    <option value="1826"style="color:red;">1826</option>
                    <option value="1831"style="color:red;">1831</option>
                    <option value="1861"style="color:red;">1861</option>                  
                    <option value="1870-1873"style="color:red;">1870-1873</option>
                    <option value="1878"style="color:red;">1878</option>                  
                    <option value="1882"style="color:red;">1882</option>
                    <option value="1882-1919"style="color:red;">1882-1919</option>
                    <option value="1886-1907"style="color:red;">1886-1907</option>
                    
                    
                    
                </select>

Open in new window

ASKER

.... which represents more that 121.000 possible combinations - so I think that running through the database perhaps is the right way to do this after all :) :)

There is an error on line 29 - missing a closing ';'

I understand what you want to do and what you have is on the right track.

You want a base SELECT to which you add optional filters based on the values that have been set.

SO

$amt = isset($_GET['amt']) ? $_GET['amt'] : false;
$type = isset($_GET['type']) ? $_GET['type'] : false;
$aar = isset($_GET['aar']) ? $_GET['aar'] : false;

// We add 1=1 so we can do the AND condition with new criteria without having
// to trim off the last one
$query = "SELECT DISTINCT Aar AS field FROM ftu WHERE 1=1";
$filter = '';
if ($type) {
    $filter .= " AND `Type` = '{$type}'";
}
if ($amt) {
   $filter .= " AND `amt` = '{$amt}'";
}
// Repeat for any other conditions you want to add
$query .= $filter;
$result = $mysqli->query($query);
if ($result) {
  echo "<option>Select a value </option>";
   while($row = $result->fetch_object()) {
      echo <<< OPTION
        <option value="{$row->field}">{$row->field}</option>
OPTION;
    }
}

Open in new window

ASKER

Thanks Julian,

We are definately getting there :)

But I need to fix a few items.

First one is that I need that the reset button, also resets the "År" box .

Code for the reset button is this

<input type="reset" name="reset" id="reset" value="Nulstil">
                <script>
                $(':reset', '#search').click(function(e) {
                    $('#ft_items').html('');
                });
                </script>

Open in new window

ASKER

I believe I have fixed this my self. I am testing a little more, and if anything else occurs, I will get back here :)

ASKER

Sorry - I did NOT fix the problem with resetting the "År" field after using the "1-1" model.

I keeps the latest search in the dropdown list, and I need it to clear this list when hitting the reset button.

Any ideas are very very welcome :)

We seem to be jumping around a bit - are we good on the searches?

Resetting your select's try this

$(function() {
	$('#reset').click(function() {
		$('select').val('');
	});
});

Open in new window

ASKER

Hey Julian,

We are definately getting there :)

We are there :) The search is working as I need it to work now.

Only problem I have with this is the reset button as described.

The

$(function() {
	$('#reset').click(function() {
		$('select').val('');
	});
});

Open in new window

is not doing the reset.

ASKER

Hi Julian,

As you might have guessed I am continous working on with the problems. And now I have found the solution regarding the reset that I was after. What I really needed was a reload of the page when resetting, and that I have implemented. So that problem is done and finished :)

But I have two questions more that I hope you can help me with.

1. Is it possible to sort the actual list in the "År" drilldown box ascending? I have tried with an ORDER BY statement in the SELECT line, but it has no effect. So I wondered if there were another way to do this.

2. Could you explain the ... WHERE 1=1 clause to me in prosa. I can understand the filter and how that works, but I need to understand what the 1=1 really does.

You don't need to reload a page to reset controls. I am interested to know why the reset code above does not work. Take a look at the sample here to see it in action

http://www.marcorpsa.com/ee/t2885.html

1. Sorting - ORDER BY is how you do this - if it is not ordering then I would need specifics of implementation and data to say why.

2. Where 1=1 I explained this in the comments. Lets look at what happens if it is not there

$filter = '';
if ($type) {
   $filter .= "Type='{$type}' AND";
}
if ($amt) {
   $filter .= "Amt='{$amt}' AND";
}

Open in new window

When we get here there is an extra AND on the end we have to get rid of
So lets try it this way - the first if statement we know the filter string is empty so we can do this

$filter = '';
if ($type) {
   $filter .= "Type='{$type}'";
}
// But what happens here? If $type is false then we have an empty
// string and no AND is required. If $type is true we have to add an
// AND. We could do this
if ($amt) {
   if (!empty($filter)) $filter .=' AND ';
   $filter .= "Amt='{$amt}'";
}

Open in new window

With the above solution we would have to repeat the if (!empty...) statement for every condition we are wanting to test.

By putting 1=1 - (which has no effect on the results) we know that every new condition must start with an 'AND' - just makes the code a bit neater.

ASKER

Thanks Julian,

I think I understand the 1=1 statement a little better now. Thanks.

About the sorting, ORDER BY apparently has no effect.

I have tried this:
query = "SELECT DISTINCT Aar AS field FROM ftu WHERE 1=1 ORDER BY Aar ASC";

But no matter if it there or not it shows the returned values in the "År" box in the sequence the are found in the Column named "Aar" in the database.

If you run that query directly against the database what do the first 20 records look like.

ASKER

It looks right.

Like this:

User generated image

BUT - when entering the ORDER BY statement I don't get any values in the "År" drilldown box anymore.

Please post the results of the query WITH the order by

ASKER

It is that result I have posted.

ASKER

Here is a more detailed version :)

User generated image

I don't see the problem - the results are as expected?

ASKER

Trk problem is that it is NOT showing any results in the "År" drop down box when using the ORDER BY statement.

See the problem here where the results come from:
http://kroweb.dk/gfdev/dropdown/dropdown.php

Can you post your updated code. There must be something wrong somewhere else because ORDER BY should not produce these results.

This is what your service call is returning

<h2> Feltet 'Aar' skal anvendes sammen med mindst ét af de andre valg. Klik på Nulstil og prøv igen </h2>

                    <div class="flex-container" style="width:site-width; background-color:white;">
                        <div class="flex-item" style="margin-top:10px; float:left; margin-left:20px;width: site-width; overflow:auto;">
                            <div class="row" style="margin-left:10px;margin-right:10px;">

                             <!-- Have we ran a query -->
                             

</div>
</div> 
</div>

Open in new window

ASKER

No, that is not the reason. This limitation only goes for a few of the options
It does not show anything no matter what I choose, Try to make a choice in both the "Amt" and the "Type" fields - e.g. Frederiksborg in Amt and Landdistrikter in Type. Or try to choose Søg i Diverse in Type alone which is also working when ORDER BY is not on.

Here comes the relevant code:
Main file:

<?php

    $mysqli = mysqli_connect("mysql31.unoeuro.com", "genealogisk_dk1", "AevleBaevle194287Bum", "genealogiskforum_dk_db8") or die("Error connecting to database!");
$mysqli->set_charset("utf8");

?>

<!doctype html>
<html>
<head>
<meta charset="UTF-8"/>
<meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=2.0, user-scalable=yes" />
<title>Linked dropdowns using AJAX</title>

<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">

<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">

<link rel="stylesheet" type="text/css" href="style.css">



<!--<link href="css/custom.css" rel="stylesheet" />-->

<style type="text/css">
</style>

 <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/dt-1.10.16/datatables.min.css" />

  <script type="text/javascript" src="https://cdn.datatables.net/v/dt/dt-1.10.16/datatables.min.js"></script>
  <script src="http://code.jquery.com/jquery.js"></script>

  <script type="text/javascript" src="jquery.dataTables.js"></script>
 
  <script type="text/javascript">
    $(function(){
        $('#ft_items').on('click', '.toggle', function() {
            $(this).parents('tr').nextAll('tr.child').toggle();
        });
    });
    </script>
</head>
<body>
<div class="wrapper">
  <header>
    <!--<div class="container">
    <h1 class="col-lg-9">Linked dropdowns using AJAX</h1>
    
    </div> -->
  </header>
  <div class="container">
    <h5>Testpilot: Peter Kroman, December 2017</h5>
  <!--<p class="alert alert-success"></p>-->
<!-- Søge felter -->
        <div class="container" style="width:100%;padding-top:10px;background-color:#f6f6f6;">
 
            <!-- HEADER START -->
            <div class="row" id="header">
                <form method="POST" id="search" name="search" style="margin-left:30px; margin-right: 30px;"> 
                <label for="amt">Amt</label> 
                <select id="amt" name="amt" style="width: 125px; margin-right: 20px; font-weight:normal; ">
                    <option value=""></option>
                    <option value="Bornholm">Bornholm</option>
                    <option value="Frederiksborg">Frederiksborg</option>
                    <option value="Færøerne">Færøerne</option>
                    <option value="Grønland">Grønland</option>
                    <option value="Haderslev">Haderslev</option>
                    <option value="Hjørring">Hjørring</option>
                    <option value="Holbæk">Holbæk</option>
                    <option value="København">København</option>
                    <option value="Køøge">Køge</option>               
                    <option value="Maribo">Maribo</option>
                    <option value="Odense">Odense</option>
                    <option value="Præstø">Præstø</option>
                    <option value="Randers">Randers</option>
                    <option value="Ribe">Ribe</option>
                    <option value="Ringkøbing">Ringkøbing</option>
                    <option value="Roskilde">Roskilde</option>
                    <option value="Skanderborg">Skanderborg</option>
                    <option value="Sorø">Sorø</option>
                    <option value="Svendborg">Svendborg</option>
                    <option value="Sønderborg">Sønderborg</option>
                    <option value="Thisted">Thisted</option>
                    <option value="Tønder">Tønder</option> 
                    <option value="Vejle">Vejle</option>
                    <option value="Viborg">Viborg</option>
                    <option value="Aabenraa">Aabenraa</option>
                    <option value="Aalborg">Aalborg</option>
                    <option value="Aarhus">Aarhus</option>
                    <option value="Åbenrå-Sønderborg">Åbenrå-Sønderborg</option>
                </select>

                <label for="type">Type</label>
                <select id="type" name="type" style="width: 140px; margin-right: 20px; font-weight:normal;">
                    <option value=""></option>
                    <option value="Landdistrikt">Søg i Landdistrikter</option>
                    <option value="Købstad">Søg i Købstæder</option>
                    <option value="Hovedstaden">Søg i Hovedstaden</option>
                    <option value="Slesvig">Søg i Selsvig</option>
                    <option value="Særtælling">Søg i Særtællinger (alle Typer)</option>
                    <option value="Diverse">Søg i Diverse</option>
                </select>


                <label for="aar">År</label>                
                <select id="aar" name="aar" style=" width: 100px; margin-right: 20px; font-weight:normal; ">
                    <option value=""></option> 
                    <option value="1787"style=" color: black; ">1787</option> 
                    <option value="1801"style=" color: black; ">1801</option> 
                    <option value="1834"style=" color: black; ">1834</option>
                    <option value="1835"style=" color: black; ">1835</option>
                    <option value="1840"style=" color: black; ">1840</option>
                    <option value="1845"style=" color: black; ">1845</option>
                    <option value="1850"style=" color: black; ">1850</option>
                    <option value="1855"style=" color: black; ">1855</option>
                    <option value="1860"style=" color: black; ">1860</option>  
                    <option value="1870"style=" color: black; ">1870</option>
                    <option value="1880"style=" color: black; ">1880</option> 
                    <option value="1890"style=" color: black; ">1890</option> 
                    <option value="1901"style=" color: black; ">1901</option>
                    <option value="1906"style=" color: black; ">1906</option>
                    <option value="1911"style=" color: black; ">1911</option>
                    <option value="1916"style=" color: black; ">1916</option>
                    <option value="1921"style=" color: black; ">1921</option>
                    <option value="1925"style=" color: black; ">1925</option>
                    <option value="1930"style=" color: black; ">1930</option>
                    <option value="1940"style=" color: black; ">1940</option>                             
                    <option value="1645"style="color:red;">1645</option>
                    <option value="1664-1833"style="color:red;">1664-1833</option>
                    <option value="1702-1741"style="color:red;">1702-1741</option>
                    <option value="1709-1831"style="color:red;">1709-1831</option>
                    <option value="1717"style="color:red;">1717</option>
                    <option value="1721-1833"style="color:red;">1721-1833</option>
                    <option value="1728"style="color:red;">1728</option>
                    <option value="1731"style="color:red;">1731</option>
                    <option value="1732-1761"style="color:red;">1732-1761</option>
                    <option value="1764-1819"style="color:red;">1764-1819</option>
                    <option value="1764-1848"style="color:red;">1764-1848</option>
                    <option value="1769"style="color:red;">1769</option>
                    <option value="1772-1846"style="color:red;">1772-1846</option>
                    <option value="1773-1846"style="color:red;">1773-1846</option>
                    <option value="1787-1851"style="color:red;">1787-1851</option>
                    <option value="1790"style="color:red;">1790</option>
                    <option value="1803"style="color:red;">1803</option>
                    <option value="1805"style="color:red;">1805</option>
                    <option value="1805-1814"style="color:red;">1805-1814</option>
                    <option value="1810"style="color:red;">1810</option>
                    <option value="1811"style="color:red;">1811</option>
                    <option value="1812-1823"style="color:red;">1812-1823</option>
                    <option value="1812-1871"style="color:red;">1812-1871</option>
                    <option value="1814"style="color:red;">1814</option>
                    <option value="1814-1841"style="color:red;">1814-1841</option>
                    <option value="1815-1867"style="color:red;">1815-1867</option>
                    <option value="1818"style="color:red;">1818</option>
                    <option value="1820"style="color:red;">1820</option>
                    <option value="1822"style="color:red;">1822</option>
                    <option value="1823"style="color:red;">1823</option>
                    <option value="1824"style="color:red;">1824</option>
                    <option value="1826"style="color:red;">1826</option>
                    <option value="1831"style="color:red;">1831</option>
                    <option value="1861"style="color:red;">1861</option>                  
                    <option value="1870-1873"style="color:red;">1870-1873</option>
                    <option value="1878"style="color:red;">1878</option>                  
                    <option value="1882"style="color:red;">1882</option>
                    <option value="1882-1919"style="color:red;">1882-1919</option>
                    <option value="1886-1907"style="color:red;">1886-1907</option>
                    
                </select>

                <label for="sogn">Sted, By, Sogn, Gade</label>
                <input type="text" name="sogn" id="sogn" placeholder="Fritekst" style="color: #e08a94;">
                <input type="submit" value="Søg" name="Sog" id="Sog">  
                <input type="reset" name="reset" id="reset" value="Nulstil">
                <script>
          
                $(':reset', '#search').click(function(e) {
                    $('#ft_items').html('');
                    $('select').val('');
                    location.reload();


                });
                </script>



            </form>
            </div>
    </div>
 
</div>

</div>
    <div id="ft_items"> </div>

<footer>
  <div class="container">
    Testing Peter Kroman &copy; 2017
  </div>
</footer>

    <script>

      // Search Button and Return key search function
      $('#search').submit(function(e) {
        e.preventDefault();
        $.ajax({
            url : 'ajaxFtHtml.php',
            method: 'post',
            data : $('#search').serialize(),
            dataType: 'html'
        }).done(function(res){
            $('#ft_items').html(res);
            $('#myTable').dataTable({searching: true, paging: false, info: false, "dom": '<"top"<if>', order: []}); 
        });
    });


      </script>


<script>
$(function() {
  $('#amt').change(function() {
     $.post('limit.php',{amt: $(this).val()}, function(options) {
        $('#aar').html(options)
     }) 
  });
  $('#type').change(function() {
     $.post('limit.php',{amt: $('#amt').val(), type: $(this).val()}, function(options) {
        $('#aar').html(options)
     })
  }); 

})
</script>





</body>
</html>

Open in new window

php/ajax file to limit the listings in the "År" box:

<?php

// Turn on error reporting
error_reporting(E_ALL);
ini_set('display_errors', 1);
 

    $mysqli = mysqli_connect("db credentials") or die("Error connecting to database!");
$mysqli->set_charset("utf8");
 
$amt = isset($_POST['amt']) ? $_POST['amt'] : false;
$type = isset($_POST['type']) ? $_POST['type'] : false;
$aar = isset($_POST['aar']) ? $_POST['aar'] : false;
 


// We add 1=1 so we can do the AND condition with new criteria without having
// to trim off the last one
$query = "SELECT DISTINCT Aar AS field FROM ftu WHERE 1=1 ORDER By Aar ASC";
$filter = '';
if ($type) {
    $filter .= " AND `Type` = '{$type}'";
}
if ($amt) {
   $filter .= " AND `Amt` = '{$amt}'";
}
// Repeat for any other conditions you want to add
$query .= $filter;

var_dump($query);
 
  $result = $mysqli->query($query);
  if ($result) :
	echo "<option> </option>";
    while($row = $result->fetch_object()) {
      echo <<< OPTION
        <option value="{$row->field}">{$row->field}</option>
OPTION;
    }
  endif;

Open in new window

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Thanks for the hints Julian,

I am not yet a master of debugging, but I am doing all I can manage, and I am learning all the time :)

It is working nicely now, and I have learned that the statements not always are what they seem to be - when you explain it I can understand that the filter is part of the WHERE clause, which I was not sharp enough to spot.

Thanks for all your help with this.

You are welcome, Peter,