Good solution for simplifying and making MYSQLi queries dynamic?

Question

I was looking for a way to dynamically alter the number of variables used in a MYSQLi call. I stumbled on one very helpful post on PHP.net from 5 years ago (http://php.net/manual/en/mysqli-stmt.bind-param.php#100879). However, I then went a little crazy on things and would like help knowing if my adaptation of his work is still safe/efficient/full of errors I'm not smart enough to see.

The idea is five-fold:

1. To allow for easily using as many variables as desired dynamically.
2. To make writing queries as easy as old MYSQL (while still utilizing the modernity and security of MYSQLi prepared statements).
3. To negate the need to manually add classes, but rather let PHP handle it.
4. To automatically differentiate between queries that expect returns (SELECT and EXPLAIN) and those that do not (INSERT, DELETE, and UPDATE).
5. To provide an easy way to debug an individual line or entire page by changing a single variable.

All of these are accomplished, I hope, with something like this:

doMYSQL('INSERT INTO table(id, name) VALUES ($id,$name)');

Note, if you would, that in the below function, the query (with variables inline, like old MYSQL) is surrounded by single quotes - the variables are parsed as actual variable names, not the values thereof. The values only take place once in the stage of preparing the MYSQLi prepared statements (and thus there should be, so far as I can tell, the same security against injunction attacks).

And now, the official notes. I'd love any feedback on how to make this better or if there is a glaring error someplace. All the code below the last note ("Misc code") was from the PHP.net post, most of which I don't understand, so any comments on that would be helpful as well. If this function does pass the bar, it's certainly going to make my life easier, so hopefully other people find use for it as well :).

Just to clarify, this has worked on all of the tests I've attempted on it, so I have no reason to think there's anything wrong. I'm just experienced enough to know that I'm not experienced enough to know if there are any red flags. Thus I tip my hat to y'all and ask for assistance in verifying the function's safety.

<?php
/*
doMYSQL($sql, $debug_local [optional]);
$sql = Statement to execute;
$debug_local = 'print' to show query on page but not run, 'both' to show it and run, leave blank for normal execution.
(You can add a $debug variable at the top of the page to control all doMYSQL functions at once, though local ones take precedence.
*/

function doMYSQL($sql, $debug_local = 'none')
{
  $mysqli = new mysqli("localhost", "username", "password", "database");
  $print = $sql; // Save unaltered copy in case 'print' is enabled later

  // Get debug settings (priority is user-set $debug_local, then global $debug, then default to 'none')
  global $debug;
  if (($debug == 'print' OR $debug == 'both') AND $debug_local == 'none'){$debug_local = $debug;}

  // Create list of variables in the query
  preg_match_all('/\$\w+/',$sql,$matches);

  // For each variable found, find its value and add its kind and value to $params 
  $params = array();
  foreach ($matches[0] AS $match)
  {
      $match = substr($match,1); // Get rid of the now-unneccessary '$'' on the variable name
      global $$match; // Get the global value for that variable
      $kind = gettype($$match);  // Get the kind for that variable

        // Convert PHP kind to mysqli kind for bind_result
        if ($kind == "integer"){$kind = 'i';} 
        if ($kind == "double"){$kind = 'd';}
        if ($kind == "string"){$kind = 's';}

      $params[0] .= $kind; // Adds to ongoing list of types in $param[0]
      $params[] = $$match; // Adds to ongoing list of values in $params[1+]
      $sql = str_replace("$"."$match", '?', $sql); // Switch variable with '?' in the query
      $print = str_replace("$"."$match", $$match."[$kind]", $print); // Switch variable with '?' in the query      
  }

  // If debug is print or both, print
  if ($debug_local == "print" OR $debug_local == "both")
  {
    echo "MYSQLi Debug: $print<br>"; 
  }


  // If debug is not 'print', run it
  if ($debug_local != 'print')
  {
    // Get first word; if a select/explain, set $close to false; otherwise set to 'true.'  If irregular query, error message.
    $temp = explode(' ',trim($sql),2);
    $firstword = strtolower($temp[0]);
    if ($firstword == 'select' OR $firstword == 'explain'){$close=false;}
    else if ($firstword == 'update' OR $firstword == 'delete' OR $firstword == 'insert'){$close=true;}
    else {echo "Invalid first word on query $query!<br>";}


    // Start misc code found on the PHP link
    $stmt = $mysqli->prepare($sql) or die ("Failed to prepared the statement!");

    call_user_func_array(array($stmt, 'bind_param'), refValues($params));

    $stmt->execute();

     if($close){
         $result = $mysqli->affected_rows;
     } else {
         $meta = $stmt->result_metadata();

         while ( $field = $meta->fetch_field() ) {
             $parameters[] = &$row[$field->name];
         }  

      call_user_func_array(array($stmt, 'bind_result'), refValues($parameters));

      while ( $stmt->fetch() ) {  
         $x = array();  
         foreach( $row as $key => $val ) {  
            $x[$key] = $val;  
         }  
         $results[] = $x;  
      }

      $result = $results;
     }

     $stmt->close();
     $mysqli->close();

     return  $result;   
   } 
}

function refValues($arr)
{
    if (strnatcmp(phpversion(),'5.3') >= 0) //Reference is required for PHP 5.3+
    {
        $refs = array();
        foreach($arr as $key => $value)
            $refs[$key] = &$arr[$key];
        return $refs;
    }
    return $arr;
}

Examples (generic):

doMYSQL('SELECT * FROM table WHERE id = $id');
doMYSQL('SELECT * FROM table');
doMYSQL('INSERT INTO table(id, name) VALUES ($id,$name)');


Examples (with data):
$user = 1;
$location = 'California';

$result = doMYSQL('SELECT * FROM watchlists_locations WHERE user = $user AND location = $location');
print_r($result);

doMYSQL('INSERT INTO watchlists_locations(user, location) VALUES ($user,"1000")');
?>

Answer 1

Hehe I get what you're going for but it doesn't have to be so complicated :)

If you want to use mysqli i would just just use double quotes and send your SQL through like "SELECT * FROM table WHERE id = $id". Anything that comes from user input you run through mysqli_real_escape_string() first.

As far as returning the appropriate response depending on the query type, here is a cut-down version of a function that i use.

function query($sql) { 

    $arr = explode(' ',trim($sql));
    $command = strtolower($arr[0]); 

    switch ($command) {
        case 'call':
        case 'select':
            // run query and return results
        break;
        case 'insert':
        case 'replace':
            // run query, then return insert_id
        break;
        case 'update':
        case 'delete':
            // run query and return resulting integer (rows affected)
        break;
    }

}  

Though if you want to safely and quickly bind variables i would scrap 'mysqli' and go with the PDO approach.

$result = query("SELECT * FROM table WHERE id = :id", [':id' => $id]);

function query($sql, $params) { 

    $db = new PDO('mysql:database=yourdb;host=127.0.0.1', 'user', 'password');
    $stmt = $db->prepare($sql);

    $arr = explode(' ',trim($sql));
    $command = strtolower($arr[0]); 

    switch ($command) {
        case 'call':
        case 'select':
            // run query and return results
            $stmt->execute($params);
            return $stmt->fetchAll();
        break;
        case 'insert':
        case 'replace':
            // run query, then return insert_id
            return $stmt->execute($params);
        break;
        case 'update':
        case 'delete':
            // run query and return resulting integer (rows affected)
            return $stmt->execute($params);
        break;
    }

}