使用PDO从php页面将包含更多列的表单数据插入到mysql数据库中

I have a HTML form which has more than 25 entries.

我有一个HTML表单,其中包含超过25个条目。

I know how to insert normal form data into MySQL database using PHP PDO. But I just want to know if there is any alternative way in which I can store the form entries to an array and insert the data into database using the array.

我知道如何使用PHP PDO将普通表单数据插入MySQL数据库。但我只想知道是否有任何替代方法可以将表单条目存储到数组中并使用该数组将数据插入数据库。

Because writing an insert statement for more than 25 columns is cumbersome.

因为为超过25列写入insert语句非常麻烦。

2 个解决方案

#1

You could always use a PDO wrapper class, I use the class below to handle most of my PDO queries:

您总是可以使用PDO包装器类,我使用下面的类来处理我的大多数PDO查询:

class DB {
    protected
        // Return from mysql statement
        $data = array(),
        // Used for checking whether something was added to the JSON object and remove it if the table column doens't exist
        $table_cols = array(),
        // Storing the table name we are working with
        $table = '';
    protected static
        // PDO connection to the DB
        $_conn = null,
        // The DB credentials retrieved from the ini file
        $_credentials = array ();
    private
        $_id = -1,
        $_keys = array(),
        $_values = array(),
        $_last_insert_id = -1,
        $_results = array();

    //
    //  PUBLIC FUNCTIONS
    //
    public function __construct () {
        if (self::$_conn === null) {
            self::setCredentials();
            try {
                self::$_conn = new \PDO("mysql:host=" . self::$_credentials['host'] . ";dbname=" . self::$_credentials['dbname'] , self::$_credentials['username'], self::$_credentials['password']);
            } catch (\PDOException $e) {
                DebugLog::instance('DB')->error($e, 'db_connection');
            }
        }
    }

    public function insert ($data) {
        $data = $this->checkCols($data);
        // Allows us to quickly clone data by removing the id and inserting as a new record
        if (isset($data['id'])) {
            unset($data['id']);
        }
        $this->data = $data;
        $this->setDataBinding();
        $sql = "INSERT INTO `" . self::$_credentials['dbname'] . "`.`{$this->table}` (`" . implode('`, `', $this->_keys) . "`) VALUES (:" . implode(', :', $this->_keys) . ");";
        return $this->prepareAndExecute($sql);
    }

    public function update ($data) {
        $data = $this->checkCols($data);
        if (!isset($data['id'])) {
            // Houston we have a problem, there needs to be an id to update a record
            DebugLog::instance('DB')->error("No ID set for Update: " . implode(', ', array_keys($data)), 'db_id_' . $this->table);
        } else {
            // We need to unset the id because it shouldn't be in the data binding
            // But we still hold onto it for the where clause
            $id = $data['id'];
            unset($data['id']);
            $this->data = $data;

            $this->setDataBinding();

            $sql = "UPDATE `" . self::$_credentials['dbname'] . "`.`{$this->table}` SET ";
            $query_string = "";

            foreach ($this->_keys as $i => $key) {
                $query_string .= "`{$key}` = :{$key}, ";
            }

            $query_string = trim($query_string);
            if (substr($query_string, -1) === ',') {
                $query_string = substr($query_string, 0, -1);
            }

            $sql .= $query_string . " WHERE `id` = '{$id}'";
            return $this->prepareAndExecute($sql);
        }
        return false;
    }

    public function remove ($id) {
        $this->rawQuery("DELETE FROM `{$this->table}` WHERE `id` = '{$id}';");
    }

    public function rawQuery ($sql) {
        try {
            $pdo = self::$_conn->query($sql);
            $pdo->setFetchMode(\PDO::FETCH_ASSOC);
        } catch (\PDOException $e) {
            DebugLog::instance('DB')->error($e, 'db_query_' . $this->table);
            return array();
        }
        return $pdo->fetchAll();
    }

    //
    //  GETTERS
    //
    public function getColumns () {
        return $this->table_cols;
    }

    public function getLastInsertID () {
        return $this->_last_insert_id;
    }

    public function getRecord ($id) {
        $this->_id = $id;
        $response = $this->rawQuery("SELECT * FROM `{$this->table}` WHERE `id` = '{$id}'");
        $this->_results = $response[0];
    }

    public function getResults () {
        return $this->_results;
    }

    public function close () {
        $this->setDefaults();
    }

    //
    //  PROTECTED FUNCTIONS
    //
    protected function initColumns () {
        $sql = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '" . self::$_credentials['dbname'] . "' AND TABLE_NAME = '{$this->table}';";
        $response = $this->rawQuery($sql);
        if (!empty($response)) {
            return $this->parseColumns($response);
        }
        return array();
    }

    //
    //  PRIVATE FUNCTIONS
    //
    private function setDataBinding () {
        $this->_keys = array_keys($this->data);
        foreach ($this->data as $k => $v) {
            $this->_values[':' . $k] = $v;
        }
    }

    private function prepareAndExecute ($sql) {
        try {
            $q = self::$_conn->prepare($sql);
            $q->setFetchMode(\PDO::FETCH_ASSOC);
            if ($q->execute($this->_values)) {
                while ($r = $q->fetch()) {
                    $this->_results[] = $r;
                }
                $this->_last_insert_id = self::$_conn->lastInsertId();
                return true;
            } else {
                DebugLog::instance('DB')->error('Failed to execute', 'db_' . $this->table);
            }
        } catch (\PDOException $e) {
            DebugLog::instance('DB')->error($e, 'db_' . $this->table);
        }
        return false;
    }

    private function checkCols ($array) {
        foreach ($array as $col => $val) {
            if (!in_array($col, $this->table_cols)) {
                unset($array[$col]);
            }
        }
        return $array;
    }

    private static function setCredentials () {
            // I actually use a config file here, instead of hard coding
            self::$_credentials = array(
                'host' => '',
                'dbname' => '',
                'username' => '',
                'password' => ''
            );
    }

    private function parseColumns ($cols) {
        $array = array();
        foreach ($cols as $index => $col_array) {
            $array[] = $col_array['COLUMN_NAME'];
        }
        return $array;
    }

    private function setDefaults () {
        $this->data = array();
        $this->table_cols = array();
        $this->table = '';
        self::$_conn = null;
        $this->_keys = array();
        $this->_values = array();
        $this->_last_insert_id = -1;
        $this->_results = array();
    }
}

Then for each table, create a class that extends the class above. For example, lets say we have a users table:

然后为每个表创建一个扩展上面类的类。例如,假设我们有一个用户表:

class UsersTable extends DB {
    public function __construct () {
        // Parent constructor creates the DB connection
        parent::__construct();
        // Now let's set the desired table based on this class
        $this->table = "users";
        // Set the table columns, for mysql column validation
        $this->table_cols = $this->initColumns();
    }
}

Usage is than as simple as:

用法非常简单:

$table = new UsersTable();
$table->insert($record);

#2

As long as your array has the 25 values in the same order as the table you can use unnamed parameters and lazy binding See PDO info

只要您的数组具有与表相同顺序的25个值,您就可以使用未命名参数和延迟绑定请参阅PDO信息

$sql ="INSERT INTO table_name VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,)";
$stmt = $dbh->prepare($sql); 
$stmt->execute($array);