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

Question

I got for this example 7 checkboxes:

    <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>

Here is the SQL Table for this Checkbox:

+--------+------+------+------+------+------+------+------+------+------+
| 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 |
+--------+------+------+------+------+------+------+------+------+------+

You see already if I check Tec1 and Tec5, I want to get Tec_ID 1, so I need a combined checkbox select to get the right ID and I want to Insert this Primary Key as an foreign key into a other table to handle with the id in further functions.

But atm I have no idea, how I can handle this in MSSQL and PHP Code? Can someone help?

Original table

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

Solution:

Simple example with some notes:

• your SQL table doesn't have all combinations, so it is possibe Tec_ID to be undefined
• example uses PHP Driver for SQL Server

Table (T-SQL script):

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;
?>

Update:

If you use PDO, then try to execute your statement with this:

// 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;