Chained Select with MySQL

NewbieX has asked for the wisdom of the Perl Monks concerning the following question:

Hello all. I have a problem I'm trying to solve. I am trying to create a new script that will pull data from a MySQL database. This is what I want to do. On page load, I want to pull a location field from the database and based on the location, populate a drop down list with pending dates from the databaase for that location. However, I want the user to be able to pick a new location and when they do, I want the pending dates to query the database and re-populate the select box with those dates, instead of the original ones. I found a javascript on the web that worked however, all of the array options were hard coded and I need mine to come from the database. I've been playing around with the code but I'm stuck and hoping that someone here could help me out. Herre is the script as it stands now. It is pulling the locations and dates from the database on load, but now it's not changing values when I select another option from the list, and I can't figure out how to query the db for the 2nd and 3rd locations.

#!/usr/bin/perl -w
use strict;
use warnings;
use CGI qw(:standard);
$|++;

use DBI;
my $DBI;
my @row;
use Database::Mysql;
my $db=Database::Mysql->new();
my $dbh=$db->dbh();

$sql1 = "select id, Date from Ceremony where Location = 'City1' and St
+atus = 'Pending'";
$sth = $dbh->prepare($sql1);
$sth->execute
 or die "SQL Error: $DBI::errstr\n";
 while (@row = $sth->fetchrow_array) {
our $C_id = $row[0];
our $C_date= $row[1];
   my ($y, $m, $d) = split('-', $C_date);
   $C_date2 = "$m-$d-$y";
our $Part1 = "<option value='";
our $Part2="'>";
our $Part3="</option>";
our $option=$Part1 . $C_id . $Part2 . $C_date2 . $Part3 . "\n";       
+#formats my option for the html select statement
our $Part4="\, ";
our $option2=$C_date . $Part4;          #formats the var city# in the 
+javascript
push (@option, $option);
push (@option, $option2);
}

print header;
print <<"EOF";

<html>
<head>

<script language = "JavaScript">
<!-- hide me

var City1 = Array(@option);
var City2 = Array("trout", "mackerel", "bass");
var City3 = Array("robin", "hummingbird", "crow");

function swapOptions(the_array_name)
{
        var numbers_select = window.document.the_form.the_examples;
        var the_array = eval(the_array_name);
        setOptionText(window.document.the_form.the_examples, the_array
+);
}

function setOptionText(the_select, the_array)
{
        for (loop=0; loop < the_select.options.length; loop++)
        {
                the_select.options[loop].text = the_array[loop];
        }
}

// show me -->
</script>
</head>
EOF
my $sql2 = "select id,PrefLoc from Person where id = 96";
$sth = $dbh->prepare($sql2);
 $sth->execute
 or die "SQL Error: $DBI::errstr\n";
 while (@row = $sth->fetchrow_array) {
 our $ID = $row[0];
 our $Location = $row[1];
 }

print <<"EOF";
<body>
<form name="the_form">

<select name="choose_category"
                onChange="swapOptions(window.document.the_form.choose_
+category.options[selectedIndex].text);">
EOF

if ($Location eq "City1")  {
        print "<option selected>City1</option><option>City2</option><o
+ption>City3</option>"; }
elsif ($Location eq "City2")  {
        print "<option>City1</option><option selected>City2</option><o
+ption>City3</option>"; }
elsif ($Location eq "City3")  {
        print "<option>City1</option><option>City2</option><option sel
+ected>City3</option>"; }


print << "EOF";
</select>

<select name="the_examples" multiple>
EOF

if ($Location eq "City1") {
print "@option<br>";  }

print << "EOF";
</select>

</form>
</body>
</html>
EOF
[download]

This was the original javascript code:

<html>
<head>
<script language = "JavaScript">
<!-- hide me
var dogs = new Array("poodle","puli","greyhound");
var fish = new Array("trout", "mackerel", "bass");
var birds = new Array("robin", "hummingbird", "crow");

function swapOptions(the_array_name)
{
    var numbers_select = window.document.the_form.the_examples;
    var the_array = eval(the_array_name);
    setOptionText(window.document.the_form.the_examples, the_array);
}

function setOptionText(the_select, the_array)
{
    for (loop=0; loop < the_select.options.length; loop++)
    {
        the_select.options[loop].text = the_array[loop];
    }
}

// show me -->

</script>
</head>
<body>
<form name="the_form">

<select name="choose_category" 

        onChange="swapOptions(window.document.the_form.choose_category
+.options[selectedIndex].text);">

<option selected>dogs
<option>fish
<option>birds
</select>

<select name="the_examples" multiple>

<option>poodle

<option>puli

<option>greyhound      .

</select>

</form>
</body>
</html>
[download]

Comment on Chained Select with MySQL Select or Download Code

Replies are listed 'Best First'.
Re: Chained Select with MySQL by kcott (Archbishop) on Jan 25, 2014 at 04:07 UTC
G'day NewbieX, Welcome to the monastery. "Herre is the script as it stands now. It is pulling the locations and dates from the database on load, ..." The code you posted will not compile and, therefore, will not execute. Please post the real code that's "pulling the locations and dates from the database ...". I see you've used our in 8 places; my is probably more appropriate. [Note: that's just a guess as I haven't seen your real code.] Logical indentation of your code will improve readability. perltidy can help you with this. -- Ken	[reply]

Replies are listed 'Best First'.

Re: Chained Select with MySQL
by kcott (Archbishop) on Jan 25, 2014 at 04:07 UTC

G'day NewbieX,

Welcome to the monastery.

"Herre is the script as it stands now. It is pulling the locations and dates from the database on load, ..."

The code you posted will not compile and, therefore, will not execute. Please post the real code that's "pulling the locations and dates from the database ...".

I see you've used our in 8 places; my is probably more appropriate. [Note: that's just a guess as I haven't seen your real code.]

Logical indentation of your code will improve readability. perltidy can help you with this.

-- Ken

[reply]