执行SELECT查询并检查是否返回任何行，如果结果集为空则执行INSERT - executing a SELECT query and check if any row is returned and if empty resultset do an INSERT

executing a SELECT query and check if any row is returned and if empty resultset do an INSERT

一直遵循这个php/mysql教程，我在网上找到，现在我不能得到这个查询工作。我认为的问题是使用插入查询空结果集从选择查询返回后。

$new_start_no = isset($_POST['start_no']) ? $_POST['start_no'] : '';
$new_end_no = isset($_POST['end_no']) ? $_POST['end_no'] : '';
$new_job_no = isset($_POST['job_no']) ? $_POST['job_no'] : '';
$query = "SELECT COUNT (*)"; 
$query .= "FROM jobs";
$query .= "WHERE job_no=$new_job_no"; 
$query .= "AND start_no <= $new_end_no";
$query .= "AND end_no >= $new_start_no";
$result = mysqli_query($connection, $query);
$num_rows = mysqli_num_rows($result);
if (is_null) { 
$query = "INSERT INTO jobs (";
 $query .= " user_id, start_no, end_no, start_date, client_name, card_type, job_no, job_quantity, end_date, shred_option";
 $query .= ") VALUES (";
 $query .= " '{$user_id}', '{$start_no}', '{$end_no}', '{$start_date}', '{$client_name}', '{$card_type}', '{$job_no}', '{$job_quantity}', '{$end_date}', '{$shred_option}'";
 $query .= ")";
 $result = mysqli_query($connection, $query);
 if ($result) {
// success
   $_SESSION["message"] = "job created.";
   redirect_to("manage_job.php"); 
  } else {
// failure  
   $_SESSION["message"] = "job creation failed.";
   redirect_to("new_job.php");
  }  
}

您的查询总是返回1行，因为它使用COUNT(*)来计算匹配的行。您需要检索行并从中获取计数。您应该为计数分配别名，以便检索它。那么您需要测试计数，而不是is_null。

$query = "SELECT COUNT (*) AS ct"; 
$query .= "FROM jobs";
$query .= "WHERE job_no=$new_job_no"; 
$query .= "AND start_no <= $new_end_no";
$query .= "AND end_no >= $new_start_no";
$result = mysqli_query($connection, $query);
$row = mysqli_fetch_assoc($result);
$num_rows = $row['ct'];
if ($num_rows == 0) {
    // INSERT code
} else {
    $_session["message"] = "job no is not available";
}