Member Avatar for NoGood

Hi All,
I am trying to the following for an appointments system. So far i am able to add to my Sql table 'bookings' values i select from a drop down stored as $day, $month, $year and $time.
I want to be able to do a check nefore a write the row to the database to see if a record with the same day mont year time already exists - but not too sure how. Can anyone tell me where in my php code i can put this check!

<?php 
if ($_POST["addClient"]=="yes") {

    // retrieve values from system $_POST variable
    $day=$_POST["day"];
    $month=$_POST["month"];
    $year=$_POST["year"];
    $time=$_POST["time"];

    include("dbVariables.php");

    // connect to database
    $db = mysql_connect($dbHost, $dbUsername, $dbPassword);
    mysql_select_db($databasename,$db);

    // compose query and send
    $dbQuery="INSERT INTO bookings VALUES ('$day', '$month', '$year', '$time')";
    $result = mysql_query($dbQuery,$db);

    } else {}
?>

Thanks in Advance :)

Edited by Reverend Jim because: Fixed formatting
  • 3 Contributors
  • 3 Replies
  • 2K Views
  • 1 Year Discussion Span
  • Latest Post Latest Post by sophia nayagi

Dani AI

Generated

, you can certainly pre-check with a SELECT, but that pattern is race-prone under load and depends on app logic staying perfect. @Rhyan is right about the intent (also note the small typo time='time'), but the sturdier fix is to let MySQL enforce uniqueness and have PHP handle the duplicate gracefully. Also, the old mysql_* API is long deprecated; use PDO or MySQLi.

Add a composite UNIQUE index (or, better, store a single DATETIME), then attempt the insert:

-- if you keep separate columns
ALTER TABLE bookings
  ADD UNIQUE KEY uniq_booking (year, month, day, time);
$pdo = new PDO('mysql:host=HOST;dbname=DB;charset=utf8mb4', $user, $pass, [
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]);

$sql = 'INSERT INTO bookings (year, month, day, time) VALUES (?, ?, ?, ?)';
try {
  $pdo->prepare($sql)->execute([$year, $month, $day, $time]);
  echo 'New appointment created!';
} catch (PDOException $e) {
  // 23000 = integrity constraint violation; 1062 = duplicate key in MySQL
  if ($e->getCode() === '23000' || ($e->errorInfo[1] ?? null) == 1062) {
    echo 'That slot is already booked.';
  } else {
    throw $e;
  }
}

For durations (as @Rhyan flagged), store start_at and end_at and reject overlaps:

-- recommended schema
CREATE TABLE bookings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  start_at DATETIME NOT NULL,
  end_at   DATETIME NOT NULL,
  INDEX ix_booking_range (start_at, end_at)
);

-- overlap test: any row that starts before new end AND ends after new start
SELECT 1
FROM bookings
WHERE start_at < :new_end AND end_at > :new_start
LIMIT 1;

In a busy app, run the overlap check inside a transaction (optionally with FOR UPDATE) before inserting to avoid races.

Recommended Answers

All 3 Replies

Member Avatar for Member #117553

OK, as far as I understand you first want to check if such an appointment already exists, then, if the date-hour is free, you want to write the appointment. Is that correct? If it is this way, you first need to look for existing appointment, then try to write a new one.

You have to change the code from this part on.

// compose query and send
$dbQuery="INSERT INTO bookings VALUES ('$day', '$month', '$year', '$time')";
$result = mysql_query($dbQuery,$db);

} else {}

// SHOULD LOOK LIKE THIS
//compose search string
$checkAppointment = "SELECT count(*) FROM bookings WHERE day='$day' and month='$month' and year='$year' and time='time')";
$check = mysql_query($checkAppointment, $db);
$result = mysql_fetch_row($check);
$result = $result['0'];

if($result >0)
  {
   print ('You already have an appointment on selected day and hour!');
  }
else
  {
    $insertAppointment="INSERT INTO bookings VALUES ('$day', '$month', '$year', '$time')";
    $write = mysql_query($insertAppointment,$db);
    if(!$write) //checking here if record is inserted
     {
        echo mysql_error();
     }
    else
  {
  print('New appointment created!');
  }
}

However, if you are going to create an appointment system, better think about overlapping events. E.g. a meeting starts at 10 o'clock and is scheduled to run 2 hours.
Your current code will perfectly allow a new appointment to be created when the hour is set to be 10:01...

Edited by Reverend Jim because: Fixed formatting
Member Avatar for NoGood

Ok Thats great thankyou so much. I will give it ago and let you know how i get on!

Cheers :)

Member Avatar for sophia nayagi

i want the same in vb

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.