Dropdown selection based on other dropdown

Question

I am working on a webform that will allow the user to simulate a purchase of a movie ticket.

The date/time should change dynamically based on the movie selection. The data is retrieved from the database, and ajax is used to achieve this.

The problem is when I select the movie, the div of date/time disappear, I am probably doing something wrong I guess.

To follow, I have attached the code and the database data.

Index page:

<!DOCTYPE html>
<html lang="en">
    <head>
            <?php
                require_once("php/connection.php");
                    $query = "SELECT movieID,title FROM movie";
                    $result1 = mysqli_query($conn,$query);
            ?>
            <meta charset="utf-8">
            <title>Bollywood movies | Tickets</title>
            <meta name="description" content="">
            <meta name="viewport" content="width=device-width, initial-scale=1">
            <link rel="stylesheet" type="text/css" href="css/Style.css">
            <script defer src="https://use.fontawesome.com/releases/v5.0.8/js/all.js"></script>
            <script src="js/jquery-3.5.1.min.js"></script>
    </head>
    <body>
        <header>
        <h1>Bollywood movies</h1>
        </header>
        <section class="banner">
            <img src="images/third-serving-nLl5sJnElxY-unsplash.jpg" alt="Movies">
        </section>
        <nav id="navbar">
            <ul>
                <li><a href="">Home</a> </li>
                <li>  <a href="">Bookings</a> </li>
                <li> <a href="">Coming soon..</a> </li>
            </ul>
        </nav>
        <h2>You can book your tickets here!</h2>
        <form action="php/action.php" method="post" onsubmit="return validateForm()" id="form">
            <div id="customer">
                <div id="first">
                    <label for="fname">First Name</label><br>
                    <input type="text" id="fname" name="fname" class="fields"><br>
                </div>
                <div id="last">
                    <label for="lname">Last Name</label><br>
                    <input type="text" id="lname" name="lname" required class="fields"><br>
                </div>
                <div id="Email">
                    <label for="email">Email</label><br>
                    <input type="email" id="email" name="email" required class="fields"><br>
                </div>
                <div id="mobile">
                    <label for="Mobile_Number">Mobile Number</label><br>
                    <input type="text" id="Mobile_Number" name="Mobile_Number" required class="fields"><br>
                </div>
            </div>
            <div id="movie" >
                <div id="Movies">
                    <label for="movies">Movie selection</label><br>
                    <select name="movies" id="movies" class="fields">
                    <option selected="" disabled="" >Select movie</option>
                    <?php 
                    while($row = mysqli_fetch_array($result1)):; ?>
                        <option  value=" <?php echo $row['movieID']; ?>"><?php echo $row['title'];?></option>
                    <?php endwhile;
                    ?>
                    </select> <br>
                </div>
                <div id="Date">
                    <label for="date">Session date/time</label><br>
                    <select name="date"  id="date" class="fields" >
                    <option  value="">Select date/time</option>
                    </select> 
                </div>
            <div>
            </div id="credit">
                <div id="card" class="hide">
                    <label for="Credit_card">Credit card number</label><br>
                    <input type="tel" id="Credit_card" name="credit" class="fields" ><br>
                </div>
            </div>
            <br>
            <div id="submit">
                <input class="fields" id="sub" type="submit" value="Submit">
                <input class="fields" type="reset">
            </div>
            <br>
            <hr>
            <br>
            <span id="error_message"></span>
            <span id="success_message"></span>
            <div id="yes"> </div>
        </form>
        <script>
                $("#fname, #lname, #email, #Mobile_Number").change(function() {
                if ($(this).val() .length == 0) {
                $('#movie').hide();
                } else {
                $('#movie').show();
                }
                });
                $("#fname, #lname, #email, #Mobile_Number").trigger("change");

                $("#movie, #Date").change(function() {
                if ($(this).val() .length == 0) {
                $('#credit').hide();
                } else {
                $('#credit').show();
                }
                });
                $("#movie, #Date").trigger("change");
        </script>
        <script>
                $(document).on('change','#movies', function() { 
                    var movieID = this.value;
                    if(movieID){
                        $.ajax({
                            type:'POST',
                            url:'php/ajaxData.php',
                            data:'movieID'+movieID,
                            success:function(html){
                                $('#Date').html(html);
                            } 
                        });
                    } else {
                        $('#Date').html('<option value="">Select date/time</option>');
                    }
                });
        </script>
        <script>
                function validateForm() {
                var x = document.forms["form"]["fname"].value;
                if (x == "") {
                    alert("Name must be filled out");
                    return false;
                }
                }
        </script>
         <script>
                var name= $('#fname').val() ;
                var lname= $('#lname').val() ;            
                $('#sub').click(function() {
                alert("Thank you for your booking, " + $( '#fname' ).val() + " " + $('#lname').val() + " , have a great day! " );
                });
        </script>

ajaxData.php:

<?php
include_once 'connection.php';

if(!empty($_POST["movieID"])){

    $query = "SELECT * FROM sessions WHERE movieID = ".$_POST['movieID']." " ;
    $result = $db->query($query);
    

    if($result->num_rows > 0){
        echo '<option value="">Select date/time</option>';
        while($row = $result->fetch_assoc()){
            echo'<option value="'.$row['movieID'].'">'.row['sessionDate'].'</option>';
        }
    } else {
        echo '<option value="">Date not available</option>';
    }

}
?>

Database:

-- phpMyAdmin SQL Dump
-- version 5.0.4
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Jan 11, 2021 at 03:48 AM
-- Server version: 10.4.14-MariaDB
-- PHP Version: 7.4.10

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `bollywood_movies`
--

-- --------------------------------------------------------

--
-- Table structure for table `booking`
--

CREATE TABLE `booking` (
  `bookingID` int(11) NOT NULL,
  `patronID` int(11) NOT NULL,
  `sessionID` int(11) NOT NULL,
  `paid` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `movie`
--

CREATE TABLE `movie` (
  `movieID` int(11) NOT NULL,
  `title` varchar(100) NOT NULL,
  `description` text NOT NULL,
  `year` int(4) NOT NULL,
  `active` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `movie`
--

INSERT INTO `movie` (`movieID`, `title`, `description`, `year`, `active`) VALUES
(1, 'The Godfather', 'Gangsters movie with plenty of suspance', 1972, 1),
(2, 'Raging Bull', 'The story of a bull that was raging', 1980, 1),
(3, 'Schindlers list', 'history movie about the war', 1993, 1),
(4, 'The Shawshank Redemption', 'The redemption of the shanks', 1994, 1),
(5, 'Casablanca', 'Crime and action', 1942, 1);

-- --------------------------------------------------------

--
-- Table structure for table `patron`
--

CREATE TABLE `patron` (
  `patronID` int(11) NOT NULL,
  `first_name` varchar(200) NOT NULL,
  `last_name` varchar(200) NOT NULL,
  `email` varchar(100) NOT NULL,
  `mobile` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `sessions`
--

CREATE TABLE `sessions` (
  `sessionID` int(11) NOT NULL,
  `movieID` int(11) NOT NULL,
  `sessionDate` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `sessions`
--

INSERT INTO `sessions` (`sessionID`, `movieID`, `sessionDate`) VALUES
(1, 1, '2020-12-23 18:51:33'),
(2, 2, '2020-12-16 10:25:33'),
(3, 3, '2020-12-31 09:51:33'),
(4, 4, '2020-12-29 11:51:33'),
(5, 5, '2020-12-17 22:51:33');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `booking`
--
ALTER TABLE `booking`
  ADD PRIMARY KEY (`bookingID`),
  ADD KEY `patronID` (`patronID`),
  ADD KEY `sessionID` (`sessionID`);

--
-- Indexes for table `movie`
--
ALTER TABLE `movie`
  ADD PRIMARY KEY (`movieID`);

--
-- Indexes for table `patron`
--
ALTER TABLE `patron`
  ADD PRIMARY KEY (`patronID`);

--
-- Indexes for table `sessions`
--
ALTER TABLE `sessions`
  ADD PRIMARY KEY (`sessionID`),
  ADD KEY `movieID` (`movieID`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `booking`
--
ALTER TABLE `booking`
  MODIFY `bookingID` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `movie`
--
ALTER TABLE `movie`
  MODIFY `movieID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `patron`
--
ALTER TABLE `patron`
  MODIFY `patronID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=34;

--
-- AUTO_INCREMENT for table `sessions`
--
ALTER TABLE `sessions`
  MODIFY `sessionID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `booking`
--
ALTER TABLE `booking`
  ADD CONSTRAINT `booking_ibfk_1` FOREIGN KEY (`patronID`) REFERENCES `patron` (`patronID`),
  ADD CONSTRAINT `booking_ibfk_2` FOREIGN KEY (`sessionID`) REFERENCES `sessions` (`sessionID`);

--
-- Constraints for table `sessions`
--
ALTER TABLE `sessions`
  ADD CONSTRAINT `sessions_ibfk_1` FOREIGN KEY (`movieID`) REFERENCES `movie` (`movieID`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

I have been working on this for a while and still learning but it doesn't seem to work for some reason.

Any help will be appreciated... Thanks

First of all - `YOU ARE OPEN TO SQL INJECTIONS!`. Read this: https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php. Second: You trying to insert return to object with id `#Data` when it is a div element, but not a select which has an id `#data`. Also you cant echo each row in your response script. You have to embed that in the array and echo after all loops. — Serghei Leonenco, Feb 07 '21 at 04:04

Serghei Leonenco · Accepted Answer · 2021-02-07T22:10:00.503

0

You have couple of errors:

1.Index page

var movie = this.value;
...
$.ajax({
    type:'POST',
    url:'php/ajaxData.php',
    data:{movieID: movie},
    success:function(html){
       $('select#date').html(html);//<--here
    } 
});

2.ajaxData.php

<?php
include_once 'connection.php';
$out = ''; //initial string
if(isset($_POST["movieID"])){//here
    $stmt = $mysql->prepare("SELECT * FROM sessions WHERE movieID = ?");
    $stmt->bind_param('i', $_POST["movieID"]);
    $stmt->execute();
    $result = $stmt->get_result();
    

    if($result->num_rows > 0){
        $out .= '<option value="">Select date/time</option>';
        while($row = $result->fetch_assoc()){
            $out .= '<option value="'.$row['movieID'].'">'.row['sessionDate'].'</option>';
        }
    }
    $stmt->close();
} else {
    $out .= '<option value="">Date not available</option>';
}
echo $out;//Return final result;
?>

edited Feb 07 '21 at 22:10

answered Feb 07 '21 at 04:16

Serghei Leonenco

3,478
2
8
16

Hi Serghei and thanks for your help especially regarding the prepare statements, however, now when I select the movie it shows date not available which tells me that something is not working and the date is not retrieved from the database for some reason. – Developer_life Feb 07 '21 at 07:40
@Developer_life can you post `request` body from web console? – Serghei Leonenco Feb 07 '21 at 17:34
The error that I receive is this in the console when i select the movie : jquery-3.5.1.min.js:2 Uncaught TypeError: Cannot read property 'toLowerCase' of undefined at S.fn.init.val (jquery-3.5.1.min.js:2) at index.php:101 at i (jquery-3.5.1.min.js:2) at Dt (jquery-3.5.1.min.js:2) at Dt (jquery-3.5.1.min.js:2) at Object. (jquery-3.5.1.min.js:2) at Function.each (jquery-3.5.1.min.js:2) at Dt (jquery-3.5.1.min.js:2) at Dt (jquery-3.5.1.min.js:2) at Dt (jquery-3.5.1.min.js:2) – Developer_life Feb 07 '21 at 20:46
@Developer_life here is good explanation what cause this error: https://stackoverflow.com/questions/23723005/uncaught-typeerror-cannot-read-property-tolowercase-of-undefined – Serghei Leonenco Feb 07 '21 at 21:37
@Developer_life check my ajax code again, it was one comma missing after data attribute. Also i checked this code on my localhost and it is working. – Serghei Leonenco Feb 07 '21 at 22:12
Hi Serghei I was finally able to make it work, thanks a lot for your help. – Developer_life Feb 08 '21 at 07:18

Dropdown selection based on other dropdown

1 Answers1