👀 Views: 72 💬 Answers: 1 📅 Created: 2025-06-12

php pdo mysql transactions

Hey everyone, I'm running into an issue that's driving me crazy. Hey everyone, I'm running into an issue that's driving me crazy... I'm working with an scenario when trying to insert data into a MySQL database using PDO in PHP 8.1 within a transaction. The goal is to insert data into two related tables, and if either insert fails, I want to roll back the transaction. However, I receive a `SQLSTATE[HY000]: General behavior: 1364 Field 'created_at' doesn't have a default value` behavior on the second insert, even though I have the correct timestamps set in the SQL statement. Here's the code I'm using: ```php try { $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password'); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $pdo->beginTransaction(); $stmt1 = $pdo->prepare('INSERT INTO users (name, email, created_at) VALUES (:name, :email, NOW())'); $stmt1->execute([':name' => 'John Doe', ':email' => 'john@example.com']); // Simulating a potential behavior situation $stmt2 = $pdo->prepare('INSERT INTO orders (user_id, order_date) VALUES (:user_id, NOW())'); $stmt2->execute([':user_id' => $pdo->lastInsertId()]); $pdo->commit(); } catch (PDOException $e) { $pdo->rollBack(); echo 'behavior: ' . $e->getMessage(); } ``` I've confirmed that the `created_at` field in the `orders` table is set to `NULL` as the default value in the database schema. My expectation was that the `NOW()` function in the SQL statement would ensure a timestamp was provided. I've also tried explicitly setting the `created_at` field in the `orders` insert statement, but that didn't help either. Any insights on what could be causing this behavior or how to better handle this situation would be greatly appreciated. I recently upgraded to Php LTS. What am I doing wrong? For context: I'm using Php on Linux.