1
\$\begingroup\$

I did some looking around and found some more topics on how to use the $stmt->bind_param function inside another function or class what means the reference variables have to be sort of passed on. With the information, I obtained I was able to create a class that allows a mysqli prepared statement with fewer lines in the code itself (faster coding) and allows for error checking in the prepared statement query. I'd like to know if this method is useful and "correct" as far as code can be correct. I've planned to add more of the prepared statement's functions to the class later on but for now, this is it.

class db_prepare {

    public function __construct($query = false) {
        global $DB;
        $this->stmt = $DB->prepare($query);
        $this->error = false;
        if($this->stmt == false) {
            $this->error = true;
            echo $mysqli->error;
        }
    }

    public function bind_param($type, &...$args) {
        if($this->error == false) {
            call_user_func_array(array($this->stmt, "bind_param"), array_merge([$type], $args));
        }

    }

    public function get_result() {
        if($this->error == false) {
            $this->stmt->execute();
            return $this->stmt->get_result();
        }
    }

    public function execute() {
        if($this->error == false) {
            return $this->stmt->execute();
        }else {
            return false;
        }
    }

    public function get_result_array() {
        $array = array();
        if($this->error == false) {
            $this->stmt->execute();
            $rows = $this->stmt->get_result();
            while($row = $rows->fetch_array()) {
                $array[] = $row;
            }
        }
        return $array;
    }

    public function close() {
        $this->stmt->close();
    }

}

This means i can call upon the class in a way like this:

$query = "SELECT * FROM `reactions` WHERE `date` = ? AND `author` = ?";
$stmt = new db_prepare($query);
$stmt->bind_param("ss", $date, $author);

$date = '2018-17-07';
$author = 'Admin';
$results = $stmt->get_result();

$date = '2018-17-07';
$author = 'Mike';
$results = $stmt->get_result();
$stmt->close();
\$\endgroup\$
  • 1
    \$\begingroup\$ Please do not update the code in your question to incorporate feedback from answers, doing so goes against the Question + Answer style of Code Review. This is not a forum where you should keep the most updated version in your question. Please see what you may and may not do after receiving answers. \$\endgroup\$ – Vogel612 Jul 19 '18 at 9:07
  • \$\begingroup\$ Using references for params feels dangerous. I would say that passing params for query would be better. \$\endgroup\$ – Volvox Jul 19 '18 at 9:30
  • \$\begingroup\$ 'feels' dangerous.... could you elaborate more on that? Because the stmt->bind_param only accepts references? \$\endgroup\$ – Mart Jul 19 '18 at 9:33
1
\$\begingroup\$

Given the main added value in this class is a sort of error handler, I would suggest to make a little addition to your connection code that will make mysqli report errors by itself, which will make your class pretty much obsoleted.

Just add the following line before new mysqli...

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

and you won't need to do things like echo $mysqli->error anymore simply because mysqli will do everything you wrote here by hand.

Which means that you will perfectly do with vanilla mysqli:

$query = "SELECT * FROM `posts` WHERE `ID` = ? AND `author` = ?";
$stmt = $DB->prepare($query);
$stmt->bind_param("ss", $ID, $author);

$ID = 1;
$author = 'Admin';
$results = $stmt->get_result();

$ID = 2;
$author = 'Admin';
$results = $stmt->get_result();
$stmt->close();

and there is even get_result_array() available in mysqli out of the box:

$query = "SELECT * FROM `posts` WHERE `author` = ?";
$stmt = $DB->prepare($query);
$stmt->bind_param("s", $author);
$results = $stmt->get_result()->fetch_all();
                                ^^^^^^^^^ notice this part

What I would do if I would decide to create a prepared statement shortcut class, is a method that accepts both query and parameters executes them together, like

$query = "SELECT * FROM `posts` WHERE `ID` = ? AND `author` = ?";
$results = DB::run($query, [$ID, $author])->get_result()->fetch_all();

such a function I would call a shortcut

\$\endgroup\$
  • \$\begingroup\$ Good feedback, however your last suggestion kind of ignores the added value of prepare statements because for repetition of the query (where prepared statements come in handy) you suggest to also repeat the query itself. (Deduced from your latest code). However, the bind parameter will also return an error when the query is incorrect, this is now prevented and did you forget the execute() function or can it be left out? Does the fetch_assoc work for when the query returns multiple rows? I admit, my example code is suggesting single row outputs. I'll change that \$\endgroup\$ – Mart Jul 17 '18 at 11:32
  • \$\begingroup\$ The last suggestion simply doesn't claim to be the only method available. Nobody forbade you a regular prepared statement in case you need a repetition. But given 99% of queries are unique, it would be handy to have a function that encapsulates prepare, bind and execute in a single call. As of the bind_param on the erroneous query, just try it and see - it won't be even called :) \$\endgroup\$ – Your Common Sense Jul 17 '18 at 11:45
  • \$\begingroup\$ I definitely will! Thanks! I will update my db_prepare class to make it work for single line code/single query as well! \$\endgroup\$ – Mart Jul 17 '18 at 11:47
  • \$\begingroup\$ just keep in mind that 99.99% of time you don't need any type other than "s" so you can omit type parameter in your function making it less verbose. you can learn from PDO - it's a very smart database driver that could be called a model prepared statement shortcut class... had it such a single query execution method. \$\endgroup\$ – Your Common Sense Jul 17 '18 at 11:51
  • 1
    \$\begingroup\$ What do you think of not using comments with their rather limited markup capability and the lack of reputation to answer the question? May I instead suggest that @Mart post a follow-up question with the improvements incorporated in the code? Thanks! \$\endgroup\$ – Vogel612 Jul 19 '18 at 9:08

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.