How to build a dynamic MySQL INSERT statement with PHP

Question

Hello

This part of a form is showing columns names from mysql table (names of applications installed on a computer) and creating a form with YES/NO option or input type="text" box for additional privileges to a application..

How can I insert it back to a mysql table using POST and mysql_query INSERT INTO?????
Quantity of columns is changing because there is another form for adding applications with/without privileges..

<tr bgcolor=#ddddff>';

//mysql_query for getting columns names
$result = mysql_query("SHOW COLUMNS FROM employees") or   die(mysql_error());   
while ($row = mysql_fetch_array($result))
{
    //exclude these columns bcs these are in other part of form
    if($row[0] == 'id' || $row[0] == 'nameandsurname' || $row[0] == 'department' 
            || $row[0] == 'phone' || $row[0] == 'computer'  || $row[0] == 'data') 
        continue;
    echo '<td bgcolor=#ddddff>'.$row[0].'<br />';

    if (stripos($row[0], "privileges") !== false) {
        echo '<td bgcolor=#ddddff><p><a class=hint href=#>
            <input type="text" name="'.$row[0].'">
            <span>Privileges like "occupation" or "like  someone"</span></a></p></td></tr>';
    }
    else
    {
        echo '<td bgcolor=#ddddff align=center><select name="'.$row[0].'">
            <option value = "No">No
            <option value = "Yes">Yes
            </td>
            </tr>';
    }
}

trim($_POST); // ????

$query = "INSERT INTO 'employees' VALUES (??)";  // ????

where are you using html form tag? without that submitting form post can't be created and if you want to use on click event then use AJAX — Vineet1982
– Vineet1982, Commented Jul 31, 2013 at 11:27

mzalazar · Accepted Answer · 2017-09-14 21:04:55Z

6

Because you're not inserting ALL columns, you need to dynamically build an insert statement that will specify the columns you're inserting into.

First, create an array of the columns you want to use. Use this both to generate your form and to retrieve the values

$exclude = array("id", "nameandsurname", "departument", "phone", "computer", "date");
$result = mysql_query("SHOW COLUMNS FROM employees") or   die(mysql_error());
$columns = array();
while ($row = mysql_fetch_array($result)) {
    if (!in_array($row[0], $exclude) {
        $columns[] = $row[0];
    }
}

Render your form from the $columns array:

foreach ($columns as $column) {
    echo '<tr><td bgcolor="#ddddff">'.$column.'<br />';
    if (stripos($column, "privileges") !== false) {
        echo '<p><a class="hint" href="#">
                <input type="text" name="'.$column.'">
                <span>Privileges like "occupation" or "like  someone"</span></a>';
    } else {
        echo '<select name="'.$column.'">
                <option value = "No">No
                <option value = "Yes">Yes
              </select>';
    }
    echo '</td></tr>';
}

Then, dynamically build your INSERT string from the posted values for those columns. Be sure to protect against SQL injection:

$keys = array();
$values = array();
foreach ($columns as $column) {
    $value = trim($_POST[$column]);
    $value = mysql_real_escape_string($value);
    $keys[] = "`{$column}`";
    $values[] = "'{$value}'";
}
$query = "INSERT INTO 'employees' (" . implode(",", $keys) . ") 
          VALUES (" . implode(",", $values) . ");";

Note: this will work better if you select from INFORMATION_SCHEMA.COLUMNS so that you can know the type of column you're inserting into. That way, you won't have to quote everything.

edited Sep 14, 2017 at 21:04

mzalazar

6,6363 gold badges39 silver badges33 bronze badges

answered Jul 31, 2013 at 11:28

Steven Moseley

16.4k4 gold badges43 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ja͢ck Over a year ago

mysql_real_escape_string() exists too :)

Steven Moseley Over a year ago

@Jack - yes, but it requires a mysql_connection, and the mysql library is deprecated in later versions of PHP

Steven Moseley Over a year ago

The reason I showed the line commented out is because if he tries out my code without an active connection, that line would fail.

Steven Moseley Over a year ago

@Jack - Derp! Nice catch. :)

sam · Accepted Answer · 2015-07-02 12:44:22Z

<html>
<body>
<form action="dynamicinsert.php" method="POST" >
user name:<br>
<input type="text" id="username" name="username">
<br><br>
first name:<br>
<input type="text" id="firstname" name="firstname">
<br><br>
password:<br>
<input type="password" id="password" name="password">
<br><br>
<input type="submit" name="submit" value="add" />
</form>
</body>
</html>

<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "you_DB_name";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

function insertqueryfunction($dbfield,$table) {
   $count = 0;
   $fields = '';

   foreach($dbfield as $col => $val) {
      if ($count++ != 0) $fields .= ', ';
      $col = addslashes($col);
      $val = addslashes($val);
      $fields .= "`$col` = '$val'";
   }
   $query = "INSERT INTO $table SET $fields;";
   return $query;

} 

if(isset($_POST['submit']))
{

    // Report all errors
error_reporting(E_ALL);

    // Same as error_reporting(E_ALL);
     ini_set("error_reporting", E_ALL);
     $username_form = $_POST['username'];
     $firstname_form = $_POST['firstname'];
     $password_form = $_POST['password'];
     $you_table_name = 'you_table_name';

     $dbfield = array("username"=>$username_form, "firstname"=>$firstname_form,"password"=>$password_form);

     $querytest =  insertqueryfunction($dbfield,'you_table_name');

     if ($conn->query($querytest) === TRUE) {
    echo "New record created successfully";
    } else {
    echo "Error: " . $sql . "<br>" . $conn->error;
    }

    $conn->close();

}  
?>

Hi sam, welcome to SO and thank you for signing up to answer this question. To make your answer even better, it would be good to add some explanations as to why your code solves the OP's problem.

Collectives™ on Stack Overflow

How to build a dynamic MySQL INSERT statement with PHP

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related