This is the function I use to access the DB, theoretically maybe up 10-20 times in a do while loop, though right now I removed the loop so it only can do one db query at a time. Can this be optimized more or is this how transactions and commits are properly done? Also, I don't know what $db->rollBack() actually does, I just saw it on a stackoverflow
<?php
function util_db_access($statement, $array) {
$db = new PDO('mysql:host=localhost;dbname=db;charset=UTF8', 'user', 'pass');
try {
//echo "1";
$db->beginTransaction();
//echo "2";
$stmt = $db->prepare($statement);
//echo "3";
if($stmt->execute($array)) {
$db->commit();
//echo "4";
if($rows = $stmt->fetchAll(PDO::FETCH_ASSOC)) {
//echo "5";
if($stmt->rowCount() < 2) {
$rows = $rows[0];
}
return $rows;
} else {
//echo "6.1";
//$db->commit();
return true;
}
} else {
//echo "6.2";
//$db->commit();
return false;
}
} catch(PDOException $e) {
$db->rollBack();
//log, we are gonna keep a log eventually.. right?
return -1;
}
}
?>
Try using a profiler to determine where your bottleneck is - there's one included in xdebug. Given the simplicity of this code, it may be the query itself - try running the query standalone, either using the mysql cli client or MySQL Workbench, and see what timings you get back. If it is the query that's slow, you can use EXPLAIN and the bountiful optimization sections in the MySQL manual to improve it.
This thing can be optimized very quickly. For starters you are creating a new connection to the database with every function call. I don't know for sure if the connection is closed when the PDO object goes out of scope but nevertheless it's bad design.
UPDATE
PHP will close the connection to the database when the PDO object is destroyed. Reference http://php.net/manual/en/pdo.connections.php
$db->rollBack() make a roll back for the transaction. If you don't know what transactions are there is no point for you to use them, because it creates an unnecessary overhead for the server.
commit() permanently writes the data from the query, rollback() undo everything to the state where you called beginTransaction()
Transactions are to be used when its crucial that changes in more then one place happens simultaneously, imagine a transaction of money between two accounts. On simple selects there is absolutely no need for transactions.
(I'm also sceptic to how you use try/catch)
However... If you run one query directly on the db server, is it also slow? Do you open/close the db connection between each call? (if so -(yes)- don't). What is the network relationship between the http/db server?
I can't help rewriting your function, removing useless parts and adding essential ones
function util_db_access($type, $statement, $array) {
static $db;
if (empty($db)) {
$dsn = 'mysql:host=localhost;dbname=db;charset=UTF8';
$opt = array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
);
$db = new PDO($dsn, 'user', 'pass', $opt);
}
$stmt = $db->prepare($statement);
$stmt->execute($array);
if($type == 'all') {
return $stmt->fetchAll();
}
if($type == 'row') {
return $stmt->fetch();
}
if($type == 'one') {
return $stmt->fetchColumn();
}
if($type == 'id') {
return $db->lastInsertId();
}
if($type == 'numrows') {
return $stmt->rowCount();
}
}
most likely this new version won't suffer any delays too
now you can use this great function without any delay or inconvenience:
$user = util_db_access("row", "SELECT * FROM users WHERE id = ?", array($someId));
$id = util_db_access("id", "INSERT INTO users SET name=?", array($name));
and so on
However, having just a function for this purpose is quite inconvenient, and I hope you will grow it into class soon. You can take my Safemysql class as a source of some ideas.