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

Question

我在这个例子中得到了 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 |
+--------+------+------+------+------+------+------+------+------+------+

你已经看到了，如果我选中 Tec1 和 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

Answer 1

解决方案:

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

• 您的 SQL 表没有所有组合，因此 Tec_ID 可能未定义
• 示例使用 PHP Driver for SQL Server

表(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 (checkbox-table.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 (checkbox-table-submit.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;