CODEWITHSUNDEEP
phpmysqlsqlmysqlipdo
M1X
M1X

Reputation: 5354

Check status of previous MySQL query and execute next query

I want to execute multiple MySQL queries. Where the next query depends on the status of the previous one. I need to check the status of the query and in some cases also rowCount(). If the first query returns what I want the next query will be executed and so on. If one of them fail the whole process will be stopped.

I usually nest my queries inside of a try/catch block. Is there a better way to do this? Here is my code. I do not want you to fix my code just see it and give me any suggestions. I'm using PDO with MySQL 5.6.26.

Thanks

$updated = false;
//#1
$query = "select username, forgot_code, time, valid from forgot_requests where forgot_code = :forgot_code";

try {
    $run_query = $db->prepare($query);
    $run_query->execute(array(':forgot_code' => $_POST['forgot_code']));
    $data = $run_query->fetch(PDO::FETCH_OBJ);

    //13min = 780s
    if($run_query->rowCount() == 1 && (time() - $data->time < 7800000) && $data->valid) {

        //#2
        $query = "update users set password = :password where username = :username";

        try {
            $run_query = $db->prepare($query);
            $run_query->execute(array(
                ':password' => password_hash($_POST['password'], PASSWORD_DEFAULT),
                ':username' => $data->username
            ));

            //#3
            $query = "update forgot_requests set valid = 0 where forgot_code = :forgot_code";

            try {
                $run_query = $db->prepare($query);
                $run_query->execute(array(':forgot_code' => $_POST['forgot_code']));

                //update
                $updated = true;

            } catch(PDOException $e) {}

        } catch(PDOException $e) {}

    }

} catch(PDOException $e) {}

Upvotes: 0

Views: 464

Answers (1)

maxhb
maxhb

Reputation: 8855

I assume you want to enforce a valid database state with your pre cautions.

MySQL and PDO offer you the concept of transactions to ensure that a series of sql statements will only be executed all together.

Example

<?php
$db->beginTransaction();

// Query 2
$query = "update users set password = :password where username = :username";
$run_query = $db->prepare($query);
$run_query->execute(array(
  ':password' => password_hash($_POST['password'], PASSWORD_DEFAULT),
  ':username' => $data->username
 ));

// Query 3
$query = "update forgot_requests set valid = 0 where forgot_code = :forgot_code";
$run_query = $db->prepare($query);
$run_query->execute(array(':forgot_code' => $_POST['forgot_code']));

// All queries will be executed or no query will be executed
$db->commit();
?>

If you encounter any problems you can roll back a transaction:

<?php
$db->rollBack();
?>

Further information can be found in MySql manual: (http://dev.mysql.com/doc/refman/5.7/en/commit.html) and in php documentation (http://php.net/manual/de/pdo.begintransaction.php)

Upvotes: 1

Related Questions