Add multiple combined PHP form data from multiple foreign MSSQL tables to one primary table

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

Zhorov · Accepted Answer

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;

Jonathan Clark · Answer

Firstly, your inputs would need to be an associative array because otherwise you won't know which value '1' relates to which 'Tec'.

<input type="checkbox" name="tec[Tec1]" value="1" />
<input type="checkbox" name="tec[Tec2]" value="1" />
<input type="checkbox" name="tec[Tec3]" value="1" />
...

Checkboxes only get posted when they are checked, so in PHP you can:

$sql = "SELECT * FROM tec WHERE 1=1 "; //WHERE 1=1 is a nice 'hack' so you can have *none* or many AND statements.
foreach($_POST['tec'] as $key => $value) {
    $sql .= "AND $key > 0 "; //Make sure to sanitise your data!!!
    //We can do this because checkboxes are only posted if they're ticked
}

$result = mysqli_query($link, $sql);

$rows = mysqli_fetch_all($result, MYSQLI_ASSOC);

if(count($rows) == 1) {
    $my_found_tec_id = $rows[0]['Tec_ID'];
}

//if count is not equal to 1, then do something else... question just talked about 1 match.

Add multiple combined PHP form data from multiple foreign MSSQL tables to one primary table

Tags:

html

checkbox

php

sql-server

Daniel

2 Answers

Zhorov

Jonathan Clark

Recent Activity

Donate For Us

Add multiple combined PHP form data from multiple foreign MSSQL tables to one primary table

Tags:

html

checkbox

php

sql-server

Daniel

2 Answers

Zhorov

Jonathan Clark

Related questions

Recent Activity

Donate For Us