如何使用 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&nbsp; &nbsp; &nbsp;= filter_input(INPUT_POST, 'score', FILTER_VALIDATE_INT);$max_score = filter_input(INPUT_POST, 'maxScore', FILTER_VALIDATE_INT);$opened&nbsp; &nbsp; = filter_input(INPUT_POST, 'opened', FILTER_VALIDATE_INT);$finished&nbsp; = filter_input(INPUT_POST, 'finished', FILTER_VALIDATE_INT);$time&nbsp; &nbsp; &nbsp; = filter_input(INPUT_POST, 'time', FILTER_VALIDATE_INT);$user_id&nbsp; &nbsp;= $_SESSION['ex_uid'];// Prepare and execute statement&nbsp; &nbsp; &nbsp;$params = array($score, $max_score, $opened, $finished, $time, $user_id, $content_id);&nbsp;&nbsp;$sql = "&nbsp; &nbsp; INSERT INTO results (score, max_score, opened, finished, time)&nbsp;&nbsp; &nbsp; VALUES (?, ?, ?, ?, ?, ?, ?)";$stmt = sqlsrv_query($connmssql, $sql, $params);if ($stmt === false) {&nbsp;&nbsp;&nbsp; &nbsp; echo "Row insertion failed.\n";&nbsp;&nbsp;&nbsp; &nbsp; die(print_r(sqlsrv_errors(), true));&nbsp;&nbsp;} else {&nbsp;&nbsp;&nbsp; &nbsp; echo "Row successfully inserted.\n";&nbsp;&nbsp;}&nbsp;...&nbsp;?>

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) {&nbsp; &nbsp; return '"' . $value . '"';}, $values);$sql = "INSERT INTO results (score, max_score, opened, finished, time) VALUES (" . implode(',', $values) . ")";