Inserting form array data into MySQL with PHP

Question

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

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

mysql_* has been *deprecated*. Please switch to either mysqli_* or PDO. — christopher, Apr 18 '13 at 17:17
If you don't put indexes in your field names, you're gonna have a bad time. `` — Sammitch, Apr 18 '13 at 17:24

score 0 · Accepted Answer · edited May 23 '17 at 12:04

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

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

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

PDO is a lot simpler to use and understand IMO

UPDATE:

tutorials on PDO

UPDATE #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.

I like the looks of this, thank you. Any idea where I would start to process the arrays of each day into seperate rows? — Nolan Robinson, Apr 18 '13 at 17:58
If i understand you, each time you insert that query into your database, that is it own separate row. You can then write some php code to seperate SELECT statements by day, month, employee. Essentially then you have your database constantly adding rows and then parsing the data through php and doing arithmetic server side rather than DB side. — Dan, Apr 18 '13 at 18:05
Sorry, my post wasn't very clear. I want the user to be able to fill out the form, and add additional days if needed. Here's an example of the form: http://jsfiddle.net/BP76d/ from there, I wanted to store the data in an array and insert each day into the table as a new row containing the employees name, date, and hours. — Nolan Robinson, Apr 18 '13 at 18:26
I believe I can achieve my goal with something like this. Thank you sir. — Nolan Robinson, Apr 18 '13 at 19:14

Inserting form array data into MySQL with PHP

1 Answers1

Linked