如何使用 PHP 将数组插入 SQL Server 数据库

Question

我想使用 PHP 在 SQL Server 数据库中插入一个数组作为值。这是我的代码：   $data = array(  'score' => filter_input(INPUT_POST, 'score', FILTER_VALIDATE_INT),  'max_score' => filter_input(INPUT_POST, 'maxScore',    FILTER_VALIDATE_INT),  'opened' => filter_input(INPUT_POST, 'opened', FILTER_VALIDATE_INT),  'finished' => filter_input(INPUT_POST, 'finished',    FILTER_VALIDATE_INT),  'time' => filter_input(INPUT_POST, 'time', FILTER_VALIDATE_INT)    );   $data['user_id'] = $_SESSION['ex_uid'];   $data['content_id'] = $content_id;   $sql = "INSERT INTO results (content_id, user_id, score, max_score, opened, finished, time)      VALUES ($data)";   $params = array(1, "some data");   $stmt = sqlsrv_query( $connmssql, $sql, $params);

Answer 1

始终尝试使用参数化语句。函数sqlsrv_query()既做语句准备又做语句执行，可用于执行参数化查询。在您的情况下，您需要为数组中的?每个项目放置一个占位符$params：

<?php

...

// Parameters

$score     = filter_input(INPUT_POST, 'score', FILTER_VALIDATE_INT);

$max_score = filter_input(INPUT_POST, 'maxScore', FILTER_VALIDATE_INT);

$opened    = filter_input(INPUT_POST, 'opened', FILTER_VALIDATE_INT);

$finished  = filter_input(INPUT_POST, 'finished', FILTER_VALIDATE_INT);

$time      = filter_input(INPUT_POST, 'time', FILTER_VALIDATE_INT);

$user_id   = $_SESSION['ex_uid'];

// Prepare and execute statement     

$params = array($score, $max_score, $opened, $finished, $time, $user_id, $content_id);  

$sql = "

    INSERT INTO results (score, max_score, opened, finished, time) 

    VALUES (?, ?, ?, ?, ?, ?, ?)

";

$stmt = sqlsrv_query($connmssql, $sql, $params);

if ($stmt === false) {  

    echo "Row insertion failed.\n";  

    die(print_r(sqlsrv_errors(), true));  

} else {  

    echo "Row successfully inserted.\n";  

} 

... 

?>

Answer 2

// Get only values

// You must be sure that your data in the same order that your SET keys in the sql string

$values = array_values($data);

// Wrap with quotes

$values = array_map(function ($value) {

    return '"' . $value . '"';

}, $values);

$sql = "INSERT INTO results (score, max_score, opened, finished, time) VALUES (" . implode(',', $values) . ")";