php PDO sometimes not insert data into table

Question

This is not duplicated question.
The different thing is from other questions:
- My case is that it doesn't work sometimes without any exceptions.
I would like to know which part I need to check for this case.

My development environment is below: - php7 - MySQL5.7

For sql process in php, I use PDO.

Here are snippets of source code.

    $this->conn = new PDO('mysql:host='. $server. ';dbname='. $database, $username, $password);
    $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $this->conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $this->conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

    try {
        $this->conn->beginTransaction();

        $total_amt = 100;
        $sql = "INSERT INTO test_table (total_amt)"
           ."           VALUES (:total_amt)";
        $stmt_test = $this->conn->prepare($sql);
        $stmt_test->bindParam (':total_amt', $total_amt, PDO::PARAM_INT);
        $stmt_test->execute();

        $inserted_id = $this->conn->lastInsertId();

        $this->conn->commit();
    } catch (PDOException $ex) {
        $this->conn->rollBack();
        exit();
    } catch (Exception $e) {
        $this->conn->rollBack();
        exit();
    } finally {
        $closeDB(); // this is a function to close database connection
    }

Mostly data inserted, but sometimes data was not inserted.
My question is... When it sometimes doesn't work, which part do I need to check?

I logged in the 'try' statement and 'catch' statement and the insert statement ran and I got the 'inserted_id' as well and no exceptions.

However when I check the data in the table, there is no the data.
If it always happen, I will understand, but sometimes it happens.

I am a newbie for php, if someone knows the reason, please let me know.

why are you exiting when there is an exception without logging anything? — e4c5, May 04 '17 at 07:06
If the query failed for any reason, there will most likely be an error thrown back, you just need to get it. `$ex->getMessage()` would hold it inside the catches. General PHP error-reporting would also help you. — Qirel, May 04 '17 at 07:08
e4c5, If there is an exception, it doesn't need to keep next process. So there is 'exit()'. — Kyumin, May 04 '17 at 07:11
Qirel, I left it in the 'catch', but there was no any exception. and no data in the table. (not always but sometimes) — Kyumin, May 04 '17 at 07:12
Log those Exception report in some log file rather than keeping ```exit();```. So, whenever you'll get any issue you can check your log to know about the problem in more details. — Suresh, May 04 '17 at 07:14
mi6crazyheart, I did not leave it in this question, but I have used it in 'try' and 'catch' statement, but there was no any exceptions and no data in the table. (sometimes) — Kyumin, May 04 '17 at 07:16
$this->conn->beginTransaction(); : You are using transactions, if for any reason an error occurs that query's effect will be reversed as a rollback of all the operations will happen. — JoshulSharma, May 04 '17 at 07:21
JoshulSharma, yes, I know. it should be in a transaction, so I used it. My question was not about it. My question was about the reason why data is not inserted sometimes. — Kyumin, May 04 '17 at 07:28
To answer your updated question: you should check the error message. — Your Common Sense, May 04 '17 at 11:19

php PDO sometimes not insert data into table

0 Answers0