数据库类，insert方法

nkhmeac6  于 2021-06-21  发布在  Mysql
关注(0)|答案(3)|浏览(236)

我正在尝试用insert方法创建一个数据库类，以便更容易地将数据从代码中的不同位置插入到数据库中。我现在得到的是工作，唯一的问题是我希望$variable也是动态的。所以我可以这样使用它：

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

我想传递多少变量就传递多少。但我真的不知道该怎么做。有什么建议吗？

<?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);
?>
mysqlphp

3条答案

按热度按时间
uelo1irk

uelo1irk1#

我找到了一个解决方案，通过传入一个数组，然后在绑定中在其前面添加“…”，如下所示：
$stmt->绑定参数（$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);
?>
赞(0）分享  回复(0）举报  2021-06-21
ujv3wf0j

ujv3wf0j2#

可能还有其他一些方法可以做到这一点，但这或多或少是我在一个奇怪的场合使用的方法-你将需要研究这一点，并调整/采用，以适应你的需要。

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

快速将其散列到几个函数中

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

你可以这样称呼它

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

作为在mysqli中使用动态查询构建的完整演示，请考虑以下内容

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

在事情的另一面

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

在运行了几次脚本之后，一个快速的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）分享  回复(0）举报  2021-06-21
vuktfyat

vuktfyat3#

请这样试试

$sql_columns = array();
$sql_values = array();
$stmt = "INSERT INTO  ".$database." (".implode(",",$sql_columns). ") VALUES (".implode(",",$sql_values). ")";
赞(0）分享  回复(0）举报  2021-06-21
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com