有没有办法将1加到列值而不先通过查询检索值？

Question

I'm checking to see if a value already exists in the DB, if it does I want to just update the last_date as well as up the count (visit_num) by 1. I'm not sure if there's a more efficient way to do that than by running a query to retrieve the current number and then doing ++. This is what I currently have (without retrieving the column).

function subscribe($visit_num, $email, $last_visit_date){

$sql = 'SELECT 1 from `visits` WHERE `email` = ? LIMIT 1';
        $stmt = $dbh->prepare($sql);
        $stmt->bindParam(1, $email, PDO::PARAM_INT);
        $stmt->execute();

        if($stmt->fetchColumn()){
            $totalVisits = /*Somehow retrieve the current count*/ + $visit_num;

            $query = "UPDATE `visits` SET
                        `visit_num`=:visit_num,
                        `last_visit_date`=:last_visit_date
                    WHERE
                        `email` = :email";

            // prepare query statement
            $stmt = $dbh->prepare($query);

            // bind values
            $stmt->bindParam(":visit_num", $totalVisits);
            $stmt->bindParam(":last_visit_date", $last_visit_date);
            $stmt->bindParam(":email", $email);
            $res = $stmt->execute();

            if($res){
                $arr = array('message' => 'Added');
                echo json_encode($arr);
            }else{
                $arr = array('message' => "Error");
                echo json_encode($arr);
            }
}

I can just do (previous to this) a "SELECT COUNT(*) FROM 'visits' WHERE 'email' = '$email'" and add it but I'm not sure whether that's the best approach or if there's something better.

Answer 1

Do this in one statement:

UPDATE `visits` SET
   `visit_num`=`visit_num` + 1,
   `last_visit_date`=CURDATE()
WHERE
   `email` = :email";

If you need to know the number of visits for display or downstream logic in your application, then run the separate select, but that's an unrelated use case. Obviously you can switch out what you need to here, like changing the CURDATE() to your incoming parameter for this function.

In the event that this email doesn't exist in the table and you want to perform an INSERT where the UPDATE would fail, you can still do this in one SQL Statement using INSERT...ON DUPLICATE KEY UPDATE syntax