StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSimple PDO wrapper
text
Body
copied!<p>My web application currently has do execute simple queries: simple CRUD operations, counting,...</p> <p>A few months ago, someone recommended me here to write a simple PDO wrapper for this (to avoid writing try/catch, prepare(), execute(), etc. each time a query should be executed). This example method was shown (I've made some changes so I could use it in my own project):</p> <pre><code>public function execute() { $args = func_get_args(); $query = array_shift($args); $result = false; try { $res = $this->pdo->prepare($query); $result = $res->execute($args); } catch (PDOException $e) { echo $e->getMessage(); } return $result; } </code></pre> <p>As I need to perform more operations (executing queries, retrieving 1 record, retrieving multiple records, counting results) I created a method for all of these:</p> <pre><code> public function getMultipleRecords() { $args = func_get_args(); $query = array_shift($args); $records = array(); try { $res = $this->pdo->prepare($query); $res->execute($args); $records = $res->fetchAll(); } catch (PDOException $e) { echo $e->getMessage(); } return $records; } public function getSingleRecord() { $args = func_get_args(); $query = array_shift($args); $record = array(); try { $res = $this->pdo->prepare($query); $res->execute($args); $record = $res->fetch(); } catch (PDOException $e) { echo $e->getMessage(); } return $record; } public function execute() { $args = func_get_args(); $query = array_shift($args); $result = false; try { $res = $this->pdo->prepare($query); $result = $res->execute($args); } catch (PDOException $e) { echo $e->getMessage(); } return $result; } public function count() { $args = func_get_args(); $query = array_shift($args); $result = -1; try { $res = $this->pdo->prepare($query); $res->execute($args); $result = $res->fetchColumn(); } catch(PDOException $e) { echo $e->getMessage(); } return $result; } </code></pre> <p>As you see, most of the code is the same. Only 2 lines of code are different for each method: the initialisation of $result (I always want to return a value, even if the query fails) and the fetching. Instead of using 4 methods, I could write just one of them and pass an extra parameter with the type of action. That way, I could use a bunch of if/else statements of a switch statement. However, I think the code can get messy. Is this a good way for solving this problem? If not, what would be a good solution to it?</p> <p>The second problem I have (which is why I'm working on this class right now) is that I want to use prepared statements with the LIMIT SQL statement. However, it is not possible to do this:</p> <pre><code>$res = $pdo->prepare("SELECT * FROM table LIMIT ?"); $res->execute(array($int)); </code></pre> <p>The variabele will be quoted for some reason (and so the query will fail), as explained here: <a href="https://bugs.php.net/bug.php?id=40740">https://bugs.php.net/bug.php?id=40740</a></p> <p>The solution seems to use bindValue() and use the int datatype as a parameter: <a href="http://www.php.net/manual/de/pdostatement.bindvalue.php">http://www.php.net/manual/de/pdostatement.bindvalue.php</a></p> <p>I could rewrite the method(s) to support this, but I would also need to use an extra parameter. I can't just use <code>$db->execute($sql, $variable1, $variable2);</code> anymore as I need to know the data type.</p> <p>What's the best way to solve this?</p> <p>Thanks</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload