时间戳

首页 > 解决方案 > 将多个外部 MSSQL 表中的多个组合 PHP 表单数据添加到一个主表中

php - 将多个外部 MSSQL 表中的多个组合 PHP 表单数据添加到一个主表中

问题描述

我得到了这个例子 7 个复选框：

    <table style="border-collapse: collapse; width: 100%;" border="1">
    <tbody>


    <tr style="height: 21px;">
    <td style="width: 25%; height: 21px;"><strong>Technologie</strong></td>
    <td style="width: 25%; height: 21px;"></td>
    <td style="width: 25%; height: 21px;"></td>
    <td style="width: 25%; height: 21px;"></td>
    </tr>
    <tr style="height: 21px;">
    <td style="width: 25%; height: 21px;">Tec1</td>
    <td style="width: 25%; height: 21px;">  <input name="Technoloie[]" type="checkbox" value="1" /> </td>
    <td style="width: 25%; height: 21px;">Tec2</td>
    <td style="width: 25%; height: 21px;"><input name="Technoloie[]" type="checkbox" value="1" /></td>
    </tr>
    <tr style="height: 21px;">
    <td style="width: 25%; height: 21px;">Tec3</td>
    <td style="width: 25%; height: 21px;">  <input name="Technoloie[]" type="checkbox" value="1" /> </td>
    <td style="width: 25%; height: 21px;"Tec4</td>
    <td style="width: 25%; height: 21px;">  <input name="Technoloie[]" type="checkbox" value="1" /> </td>
    </tr>
    <tr style="height: 21px;">
    <td style="width: 25%; height: 21px;">Tec5</td>
    <td style="width: 25%; height: 21px;">  <input name="Technoloie[]" type="checkbox" value="1" /> </td>
    <td style="width: 25%; height: 21px;">Tec6</td>
    <td style="width: 25%; height: 21px;">  <input name="Technoloie[]" type="checkbox" value="1" /> </td>
    </tr>
    <tr style="height: 21px;">
    <td style="width: 25%; height: 21px;"></td>
    <td style="width: 25%; height: 21px;">Tec7</td>
    <td style="width: 25%; height: 21px;">  <input name="Technoloie[]" type="checkbox" value="1" /> </td>
   <td style="width: 25%; height: 21px;">Tec8</td>
    <td style="width: 25%; height: 21px;">  <input name="Technoloie[]" type="checkbox" value="2" /> </td>

    </tr>
    </tbody>
    </table>

这是此复选框的 SQL 表：

+--------+------+------+------+------+------+------+------+------+------+
| Tec_ID | Tec1 | Tec2 | Tec3 | Tec4 | Tec5 | Tec6 | Tec7 | Tec8 |RanNr |
+--------+------+------+------+------+------+------+------+------+------+
|      1 |    1 |    0 |    0 |    0 |    1 |    0 |    0 |    0 | 1353 |
|      2 |    1 |    0 |    0 |    0 |    0 |    1 |    0 |    0 | 0000 |
|      3 |    1 |    0 |    0 |    0 |    0 |    0 |    1 |    1 | 1353 |
|      4 |    1 |    1 |    1 |    0 |    1 |    0 |    0 |    0 | 1123 |
|      5 |    1 |    1 |    1 |    0 |    0 |    1 |    0 |    2 | 1353 |
|      6 |    1 |    1 |    1 |    0 |    0 |    0 |    1 |    2 | 1353 |
|      7 |    0 |    0 |    0 |    1 |    0 |    0 |    0 |    1 | 1993 |
|      8 |    0 |    1 |    1 |    0 |    1 |    0 |    0 |    0 | 1123 |
|      9 |    0 |    1 |    1 |    0 |    0 |    1 |    0 |    0 | 1353 |
|     10 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    2 | 1366 |
+--------+------+------+------+------+------+------+------+------+------+

您已经看到如果我检查Tec1and Tec5，我想得到Tec_ID 1，所以我需要一个组合复选框选择来获得正确的ID，我想将此主键作为外键插入到另一个表中以处理id其他功能。

但是 atm 我不知道如何在 MSSQL 和 PHP 代码中处理这个问题？有人可以帮忙吗？

原表

FlashID SAP COB SMT BGA TSOP    LGA
1   102292  0   1   0   2   0
3   102293  0   1   0   2   0
4   102294  0   1   0   2   0
5   102296  0   1   0   0   1
6   102412  0   1   0   1   0
7   102413  0   1   0   1   0
8   102414  0   1   0   1   0
9   102651  0   1   0   2   0
10  102652  0   1   0   2   0
11  102664  0   1   0   2   0

标签： phphtmlsql-servercheckbox

解决方案

解决方案：

带有一些注释的简单示例：

表（T-SQL 脚本）：

CREATE TABLE [dbo].[TechnoloieTable] (
    Tec_ID int,
    Tec1 int,
    Tec2 int,
    Tec3 int,
    Tec4 int,
    Tec5 int,
    Tec6 int,
    Tec7 int,
    Tec8 int
)

INSERT INTO [dbo].[TechnoloieTable] 
    (Tec_ID, Tec1, Tec2, Tec3, Tec4, Tec5, Tec6, Tec7, Tec8)
VALUES
    (1, 1, 0, 0, 0, 1, 0, 0, 0),
    (2, 1, 0, 0, 0, 0, 1, 0, 0),
    (3, 1, 0, 0, 0, 0, 0, 1, 1),
    (4, 1, 1, 1, 0, 1, 0, 0, 0),
    (5, 1, 1, 1, 0, 0, 1, 0, 2),
    (6, 1, 1, 1, 0, 0, 0, 1, 2),
    (7, 0, 0, 0, 1, 0, 0, 0, 1),
    (8, 0, 1, 1, 0, 1, 0, 0, 0),
    (9, 0, 1, 1, 0, 0, 1, 0, 0),
    (10, 0, 0, 0, 0, 0, 0, 0, 2)

HTML（复选框表.php）：

<html>
<body>

<form action="checkbox-table-submit.php" method="post">

    <table style="border-collapse: collapse; width: 100%;" border="1">
    <tbody>
    <tr style="height: 21px;">
    <td style="width: 25%; height: 21px;"><strong>Technologie</strong></td>
    <td style="width: 25%; height: 21px;"></td>
    <td style="width: 25%; height: 21px;"></td>
    <td style="width: 25%; height: 21px;"></td>
    </tr>
    <tr style="height: 21px;">
    <td style="width: 25%; height: 21px;">Tec1</td>
    <td style="width: 25%; height: 21px;"><input name="Technoloie[Tec1]" type="checkbox" value="1"/></td>
    <td style="width: 25%; height: 21px;">Tec2</td>
    <td style="width: 25%; height: 21px;"><input name="Technoloie[Tec2]" type="checkbox" value="1"/></td>
    </tr>
    <tr style="height: 21px;">
    <td style="width: 25%; height: 21px;">Tec3</td>
    <td style="width: 25%; height: 21px;"><input name="Technoloie[Tec3]" type="checkbox" value="1"/></td>
    <td style="width: 25%; height: 21px;">Tec4</td>
    <td style="width: 25%; height: 21px;"><input name="Technoloie[Tec4]" type="checkbox" value="1"/></td>
    </tr>
    <tr style="height: 21px;">
    <td style="width: 25%; height: 21px;">Tec5</td>
    <td style="width: 25%; height: 21px;"><input name="Technoloie[Tec5]" type="checkbox" value="1"/></td>
    <td style="width: 25%; height: 21px;">Tec6</td>
    <td style="width: 25%; height: 21px;"><input name="Technoloie[Tec6]" type="checkbox" value="1"/></td>
    </tr>
    <tr style="height: 21px;">
    <td style="width: 25%; height: 21px;">Tec7</td>
    <td style="width: 25%; height: 21px;"><input name="Technoloie[Tec7]" type="checkbox" value="1"/></td>
    <td style="width: 25%; height: 21px;">Tec8</td>
    <td style="width: 25%; height: 21px;"><input name="Technoloie[Tec8]" type="checkbox" value="2"/></td>
    </tr>
    </tbody>
    </table>

    <input type="submit">
</form>

</body>
</html>

PHP（复选框表提交.php）：

<?php
// Selection
$selection = array();

// Form selection
if (isset($_POST['Technoloie'])) {
    foreach($_POST['Technoloie'] as $key => $value) {
        $selection[$key] = $value;
    }   
}   
if (empty($selection)) {
    echo 'Make a selection.';
    exit;
}   

// Statement generation.
$sql = "SELECT Tec_ID FROM [dbo].[TechnoloieTable] WHERE ";
foreach ($selection as $field => $value) {
    $sql .= "(".$field."=".$value.")AND";
}   
$sql = substr($sql, 0, strlen($sql)-3);

// Connection with SQLSRV
$server   = 'server\instance,port';
$database = 'database';
$username = 'username';
$password = 'password';
$cinfo = array(
    "Database" => $database,
    "UID" => $username,
    "PWD" => $password
);
$conn = sqlsrv_connect($server, $cinfo);
if ($conn === false) {
    echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
    exit;
}
$stmt = sqlsrv_query($conn, $sql);  
if ($stmt === false) {
    echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
    exit;
}
$id = 0;
while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
    $id = $row['Tec_ID'];
}
sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);

// Echo ID
echo 'ID: '.$id;
?>

更新：

如果您使用 PDO，请尝试使用以下命令执行您的语句：

// Connection with PDO_SQLSRV
$server   = 'server\instance,port';
$database = 'database';
$username = 'username';
$password = 'password';
try {
    $conn = new PDO("sqlsrv:server=$server;Database=$database", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    die("Error connecting to SQL Server".$e->getMessage());
}
try {
    $stmt = $conn->query($sql);
    $id = 0;
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $id = $row['Tec_ID'];
    }   
} catch(PDOException $e) {
    die("Error executing query".$e->getMessage());
}
$stmt = null;
$conn = null;

推荐阅读