0
\$\begingroup\$

I have the following functions:

function db ($query, $parameters = false, $results = false, $fetch = false, $error_reporting = true) {

    global $db;
    global $db_query;

    if(!isset($db)) {

        $db = new mysqli("localhost", "username", "password", "db");

    }

    if ($db->connect_error == NULL) {

        $db->set_charset("utf8");

        if(is_array($parameters)) {

            $db_query = $db->prepare($query);

            if($db_query) {

                foreach($parameters as $key => $value) {
                    $parameters_reference[$key] = &$parameters[$key];
                }

                $ReflectionMethod = new ReflectionMethod ('mysqli_stmt', 'bind_param');
                @$ReflectionMethod -> invokeArgs($db_query, $parameters_reference);

                $execute = $db_query->execute();

                if($execute) {

                    if($results === false && $fetch === false) {

                        $return = $db_query;

                    } else {

                        if($results === true) {
                            $return = $db_query->get_result();
                        }

                        if($results === true && $fetch === true) {
                            $return = $return->fetch_array(MYSQLI_ASSOC);
                        }
                    }

                } else {
                    $db_error = "Execution error.";
                }

            } else {
                $db_error = "Preparation error.";
            }

        } else {

            $return = $db->query($query);

            if($parameters === true) {
                $return = $return->fetch_array(MYSQLI_ASSOC);;
            }

        }

    } else {
        $db_error = "Connection error.";
    }

    if(!isset($db_error)) {

        return $return;

    } else {

        if($error_reporting === true) {

            die("Database Error: ".$db_error);

        } else {

            return false;
        }
    }
}

function db_fetch($query) {
    return $query->fetch_array(MYSQLI_ASSOC);
}

Their main purpose is to save me time when executing queries to the database. The usage is as follows:

$query = db("SELECT * FROM `table`");

When I need to execute a simple query:

$query = db("SELECT * FROM `table` WHERE `var` = ?", ['i', $var], true);

When I need to add some variables to the query. Then I use $row = db_fetch($query) to get the results. It's possible to skip the db_fetch function if i set 4th parameter of the db function to true.

How can I make the functions better? I mean, is it written well or can I change something to make it better?

\$\endgroup\$
3
  • \$\begingroup\$ Error reporting is just awful but the rest is awkward but usable. \$\endgroup\$ Apr 24, 2017 at 8:28
  • \$\begingroup\$ Can you give me advises on how to make the error reporting better? And why awkward? \$\endgroup\$
    – Luchezar
    Apr 24, 2017 at 8:30
  • \$\begingroup\$ Imagine a site user sees a "Preparation error." text on their screen. Your actions? \$\endgroup\$ Apr 24, 2017 at 8:44

1 Answer 1

1
\$\begingroup\$

An obligatory suggestion: you can greatly improve any mysqli code by switching to PDO. So your function would be three times shorter

function db ($query, $parameters = false)
{
    global $db;
    if(!isset($db))
    {
        $options = array(
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES   => FALSE,
        );
        $dsn = 'mysql:host=localhost;dbname=db;charset=utf8';
        $db = new PDO($dsn, "username", "password", $options);
    }

    if (!$parameters)
    {
        return $db->query($query);
    } else {
        $stmt = $db->prepare($query);
        $stmt->execute($parameters);
        return $stmt;
    }
}

note that PDO has much better equivalent of your db_fetch() function, letting you to get the data simpler and in more formats than your awkward $results / $fetch combination:

$row = db("SELECT * FROM t WHERE id=?", [$id])->fetch();
$count = db("SELECT count(*) FROM t")->fetchColumn();
$data = db("SELECT name, id FROM t")->fetchAll(PDO::FETCH_KEY_PAIR);

compare these explicit calls where you always can tell what does your code return with your

 $data = db("SELECT name, id FROM t", true, true);

show this code to someone and ask what's in $data. Or look at this code after a couple of months and try to tell it yourself without confusing all these true ans false parameters.

\$\endgroup\$
1
  • \$\begingroup\$ Thank you. I will try to build on top your functions. PDO is really a better compared to MySQLi. \$\endgroup\$
    – Luchezar
    Apr 25, 2017 at 17:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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