Matthew Hait Matthew Hait - 3 months ago 17
PHP Question

Efficient way to insert 340 HTML Fields into a MS SQL Database using PHP

I have a large HTML form containing 340 fields that I need entered into a Microsoft SQL Server 2008 R2. I'm trying to find a way to enter the data without meticulously writing out each variable in the PHP code (in $sql & $params). Maybe it's possible if the columns and variables had the same name.Here's a smaller version of the HTML form and the entire PHP coode. The SQL table currently only has the columns "Date" & "PartNumber".

HTML code:

<!DOCTYPE html>
<html>

<head>
<title>Form</title>
</head>

<body>
<form id="test" action="http://10.0.0.252/test.php" method="post" accept-charset="ISO-8859-1">
<input type="text" tabindex="1" id="form283_1" value="" data-objref="61 0 R" title="Date:" name="Date" />
<input type="text" tabindex="4" id="form339_1" value="" data-objref="62 0 R" title="PartNo." name="PartNo" />
<input type="submit" value="Submit" id="form366_1">
</form>

</body>

</html>


PHP code:

<?php

$post = file_get_contents('php://input');
$serverName = "FILESERV1\SQLEXPRESS";
$connectionInfo = array(
"UID" => "user",
"PWD" => "Password",
"Database" => "ipadforms"
);

$conn = sqlsrv_connect($serverName, $connectionInfo);

if ($conn === false) die("<pre>".print_r(sqlsrv_errors(), true));
echo "Successfully connected!";

if(empty($_POST) === false && empty($errors)=== true)
{
$sql = "INSERT INTO dbo.MF001 (Date,PartNumber) VALUES (?,?)";
$params = array($post);

$stmt = sqlsrv_query( $conn, $sql , $params);
if( $stmt === false ) {
die( print_r( sqlsrv_errors(), true));
}
sqlsrv_close($conn);
}

Answer

Have an array of form field names -> table field names, then iterate it?

$fields = array('formfield1' => 'dbfield1', 'formfield2' => 'dbfield2', etc...);

foreach($fields as $formfield => $dbfield) {
    $sql = "INSERT INTO dbo.FM001 ($dbfield) VALUES (?)";
    $stmt = $dbh->prepare($sql);
    $stmt->execute(array($_POST[$formfield));
}

Note that the $dbfield has to be interpolated into the query string directly, since placeholders can only represent VALUES, not sql keywords or identifiers.

Comments