MySQLi准备的带有动态更新查询的语句

问题描述

我正忙于从正常的mysql_queries更改为准备好的语句，现在我发现了一个函数，该函数根据有多少个非空字段生成动态查询.

I'm busy changing from normal mysql_queries to prepared statements, now I found a function that generated a dynamic query based on how many fields were not empty.

我设法对其进行了转换，因此它可以将每个字段作为单独的查询运行，但是有没有一种方法可以将所有这些查询放入一个查询而无需转换为PDO?

I managed to convert it so it runs each field as a separate query, but is there a way to put all these queries into one query without converting to PDO ?

public function edit($ticket_id, $department_id = '', $location_id = '', $ticketcat_id = '', $ticketsta_id = '',
                     $ticketmed_id = '', $ticketpri_id = '', $ticket_assigned = '', $ticket_plandate = '',
                     $ticket_user_name = '', $ticket_user_email = '', $ticket_user_phone = '', $ticket_subject = '') {

            $data = array(
            array('field' => 'department_id', 'value' => $department_id, 'type' => 'i'),
            array('field' => 'location_id', 'value' => $location_id, 'type' => 'i'),
            array('field' => 'ticketcat_id', 'value' => $ticketcat_id, 'type' => 'i'),
            array('field' => 'ticketsta_id', 'value' => $ticketsta_id, 'type' => 'i'),
            array('field' => 'ticketmed_id', 'value' => $ticketmed_id, 'type' => 'i'),
            array('field' => 'ticketpri_id', 'value' => $ticketpri_id, 'type' => 'i'),
            array('field' => 'ticket_assigned', 'value' => $ticket_assigned, 'type' => 'i'),
            array('field' => 'ticket_plandate', 'value' => $ticket_plandate, 'type' => 's'),
            array('field' => 'ticket_user_name', 'value' => $ticket_user_name, 'type' => 's'),
            array('field' => 'ticket_user_email', 'value' => $ticket_user_email, 'type' => 's'),
            array('field' => 'ticket_user_phone', 'value' => $ticket_user_phone, 'type' => 's'),
            array('field' => 'ticket_subject', 'value' => $ticket_subject, 'type' => 's')
            );

            foreach($data as $id => $data_) {
                IF(empty($data_['value'])) unset($data[$id]);
            }

            IF(count($data) > 0) {

                $errors = false;
                $query = 'UPDATE tickets SET ';
                foreach($data as $id => $values) {
                    $query2 = $query.$values['field'].' = ? WHERE ticket_id = ? ';
                    echo $query2.'<br />';
                    IF($stmt = $this->db->prepare($query2)) {
                        $types = $values['type'].'i';
                        $stmt->bind_param($types, $values['value'], $ticket_id);

                        IF(!($stmt->execute())) {
                            $errors = true;
                        }
                        $stmt->close();
                    }
                }

                IF(!$errors) {
                    $this->db->commit();
                    return true;
                }

                return false;
            }
        }

推荐答案

技巧是构造一个包含要绑定的参数的数组，然后在call_user_func_array的帮助下，可以将该数组传递给bind_param.

The trick is to construct an array that contains the parameters that you want to bind, then with the help of call_user_func_array, you can pass this array to bind_param.

请参见 http://www.php.net /manual/zh-CN/function.call-user-func-array.php 了解有关call_user_func_array的详细信息.

See http://www.php.net/manual/en/function.call-user-func-array.php for details on call_user_func_array.

您的代码可以类似于:

    $para_type="";
    /* $para is the array that later passed into bind_param */
    $para=array($para_type);
    $query = 'UPDATE tickets SET ';

    IF(count($data) != 0) {
        /* Looping all values */

        foreach($data as $k=>$d) {
            $query .= '`'.$d['field'].'` = ? ,';

            $para_type .=$d['type'];

            $para[] = &$data[$k]['value'];
        }

        /* removing last comma */
        $query[(strlen($query)-2)] = '';

        /* adding where */
        $query .= ' WHERE `ticket_id` = ?';
        $para_type .= 'i';
        $para[]=&$ticket_id;

        call_user_func_array(array($stmt, 'bind_param'), $para);

        return true;
    }

请注意所有参数前面的&，这是bind_param所必需的.

Notice the & in front of all parameters, it is required by bind_param.

我认为更好的另一种方法是使用PDO.它使用命名参数，并且可以进行增量绑定.

Another way which I think is better is to use PDO. It takes named parameter and can do incremental bind.

这篇关于MySQLi准备的带有动态更新查询的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！