PHP Mysql - how to create a dynamic drop down list using javascript? [closed]

Question

I am totally new to scripting, basically i have to try and finish a school project on a coaching tour project. The idea is to allow customers to register and book a tour.

What i am having problem with is, i have no idea on how to create a dynamic drop down list where the booking form will take the data from mysql database.

Lets say for example :

The customer needs to select a choice of tour i.e (industrial, cultural, military) from the first drop down list.
After selecting a tour, the next drop down list should list the destinations i.e. (UK, France, Germany, etc) dependant on the above (tour) choice.
Then the third list should show the start dates dependant on the above (destination) choice.

All the data in the drop down list should come from the database. Also when the customer submits the form, all those data should go into the customers booking table in the database.

I was told that javascript should be the answer, but i have searched so many forums and look at tutorials which they are all confusing. Hence I came to ask here! Any help on how to do this is much appreciated. Thnx!

Below is the code of how i only know how to do this:

           <h2><center>PLEASE PLACE YOUR BOOKING</center></h2>


<form action="booking.php" method="post">
<table width="700 border="0" align="center" cellpadding="0" cellspacing="0">
    <tr><td width="210" height="45">Tour Type:</td><td>
            <select name="tour_type">
            <option value="Cultural">Cultural</option>
            <option value="Industrial">Industrial</option>
            <option value="Military">Military</option>
     </select></td></tr>

    <tr><td width="210" height="45">Duration:</td><td>
            <select name="duration" >
            <option value="1_Day">1_Day</option>
            <option value="7_Days">7_Days</option>
            <option value="14_Days">14_Days</option>
        </select></td></tr>      

    <tr><td width="210" height="45">Destination:</td><td>
            <select name="destination" >
            <option value="England">England</option>
            <option value="Wales">Wales</option>
            <option value="Scotland">Scotland</option>
            <option value="France">France</option>
            <option value="Belgium">Belgium</option>
            <option value="Germany">Germany</option>
        </select></td></tr>

    <tr><td width="210" height="45">No. of Passengers:</td><td>
            <select name="no_of_passengers" >
            <option value="1">1</option>
            <option value="2">2</option>
            <option value="3">3</option>
            <option value="4">4</option>
            <option value="5">5</option>
            <option value="6">6</option>
        </select></td></tr>

    <tr><td width="210" height="45">Depature:</td><td>
            <select name="departure" >
            <option value="13 May 2013">13 May 2013</option>
            <option value="28 May 2013">28 May 2013</option>
            <option value="11 June 2013">11 June 2013</option>
            <option value="26 June 2013">26 June 2013</option>
            <option value="14 July 2013">14 July 2013</option>
            <option value="27 July 2013">27 July 2013</option>
        </select></td></tr>

    <tr><td width="210" height="45"><input type="checkbox" name="accomodation" 
     value="YES"/>Accomodation</td></tr>
    <tr><td width="210" height="45"><input type="checkbox" name="mailshot" 
    value="YES"/>Mailshot</td></tr>
    <tr><td width="210" height="45"><input type="submit" name="submit" value="submit"></td></tr>
    </table>
    </form>
</html>

    <?php
//When submit button is pressed.
if (isset($_POST['submit'])) {

//Include the server and database connection. 
include('cn.php');

session_start();

//retrieve data input from booking form and make it equal to the variable ($)
$tour_type = $_POST['tour_type'];
$duration = $_POST['duration'];
$destination = $_POST['destination'];
$no_of_passengers = $_POST['no_of_passengers'];
$departure = $_POST['departure'];

// accomodation confirmation
if (isset($_POST['accomodation'] ))
{$accom = $_POST["accomodation"];
} else {
$accom = "NO";
}

// mailshot confirmation
if (isset($_POST['mailshot'] ))
{$mail = $_POST["mailshot"];
} else {
$mail = "NO";
}

$userUsername = $_SESSION['loggedInUser'];

// Build the SQL query to retreive the variables ($) and input the data into the database.
$sql = "INSERT INTO booking 
      (user_id,tour_type,duration,destination,no_of_passengers,departure,accomodation,mailshot) 
VALUES ((SELECT user_id FROM user WHERE user_username = '" . 
       $userUsername . "'),'$tour_type','$duration','$destination',
       '$no_of_passengers','$departure','$accom' ,'$mail')";



// test the sql statement.
if(!mysql_query($sql,$cn)) {
die(mysql_error($cn));

}
// direct to this page when booking is successful.
header('Location: booking_success.php');
}

?>

Welcome to Stack Overflow! You'll find our community much more helpful when you've posted whatever code you've attempted. Please show us some of your work and we'll be happy to give you some advice from that point on.
plenty of examples of this on your site. if you've searched "so many forums" and tutorials, you've obviously missed looking around on SO.
sorry to ask, but how to post codes onto the forum? Is it basically copy and paste into the message box?
@kev Just indent your code 4 spaces, and then continue normal indentation from there. It'll format it for you
i have looked at some examples on this site, but they only show whats wrong with their codes and dont show how to get the full thing done.

StenW · Accepted Answer · 2013-03-12 22:03:20Z

I had a similar issue. I found one guide that actually work and where he explains what he does. One thing that you should keep in mind when reading it though that it uses a different version of Jquery than the latest one, so there are a few differences in the syntax. These differences are minor though and can be found in the comments.

http://www.ssdtutorials.com/tutorials/series/dependable-dropdown.html

In your case it would look something like this:

SQL

Create three tables one called id(Integer), one called master_id(Integer) and one called names(varchar).

Id is going to go from 1->x (meaning the last row is number x). master_id is going to be 0 for the items that is in the first drop down military etc. All of the items that you want to go in to the category military will have the same value of master_id as military has as id, for example 1. You follow this principal along with all the sub categories and dates. name is just a label...

Main site php/HTML

The Html would look something like this, where you put the script in the php in the head of your site.

?php
try {

 $objDb = new PDO('mysql:host=localhost;dbname=tour', 'root', 'root');
 $objDb->exec('SET CHARACTER SET utf8');

 $sql = "SELECT * 
  FROM `region`
  WHERE `master_id` = 0";
 $statement = $objDb->query($sql);
 $list = $statement->fetchAll(PDO::FETCH_ASSOC);

 } catch(PDOException $e) {
 echo 'There was a problem';
 }

 ?>

This is the forms.

    <select name="tour" id="tour" class="update">
        <option value="">Select one</option>
        <?php if (!empty($list)) { ?>
            <?php foreach($list as $row) { ?>
                <option value="<?php echo $row['id']; ?>">
                    <?php echo $row['name']; ?>
                </option>
            <?php } ?>
        <?php } ?>
    </select>

    <select name="location" id="location" class="update"
        disabled="disabled">
        <option value="">----</option>
    </select>

    <select name="date" id="date" class="update"
        disabled="disabled">
        <option value="">----</option>
    </select>

</form>

Remember to add the jquery and corect path to the ajax script.

PHP function

       <?php
  if (!empty($_GET['id']) && !empty($_GET['value'])) {

$id = $_GET['id'];
$value = $_GET['value'];

try {

    $objDb = new PDO('mysql:host=localhost;dbname=tour', 'root', 'password');
    $objDb->exec('SET CHARACTER SET utf8');

    $sql = "SELECT * 
            FROM `categories`
            WHERE `master` = ?";
    $statement = $objDb->prepare($sql);
    $statement->execute(array($value));
    $list = $statement->fetchAll(PDO::FETCH_ASSOC);

    if (!empty($list)) {

        $out = array('<option value="">Select one</option>');

        foreach($list as $row) {
            $out[] = '<option value="'.$row['id'].'">'.$row['name'].'</option>';
        }

        echo json_encode(array('error' => false, 'list' => implode('', $out)));

    } else {
        echo json_encode(array('error' => true));
    }

} catch(PDOException $e) {
    echo json_encode(array('error' => true));
}

   } else {
echo json_encode(array('error' => true));
  }

ajax.js - the script

     var formObject = {
run : function(obj) {
    if (obj.val() === '') {
        obj.nextAll('.update').html('<option value="">----</option>').attr('disabled', true);
    } else {
        var id = obj.attr('id');
        var v = obj.val();
        jQuery.getJSON('func/tour_function.php', { id : id, value : v }, function(data) {
            if (!data.error) {
                obj.next('.update').html(data.list).removeAttr('disabled');
            } else {
                obj.nextAll('.update').html('<option value="">----   </option>').attr('disabled', true);
            }
        });
    }
}
  };
  $(function() {

$('.update').on('change', function() {
    formObject.run($(this));
});

 });

Just remember to get the paths right and to include Jquery 1.9.1 and it should work.

thnx, i will take a look and see if this works. It's just fustrating that i spent like 2 whole days sitting at this computer searching and testing and browsing forever.
I did the same! This one really works and you learn why it does what it does. It uses Ajax though, which you can see from the discussion underneath is a bit complicated. I think it is worth it though! If you wait a minuet I can show you how it would look like.
Hi there! Yes i would be grateful if you could show me please!
That was alot of code. So just put it tougher and send me a pm if it does not work. Had a shit load of problems building my first cascading drop down.

David L · Answer 2 · 2013-03-12 21:35:21Z

To point out an answer already on this site Ajax approach to populating a second dynamic dropdown based on the selection in the first

Essentially, the short version would be an onchange event that loads data from your server

<select name="first_dropdown" onchange="$('#dropdown2_container').load('your_script.php?nid='+this.value);">
    <option....
</select>
...
<div id="dropdown2_container" style="display:none"> </div>

I'm not a big fan of obtrusive javascript like this, but it's a start for you. If you want something a bit more elegant, try the following link.

http://www.99points.info/2010/12/n-level-dynamic-loading-of-dropdowns-using-ajax-and-php/

At least a bit of this should get you started.

@Kev Glad to help! If one of these answers helped resolve your question, please give them an upvote and a selected answer. It shows the community that you follow through on answers provided :)
yes i would love to vote well on all the answers that were given but it say i need 15 reputations :-(
I will accept the answers though when/if i managed to fix this problem. i got limited time to work on this project because been in the military restricts me from working alot on it plus the fact that i only learnt coding for 1 week. Sorry for asking newbie question because i am totally new to coding and i have to try to teach myself how to do everything whilst trying to work on this project

mellamokb · Answer 3 · 2013-03-12 21:15:07Z

There are a couple of approaches you can take. Since you are new to scripting, I would suggest avoiding AJAX at this point. You need something like the following:

On the server-side (PHP), output the data into JavaScript variables that you can work with. For example, you could store the data in an array.

Example: var tours = [{ id:1, name:"Tour#1" }, { id:2, name:"Tour#2" }, etc.]
You'll want to load all tour data, destination data, and start dates into the script, with enough information to link which child records belong to which parent records.
When the page first loads, the tour data should be populated into the first dropdown, but the second and third should be blank since they populate base on the previous ones.
When the user makes a change to a dropdown value (onchange event), then apply the logic to load the entries to the next dropdown based on the selection in the previous dropdown.

Example: <select id='tours' onchange='changetour();'></select>
You'll also want to handle the situation where the user clears out a selection (clear out all the child selections in that case), or the user goes back to the first dropdown and changes their selection after they've already selected something in the second and third.

This should give you a rough idea of an algorithm to get started. I would suggest giving your best effort on each item above, and come back with specific questions after you've tried writing a solution and get stuck with a specific problem.

I understand your desire to avoid confusing him, but I think AJAX is absolutely the correct approach for this particular use case. I don't think shying away from the correct tool just because it is difficult is ever a good policy.
@DavidL: AJAX is not always the answer. Especially if you have less than a hundred rows in each dropdown (which I would guess is the case for a school project), it would be more user-friendly to have all the data preloaded to the browser.
Agreed, it is not always the answer. That said, it wouldn't be preloaded into a browser. The correct approach would be to filter server-side and return ONLY the applicable data set and then create options based off of that. The paradigm of the future of web development is to allow the client to perform more and more of the heavy lifting and I don't see a reason to shy away from it here.
@kev: You need to break it down into steps. Don't try to implement the whole thing at once. Start with trying to run a query to get the data and spit it out on the page. Can you make it that far? Then try to get that data dumped into an array in JavaScript. From there, try to populate a dropdown with the data. Each of these steps can be done separately and there are many examples to solve each one. As a programmer you'll need to develop the skill of breaking a process down into little chunks and tasks and focus on one at a time.

asked	3 months ago
viewed	2471 times
active	3 months ago

PHP Mysql - how to create a dynamic drop down list using javascript? [closed]

closed as not a real question by Marc B, j08691, hjpotter92, Richard Brown, Jay Gilford Mar 13 at 1:05

3 Answers

SQL

Main site php/HTML

PHP function

ajax.js - the script

Not the answer you're looking for? Browse other questions tagged php javascript mysql or ask your own question.

Linked

PHP Mysql - how to create a dynamic drop down list using javascript? [closed]

closed as not a real question by Marc B, j08691, hjpotter92, Richard Brown, Jay Gilford Mar 13 at 1:05

3 Answers

SQL

Main site php/HTML

PHP function

ajax.js - the script

Not the answer you're looking for? Browse other questions tagged php javascript mysql or ask your own question.

Linked

Related