Estoy tratando de hacer una clase de base de datos con un método de inserción para que sea más fácil para mí insertar datos en mi base de datos desde diferentes lugares en mi código. Lo que tengo ahora está funcionando, el único problema es que quiero que $ variable también sea dinámica. Para que pueda usarlo así:

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

Y poder pasarle tantas variables como quiera. Pero no estoy realmente seguro de cómo puedo hacer eso. ¿Alguna sugerencia?

<?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);
?>
0
Rajohan 16 ene. 2018 a las 21:04

3 respuestas

La mejor respuesta

Encontré una solución pasando una matriz y luego simplemente agregando "..." delante de ella en el enlace de esta manera:

$ stmt-> bind_param ($ tipos, ... $ 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);
?>
0
Rajohan 16 ene. 2018 a las 19:49

Por favor intente así

$sql_columns = array();
$sql_values = array();
$stmt = "INSERT INTO  ".$database." (".implode(",",$sql_columns). ") VALUES (".implode(",",$sql_values). ")";
-1
Roshin 16 ene. 2018 a las 18:53

Es probable que haya algunas otras formas de hacerlo, pero este es más o menos el método que he usado en alguna ocasión: necesitará estudiar esto y adaptarlo / adoptarlo para satisfacer sus necesidades.

/* 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';
}

Combina rápidamente eso en un par de funciones

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 ) );
}

Entonces podrías llamarlo así

$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';
}

Como una demostración completamente funcional del uso de la creación dinámica de consultas en mysqli, considere lo siguiente

/* 
    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    |                |
+--------------+---------------------+------+-----+---------+----------------+

En el lado PHP de las cosas

/* 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 );
}

Después de algunas ejecuciones del script, una consulta rápida de cmdline

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 |
+----+--------------------+------------------+----+--------+-------------+---------------------+
0
RamRaider 16 ene. 2018 a las 20:33
48287330