PHP OOP Database Class - Is it really needed?

mysqli, oop, php

Solution

Thank you for the good question.

PHP OOP Database Class - Is it really needed?

Definitely yes, but only if you don't like screenfuls of repetitive code for the every query executed in your code.

If you take a fresh look at the code you need to perform one average query, you may notice that it's amount just horrible. While all you need is to just define a query, pass variables, run it and get results, you have to write thousands of useless operators, repeated for the every query.

An example right from the manual (even without error handling)

if ($stmt = $mysqli->prepare("SELECT District FROM City WHERE Name=?")) {
    $stmt->bind_param("s", $city);
    $stmt->execute();
    $stmt->bind_result($district);
    $stmt->fetch();
    echo $district;
}

An example of using a database helper class:

$district = $db->getOne("SELECT District FROM City WHERE Name=?s", $city);
echo $district;

and that's for the single row only. For the array of rows the amount of code will be doubled in case of using raw API

In case of using mysqli things gets even worse.

Let's take simple example. Imagine there is a array of ids of unknown size. You need to add them all tho IN statement in the simple query

SELECT * FROM t WHERE id IN (1,2,3) // filled from array.

Try to get your data using mysqli prepared statements

Problem

I'm currently learning to code using OOP in PHP and have quite often come across database classes and abstraction layers. I'm coding a CMS as a big challenge and I started to code a database class without thinking about it too much. I'm actually leaning towards using MySQLi as is (I think it's pretty good to use as is), rather than creating a class to deal with CRUD operations so do I really need a class to deal with all that stuff? If not, what would be the best way of going about connecting to a database, just do it at the start of my application and pass the connection around? Or is there really a huge benefit of coding a database class that I'm missing? I'm thinking that error handling would be one such benefit but if I'm keeping my class as close to MySQLi as possible, is that a good enough reason to go ahead with a class?

Original source