在插入 SQL 之前，先检查计划是否重叠日期

Question

我想先在我的数据库中检查其中两个计划是否会重叠。 如果计划重叠，它将返回。 否则，信息将入到数据库中。 这是我的代码：

 <?php
include("../sql_connect.php");
session_start();
$user_id = $_SESSION['num'];
$event_id = $_GET['id'];
$event_start = $_POST['event_start'];

$query = "SELECT * FROM user WHERE user_id = $user_id";
$data = mysqli_query($sql,$query);
$row = mysqli_fetch_array($data);
$first_name=$row['first_name'];
$last_name=$row['last_name'];

$query1 = "SELECT * FROM event WHERE $event_start >= 'event_start' and $event_start <= 'event_end' ";
$data1 = mysqli_query($sql,$query1);
echo "$query1";
if (!empty($data1)){
    echo "cannot pre reg"
}else {
//check if pre registered
$check_query = "SELECT * FROM record";
$check_data = mysqli_query($sql,$check_query);
$cnt = mysqli_num_rows($check_data);
$check_row = mysqli_fetch_array($check_data);
//echo $cnt;
//loop through records table
    //pre record query
$user_query = "INSERT INTO records VALUES ('',
                                                $event_id,
                                                $user_id,
                                                NOW())";
$user_data = mysqli_query($sql,$user_query);
$select_query = "SELECT * FROM record WHERE user_id = $user_id AND event_id = $event_id";
$select_data = mysqli_query($sql, $select_query);                                                   
$output = array();
while($select_row = mysqli_fetch_assoc($select_data)){
    $output[] = $select_row;
}
}
echo json_encode($output);

}

?>

希望很快得到反馈！

Answer 1

使用 mysqli_num_rows 而不是 empty

if (mysqli_num_rows($data1) > 0){

Answer 2

这有很多

问题。仅举几例。

1. 无需对$_POST和$_SESSION变量进行isset()或empty()检查
2. 使用*而不是单个列
3. 使用相对绝对路径
4. 不使用预准备语句，因为容易受到 SQL 注入的影响
5. 不是问题，但我建议使用 OOP 而不是程序 MySQLi
6. 插入完全错误

这是我根据我对你的代码的理解而做的。我可能误解了某些部分。

<?php
include $_SERVER['DOCUMENT_ROOT'] . "../sql_connect.php";
session_start();

//Use isset() instead of empty() if you're anticipating falsy values
if(empty($_SESSION['num'])) { 
  exit(json_encode(['status' => 0, 'msg' => 'Session variable is empty']));
}
else if(empty($_GET['id'])) { 
  exit(json_encode(['status' => 0, 'msg' => 'No id given']));
}
else if(empty($_POST['event_start'])) { 
  exit(json_encode(['status' => 0, 'msg' => 'No event start given']));
}
else if(empty($_POST['event_end'])) { 
  exit(json_encode(['status' => 0, 'msg' => 'No event end given']));
}
$user_id = $_SESSION['num'];
$event_id = $_GET['id'];
$event_start = $_POST['event_start'];
$event_start = $_POST['event_end'];
//Select columns individually, instead of * and use prepared statements
$stmt = $mysqli->prepare("SELECT first_name, last_name FROM user WHERE user_id = ?");
$stmt->bind_param("i", $user_id);
$stmt->execute();
$nameArr = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
if(!$nameArr) exit(json_encode(['status' => 0, 'msg' => 'No rows']));
echo $nameArr['first_name'];
echo $nameArr['last_name'];
$stmt->close();

$stmt = $mysqli->prepare("SELECT COUNT(*) FROM event WHERE event_start <= ? AND event_end >= ?");
$stmt->bind_param("ss", $event_start, $event_start);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($countPreregister);
$stmt->fetch();
$stmt->close();
if($countPreregister === 0) {
  exit(json_encode(['status' => 0, 'msg' => 'Cannot preregister']));
}
//**No idea what the purpose of this is, as values aren't used again**   
//check if pre registered
//$check_query = "SELECT * FROM record";
//$check_data = mysqli_query($sql,$check_query);
//$cnt = mysqli_num_rows($check_data);
//$check_row = mysqli_fetch_array($check_data);
//echo $cnt;
//loop through records table
//Column names need to be entered. No need for NOW() if your DB already does this
$stmt = $mysqli->prepare("INSERT INTO records (event_id, user_id) VALUES (?, ?)");
$stmt->bind_param("ii", $event_id, $user_id);
$stmt->execute();
$stmt->close();

$stmt = $mysqli->prepare("SELECT * FROM record WHERE user_id = ? AND event_id = ?");
$stmt->bind_param("ii", $user_id, $event_id);
$stmt->execute();
$output = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
if(!$output) exit(json_encode(['status' => 0, 'msg' => 'No rows']));
$stmt->close();
exit(json_encode(['status' => 1, 'msg' => $output]));