Custom mysqli prepare function

I’m doing my first own database class at the moment and currently I’m doing the prepare function.

What this function does is to take in an SQL-query and then an array containing the variables for the statement. I’m having problems with binding the parameters to the statement.

This is how the function looks like now

public function prepare($query, $var) {
    $types = '';
    foreach($var as $a) {
        $type = gettype($a);
        switch($type) {
            case 'integer':
                $types .= 'i';
                break;

            case 'string':
                $types .= 's';
                break;

            default:
                exit('Invalid type: ' . $a .' ' . $type . '(' . count($a) . ')');
                break;
        }
    }

    $stmt = self::$connection->prepare($query);
    $stmt->bind_param($types, $var); // How do I do here??
    $stmt->execute();
    $result = $stmt->get_result();
    while($row = $result->fetch_assoc()) {
        print_r($row);
    }
}

Everything works as I want it to (I know this function could do some polishing but it does what it needs to do). I have commented the line where I’m having trouble figuring out what to do. $var is an array and if I recall things correctly the variables needs to be passed seperately seperated with a comma. This is where I’m clueless.

Answer

The very idea of your own database class is great.
Very few people here do share it, for some reason prefers raw api calls all over their code.
So, you’re taking great step further.
However, here are some objections:

1. Don’t use mysqli for your first own database class if you’re going to use native prepared statements.
   Use PDO instead. It will save you a ton of headaches.
2. Despite of the fact this function works all right for you, it makes not much sense:
   • switch($type) code block is useless. Mysql can understand every scalar value as a string – so you can bind every value as s with no problem.
   • most integers coming from the client side have string type anyway.
   • there are some legitimate types like float or NULL or object that can return a string. So, automation won’t work here. If you want to distinguish different types, you have to implement type hinted placeholders instead.
   • Never use exit in your scripts. throw new Exception('put here your error message') instead.
3. This is not actually a prepare function as it does execute as well. So, give it more generic name

But now to your problem
It is direct consequence of using mysqli. It is a nightmare when dealing with prepared statements. Not even only with binding but with retrieving your data as well (because get_result() works not everywhere, and after creating your application locally you will find it doesn’t work on the shared hosting). You can make yourself an idea looking at this bunch of code served for this very purpose – to bind dynamical number of variables.

So, just keep away from mysqli as far as as you could.
With PDO your function will be as simple as this

public function query($query, $var=array())
{
    $stmt = self::$connection->prepare($query);
    $stmt->execute($var);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
// and then used
$data = $db->("SELECT 1");
print_r($data);

You can take a look at my class to get some ideas.
Feel free to ask any questions regarding database classes – it’s great thing, and I am glad you’re going this way.

To answer questions from the comments.

To let you know, you’re not the only user of the site. There are also some innocent visitors. Unlike you, they don’t need no error messages, and they get scared with some strange behavior and lack of familiar controls.

exit() with error message does many evil things

• throws an error message out, revealing some system internals to a potential attacker
• scaring innocent user with strange message. “What’s that? Who is invalid? Is it mine fault or what? Or may be it’s a virus? Better leave the site at all” – thinks them.
• killing the script in the middle, so it may cause torn design (or no design at all) shown
• killing the script irrecoverably. while thrown exception can be caught and gracefully handled

When connecting to PDO, no need to throw anything here as the exception already thrown by PDO. So, get rid of try ... catch and just leave it one line:

self::$connection = new PDO($dsn, $user, $pass); 

then create a custom exception handler to work in 2 modes:

• on a development server let it throw the message on the screen.
• on a live server let it log the error while showing generic error page to the user

Use try … catch only if you don’t want to whole script die – i.e. to handle recoverable issue only.

By the way, PDO don’t throw exception on connect by default. You have to set it manually:

$opt = array( PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION );
self::$connection = new PDO($dsn, $user, $pass, $opt);