Jump to content

Associating cinema times with Screen numbers


webdeveloper123
Go to solution Solved by Barand,

Recommended Posts

Hi Guys,

I am building a cinema ticket booking system. All is going well but I just realised something about my SQL. What I want to do is associate screen numbers with screening times. Like the attached screenshot.

I want the start time and screen number in there. Not the price or movie finish time. And in ASC order, earliest times first.

Here is my code:

 if (isset($_GET['day'])) {
        $day = $_GET['day'];

        $query = "SELECT m.title,
     GROUP_CONCAT(s.screen_num) as screens,
     date_format(sg.screen_on, '%W, %D %b') as date,
     m.image, g.description, CONCAT(m.running_time DIV 60, ' hrs ', m.running_time % 60, ' mins') as running_time,
      GROUP_CONCAT(TIME_FORMAT(sg.screen_at, '%H:%i')) as start_times, m.movie_desc
FROM screening sg
     JOIN screen s ON sg.screen_id = s.screen_id
     JOIN movie m ON sg.movie_id = m.movie_id
     JOIN genre g ON g.genre_id = m.genre_id
WHERE dayname(screen_on) = :day
GROUP BY sg.screen_on, m.title
ORDER BY sg.screen_on;";


        $statement = $conn->prepare($query);
        $statement->bindParam(':day', $day);
        $statement->execute();

        $movies = $statement->fetchAll((PDO::FETCH_ASSOC));  

    echo json_encode($movies);

I'm going to take an educated guess.....is it some sort of group function, grouping screen_at and screen_num together?

I have also attached my data model for reference.

Thanks

timings.jpg

barand model v5.jpg

Edited by webdeveloper123
Link to comment
Share on other sites

1 minute ago, Barand said:

What about all the other data in your query results

I am displaying them in a Div container via JavaScript. So my html is:

    <nav class="days">
         <button onclick="showScreenings('Monday')">Monday</button>
         <button onclick="showScreenings('Tuesday')">Tuesday</button>
        <button onclick="showScreenings('Wednesday')">Wednesday</button>
        <button onclick="showScreenings('Thursday')">Thursday</button>
        <button onclick="showScreenings('Friday')">Friday</button>
        <button onclick="showScreenings('Saturday')">Saturday</button>
        <button onclick="showScreenings('Sunday')">Sunday</button>
    </nav>
 

    <!-- Placeholder for movie details -->
    <section id="screenings-container"></section>

And my JS is:

function showScreenings(day) {
    const screeningsContainer = document.getElementById("screenings-container");

    screeningsContainer.innerHTML = "";

    // Fetch movie data from the PHP file
    fetch(`getMovies.php?day=${day}`)
        .then(response => {
            console.log('Response:', response);  
            return response.json();
        })
        .then(movies => {
            console.log('Movies:', movies);  

            
            movies.forEach(movie => {
                const movieTitle = document.createElement("h2");
                movieTitle.textContent = movie.title;

                const movieImage = document.createElement("img");
                movieImage.src = `images/${movie.image}`;

                const date = document.createElement("p");
                date.textContent = "Date: " + movie.date;

                const startTimes = document.createElement("p");
                startTimes.textContent = "Start Times: " + movie.start_times;

                 const screens = document.createElement("p");
                screens.textContent = "Screen number " + movie.screens;

                const movie_genres = document.createElement("p");
                movie_genres.textContent = movie.description;

                const running = document.createElement("p");
                running.textContent = movie.running_time;

                const desc = document.createElement("p");
                desc.textContent = movie.movie_desc;

                




              

                // Append details to the screeningsContainer
                screeningsContainer.appendChild(movieTitle);
                screeningsContainer.appendChild(movieImage);
                screeningsContainer.appendChild(date);
                screeningsContainer.appendChild(startTimes);
                screeningsContainer.appendChild(screens);
                screeningsContainer.appendChild(movie_genres);
                screeningsContainer.appendChild(running);
                screeningsContainer.appendChild(desc);
               

            });
        })
        .catch(error => console.error('Error:', error));
}

But I am going to re-write some of it - I am not going to use document.createElement - instead create 8 Divs (for 8 attributes) and give them Class names instead of Id's, then style using cssĀ 

Link to comment
Share on other sites

  • Solution

Something like this?

image.thumb.png.ac5bd6320b385938853d70cfd41d5603.png

CODE

<?php
include 'db_inc.php';             //   YOUR CONNECTION
$pdo = pdoConnect('movies');      //   CODE GOES HERE

################################################################################
## PROCESS AJAX REQUESTS
################################################################################
if (isset($_GET['ajax'])) {

        $res = $pdo->prepare("SELECT m.id as movie_id
                                     , m.title
                                     , m.image
                                     , g.description as genre
                                     , CONCAT(m.running_time DIV 60, ' hrs ', m.running_time % 60, ' mins') as running_time
                                     , date_format(sg.screen_on, '%W, %D %b') as date
                                     , s.name as screen_num
                                     , TIME_FORMAT(sg.screen_at, '%H:%i') as start_time
                                FROM screening sg
                                     JOIN screen s ON sg.screen_id = s.id
                                     JOIN movie m ON sg.movie_id = m.id
                                     JOIN genre g ON g.id = m.genre
                                WHERE dayname(screen_on) = :day
                                ORDER BY movie_id, screen_on, sg.screen_at
                              ");

        $res->execute([ 'day' => $_GET['day'] ]);
        $data = [];
        #
        #  Put data into an array with same structure a required output
        #     - array of movies, each movie having arrays of screenings
        #
        foreach ($res as $r)   {
            if (!isset($data[$r['movie_id']])) {
                $data[$r['movie_id']] = [ 'title' => $r['title'],
                                          'image' => $r['image'],
                                          'genre' => $r['genre'],
                                          'runtime' => $r['running_time'],
                                          'screenings' => []
                                        ];
            }
            $data[$r['movie_id']]['screenings'][$r['date']][] = ['start' => $r['start_time'],
                                                                 'sno'   => $r['screen_num']
                                                                ];
        }
        exit(json_encode($data));
}   
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta name="generator" content="PhpED 12.0 (Build 12010, 64bit)">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>olumide</title>
    <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.2.1/css/all.min.css">
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script type='text/javascript'>

         function showScreenings(day)
         {
             $("#movie-listings").html("")
             $.get(
                "",
                {"ajax":1, "day":day},
                function(resp)  {
                    $.each(resp, function(mid, mdata)  {
                        let title = `<h2>${mdata.title}</h2><h4 class='w3-text-gray'>${mdata.genre} (${mdata.runtime})</h4>`
                        $("#movie-listings").append(title)
                        $.each(mdata.screenings, function(dt, ddata) {
                            let datesub = `<h3>${dt}</h3>`
                            $("#movie-listings").append(datesub)
                            $("#movie-listings").append("<div class='screenings'")
                            $.each(ddata, function(k, sdata) {
                                let scr = `<div class='screening'><b>${sdata.start}</b><br>${sdata.sno}</div>`
                                $("#movie-listings").append(scr)
                            })
                            $("#movie-listings").append("</div>")
                        })
                        

                    })
                },
                "JSON"
             )
         }
    </script>
    <style type='text/css'>
         .days  {
             padding: 16px;
             text-align: center;
         }
         .screening {
             width : 20%;
             display: inline-block;
             margin-right: 16px;
             margin-bottom: 8px;
             padding: 4px;
             border: 5px solid black;
             font-size: 9pt;
         }
    </style>
</head>
<body>
    
    <nav class="days">
         <button onclick="showScreenings('Monday')">Monday</button>
         <button onclick="showScreenings('Tuesday')">Tuesday</button>
         <button onclick="showScreenings('Wednesday')">Wednesday</button>
         <button onclick="showScreenings('Thursday')">Thursday</button>
         <button onclick="showScreenings('Friday')">Friday</button>
         <button onclick="showScreenings('Saturday')">Saturday</button>
         <button onclick="showScreenings('Sunday')">Sunday</button>
    </nav>
    
    <div id='movie-listings'class='w3-content w3-padding w3-card-4'>
        <!-- LISTINGS GO HERE -->
    </div>
</body>
</html>

Ā 

  • Like 2
  • Great Answer 1
Link to comment
Share on other sites

Hey Barand,

I'm a bit lost. I ran your code, edited the SQL slightly to fit with my entity names and attributes - ran the SQL, that's fine.

But I get a:

Fatal error: Uncaught Error: Call to undefined function pdoConnect() in /var/www/vhosts//getMoviesv2.php:4 Stack trace: 
#0 {main} thrown in /var/www/vhosts/getMoviesv2.php on line 4

on this line:

$pdo = pdoConnect('movies');      //   CODE GOES HERE

I tried changing "movies" to my db name, but no luck.

I have also replaced :Ā 

include 'db_inc.php'

With my own db.php connection file, and the path and everything is ok

When you say:

16 hours ago, Barand said:
$pdo = pdoConnect('movies');      //   CODE GOES HERE

What code goes there? I'm lost

Sorry, I know this sounds really amateur - But I don't get itĀ 

Edited by webdeveloper123
Link to comment
Share on other sites

  • 2 weeks later...

Hey Barand,

The code works great - but I am having difficulties in editing the code.

Firstly, I can't print the array - I am using this:

 echo '<pre>',print_r($data,1),'</pre>';

Now I noticed the exit statement at the end, so I thought put it before, otherwise it probably won't run. But putting it before raises no errors, but then when I click on the days of the week, none of the listings show. So I put it after the exit statement and it still raises no errors, but the listings do show, but not the array.

I have updated your code to include 2 new attributes in the SQL (movie_desc & screening_id) and adjusted the movies array accordingly. Here is your code, with my updates:

getMovies2.php:

<?php
include 'includes/db.php'; 


################################################################################
## PROCESS AJAX REQUESTS
################################################################################
if (isset($_GET['ajax'])) {

        $res = $pdo->prepare(" SELECT m.movie_id as movie_id
                                     , m.title
                                     , m.image
                                     , g.description as genre
                                     , CONCAT(m.running_time DIV 60, ' hrs ', m.running_time % 60, ' mins') as running_time
                                     , date_format(sg.screen_on, '%W, %D %b') as date
                                     , s.screen_num as screen_num
                                     , TIME_FORMAT(sg.screen_at, '%H:%i') as start_time, m.movie_desc, sg.screening_id
                                FROM screening sg
                                     JOIN screen s ON sg.screen_id = s.screen_id
                                     JOIN movie m ON sg.movie_id = m.movie_id
                                     JOIN genre g ON g.genre_id = m.genre_id
                                WHERE dayname(screen_on) = :day
                                ORDER BY movie_id, screen_on, sg.screen_at; 
                              ");

        $res->execute([ 'day' => $_GET['day'] ]);
        $data = [];
        #
        #  Put data into an array with same structure a required output
        #     - array of movies, each movie having arrays of screenings
        #
        foreach ($res as $r)   {
            if (!isset($data[$r['movie_id']])) {
                $data[$r['movie_id']] = [ 'title' => $r['title'],
                                          'image' => $r['image'],
                                          'genre' => $r['genre'],
                                          'runtime' => $r['running_time'],
                                          'movieinfo' => $r['movie_desc'],
                                          'sgid' => $r['screening_id'],
                                          'screenings' => []
                                        ];
            }
            $data[$r['movie_id']]['screenings'][$r['date']][] = ['start' => $r['start_time'],
                                                                 'sno'   => $r['screen_num']
                                                                ];
        }




       exit(json_encode($data));         
}

?>


I want to see the array because I want to save screening_id to a variable and pass that to another page using hidden input inside a form. Thing is, because I can't see the array, I don't know what the code would be. I tried:

$screeningId = $data['movie_id']['sgid'];
$screeningId = $data['sgid'];
$screeningId = $data['screening_id'];

Each at a time, tried to echo them, but got anĀ Undefined variableĀ error.

Can you help please?
Thanks

Link to comment
Share on other sites

Hey Barand,

Thanks for the advice.

I managed to get the array printed and I realised it would be better to have screening_id inside of screening array instead of movie. Here is the updated code and a sample of the array:

$data = [];
        #
        #  Put data into an array with same structure a required output
        #     - array of movies, each movie having arrays of screenings
        #
        foreach ($res as $r)   {
            if (!isset($data[$r['movie_id']])) {
                $data[$r['movie_id']] = [ 'title' => $r['title'],
                                          'image' => $r['image'],
                                          'genre' => $r['genre'],
                                          'runtime' => $r['running_time'],
                                          'movieinfo' => $r['movie_desc'],
                                          'screenings' => []
                                        ];
            }
            $data[$r['movie_id']]['screenings'][$r['date']][] = ['start' => $r['start_time'],
                                                                 'sno'   => $r['screen_num'],
                                                                 'sgid' => $r['screening_id'],
                                                                ];
        }

array sample:

(
    [1] => Array
        (
            [title] => Aquaman And The Lost Kingdom
            [image] => aquaman.jpg
            [genre] => Action
            [runtime] => 2 hrs 4 mins
            [movieinfo] => When an ancient power is unleashed, Aquaman must 
forge an uneasy alliance with an unlikely ally to protect Atlantis, and the world, from irreversible devastation.
            [screenings] => Array
                (
                    [Tuesday, 16th Jan] => Array
                        (
                            [0] => Array
                                (
                                    [start] => 10:00
                                    [sno] => 1
                                    [sgid] => 1
                                )

                            [1] => Array
                                (
                                    [start] => 13:00
                                    [sno] => 4
                                    [sgid] => 2
                                )

                            [2] => Array
                                (
                                    [start] => 16:00
                                    [sno] => 7
                                    [sgid] => 3
                                )

                            [3] => Array
                                (
                                    [start] => 19:00
                                    [sno] => 5
                                    [sgid] => 4
                                )

                        )

                )

        )

Now I am trying to save screening_id into a variable and echo it with:

$screeningId = $data['screenings']['sgid'];
echo "Sgid is $screeningId";

But when I run it, before the exit statement, I get no error but when I click on any of the days of week, I get nothing, blank page.

Can you help please?

Btw, I also tried:

$screeningId = $data['screenings']['date']['sgid'];

Thanks

Edited by webdeveloper123
Link to comment
Share on other sites

All you're showing is snippets of code with no context of where in the code they belong.

Anything output from the php script in response to an ajax request is sent back in the ajax response. If ypu want to output the screening_id to the screen it needs to be done in the response-handling function.

For example, to create a link to another page, passing the sgid, when a screen number id clicked

                function(resp)  {
                    $.each(resp, function(mid, mdata)  {
                        let title = `<h2>${mdata.title}</h2><h4 class='w3-text-gray'>${mdata.genre} (${mdata.runtime})</h4>`
                        $("#movie-listings").append(title)
                        $.each(mdata.screenings, function(dt, ddata) {
                            let datesub = `<h3>${dt}</h3>`
                            $("#movie-listings").append(datesub)
                            $("#movie-listings").append("<div class='screenings'")
                            $.each(ddata, function(k, sdata) {
                                let scr = `<div class='screening'><b>${sdata.start}</b><br>
                                <a href='myOtherPage.php?sgid=${sdata.sgid}'>${sdata.sno}</a>               //<<<< ADD
                                </div>`
                                $("#movie-listings").append(scr)
                            })
                            $("#movie-listings").append("</div>")
                        })
                        

                    })
                },

Ā 

Link to comment
Share on other sites

19 hours ago, Barand said:

Anything output from the php script in response to an ajax request is sent back in the ajax response. If ypu want to output the screening_id to the screen it needs to be done in the response-handling function.

Ā 

Yes, your right. That only hit me about 2 hours after I logged off yesterday.

Thanks for the code - I'll play around with it, see where I get to.

Ā 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.