2
\$\begingroup\$

This function arguably has little use as it's so simple, but I'm writing a web application with a lot of SQL statements littered all over the place and I thought this function might reduce code duplication and improve readability. I'm wondering if what I'm doing is a good idea, what the "best practice" is here, and any suggestions to make the function more robust and secure. It seems like attempts to further facilitate querying with PDO often trades efficiency or security for ease of use, which I'm trying to avoid.

<?php
// Configs
define("HOST", "localhost");
define("USER", "username");
define("PASS", "password");
define("NAME", "database");

// Connect to database
try {
  $db = new PDO("mysql:host=".HOST.";dbname=".NAME.";charset=utf8", "".USER."", "".PASS."");
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  // PDO fetch docs: http://php.net/manual/en/pdostatement.fetch.php
  $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
} catch (PDOException $e) {
  // User friendly page to show on connection error
  include 'includes/sql-error.php';
  die();
}

// Simple function to handle PDO prepared statements
function sql($db, $q, $params, $return) {
  $stmt = $db->prepare($q);
  $stmt->execute($params);
  if ($return == "rows") {
    return $stmt->fetchAll();
  }
  elseif ($return == "count") {
    return $stmt->rowCount();
  }
}

?>

The usage of this function would look something like this

<?php
// With SELECT
// Call function
$rows = sql($db, "SELECT * FROM table WHERE id = ?", array($id), "rows");
// Get results
foreach($rows as $row) {
    echo $row['field1'].' '.$row['field2']; //etc...
}

// With INSERT
// Call function
sql($db, "INSERT INTO table (field1, field2, field3) VALUES (?, ?, ?)", array($id, $name, $pass));

// With UPDATE
// Call function
sql($db, "UPDATE table SET name = ? WHERE id = ?", array($name, $id));

// With DELETE
// Call function
sql($db, "DELETE FROM table WHERE id = ?", array($id));
?>
\$\endgroup\$
2
\$\begingroup\$

I think that you should consider an important drawback of your solution: every query execution will read in main memory all the records that satisfy the query, before processing them. This is due to the fact that the sql function performs a fetchAll.

For instance, the php manual says:

Using this method to fetch large result sets will result in a heavy demand on system and possibly network resources.

So, if your application only does queries that retrieve small quantities of data, I think your solution is ok, since it improves the readability of the program, but if the program sometimes need large quantities of data, you should avoid the use of fetchAll.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.