Jump to content

chrisrulez001

Members
  • Content Count

    38
  • Joined

  • Last visited

  • Days Won

    1

chrisrulez001 last won the day on June 28 2018

chrisrulez001 had the most liked content!

Community Reputation

2 Neutral

About chrisrulez001

  • Rank
    Member

Profile Information

  • Gender
    Not Telling
  1. chrisrulez001

    Booking System, MySQL Issue

    Yeah, that makes life a lot easier with the dates that are booked in between the arrive date and departure date in a separate table. Here's the query: SELECT rooms.Room, roomtype.Type, roomtype.Capacity, roomtype.Adults, roomtype.Children, Cost, (SELECT GROUP_CONCAT(facilities.Facility SEPARATOR ', ') FROM room_facilities INNER JOIN facilities ON room_facilities.FacilityID = facilities.FacilityID WHERE room_facilities.Room = rooms.Room) AS Facilities FROM rooms INNER JOIN(SELECT rooms.Room, SUM(roomprice.Price) AS Cost FROM rooms CROSS JOIN temp_room_booking INNER JOIN roomtype ON rooms.TypeID = roomtype.TypeID INNER JOIN roomprice ON roomprice.TypeID = rooms.TypeID AND night BETWEEN roomprice.FromDate AND roomprice.ToDate GROUP BY rooms.Room) calc_price ON calc_price.Room = rooms.Room LEFT JOIN room_booking ON rooms.Room = room_booking.Room AND room_booking.occupied >= '2019-03-15' AND room_booking.occupied < '2019-03-19' INNER JOIN roomtype ON rooms.TypeID = roomtype.TypeID WHERE room_booking.occupied IS NULL AND roomtype.TypeID = 1 AND roomtype.Adults >= 1 AND roomtype.Children >= 0 ORDER BY rooms.Room; There was a bit of head scratching before I realised I needed to use a temporary table to work out the cost of the room. Thanks again for your help 🙂
  2. chrisrulez001

    Booking System, MySQL Issue

    Hi, I recently made a change to my database to make the pricing more efficient. This is the pricing table: mysql> SELECT * FROM roomprice; +--------------+--------+-------+------------+------------+ | RoomPrice_ID | TypeID | Price | FromDate | ToDate | +--------------+--------+-------+------------+------------+ | 1 | 1 | 15.99 | 2019-01-01 | 2019-12-31 | | 2 | 2 | 10.99 | 2019-01-01 | 2019-12-31 | | 3 | 1 | 16.99 | 2020-01-01 | 2020-12-31 | +--------------+--------+-------+------------+------------+ 3 rows in set (0.00 sec) Now if I use my original query but change the query to use the new pricing table I ended up with this query, I put the TypeID column in twice in to check it was matching on both tables: mysql> SELECT rooms.Room, roomtype.Type, ROUND(roomprice.Price * DATEDIFF('2019-02-24', '2019-02-20'), 2) AS Cost, roomtype.Capacity, roomtype.Adults, -> roomtype.Children, roomprice.TypeID, roomtype.TypeID, -> (SELECT GROUP_CONCAT(facilities.Facility SEPARATOR ', ') -> FROM room_facilities -> INNER JOIN facilities ON room_facilities.FacilityID = facilities.FacilityID -> WHERE room_facilities.Room = rooms.Room) AS Facilities -> FROM rooms -> INNER JOIN roomtype ON rooms.TypeID = roomtype.TypeID -> INNER JOIN roomprice ON rooms.TypeID = roomprice.TypeID AND `roomprice`.`ToDate` >= '2019-02-20' AND `roomprice`.`FromDate` <= '2019-02-24' -> LEFT JOIN bookings ON rooms.Room = bookings.Room AND bookings.ArriveDate < '2019-02-24' AND bookings.DepartureDate > '2019-02-20' -> WHERE bookings.Room IS NULL AND roomtype.TypeID = 1 AND roomtype.Adults >= 1 AND roomtype.Children >= 0 ORDER BY Room; +------+--------+-------+----------+--------+----------+--------+--------+------------------------------------------------------------+ | Room | Type | Cost | Capacity | Adults | Children | TypeID | TypeID | Facilities | +------+--------+-------+----------+--------+----------+--------+--------+------------------------------------------------------------+ | 4 | Double | 63.96 | 2 | 2 | 0 | 1 | 1 | Freeview TV, Wi-Fi, Fridge, Tea / Coffee Making facilities | | 5 | Double | 63.96 | 2 | 2 | 0 | 1 | 1 | Freeview TV, Wi-Fi, Fridge, Tea / Coffee Making facilities | | 6 | Double | 63.96 | 2 | 2 | 0 | 1 | 1 | Freeview TV, Wi-Fi, Fridge, Tea / Coffee Making facilities | +------+--------+-------+----------+--------+----------+--------+--------+------------------------------------------------------------+ 3 rows in set (0.00 sec) This works however if I alter the pricing table and the pricing of the booking goes into the new pricing range like this: mysql> SELECT * FROM roomprice; +--------------+--------+-------+------------+------------+ | RoomPrice_ID | TypeID | Price | FromDate | ToDate | +--------------+--------+-------+------------+------------+ | 1 | 1 | 15.99 | 2019-01-01 | 2019-02-22 | | 2 | 2 | 10.99 | 2019-01-01 | 2019-12-31 | | 3 | 1 | 16.99 | 2019-02-23 | 2019-12-31 | +--------------+--------+-------+------------+------------+ 3 rows in set (0.00 sec) mysql> SELECT rooms.Room, roomtype.Type, ROUND(roomprice.Price * DATEDIFF('2019-02-24', '2019-02-20'), 2) AS Cost, roomtype.Capacity, roomtype.Adults, -> roomtype.Children, roomprice.TypeID, roomtype.TypeID, -> (SELECT GROUP_CONCAT(facilities.Facility SEPARATOR ', ') -> FROM room_facilities -> INNER JOIN facilities ON room_facilities.FacilityID = facilities.FacilityID -> WHERE room_facilities.Room = rooms.Room) AS Facilities -> FROM rooms -> INNER JOIN roomtype ON rooms.TypeID = roomtype.TypeID -> INNER JOIN roomprice ON rooms.TypeID = roomprice.TypeID AND `roomprice`.`ToDate` >= '2019-02-20' AND `roomprice`.`FromDate` <= '2019-02-24' -> LEFT JOIN bookings ON rooms.Room = bookings.Room AND bookings.ArriveDate < '2019-02-24' AND bookings.DepartureDate > '2019-02-20' -> WHERE bookings.Room IS NULL AND roomtype.TypeID = 1 AND roomtype.Adults >= 1 AND roomtype.Children >= 0 ORDER BY Room; +------+--------+-------+----------+--------+----------+--------+--------+------------------------------------------------------------+ | Room | Type | Cost | Capacity | Adults | Children | TypeID | TypeID | Facilities | +------+--------+-------+----------+--------+----------+--------+--------+------------------------------------------------------------+ | 4 | Double | 63.96 | 2 | 2 | 0 | 1 | 1 | Freeview TV, Wi-Fi, Fridge, Tea / Coffee Making facilities | | 4 | Double | 67.96 | 2 | 2 | 0 | 1 | 1 | Freeview TV, Wi-Fi, Fridge, Tea / Coffee Making facilities | | 5 | Double | 63.96 | 2 | 2 | 0 | 1 | 1 | Freeview TV, Wi-Fi, Fridge, Tea / Coffee Making facilities | | 5 | Double | 67.96 | 2 | 2 | 0 | 1 | 1 | Freeview TV, Wi-Fi, Fridge, Tea / Coffee Making facilities | | 6 | Double | 63.96 | 2 | 2 | 0 | 1 | 1 | Freeview TV, Wi-Fi, Fridge, Tea / Coffee Making facilities | | 6 | Double | 67.96 | 2 | 2 | 0 | 1 | 1 | Freeview TV, Wi-Fi, Fridge, Tea / Coffee Making facilities | +------+--------+-------+----------+--------+----------+--------+--------+------------------------------------------------------------+ 6 rows in set (0.00 sec) I end up with it repeating the rows and I've been scratching my head trying to figure it out.
  3. chrisrulez001

    Booking System, MySQL Issue

    Thank you both for your replies. This is the query I ended up with: mysql> SELECT rooms.room, roomtype.Type, roomtype.Price, roomtype.Adults, roomtype.Children, -> bookings.ArriveDate, bookings.DepartureDate -> FROM rooms -> INNER JOIN roomtype ON rooms.TypeID = roomtype.TypeID -> LEFT JOIN bookings ON rooms.Room = bookings.Room AND bookings.ArriveDate < '2019-02-20' AND bookings.DepartureDate > '2019-02-18' -> WHERE bookings.Room IS NULL AND roomtype.Adults = 1 AND roomtype.Children = 0 ORDER BY Room; +------+--------+-------+--------+----------+------------+---------------+ | room | Type | Price | Adults | Children | ArriveDate | DepartureDate | +------+--------+-------+--------+----------+------------+---------------+ | 1 | Single | 10.99 | 1 | 0 | NULL | NULL | | 2 | Single | 10.99 | 1 | 0 | NULL | NULL | | 3 | Single | 10.99 | 1 | 0 | NULL | NULL | +------+--------+-------+--------+----------+------------+---------------+ 3 rows in set (0.00 sec)
  4. chrisrulez001

    Booking System, MySQL Issue

    Hi, I'm not sure if I'm going about this the right way, I'm trying to create a booking system which allows you to book hotel rooms but I'm having trouble selecting the rooms that are not booked between a certain date period. I've attached a picture of my database schema. Here's what I've got when I query the database specifically for the booked rooms between a certain date period: mysql> SELECT rooms.Room, roomtype.Type, roomtype.Capacity, roomtype.Adults, roomtype.Children, -> bookings.ArriveDate, bookings.DepartureDate -> FROM rooms -> INNER JOIN roomtype on rooms.TypeID = roomtype.TypeID -> INNER JOIN bookings ON rooms.Room = bookings.Room -> WHERE roomtype.Adults = 1 AND roomtype.Children = 0 AND bookings.ArriveDate BETWEEN '2019-02-14' AND '2019-02-16'; +------+--------+----------+--------+----------+------------+---------------+ | Room | Type | Capacity | Adults | Children | ArriveDate | DepartureDate | +------+--------+----------+--------+----------+------------+---------------+ | 1 | Single | 1 | 1 | 0 | 2019-02-14 | 2019-02-16 | +------+--------+----------+--------+----------+------------+---------------+ 1 row in set (0.00 sec) If I do a left join but remove the date part of the where clause I get this: mysql> SELECT rooms.Room, roomtype.Type, roomtype.Capacity, roomtype.Adults, roomtype.Children, -> bookings.ArriveDate, bookings.DepartureDate -> FROM rooms -> INNER JOIN roomtype on rooms.TypeID = roomtype.TypeID -> LEFT JOIN bookings ON rooms.Room = bookings.Room WHERE roomtype.Adults = 1 AND roomtype.Children = 0; +------+--------+----------+--------+----------+------------+---------------+ | Room | Type | Capacity | Adults | Children | ArriveDate | DepartureDate | +------+--------+----------+--------+----------+------------+---------------+ | 1 | Single | 1 | 1 | 0 | 2019-02-14 | 2019-02-16 | | 2 | Single | 1 | 1 | 0 | NULL | NULL | | 3 | Single | 1 | 1 | 0 | NULL | NULL | +------+--------+----------+--------+----------+------------+---------------+ 3 rows in set (0.00 sec) Next, I tried doing the left join with the date part in the where clause and I get an empty set: mysql> SELECT rooms.Room, roomtype.Type, roomtype.Capacity, roomtype.Adults, roomtype.Children, -> bookings.ArriveDate, bookings.DepartureDate -> FROM rooms -> INNER JOIN roomtype on rooms.TypeID = roomtype.TypeID -> LEFT JOIN bookings ON rooms.Room = bookings.Room -> WHERE roomtype.Adults = 1 AND roomtype.Children = 0 AND bookings.ArriveDate BETWEEN '2019-02-18' AND '2019-02-20'; Empty set (0.00 sec) Now I assume this problem is down to ArriveDate and DepartureDate of the other entries being null. I'm not quite sure where to proceed from this point.
  5. chrisrulez001

    Struggling to select posts & comments

    Thank you for your help 🙂
  6. chrisrulez001

    Struggling to select posts & comments

    Ok thanks for your reply, I'll re-jig the processing. 👍
  7. chrisrulez001

    Struggling to select posts & comments

    Hi, I've posted this here as I think it's more of a MySQL issue/question than PHP. What I'd like to happen is that if this is possible using one query, I'd like to select all the posts from the posts table, and all the relevant comments from the comments table, each post to many comments. Here's what I've tried so far, I've tried an inner join but that only selects the posts with comments only. The closest I've got is with a left join but it doesn't display correctly, it repeats the post (the one with comments) and then shows the rest of the posts. I'm doing a foreach in PHP, here is a print_r from PHP: Array ( [0] => Array ( [ID] => 14 [subject] => This is a subject [content] => This is the content [post_date_posted] => 2018-07-10 12:00:46 [post_id] => 14 [comments] => test1 [comment_date_posted] => [posted_by] => ) [1] => Array ( [ID] => 14 [subject] => This is a subject [content] => This is the content [post_date_posted] => 2018-07-10 12:00:46 [post_id] => 14 [comments] => test2 [comment_date_posted] => [posted_by] => ) [2] => Array ( [ID] => 14 [subject] => This is a subject [content] => This is the content [post_date_posted] => 2018-07-10 12:00:46 [post_id] => 14 [comments] => test3 [comment_date_posted] => [posted_by] => ) [3] => Array ( [ID] => 14 [subject] => This is a subject [content] => This is the content [post_date_posted] => 2018-07-10 12:00:46 [post_id] => 14 [comments] => test4 [comment_date_posted] => [posted_by] => ) [4] => Array ( [ID] => 14 [subject] => This is a subject [content] => This is the content [post_date_posted] => 2018-07-10 12:00:46 [post_id] => 14 [comments] => test5 [comment_date_posted] => [posted_by] => ) [5] => Array ( [ID] => 14 [subject] => This is a subject [content] => This is the content [post_date_posted] => 2018-07-10 12:00:46 [post_id] => 14 [comments] => test6 [comment_date_posted] => [posted_by] => ) [6] => Array ( [ID] => 13 [subject] => test [content] => test [post_date_posted] => 2018-07-08 21:20:24 [post_id] => [comments] => [comment_date_posted] => [posted_by] => ) [7] => Array ( [ID] => 12 [subject] => test [content] => test [post_date_posted] => 2018-07-08 19:42:57 [post_id] => [comments] => [comment_date_posted] => [posted_by] => ) [8] => Array ( [ID] => 11 [subject] => test [content] => test [post_date_posted] => 2018-07-08 19:30:34 [post_id] => [comments] => [comment_date_posted] => [posted_by] => ) [9] => Array ( [ID] => 10 [subject] => test [content] => test [post_date_posted] => 2018-07-08 19:27:22 [post_id] => [comments] => [comment_date_posted] => [posted_by] => ) ) I'm not entirely sure how to proceed but I've attached my database schema below: MariaDB [blog]> show TABLES; +----------------+ | Tables_in_blog | +----------------+ | comments | | posts | +----------------+ MariaDB [blog]> DESCRIBE comments; +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | post_id | int(11) | NO | MUL | NULL | | | comment | text | NO | | NULL | | | date_posted | varchar(25) | NO | | NULL | | | posted_by | varchar(150) | NO | | NULL | | | ip | varchar(100) | NO | | NULL | | +-------------+--------------+------+-----+---------+----------------+ MariaDB [blog]> DESCRIBE posts; +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | subject | varchar(150) | NO | | NULL | | | content | text | NO | | NULL | | | date_posted | varchar(25) | NO | | NULL | | +-------------+--------------+------+-----+---------+----------------+ MariaDB [blog]> SELECT * FROM posts; +----+-------------------+---------------------+---------------------+ | ID | subject | content | date_posted | +----+-------------------+---------------------+---------------------+ | 1 | test | test | 2018-07-08 18:21:12 | | 2 | test | test | 2018-07-08 18:46:15 | | 3 | test | test | 2018-07-08 18:47:08 | | 4 | test | test | 2018-07-08 18:47:23 | | 5 | test | test | 2018-07-08 18:50:38 | | 6 | test | test | 2018-07-08 18:51:15 | | 7 | test | test | 2018-07-08 18:52:05 | | 8 | test | test | 2018-07-08 19:24:14 | | 9 | test | test | 2018-07-08 19:25:49 | | 10 | test | test | 2018-07-08 19:27:22 | | 11 | test | test | 2018-07-08 19:30:34 | | 12 | test | test | 2018-07-08 19:42:57 | | 13 | test | test | 2018-07-08 21:20:24 | | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | +----+-------------------+---------------------+---------------------+ MariaDB [blog]> SELECT * FROM comments; +----+---------+---------+-------------+-----------+----+ | ID | post_id | comment | date_posted | posted_by | ip | +----+---------+---------+-------------+-----------+----+ | 1 | 14 | test1 | | | | | 2 | 14 | test2 | | | | | 3 | 14 | test3 | | | | | 4 | 14 | test4 | | | | | 5 | 14 | test5 | | | | | 6 | 14 | test6 | | | | +----+---------+---------+-------------+-----------+----+ MariaDB [blog]> SELECT posts.ID, posts.subject, posts.content, posts.date_posted, -> comments.post_id, comments.comment AS comments, comments.date_posted, comments.posted_by -> FROM posts -> INNER JOIN comments ON posts.ID = comments.post_id; +----+-------------------+---------------------+---------------------+---------+----------+-------------+-----------+ | ID | subject | content | date_posted | post_id | comments | date_posted | posted_by | +----+-------------------+---------------------+---------------------+---------+----------+-------------+-----------+ | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test1 | | | | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test2 | | | | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test3 | | | | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test4 | | | | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test5 | | | | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test6 | | | +----+-------------------+---------------------+---------------------+---------+----------+-------------+-----------+ MariaDB [blog]> SELECT posts.ID, posts.subject, posts.content, posts.date_posted, -> comments.post_id, comments.comment AS comments, comments.date_posted, comments.posted_by -> FROM posts -> LEFT JOIN comments ON posts.ID = comments.post_id; +----+-------------------+---------------------+---------------------+---------+----------+-------------+-----------+ | ID | subject | content | date_posted | post_id | comments | date_posted | posted_by | +----+-------------------+---------------------+---------------------+---------+----------+-------------+-----------+ | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test1 | | | | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test2 | | | | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test3 | | | | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test4 | | | | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test5 | | | | 14 | This is a subject | This is the content | 2018-07-10 12:00:46 | 14 | test6 | | | | 1 | test | test | 2018-07-08 18:21:12 | NULL | NULL | NULL | NULL | | 2 | test | test | 2018-07-08 18:46:15 | NULL | NULL | NULL | NULL | | 3 | test | test | 2018-07-08 18:47:08 | NULL | NULL | NULL | NULL | | 4 | test | test | 2018-07-08 18:47:23 | NULL | NULL | NULL | NULL | | 5 | test | test | 2018-07-08 18:50:38 | NULL | NULL | NULL | NULL | | 6 | test | test | 2018-07-08 18:51:15 | NULL | NULL | NULL | NULL | | 7 | test | test | 2018-07-08 18:52:05 | NULL | NULL | NULL | NULL | | 8 | test | test | 2018-07-08 19:24:14 | NULL | NULL | NULL | NULL | | 9 | test | test | 2018-07-08 19:25:49 | NULL | NULL | NULL | NULL | | 10 | test | test | 2018-07-08 19:27:22 | NULL | NULL | NULL | NULL | | 11 | test | test | 2018-07-08 19:30:34 | NULL | NULL | NULL | NULL | | 12 | test | test | 2018-07-08 19:42:57 | NULL | NULL | NULL | NULL | | 13 | test | test | 2018-07-08 21:20:24 | NULL | NULL | NULL | NULL | +----+-------------------+---------------------+---------------------+---------+----------+-------------+-----------+
  8. chrisrulez001

    if (empty()) not working for me

    No problem 👍
  9. chrisrulez001

    if (empty()) not working for me

    I've just noticed on the form it says methd="post". Instead of method="post" But the problem could be because the form is posting to the file called: backorder.php As the form is posting to this file, the error checking would be better suited in there. Although if you wanted to handle the form processing in one file, you could remove the action="backorder.php".
  10. chrisrulez001

    jQuery progressbar issue

    Hi I'm reading from a MySQL database and then looping though the results with PHP. I'm having an issue of using a progress bar within the loop, it's showing the progress bar but it isn't reading the value of a hidden field with the value. The value of the progress bar should be 200 (value in hidden field) but it's just not showing I've uploaded an image of what's happened. Is there something obvious that I'm not seeing? PHP: <?php $List = $this->conn->query("SELECT users.username as Username, challenge.item as Item, SUM(challenge.cost) as Cost FROM users LEFT JOIN challenge ON users.ID = challenge.user_id GROUP BY users.username"); $List->execute(); $row = $List->fetchAll(PDO::FETCH_ASSOC); echo "<div id=\"Challenge\">"; foreach($row as $user) { echo "<div class=\"ChallengeHeader\">"; echo $user['Username']; echo "<span id=\"ChallengeAction\"><input data-index=\"".$user['Username']."\" type=\"submit\" class=\"ChallengeExpand\" id=\"Expand\" value=\"Expand\" /></span>"; echo "</div>"; echo "<div id=\"".$user['Username']."\">"; echo "<div class=\"ProgressBar\"></div>"; echo "<input class=\"Value\" type=\"hidden\" value=\"".$user['Cost']."\">"; echo "</div>"; } echo "</div>"; ?> jQuery: $(document).ready(function(){ $("#Challenge").each(function(){ var $div = $(this); var val = $div.find(".Value").val(); $div.find(".ProgressBar").progressbar({ max: 600, value : val }); if($div.find(".ProgressBar").progressbar("value") <= 500) { $div.find(".ProgressBar").css({ 'background': 'White' }); $div.find(".ProgressBar > div").css({ 'background': 'LightGreen' }); } else { $div.find(".ProgressBar").css({ 'background': 'White' }); $div.find(".ProgressBar > div").css({ 'background': 'Red' }); } }); })
  11. chrisrulez001

    XSS prevention

    Ok thanks your your help
  12. chrisrulez001

    XSS prevention

    Ok thank you for your informative post Jacques1 I'll have a look at Twig and implementing a Content Security Policy. With regards to htmlspecialchars(), I see from your other post you use ENT_QUOTES | ENT_SUBSITITUTE are these the best flags to use?
  13. chrisrulez001

    XSS prevention

    Hi there, It's been a few months since I've touched PHP. I've read that you only use htmlspecialchars() when outputting data (for example from a database). Is that the correct way of doing it? Put to prevent XSS from getting into the database from the form, could you not use preg_match() to whitelist what you can actually enter into the field? Thanks
  14. chrisrulez001

    Problem Creating MySQL Event using PHP

    Thank you very much for helping me out with this. I'll probably use the ->query() method to run this query. EDIT: As I need to pass values to the query, I would probably be best setting PDO prepared query to emulated as suggested EDIT 2: Just tried this with what was suggested above and it works. Thanks again
×

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.