How to build a dynamic MySQL INSERT statement with

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 (??)";  // ????

标签： php mysql post show implode

2条回答

闹够了就滚

2楼-- · 2019-07-19 18:15

<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();

}  
?>

0人赞添加讨论(0) 举报

Melony?

3楼-- · 2019-07-19 18:33

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.

0人赞添加讨论(0) 举报

How to build a dynamic MySQL INSERT statement with

采纳回答

编辑标签

举报内容

检举类型

检举原因

检举说明(必填)

打开微信“扫一扫”，打开网页后点击屏幕右上角分享按钮

付费偷看金额在0.1-10元之间