0

I'm trying to make a database class with a insert method to make it easier for me to insert data to my database from different places in my code. What I got now is working, the only problem is that I want $variable to be dynamic as well. So that I can use it like this:

db_insert('users', 'username, password, name, email', 'ssss', $variable1, $variable2, $variable3);

And be able to pass it as many variables as I want. But I'm not really sure how I can do that. Any suggestions? 

<?php 

class Database {

    public $conn;

    function __construct() {
        $this->conn = new mysqli("localhost","username","password","database");
    }

    // $database = database name 
    // $tables = table names separated by ,  
    // $types = variable types
    // $variable = variables separated by , 
    // EX: db_insert('users', 'username, password, name, email', 'ssss', $variable)

    function db_insert($database, $tables, $types, $variable) {

        // Generate values string based on the value of $types
        $replace = array("i", "d", "s", "m"); // characters to replace
        $replace_with = array("?,", "?,", "?,", "?,"); // characters to replace with
        $values = str_replace($replace, $replace_with, $types); // replace 'i', 'd', 's', 'm' with '?,'
        $values = rtrim($values,", "); // remove last ',';

        $stmt = $this->conn->prepare("INSERT INTO $database ($tables) VALUES ($values)"); // prepare statement
        $stmt->bind_param($types, $variable); // bind parameters
        $stmt->execute(); // insert to database 

    }

}

$data = "test";
$dbConn = new Database();
$dbConn->db_insert("users", "username", "s", $data);
?>
hectorct
  • 3,335
  • 1
  • 22
  • 40
Rajohan
  • 1,411
  • 2
  • 10
  • 27
  • 1
    Pass in an array? – David Jan 16 '18 at 18:07
  • But how would i use that in the bind_param? Run a loop inside it? – Rajohan Jan 16 '18 at 18:10
  • Yes, you'll have to run the loop, or pass the variables into the execute instead. – aynber Jan 16 '18 at 18:18
  • a complicated method using `call_user_func_array` is possible - build the string `iisisii` etc and bind same number of variables ~ I'll try to post an example – Professor Abronsius Jan 16 '18 at 18:21
  • 1
    PDO is much easier to use than mysqli for this kind of task. See my solution in https://stackoverflow.com/questions/7383335/mysqli-prepare-vs-pdo/7383439#7383439 or https://stackoverflow.com/questions/15931394/how-to-bind-an-arbitrary-number-of-values-to-a-prepared-statement-in-mysqli/15933696#15933696 – Bill Karwin Jan 16 '18 at 19:10
  • You want to be able to take an array of values with keys that match field names in a database table and safely insert into or update the table with as few lines of code as possible.... use Idiorm or something similar http://j4mie.github.io/idiormandparis/ – thinsoldier Jan 16 '18 at 19:51

3 Answers3

0

There is likely a few other ways to do this but this is more or less the method I have used on the odd occasion - you will need to study this and adapt/adopt to fit your needs.

/* Some source data - field name to value */
$data=array(
    'child_id'  =>  23,
    'parent_id' =>  1,
    'path'      =>  'banana',
    'page'      =>  1,
    'menuitem'  =>  1,
    'menutext'  =>  'some text',
    'active'    =>  1
);

$keys = array_keys( $data );

/* temp arrays */
$tmp=array();
$params=array();
$types=array();
$placeholders=array();

/* 
    There are 4 types of placeholders but this does only 2
    You can probably do some testing of data values using gettype
    to make this more dynamic and allow for the other placeholder
    types.
*/
foreach( $data as $item ){
    $types[] = is_string( $item ) ? 's' : 'i';
    $placeholders[]='?';
}
$params[]=implode( '', &$types ); #ie: ississi etc as 1st element


/* create params array - fields  */
foreach( $data as $item ){
    $params[]=$item;
}

/* create the actual values to be passed by ref */
foreach( $params as $key => $value )$tmp[ $key ]=&$params[ $key ];



/* construct sql statement */
$sql=sprintf('insert into `customers` ( `%s` ) values ( %s )', implode( '`,`', $keys ), implode( ',', $placeholders ) );

/* to debug/preview */
echo $sql.BR;


$dbhost =   'localhost';
$dbuser =   'root'; 
$dbpwd  =   'xxx'; 
$dbname =   'xxx';
$db  = new mysqli( $dbhost, $dbuser, $dbpwd, $dbname );


$stmt=$db->prepare( $sql );
if( $stmt ){
    call_user_func_array( array( $stmt, 'bind_param' ), $tmp );
    $result = $stmt->execute();


    echo $result ? 'ok' : 'not ok';
}

Quickly hashing that together into a couple of functions

function prepareparams( $options=array() ){
    try{
        if( !empty( $options ) ){

            $values=array();
            $params=array();
            $types=array();
            $placeholders=array();

            $keys = array_keys( $options );

            foreach( $options as $item ){
                $types[] = is_string( $item ) ? 's' : 'i';
                $placeholders[]='?';
            }
            $params[]=implode( '', &$types ); #ie: ississi etc as 1st element

            /* create params array - fields  */
            foreach( $options as $item ){
                $params[]=$item;
            }

            /* create the actual values to be passed by ref */
            foreach( $params as $key => $value )$values[ $key ]=&$params[ $key ];


            return (object)array(
                'params'        =>  $params,
                'values'        =>  $values,
                'placeholders'  =>  $placeholders,
                'keys'          =>  $keys
            );
        } else {
            throw new Exception('Bad Foo');
        }
    }catch( Exception $e ){
        echo $e->getMessage();
    }
}

function preparesql( $table=false, $obj=object ){
    return sprintf('insert into `%s` ( `%s` ) values ( %s )', $table, implode( '`,`', $obj->keys ), implode( ',', $obj->placeholders ) );
}

You could then call it like this

$obj=prepareparams( $data );
$sql=preparesql( 'customers', $obj );

$stmt=$db->prepare( $sql );
if( $stmt ){

    call_user_func_array( array( $stmt, 'bind_param' ), $obj->values );
    $result = $stmt->execute();

    echo $result ? 'ok' : 'not ok';
}

As a fully working demo of using dynamic query building in mysqli consider the following

/* 
    A table of some sort for testing porpoises

    create the table as the first stage!!
*/
create table `testtable` (
    `id` int(10) unsigned not null auto_increment,
    `login` varchar(50) not null default '0',
    `db` varchar(50) not null default '0',
    `dr` varchar(50) not null default '0',
    `status` tinyint(3) unsigned not null default '0',
    `admin_ishop` int(10) unsigned not null default '0',
    `lasteditdate` datetime null default null,
    primary key (`id`)
)
collate='utf8_general_ci'
engine=innodb;


/* From commandline */
mysql> create table `testtable` (
    ->  `id` int(10) unsigned not null auto_increment,
    ->  `login` varchar(50) not null default '0',
    ->  `db` varchar(50) not null default '0',
    ->  `dr` varchar(50) not null default '0',
    ->  `status` tinyint(3) unsigned not null default '0',
    ->  `admin_ishop` int(10) unsigned not null default '0',
    ->  `lasteditdate` datetime null default null,
    ->  primary key (`id`)
    -> )
    -> collate='utf8_general_ci'
    -> engine=innodb;


mysql> describe `testtable`;
+--------------+---------------------+------+-----+---------+----------------+
| Field        | Type                | Null | Key | Default | Extra          |
+--------------+---------------------+------+-----+---------+----------------+
| id           | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| login        | varchar(50)         | NO   |     | 0       |                |
| db           | varchar(50)         | NO   |     | 0       |                |
| dr           | varchar(50)         | NO   |     | 0       |                |
| status       | tinyint(3) unsigned | NO   |     | 0       |                |
| admin_ishop  | int(10) unsigned    | NO   |     | 0       |                |
| lasteditdate | datetime            | YES  |     | NULL    |                |
+--------------+---------------------+------+-----+---------+----------------+

On the PHP side of things

/* a mysqli connection to whatever database */
$dbhost =   'localhost';
$dbuser =   'root'; 
$dbpwd  =   'xxx'; 
$dbname =   'xxx';
$db     = new mysqli( $dbhost, $dbuser, $dbpwd, $dbname );

/* Some pseudo-random source data - `field` name to `value` */
$data=array(
    'login'         =>  uniqid('user_'),
    'db'            =>  uniqid('db_'),
    'dr'            =>  rand(10,99),
    'status'        =>  rand(0,1),
    'admin_ishop'   =>  rand(0,1),
    'lastEditDate'  =>  date('Y-m-d H:i:s')
);
function type( $value ){
    switch( gettype( $value ) ){
        case 'string':return 's';
        case 'integer':return 'i';
        case 'double':
        case 'float':return 'd';
        case 'object':return 'b';
        default:return false;
    }
}
function prepareparams( $options=array() ){
    try{
        if( !empty( $options ) ){

            $values=array();
            $params=array();
            $types=array();
            $placeholders=array();

            $keys = array_keys( $options );

            foreach( $options as $item ){
                $types[] = type( $item ) ? type( $item ) : 's';
                $placeholders[]='?';
            }
            $params[]=implode( '', &$types ); #ie: ississi etc as 1st element

            /* create params array - fields  */
            foreach( $options as $item ){
                $params[]=$item;
            }

            /* create the actual values to be passed by ref */
            foreach( $params as $key => $value )$values[ $key ]=&$params[ $key ];


            return (object)array(
                'params'        =>  $params,
                'values'        =>  $values,
                'placeholders'  =>  $placeholders,
                'keys'          =>  $keys
            );
        } else {
            throw new Exception('Bad Foo');
        }
    }catch( Exception $e ){
        echo $e->getMessage();
    }
}
function preparesql( $table=false, $obj=object ){
    return sprintf('insert into `%s` ( `%s` ) values ( %s )', $table, implode( '`,`', $obj->keys ), implode( ',', $obj->placeholders ) );
}



/* calling the functions to build and execute the sql */
$obj=prepareparams( $data );
$sql=preparesql( 'testtable', $obj );

$stmt=$db->prepare( $sql );
if( $stmt ){

    call_user_func_array( array( $stmt, 'bind_param' ), $obj->values );
    $result = $stmt->execute();

    echo $result ? sprintf( 'Record Inserted: %d', $db->insert_id ) : sprintf( 'Bad Foo! %s', $db->error );
}

After a few runs of the script, a quick cmdline query

mysql> select * from testtable;
+----+--------------------+------------------+----+--------+-------------+---------------------+
| id | login              | db               | dr | status | admin_ishop | lasteditdate        |
+----+--------------------+------------------+----+--------+-------------+---------------------+
|  1 | user_5a5e5e2a23dcd | db_5a5e5e2a23dd1 | 44 |      1 |           1 | 2018-01-16 20:18:50 |
|  2 | user_5a5e5e2c072b4 | db_5a5e5e2c072b8 | 33 |      1 |           0 | 2018-01-16 20:18:52 |
|  3 | user_5a5e605a0b224 | db_5a5e605a0b229 | 32 |      0 |           0 | 2018-01-16 20:28:10 |
|  4 | user_5a5e605b0ef33 | db_5a5e605b0ef38 | 87 |      1 |           1 | 2018-01-16 20:28:11 |
|  5 | user_5a5e605b8bf4f | db_5a5e605b8bf54 | 85 |      1 |           1 | 2018-01-16 20:28:11 |
+----+--------------------+------------------+----+--------+-------------+---------------------+
Professor Abronsius
  • 33,063
  • 5
  • 32
  • 46
0

I found a solution by passing in an array and then just adding "..." in front of it in the bind like this:

$stmt->bind_param($types, ...$variables);

<?php 

    class Database {
        public $conn;

        function __construct() {
            $this->conn = new mysqli("localhost","root","password","database");
        }

        // $database = database name 
        // $tables = table names separated by ,  
        // $types = variable types
        // $variables = variables separated by , 
        // EX: db_insert('users', 'username, password, name, email', 'ssss', $variables)

        function db_insert($database, $tables, $types, $variables) {

            // Generate values string based on the value of $types
            $replace = array("i", "d", "s", "m"); // characters to replace
            $replace_with = array("?,", "?,", "?,", "?,"); // characters to replace with
            $values = str_replace($replace, $replace_with, $types); // replace 'i', 'd', 's', 'm' with '?,'
            $values = rtrim($values,", "); // remove last ',';

            $stmt = $this->conn->prepare("INSERT INTO $database ($tables) VALUES ($values)"); // prepare statement
            $stmt->bind_param($types, ...$variables); // bind parameters
            $stmt->execute(); // insert to database 

        }

    }

    $data = array('test', 'test2', 15, "test4");
    $dbConn = new Database();
    $dbConn->db_insert("users", "username, email, name, password", "ssis", $data);
?>
Rajohan
  • 1,411
  • 2
  • 10
  • 27
-1

Please try like this

$sql_columns = array();
$sql_values = array();
$stmt = "INSERT INTO  ".$database." (".implode(",",$sql_columns). ") VALUES (".implode(",",$sql_values). ")";
Roshin
  • 91
  • 1
  • 8
  • 1
    Not only is the syntax incorrect, but it's also horrible insecure. – aynber Jan 16 '18 at 18:27
  • Thanks for pointing out that. Updated the syntax. Validation before the variables get to this point is assumed. – Roshin Jan 16 '18 at 18:54
  • Never ever assume. This method would work if the `$sql_values` actually holds the place holders, but prepared statements and parameter binding should be used, as the OP is attempting to do. – aynber Jan 16 '18 at 18:56
  • `Validation before the variables` will do nothing to quotes in field values. So, your query fails. – u_mulder Jan 16 '18 at 18:57