#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    39
    Rep Power
    10

    Inserting multiple rows into MySQL from one form


    Hello,

    I have made an attendance register that looks like this:
    Screen shot 2014-08-22 at 12.37.18.png
    What I am trying to achieve is when the submit button is clicked all the attendance data for each person is inserted as a row representing that person (so according to the picture above there should be 6 rows inserted with each row having the date, serviceuserid, hours, overtime and rateid - service user names and rates are stored in 2 separate tables)

    In the code for the form to be submitted I have assigned each row a unique number using a loop which increments by 1 at the end so will scale to how ever many names are put into the register. I know this part works as I echoed the variable at the end of the loop and each row displayed a unique number

    In the code for the part that saves all the values into MySQL I have looped the SQL insert statement for as long as there are rows from the previously submitted form. I have modified this code a couple of times and get different problems

    when I store the POST values in other variables i.e. $serviceuserid = $_POST['serviceuserid.$i.'];
    I get: "Query was empty"

    And when I just put the $POST variables straight into the SQL insert line I get:
    Parse error: syntax error, unexpected '.', expecting ']'

    Form code:
    PHP Code:
    <html>
    <head>
    <title>Change Service User Details</title>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    </head>
    <body>
    <table>
    <tr>
    <td align="center">Change Service User Details</td>
    </tr>
    <tr>
    <td>
    <table border="0">
    <?php
    $n
    =1;
    include 
    'dbconnect.php';//database connection
    $order "SELECT * FROM serviceusers ORDER BY surname ASC , firstname ASC";
    $result mysql_query($order);
    $order2 "SELECT * FROM rates";
    Echo 
    "<form name='attendanceregister' method='post' action='newregister.php'>";
    echo (
    "<tr><td>Date:</td>");
    echo (
    "<td><select name='day'>;
    <option selected='selected' disabled='disabled'>Day</option>
    <option value='1'>1</option>
    <option value='2'>2</option>
    <option value='3'>3</option>
    <option value='4'>4</option>
    <option value='5'>5</option>
    <option value='6'>6</option>
    <option value='7'>7</option>
    <option value='8'>8</option>
    <option value='9'>9</option>
    <option value='10'>10</option>
    <option value='11'>11</option>
    <option value='12'>12</option>
    <option value='13'>13</option>
    <option value='14'>14</option>
    <option value='15'>15</option>
    <option value='16'>16</option>
    <option value='17'>17</option>
    <option value='18'>18</option>
    <option value='19'>19</option>
    <option value='20'>21</option>
    <option value='22'>22</option>
    <option value='23'>23</option>
    <option value='24'>24</option>
    <option value='25'>25</option>
    <option value='26'>26</option>
    <option value='27'>27</option>
    <option value='29'>29</option>
    <option value='30'>30</option>
    <option value='31'>31</option>
    </select></td>"
    );
    echo (
    "<td><select name='month'>;
    <option selected='selected' disabled='disabled'>Month</option>
    <option value='1'>January</option>
    <option value='2'>February</option>
    <option value='3'>March</option>
    <option value='4'>April</option>
    <option value='5'>May</option>
    <option value='6'>June</option>
    <option value='7'>July</option>
    <option value='8'>August</option>
    <option value='9'>September</option>
    <option value='10'>October</option>
    <option value='11'>November</option>
    <option value='12'>December</option>
    </select></td>"
    );
    $year1 date('Y')-1;
    $year2 date('Y')+1;
    $thisyear date('Y');
    echo (
    "<td><select name='year'>;
    <option value=
    $year1>$year1</option>
    <option selected='selected' value=
    $thisyear>$thisyear</option>
    <option value=
    $year2>$year2</option>
    </select></td>"
    );
    echo (
    "<tr>
    <th>Name</th>
    <th>Hours</th>
    <th>Overtime</th>
    <th>Rate</th>
    </tr>"
    );

    while (
    $row=mysql_fetch_array($result)){

    echo (
    "<tr><td><input type='hidden' name='serviceuserid$n' value='$row[serviceuserid]'>$row[firstname] $row[surname]</td>");

    echo (
    "<td><select name='hours$n'>;
    <option selected='selected' disabled='disabled'>Hours</option>
    <option value='1'>1</option>
    <option value='1.5'>1.5</option>
    <option value='2'>2</option>
    <option value='2.5'>2.5</option>
    <option value='3'>3</option>
    <option value='3.5'>3.5</option>
    <option value='4'>4</option>
    <option value='4.5'>4.5</option>
    <option value='5'>5</option>
    <option value='5.5'>5.5</option>
    <option value='6'>6</option>
    <option value='6.5'>6.5</option>
    <option value='7'>7</option>
    <option value='7.5'>7.5</option>
    <option value='8'>8</option>
    <option value='8.5'>8.5</option>
    <option value='9'>9</option>
    <option value='9.5'>9.5</option>
    <option value='10'>10</option>
    <option value='10.5'>10.5</option>
    <option value='11'>11</option>
    <option value='11.5'>11.5</option>
    <option value='12'>12</option>
    <option value='12.5'>12.5</option>
    <option value='13'>13</option>
    <option value='13.5'>13.5</option>
    <option value='14'>14</option>
    <option value='14.5'>14.5</option>
    <option value='15'>15</option>
    <option value='15.5'>15.5</option>
    <option value='16'>16</option>
    <option value='16.5'>16.5</option>
    <option value='17'>17</option>
    <option value='17.5'>17.5</option>
    <option value='18'>18</option>
    <option value='18.5'>18.5</option>
    <option value='19'>19</option>
    <option value='19.5'>19.5</option>
    <option value='20'>20</option>
    <option value='20.5'>20.5</option>
    <option value='21'>21</option>
    <option value='21.5'>21.5</option>
    <option value='22'>22</option>
    <option value='22.5'>22.5</option>
    <option value='23'>23</option>
    <option value='23.5'>23.5</option>
    <option value='24'>24</option>
    <option value='24.5'>24.5</option>
    </select></td>"
    );

    echo (
    "<td><select name='overtime$n'>;
    <option selected='selected' value='0'>0</option>
    <option value='1'>1</option>
    <option value='1.5'>1.5</option>
    <option value='2'>2</option>
    <option value='2.5'>2.5</option>
    <option value='3'>3</option>
    <option value='3.5'>3.5</option>
    <option value='4'>4</option>
    <option value='4.5'>4.5</option>
    <option value='5'>5</option>
    <option value='5.5'>5.5</option>
    <option value='6'>6</option>
    <option value='6.5'>6.5</option>
    <option value='7'>7</option>
    <option value='7.5'>7.5</option>
    <option value='8'>8</option>
    <option value='8.5'>8.5</option>
    <option value='9'>9</option>
    <option value='9.5'>9.5</option>
    <option value='10'>10</option>
    <option value='10.5'>10.5</option>
    <option value='11'>11</option>
    <option value='11.5'>11.5</option>
    <option value='12'>12</option>
    <option value='12.5'>12.5</option>
    <option value='13'>13</option>
    <option value='13.5'>13.5</option>
    <option value='14'>14</option>
    <option value='14.5'>14.5</option>
    <option value='15'>15</option>
    <option value='15.5'>15.5</option>
    <option value='16'>16</option>
    <option value='16.5'>16.5</option>
    <option value='17'>17</option>
    <option value='17.5'>17.5</option>
    <option value='18'>18</option>
    <option value='18.5'>18.5</option>
    <option value='19'>19</option>
    <option value='19.5'>19.5</option>
    <option value='20'>20</option>
    <option value='20.5'>20.5</option>
    <option value='21'>21</option>
    <option value='21.5'>21.5</option>
    <option value='22'>22</option>
    <option value='22.5'>22.5</option>
    <option value='23'>23</option>
    <option value='23.5'>23.5</option>
    <option value='24'>24</option>
    <option value='24.5'>24.5</option>
    </select></td>"
    );
    echo (
    "<td><select name='rate$n'>");
    $result2 mysql_query($order2);
    while (
    $row2=mysql_fetch_array($result2)){
    if (
    $row2['ratename'] == "onetoone"){
    $wow2 "One To One";
    }elseif (
    $row2['ratename'] == "group"){
    $wow2 "Group";
    }elseif (
    $row2['ratename'] == "group_onetoone"){
    $wow2 "Group One To One";
    }

    echo 
    "<option name='rateid' value='$row2[rateid]'> $row2[ratename] </option>";


    }
    echo(
    "</select></td>");
    $n++;
    }
    echo 
    "<input type='hidden' name='rowcount' value='$n'>";
    Echo 
    "<input type='submit' value='Submit'></form>";

    ?>
    </table>
    </td>
    </tr>
    </table>
    </body>
    </html>
    SQL insert code:
    PHP Code:
    <?php
    $_POST
    ['year'] && $_POST['month']&& $_POST['day'] && $_POST['rowcount'];
    //$_POST["firstname"] && $_POST["surname"]
    //$tablename = $_POST["day"].$_POST["month"].$_POST["year"];
    $submitdate $_POST['year'] . "-" $_POST['month'] . "-" $_POST['day']; 
    $g strtotime($submitdate);
    if(!
    $g) { 
        print 
    "There was a problem with this register please go back and try again";
    }
    else{
    $the_date date('Y-m-d'$g);

    include 
    'dbconnect.php';
    $i 1;
    while (
    $i <= $rowcount){
    $sql="insert into attendance (attendancedate,serviceuserid,hours,overtime,rateid)values('$the_date','$_POST[serviceuserid.$i.]','$_POST[hours.$i.]','$_POST[overtime.$i.]','$_POST[rateid.$i.]')";
    $i++;
    }
    $result=mysql_query($sql,$conn) or die(mysql_error());
    print 
    "<h1>Register completed</h1>";

    print 
    "<a href='mainmenu.php'>back to main menu</a>";
    }
    ?>
    Thank you in advance for any help provided
    Last edited by smush; August 22nd, 2014 at 09:22 AM. Reason: Removed pastebin links
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jul 2003
    Posts
    4,473
    Rep Power
    653
    Do not use pastebin for your code. Post it here using [ PHP ] tags. That is what this forum is designed for. See the sticky at the top of this forum that says READ THIS BEFORE POSTING. Few, if any, will bother to go to a 3rd party site to review your code. In addition the builtin formatter makes your code easier to read.
    Last edited by gw1500se; August 22nd, 2014 at 10:48 AM.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    39
    Rep Power
    10
    Originally Posted by gw1500se
    Do not use pastebin for you code. Post it here using [ PHP ] tags. That is what this forum is designed for. See the sticky at the top of this forum that says READ THIS BEFORE POSTING. Few if any will bother to go to a 3rd party site to review your code. In addition the builtin formatter makes your code easier to read.
    Apologies, I didn't read that and used pastebin as thought my post would become too long and put people off from reading it. Will edit my post
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,122
    Rep Power
    539
    A couple of suggestions.

    First, you have WAY to much code. It makes troubleshooting and maintenance a nightmare. Instead of writing out all your options, figure a slick way to use a foreach loop or something to create them. EDIT. For your dates, maybe make an array with values January to December and a foreach loop, and for your two number selects, use a while loop to keep your values in range.

    Next, <select name='hours$n'> is wrong. PHP handles these by making the name equal to "hours[]". Use a foreach loop to create your rows this way.

    Code:
    <tr>
        <td>name1<input type="hidden" name="id[]" value="123"></td>
        <td><select name="hours[]"><option>xxx</option></select></td>
        <td><select name="overtime[]"><option>xxx</option></select></td>
        <td><select name="rate[]"><option>xxx</option></select></td>
    </tr>
    Then, before you start trying to enter your data into the DB, put exit('<pre>'.print_r($_POST,1).'</pre>'); in the top your form submitting script, and look at what you are getting.
    Last edited by NotionCommotion; August 22nd, 2014 at 09:33 AM.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jul 2003
    Posts
    4,473
    Rep Power
    653
    One other VERY, VERY, VERY important point. Take this page down immediately. You are using the deprecated (for more than a decade) MySQL extensions and your system is wide open to injection attack. Before putting this page back up, switch to PDO and prepared statements.

    Comments on this post

    • NotionCommotion agrees : Yes for PDO!
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    39
    Rep Power
    10
    Do I need to use PDO can't I just use MySQLi?
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jul 2003
    Posts
    4,473
    Rep Power
    653
    Programmers choice. I prefer PDO because of its better flexibility and usability. However, MySQLi is fine as long as you use prepared statements and validate the data.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.

IMN logo majestic logo threadwatch logo seochat tools logo