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
– christopher, Commented Apr 18, 2013 at 17:17
If you don't put indexes in your field names, you're gonna have a bad time. <input name="date[1]" type="text"> — Sammitch
– Sammitch, Commented Apr 18, 2013 at 17:24

Community · Accepted Answer · 2017-05-23 12:04:57Z

0

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.

edited May 23, 2017 at 12:04

CommunityBot

11 silver badge

answered Apr 18, 2013 at 17:34

Dan

3,8154 gold badges32 silver badges40 bronze badges

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

4 Comments

Nolan Robinson Over a year ago

I like the looks of this, thank you. Any idea where I would start to process the arrays of each day into seperate rows?

Dan Over a year ago

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.

Nolan Robinson Over a year ago

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: 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 Over a year ago

I believe I can achieve my goal with something like this. Thank you sir.

Collectives™ on Stack Overflow

Inserting form array data into MySQL with PHP

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related