在一个进程中的不同表中插入和更新

Question

I've created a form containing an input field which has an autocomplete function and the other input field was set as readonly.

<div id="windowContent">
        <form action="rdpw_monitoring_process.php" method="post">
            <table cellpadding="10px">
                <tr>
                    <td>Username</td>
                    <td><input type="text" class="form-control" style="padding-left:5px;" name="auto_username_rdpw" id="auto_username_rdpw" class="auto_username_rdpw" /></td>
                </tr>
                <tr>
                    <td>Default Password</td>
                    <td><input type="text" value="nccc2016" class="form-control" name="default_password_rdpw" id="default_password_rdpw" readonly="readonly" /></td>
                </tr>
            </table>

        <br />
        <div style="padding-right:100px; float:right;"><input type="submit" name="reset" value="RESET" id="reset" class="reset btn btn-info" /></div>
        </form>
    </div>

When I put a username and click the submit button it will automatically update the Table 1 (tbl_userlist) of some fields then it will insert the userid from Table 1 (tbl_userlist) that was base on the updated fields. My update query was working but my insert query was not working. Is it possible to combine an insert and update query in different table in one process just like my process code below?

<?php
    include('connection.php');

    $autocomplete_username = $_POST['auto_username_rdpw'];
    $default_password = $_POST['default_password_rdpw'];

    $pdo = new PDO('mysql:host=localhost;dbname=etransmittal', 'root', '');

    date_default_timezone_set('Asia/Hong_Kong');
    $current_date = date('Y-m-d H:i:s');
    $date_expiration_ninety_days = date('Y-m-d H:i:s', strtotime("+90 days"));

    $update_qry = $pdo->prepare("UPDATE tbl_userlist SET user_password = :default_password, lastdate_changepw = :date_change_password, password_expiration = :password_date_expiration WHERE username = :username");

    $insert_qry = $pdo->prepare("INSERT INTO tbl_reset_change_expire_password (userid, lastdate_resetpw)
                                VALUES ((SELECT userid FROM tbl_userlist WHERE lastdate_changepw = :change_password_date LIMIT 1), $current_date)");

    if($update_qry->execute(array(':default_password' => $default_password, ':date_change_password' => $current_date, ':password_date_expiration' => $date_expiration_ninety_days, ':username' => $autocomplete_username))){
        echo "You have successfully edit into default password";
    }
    else{
        echo 'Failed to reset default password.';
    }

    $insert_qry->execute(array(':change_password_date' => $current_date));
?>

Answer 1

Below code may be work properly........

<?php
    include('connection.php');

    $autocomplete_username = $_POST['auto_username_rdpw'];
    $default_password = $_POST['default_password_rdpw'];

    $pdo = new PDO('mysql:host=localhost;dbname=etransmittal', 'root', '');

    date_default_timezone_set('Asia/Hong_Kong');
    $current_date = date('Y-m-d H:i:s');
    $date_expiration_ninety_days = date('Y-m-d H:i:s', strtotime("+90 days"));

    $update_qry = $pdo->prepare("UPDATE tbl_userlist SET user_password = :default_password, lastdate_changepw = :date_change_password, password_expiration = :password_date_expiration WHERE username = :username");

    $insert_qry = $pdo->prepare("INSERT INTO tbl_reset_change_expire_password (userid, lastdate_resetpw)
                                VALUES ((SELECT userid FROM tbl_userlist WHERE lastdate_changepw = :change_password_date LIMIT 1), :change_password_date)");

    if($update_qry->execute(array(':default_password' => $default_password, ':date_change_password' => $current_date, ':password_date_expiration' => $date_expiration_ninety_days, ':username' => $autocomplete_username))){
        echo "You have successfully edit into default password";
    }
    else{
        echo 'Failed to reset default password.';
    }

    $insert_qry->execute(array(':change_password_date' => $current_date));
?>