使用PHP将表单数组数据插入MySQL [英] Inserting form array data into MySQL with PHP

问题描述

我对PHP和MySQL都相当陌生，希望对此有所帮助。

I am fairly new to both PHP and MySQL and would appreciate some help with this one.

我要实现的目标：将时间表存储在MySQL中表格使用下面的表格，该表格应将每天的数据发布到单独的行中，同时保持输入的每一天相同的员工姓名。用户可以选择将额外的天数添加到表单中（最多7天）。我已经测试了所有内容而无需使用数组，并且能够将数据存储到表中而没有任何问题。

What I am trying to achieve: store a time sheet into a MySQL table using the form below which should post each day's data into a separate row while keeping the same employee name for each day entered. The user has the option to add additional days to the form -- a max of 7. I've tested everything without the use of arrays and am able to store data to the table without any problems.

HTML：

<form id="timesheet" method="post" action="timekeep.php">
        <fieldset>
        <h1>Employee Info</h1>
        <ul>
            <li>
                <label>First Name:</label>
                <input name="firstname" type="text">
            </li>
            <li>
                <label>Last Name:</label>
                <input name="lastname" type="text">
            </li>
        </ul>
        </fieldset>
        <fieldset>
        <h1>Time Info</h1>
            <h3>Day: 1</h3>
            <ul>
                <li>
                    <input name="date[]" type="text">
                </li>
                <li>
                    <input name="straighthours[]" type="number">
                </li>
                <li>
                    <input name="overtimehours[]" type="number">
                </li>
                <li>
                    <input name="premiumhours[]" type="number">
                </li>
                <li>
                    <input name="perdiem[]" type="number">
                </li>
            </ul>
            <h3>Day: 2</h3>
            <ul>
                <li>
                    <input name="date[]" type="text">
                </li>
                <li>
                    <input name="straighthours[]" type="number">
                </li>
                <li>
                    <input name="overtimehours[]" type="number">
                </li>
                <li>
                    <input name="premiumhours[]" type="number">
                </li>
                <li>
                    <input name="perdiem[]" type="number">
                </li>
            </ul>

        </fieldset>

        <input id="submit" name="submit-time" type="submit" value="Submit Time">

    </form>

PHP：

$sql_connection = mysql_connect($dbhost, $dbuser, $dbpass) OR DIE ("Unable to connect to database! Please try again later.");

mysql_select_db($dbuser, $sql_connection);

$sql = "INSERT INTO table (
            Date,
            FirstName,
            LastName,
            StraightHours,
            OvertimeHours,
            PremiumHours,
            TotalHours,
            PerDiem
        )
        VALUES (".
            PrepSQL($date) . ", " .
            PrepSQL($firstName) . ", " .
            PrepSQL($lastName) . ", " .
            PrepSQL($straightHours) . ", " .
            PrepSQL($overtimeHours) . ", " .
            PrepSQL($premiumHours) . ", " .
            PrepSQL($totalHours) . ", " .
            PrepSQL($perDiem) . "
        )";

mysql_query($sql, $sql_connection);

mysql_close($sql_connection);

function PrepSQL($value)
{

    if(get_magic_quotes_gpc())
    {
        $value = stripslashes($value);
    }

    $value = "'" . mysql_real_escape_string($value) . "'";

    return($value);
}

推荐答案

使用PDO对象会使它变更容易的是，mysql_仍然是旧版：

Using PDO object would make this easier, mysql_ is legacy anyway:

$db = new PDO($hostname,$username,$password);


$qry = "INSERT INTO table (
            Date,
            FirstName,
            LastName,
            StraightHours,
            OvertimeHours,
            PremiumHours,
            TotalHours,
            PerDiem
        )
        VALUES (:date, :firstname, :lastname, :straighthours, :overtimehours, :premiumhours, :totalhours, :perdiem)"; // colon variables will be bound to actual variable

$statement = $db->prepare($query); //prevents injection

// binds variables to place holder in query
$statement->bindValue(':firstname', $firstname);
$statement->bindValue(':lastname', $lastname);
$statement->bindValue(':straighthours', $straighthours);
$statement->bindValue(':overtimehours', $overtimehours);
$statement->bindValue(':premiumhours', $premiumhours);
$statement->bindValue(':totalhours', $totalhours);
$statement->bindValue(':perdiem', $perdiem);

$statement->execute();
$statement->closeCursor();

您可以使用php做进一步的输入检查，然后再通过以下方式将任何内容传递给sql：

you can do further input checking with php before passing anything to the sql via:

trim(strip_tags(htmlentities($firstname)));

PDO易于使用和理解IMO

PDO is a lot simpler to use and understand IMO

更新：

PDO教程

更新＃2：

要添加每天可以使用数组功能：

For added functionality with arrays per day you can do:

<input type="text" name="firstname1">

// do this for all fields then
$workingday1 = array();
$workingday1['firstname'] = $_GET['firstname1'];
// etc. for all the other fields

然后，您可以通过以下方式访问该字段：

Then you can access the field by:

$workingday1 = $_GET['workingDay1']; // or post or however you want to pass it
$firstname = $workingday['firstname'];

之后，您可以根据需要修剪数据库。您可以有一个包含所有值的表格，然后编辑选择内容以按员工，按天或按周显示。您还可以为每个员工创建一个表，然后从这些表中获取并随意显示数据。

After that you can prune your database however you like. You can have a single table with all the values and edit your selects to display by employee or day or w/e. You can also have a table for each employee and then grab from those tables and display the data how ever you like.

这篇关于使用PHP将表单数组数据插入MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！