时间戳

首页 > 解决方案 > 将无线电类型的输入发送到 mysql 数据库时出现问题

php - 将无线电类型的输入发送到 mysql 数据库时出现问题

问题描述

我有一个带有多个无线电类型输入的表单，名称相同但值不同。所有的输入都放在表中。我希望用户在每一行中选择一个答案并提交，以便将答案发送到 db。但我收到一个错误：

Error: INSERT INTO `teachers_ans` (mark1, mark2, mark3, mark4, mark5, mark6, mark7, mark8, 
mark9, mark10, mark11, 2mark, 3mark, 4mark1, 4mark2, 4mark3, 4mark4, 4mark5, 4mark6, 4mark7, 
4mark8, 4mark9, 5mark1, 5mark2, 5mark3, 5mark4, 5mark5, 5mark6, 5mark7, 5mark8, 5mark9, 
5mark10, 5mark11, 6mark) VALUES ('1', '1', '1', '1', '1', '1', '1', '1', '1', '1', '1', '1', 
'1', '1', '1', '1', '1', '1', '1', '1', '1', '1', '1', '1', '1' '1', '1', '1', '1', '1', 
'1', '1', '1', 'SSSSSSSSSSSSSSSS') Column count doesn't match value count at row 1

这是 HTML 和 PHP 代码：

<?php
    if (isset($_POST['submit'])) 
    {
        // get all inputs values
        @$A1_1 = $_POST['mark1'];
        @$A1_2 = $_POST['mark2'];
        @$A1_3 = $_POST['mark3'];
        @$A1_4 = $_POST['mark4'];
        @$A1_5 = $_POST['mark5'];
        @$A1_6 = $_POST['mark6'];
        @$A1_7 = $_POST['mark7'];
        @$A1_8 = $_POST['mark8'];
        @$A1_9 = $_POST['mark9'];
        @$A1_10 = $_POST['mark10'];
        @$A1_11 = $_POST['mark11'];
        @$A2 = $_POST['2mark'];
        @$A3 = $_POST['3mark'];
        @$A4_1 = $_POST['4mark1'];
        @$A4_2 = $_POST['4mark2'];
        @$A4_3 = $_POST['4mark3'];
        @$A4_4 = $_POST['4mark4'];
        @$A4_5 = $_POST['4mark5'];
        @$A4_6 = $_POST['4mark6'];
        @$A4_7 = $_POST['4mark7'];
        @$A4_8 = $_POST['4mark8'];
        @$A4_9 = $_POST['4mark9'];
        @$A5_1 = $_POST['5mark1'];
        @$A5_2 = $_POST['5mark2'];
        @$A5_3 = $_POST['5mark3'];
        @$A5_4 = $_POST['5mark4'];
        @$A5_5 = $_POST['5mark5'];
        @$A5_6 = $_POST['5mark6'];
        @$A5_7 = $_POST['5mark7'];
        @$A5_8 = $_POST['5mark8'];
        @$A5_9 = $_POST['5mark9'];
        @$A5_10 = $_POST['5mark10'];
        @$A5_11 = $_POST['5mark11'];
        @$A6 = $_POST['6mark'];
        //init db params
        $servername = "localhost";
        $username = "root";
        $password = "";
        $dbname = "teachers";

        print_r($_POST);

       // create connection to the db
       $conn = mysqli_connect($servername, $username, $password, $dbname);
      // check connection errors, if found, display it
      if (!$conn) { echo "ERROR: " . "<br>" . mysqli_error($conn); }

      $sql_send_query = "INSERT INTO `teachers_ans` (mark1, mark2, mark3, mark4, mark5, 
                                                    mark6, mark7,
                                              mark8, mark9, mark10, mark11, 2mark, 3mark, 
                                              4mark1, 4mark2, 4mark3, 4mark4, 4mark5,  
                                                                 4mark6,
                                              4mark7, 4mark8, 4mark9, 5mark1, 5mark2, 
                                                    5mark3,
                                              5mark4, 5mark5, 5mark6, 5mark7, 5mark8, 
                                                5mark9,
                                              5mark10, 5mark11, 6mark)
                   VALUES ('$A1_1', '$A1_2', '$A1_3', '$A1_4', '$A1_5', '$A1_6',
                           '$A1_7', '$A1_8', '$A1_9', '$A1_10', '$A1_11', 
                           '$A2', '$A3', '$A4_1', '$A4_2', '$A4_3', '$A4_4', '$A4_5',
                           '$A4_6', '$A4_7', '$A4_8', '$A4_9', '$A5_1', '$A5_2', '$A5_3'
                           '$A5_4', '$A5_5', '$A5_6', '$A5_7', '$A5_8', '$A5_9', '$A5_10',
                           '$A5_11', '$A6')";

    if (!mysqli_query($conn, $sql_send_query))
    {
        echo "Error: " . $sql_send_query . "<br>" . mysqli_error($conn);
    }
?>

<form action="" method="post">
    <table>
        <tr>
            <td>1</td>
            <td>Question 1</td>
            <td><input type="radio" id="mark" name="mark1" value="1"><span>1</span></td>
            <td><input type="radio" id="mark" name="mark1" value="2"><span>2</span></td>
            <td><input type="radio" id="mark" name="mark1" value="3"><span>3</span></td>
            <td><input type="radio" id="mark" name="mark1" value="4"><span>4</span></td>
            <td><input type="radio" id="mark" name="mark1" value="5"><span>5</span></td>
       </tr>
    </table>
</form>

我也尝试过编写不带@ 的变量，但没有帮助。我应该在这里做什么？请帮忙 ：）

标签： phphtmlmysqldatabase

解决方案

好的，所以这是一个有趣的小项目，可以探索和扩展。

正如我和GrumpyCrouton在评论中提到的那样，您应该查看PHP 数组以将数据保存在list的形式中。

您还应该考虑完全重新设计数据库的结构，但我想这是一个学校项目，所以我希望您会在某个时候做到这一点。

数组使遍历列表变得更加容易，并且在您的代码中几乎没有重复。

要回答您的实际问题：

您问

将无线电类型的输入发送到 mysql 数据库时出现问题

因此，单选按钮输入设置为：

label {
    display:block;
}
<label><input type='radio' name='something' value='1'>Click me One</label>
<label><input type='radio' name='something' value='2'>Click me Two</label>

所以当这个表单被提交时，值 $_POST['something']要么是空的（没有点击，所以没有任何东西被发送到 PHP），或者 value1或2.

笔记：

准备好的语句是使用 MySQL / PHP 接口的最佳方式，但为了方便起见，我不会在这里深入研究，而是专注于数组如何帮助您的基础知识

所以; 让我们试试看。

第一的

  • 看起来您的 HTML 输入名称与您的 MySQL 列名称完全相同。因此，您可以在构建 SQL 时使用这些键值。
  • 您将拥有一组键值->对，其中“键”是输入字段和 SQL 列的名称，值是该列具有的值。

第二

我们的过程将循环通过表单提供给 PHP 的数据并将其排序到一个数组中，然后将该数组操作为一个 SQL 执行字符串，准备好由 MySQL 处理程序运行。

这样做的好处是，就其性质而言，它不会尝试插入空值。

少说话，多代码

// You may have to remove unwanted POST values from the POST array before this line
// You can also use this foreach loop to add custom functions/tweaks to each given value

foreach($_POST as $key=>$row){
     $insert[$key] = $row;
}
unset($key,$row);

现在我们有了一个元素数组（实际上是 POSTED 数据的副本），我们可以从中获得一些乐趣。

$insert = array_filter($insert); // remove empty values (optional)
// https://www.php.net/manual/en/function.array-filter.php
$column = $data = []; // set two empty arrays

foreach($insert as $key=>$value){
   $column[] = preg_replace('/[^a-z0-9]/i','', $key); // Security measure: PCRE Regex removes all non alphanumeric characters. Tweak for your own needs.
   $data[] = preg_replace('/[^a-z0-9]/i','',$value); // Security measure: PCRE Regex removes all non alphanumeric characters. Tweak for your own needs. 
}

// https://www.php.net/manual/en/function.implode
$columns = implode(",", $column);
$datas = implode("','", $data); 
// note the quote marks because you appear to be inserting into string columns.

现在我们将这些数据和列值插入到 SQL 指令中：

// note the outer single quote marks on the $datas string insert. 
$sql_send_query = "INSERT INTO `teachers_ans` (".$columns.") VALUES ('".$datas."')";

if (!mysqli_query($conn, $sql_send_query))
{
    echo "Error: " . $sql_send_query . "<br>" . mysqli_error($conn);
}

我强调这不是生产代码；这只是为了展示阵列如何减少重复和 DE-JA-VU 循环。

生产版本

首先，限定您的每个单选按钮答案都在自己的数组中；如：

<form action="" method="post">
    <table>
        <tr>
            <td>1</td>
            <td>Question 1</td>
            <td><input type="radio" id="mark" name="answer[mark1]" value="1"><span>1</span></td>
            <td><input type="radio" id="mark" name="answer[mark1]" value="2"><span>2</span></td>
            <td><input type="radio" id="mark" name="answer[mark1]" value="3"><span>3</span></td>
            <td><input type="radio" id="mark" name="answer[mark1]" value="4"><span>4</span></td>
            <td><input type="radio" id="mark" name="answer[mark1]" value="5"><span>5</span></td>
       </tr>
    </table>
</form>

这样您的所有marks 都将在 $_POST 数组中answers( $_POST['answers'])

然后我们需要设置 PDO ：

$host = 'localhost';
$db   = 'something';
$user = 'someone';
$pass = '';
$charset = 'utf8mb4'; //best one to use. 

$dsn = "mysql:host=".$host.";dbname=".$db.";charset=".$charset;
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
try {
     $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $ex) {
     throw new \PDOException($ex->getMessage(), (int)$ex->getCode());
}

现在您有了一个工作的 PDO 对象$pdo。

foreach($_POST['answers'] as $key=>$row){
     $key = preg_replace('/[^a-z0-9]/i','', $key); // Security measure
     $insert[$key] = preg_replace('/[^0-9]/i','', $row); //numeric values only 
}
unset($key,$row);

$columns = implode(",", array_keys($insert));
$references = ":".implode(", :", array_keys($insert)); //PDO SQL references.

$sql_send_query = "INSERT INTO `teachers_ans` (".$columns.") VALUES (".$references.")";

$pdo->prepare($sql_send_query)->execute($insert);

print "data inserted; insert ID is ".$pdo->lastInsertId();

推荐阅读