webdeveloper123 Posted January 24 Share Posted January 24 (edited) 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 Edited January 24 by webdeveloper123 Quote Link to comment Share on other sites More sharing options...
Barand Posted January 24 Share Posted January 24 An image (or mock-up) of what you do want would be more helpful than an image of what you don't want 🙄 Quote Link to comment Share on other sites More sharing options...
webdeveloper123 Posted January 24 Author Share Posted January 24 7 minutes ago, Barand said: An image (or mock-up) of what you do want Hey - sorry Barand. Something like this: Quote Link to comment Share on other sites More sharing options...
Barand Posted January 24 Share Posted January 24 What about all the other data in your query results - whare should that be shown (if at all)? Quote Link to comment Share on other sites More sharing options...
webdeveloper123 Posted January 24 Author Share Posted January 24 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 Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted January 24 Solution Share Posted January 24 Something like this? 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> 2 1 Quote Link to comment Share on other sites More sharing options...
webdeveloper123 Posted January 25 Author Share Posted January 25 Thanks Barand, I'll give it a go Quote Link to comment Share on other sites More sharing options...
webdeveloper123 Posted January 25 Author Share Posted January 25 (edited) 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 January 25 by webdeveloper123 Quote Link to comment Share on other sites More sharing options...
Barand Posted January 25 Share Posted January 25 Just as it says on the tin - YOUR connection code should go there Quote Link to comment Share on other sites More sharing options...
webdeveloper123 Posted January 25 Author Share Posted January 25 Hey, I googled around and got it. Was it just the connection db settings? I commented that line out and Im getting something. Will post back. Quote Link to comment Share on other sites More sharing options...
webdeveloper123 Posted January 25 Author Share Posted January 25 Oh lol. I read the comments separately. It was supposed to be one sentence! Quote Link to comment Share on other sites More sharing options...
webdeveloper123 Posted January 25 Author Share Posted January 25 lol I thought: $pdo = pdoConnect('movies'); was part of the code! feel silly now Quote Link to comment Share on other sites More sharing options...
webdeveloper123 Posted January 25 Author Share Posted January 25 Thanks for the code Barand! I got it running 😁 Quote Link to comment Share on other sites More sharing options...
webdeveloper123 Posted February 7 Author Share Posted February 7 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted February 7 Share Posted February 7 In you javascript function that handles the jax response, log the response data in the console log. From there you can copy it, json_decode() it then view it with print_r() Quote Link to comment Share on other sites More sharing options...
webdeveloper123 Posted February 7 Author Share Posted February 7 (edited) 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 February 7 by webdeveloper123 Quote Link to comment Share on other sites More sharing options...
Barand Posted February 7 Share Posted February 7 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>") }) }) }, Quote Link to comment Share on other sites More sharing options...
webdeveloper123 Posted February 8 Author Share Posted February 8 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. Quote Link to comment Share on other sites More sharing options...
webdeveloper123 Posted February 8 Author Share Posted February 8 Done it! What a good, simple idea! I ended up passing screen_num, sgid and movie id! Quote Link to comment Share on other sites More sharing options...
Barand Posted February 8 Share Posted February 8 1 hour ago, webdeveloper123 said: I ended up passing screen_num, sgid and movie id! That's a 'belt and braces' approach. If you have the screening_id then you have the movie_id and screen Quote Link to comment Share on other sites More sharing options...
webdeveloper123 Posted February 8 Author Share Posted February 8 hmm, good point. I'll remember that when I'm doing my sql Quote Link to comment Share on other sites More sharing options...
webdeveloper123 Posted February 8 Author Share Posted February 8 Hey Barand, Thanks. Took your advice and I got everything I wanted in my sql select statement just by using screening id in WHERE clause Quote Link to comment Share on other sites More sharing options...
Barand Posted February 8 Share Posted February 8 👍 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.