改进这个插入类函数php - Improve this insert class function php

Improve this insert class function php

本文关键字：类函数 php 插入 | 更新日期: 2023-09-27

我讨厌键入INSERT查询，你总是错过一些东西，并得到语法错误。因此，我想创建自己的函数，来做这个。这是我目前得到的:

$data['test'] = array('username' => 'john', 
              'password' => 'hello',
              'userlevel' => '__d');
$table = 'users';
$numItems = count($data['test']);
$i = 0;
$sql = "INSERT INTO " . $table . "(". implode(", ", array_keys($data['test'])) .")";

$sql .= " VALUES (";
foreach ($data['test'] as $value) {
    if ($i+1 == $numItems and $value == '__d') {
        $sql .= "" . 'NOW()' . ")";
    } else if ($i+1 == $numItems) {
        $sql .= "'" . $value . "')";
    } else if ($value == '__d') {
        $sql .= "" . 'NOW()' . ", ";
    } else {
        $sql .= "'" . $value . "', ";
    }
            $i++;

}
echo $sql;

嗯,是的。关于如何改进这段代码有什么建议吗?

<?php
//test data
$columns = array(
    'username'=>'john',
    'password'=>'hello',
    'userlevel'=>1,
    'date'=>'__d'
);
$table = 'users';
// replace keys and values with SQL delimeters
foreach($columns as $k=>$v) {
    unset($columns[$k]);
    if ($v != '__d' && !is_int($v))
        $v = "'$v'";
    if ($v == '__d')
        $v = 'NOW()';
    $columns["`$k`"] = $v;
}
// create the query
$sql = sprintf('INSERT INTO %s (%s) VALUES (%s)',
            $table,
            implode(",", array_keys($columns)),
            implode(",", $columns)
       );
echo $sql;
?>

输出:

INSERT INTO users (`username`,`password`,`userlevel`,`date`) VALUES ('john','hello',1,NOW())

sprintf可以使内容更具可读性

$columns = array('username'=>'john', 'password'=>'hello', 'userlevel'=>1, 'date'=>$date);
$table = 'users';
$sql = sprintf(
    "insert into %s(%s) values(%s)",
    $table,
    implode( ',', array_keys( $columns ) ),
    implode( ',', array_map( function($v){ return ':'.$v; }, array_keys( $columns ) ) )
);
$stmnt = $pdo->prepare( $sql );
foreach( $columns as $column => $value ) {
    $stmnt->bindValue( ':'.$column, $value );
}
$stmnt->execute();

$columns = array('username'=>'john', 'password'=>'hello', 'userlevel'=>1, 'date'=>$date);
$table = 'users';
//function here
$sql = "INSERT INTO " . $table . "(". implode(",", array_keys($columns)) .") VALUES ('". implode(",", $columns) ."')";
mysql_query($sql);