Posted on

How can I limit number of bookings in SQL where per month only 5 bookings

I have three tables:

First table is 'user':

user_id  |    Name   |
----------------------
1234354  | Abu Bakar |           
2543353  | Ali       |    
3342342  | Aliza     |    
4234243  | Ali       |  
5234324  | Mike      |     


Second table is 'meeting':

meeting_id  | meeting_name  |  meeting_startDate  | meeting_endDate | room_name  |
----------------------------------------------------------------------------------
1           | Discussion 1  |     2021-05-29      |   2021-05-31    |   room 1   |
2           | Discussion 2  |     2021-06-01      |   2021-06-02    |   room 2   |
3           | Discussion 3  |     2021-05-02      |   2021-05-02    |   room 1   |
4           | Discussion 4  |     2021-05-03      |   2021-05-03    |   room 1   |
5           | Discussion 5  |     2021-05-04      |   2021-05-04    |   room 1   |


Third table is 'booking':

book_id  |  meeting_id  |  user_id  | 
-------------------------------------
0001     |      1       |  1234354  |
0002     |      2       |  1234354  |
0003     |      3       |  1234354  |
0004     |      4       |  1234354  |
0005     |      5       |  1234354  |

I want to allow per user can book each room_name up until 5 times for per month.

(Example: Abu Bakar can book room 1 only for 5 times per month). However, Abu Bakar should not able to booked room 1 for more than 5 times per month.

How can I write my query for above situation because I need to prepare SQL query for above so that it can help me to perform in my php if else statement?

Can someone assist me on this?
Note: As I know i agree there is insert, select, inner join and count involve but I just dont understand how to do the calculation for the date, place, and limitation for a user ID

Posted on

if(isset($_GET)) part is not functioning

I need help for my code after the user done register for their new account in my system.

Here is the verification link that the user will receive via email for him/her to verified the email:

http://localhost/staff/email_verification.php?activation_code=49024f2e7b8c05dc1g66005g780g6060

But, when the user click on above link, I notice based on my below code yes my staff table status column changed to verified. However at my email_verification.php page its supposed to show message as Your account successfully register and you can start login now but its shows as something went wrong.

If I didnt set my $_GET['activation_code]

Then how come its reacting to my UPDATE statement?

Here is my some of my code from phpmailer:

$activation_code =  md5( rand(0,1000) );

$base_url       = "http://localhost/staff/";
$mail->Body    .= ''.$base_url.'email_verification.php?activation_code='.$activation_code.'<br><br>';

Here is my code for email_verification.php:

please take note I have already tried !empty() function as well

<?php

session_start();
include ('conn.php');

if(isset($_GET['activation_code'])){
  $activation_code = $_GET['activation_code'];
  $resultSet = $db->query("SELECT activation_code, status FROM staff WHERE activation_code = '$activation_code' AND status = 'not verified' LIMIT 1");

  if($resultSet->num_rows == 1){
    $update = $db->query("UPDATE staff SET status = 'verified' WHERE activation_code = '$activation_code' LIMIT 1");

    if($update){
      $_SESSION['can'] = "Your account successfully register and you can start login now";
      echo("<script>window.location = 'email_verification.php';</script>");
    }else{
      echo $db->error;
    }
  }else{
    $_SESSION['no'] = "This URL not valid or already verified";
    echo("<script>window.location = 'email_verification.php';</script>");
  }
}else{
  die("something went wrong");
}

?>

Someone please help me on this, I have been trying this out almost for 3 days, I am not sure where I went wrong?