I'm trying to write data from a simple form to an SQLite database, but even though I'm committing the transactions, nothing is written to the database file. This is the php code:
$expense = new Model();
$expense->add_expense($_POST);
and in the Model class:
class Model {
private $database = 'expenses.db';
private $dbh;
function __construct() {
$this->dbh = new PDO('sqlite:/var/www/expenses/' . $this->database);
$this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
public function add_expense($args) {
$stmt = $this->dbh->prepare('INSERT INTO `expenses` (`type`, `name`, `location`, `receipt_date`, `price`, `notes`) VALUES (?, ?, ?, ?, ?, ?)');
$this->dbh->beginTransaction();
$stmt->execute(array_values($args));
$this->dbh->commit();
}
}
This is the contents of $args, from inside the function:
Array
(
[type] => type
[name] => item_name
[location] => place
[date] => 1313180040
[price] => 8.86
[notes] => none
)
Also, $stmt->getMessage(); doesn't output anything. This is the SQL to create the table:
BEGIN TRANSACTION;
CREATE TABLE expenses (notes TEXT, type TEXT, location TEXT, name TEXT, price NUMERIC, receipt_date NUMERIC);
COMMIT;
Validation isn't relevant, so I left it out. The contents of $args that I listed is after validation occurs, and it's all correct.
UPDATE: Same code but using bindParam and named parameters instead:
$stmt = $this->dbh->prepare('INSERT INTO `expenses` (`type`, `name`, `location`, `receipt_date`, `price`, `notes`) VALUES (:type, :name, :location, :receipt_date, :price, :notes)');
and the binding statements:
$stmt->bindParam(':type', $args['type']);
$stmt->bindParam(':name', $args['name']);
$stmt->bindParam(':location', $args['location']);
$stmt->bindParam(':receipt_date', $args['date']);
$stmt->bindParam(':price', $args['price']);
$stmt->bindParam(':notes', $args['notes']);
$stmt->execute();
Still, nothing happens.
$this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);. This makes PDO throw exceptions when an error occurs (instead of ignoring it silently as it does by default). It might tell you what's wrong. I have never used transactions in PDO myself.sqlite expenses.db(when I'm /var/www/expenses) I get an error message ofUnable to open database "expenses.db": file is encrypted or is not a database$stmt->bindParam()? I don't know if it's significant but all examples that I found online use PDO::exec() rather than PDOStatement::execute() with beginTransaction() and commit().sqliteinstead ofsqlite3. I recreated the database using thesqlite3command and recreated the table, but nothing happens.array('type' => 'type', 'name' => 'name', 'location' => 'place', 'receipt_date' => 12345, 'price' => 15.00, 'notes' => 'none')and the record was inserted.