mysql-select中的嵌套if语句

mccptt67  于 2021-06-20  发布在  Mysql
关注(0)|答案(2)|浏览(488)

我需要交叉引用行。如果该行不存在,则插入它。
在将其插入数据库之前,需要通过以下标准:
首先查找属于该用户的约会( user_id ).
然后查找与约会id匹配的约会( appointment_id ). 如果约会id不存在,请继续下一步。
如果约会id不存在,则搜索约会是否与约会日期和时间匹配( appointment_date ) ( appointment_time ).
如果它不存在,那么 INSERT 输入数据库。
这是我目前的代码。如何生成嵌套的if语句 SELECT 更快更简单?

// Search for appointment by appointment ID to see if it already exists
$stmt = $dbh->prepare("SELECT id FROM users WHERE user_id = :user_id AND appointment_id = :appointment_id LIMIT 1");
$stmt->bindParam(':user_id', $userId);
$stmt->bindParam(':appointment_id', $appointmentId);
$stmt->execute();
$result = $stmt->fetch(PDO::FETCH_ASSOC);

// If appointment does not already exist, search for appointment by date and time
if(!$result) {
    $stmt = $dbh->prepare("SELECT id FROM users WHERE user_id = :user_id AND appointment_date = :appointment_date AND appointment_time = :appointment_time LIMIT 1");
    $stmt->bindParam(':user_id', $userId);
    $stmt->bindParam(':appointment_date', $appointmentDate);
    $stmt->bindParam(':appointment_time', $appointmentTime);
    $stmt->execute();
    $result2 = $stmt->fetch(PDO::FETCH_ASSOC);

    if(!$result2) {
        // If appointment does not already exist, insert into database:
        $stmt = $dbh->prepare("INSERT INTO...")
    }
}

我怎样才能使它更快、更简单/更短?

jjhzyzn0

jjhzyzn01#

如果您不需要区分这两个查询,只需组合您的条件:

SELECT id FROM users WHERE user_id = :user_id AND 
 (appointment_id = :appointment_id OR 
  appointment_date = :appointment_date AND appointment_time = :appointment_time)
LIMIT 1
dxxyhpgq

dxxyhpgq2#

我认为您可以尝试使用union,使其只包含一个查询和一半代码。

// Search for appointment by appointment ID to see if it already exists
$stmt = $dbh->prepare("(SELECT id FROM users WHERE user_id = :user_id AND appointment_id = :appointment_id) UNION (SELECT id FROM users WHERE user_id = :user_id AND appointment_date = :appointment_date AND appointment_time = :appointment_time) LIMIT 1");
$stmt->bindParam(':user_id', $userId);
$stmt->bindParam(':appointment_id', $appointmentId);
$stmt->bindParam(':appointment_date', $appointmentDate);
$stmt->bindParam(':appointment_time', $appointmentTime);
$stmt->execute();
$result = $stmt->fetch(PDO::FETCH_ASSOC);

// If appointment does not already exist, search for appointment by date and time
if(!$result) {
    $stmt = $dbh->prepare("INSERT INTO...")
}

我还没有真正测试过上面的代码。可能需要调整一下。
如果出了什么差错就告诉我。

相关问题