insert multiple rows via a php array into mysql

Question

i am trying to build a precence report website on php, i am have built a form that generates the dates between the 21st of the selected month till the 20th of the next month. the data outputs as a table with number of rows equal to the days of the month. each row contains 8 prameters.

the problem is that I can't insert the data to the db.

i have made an array that contains all the parameters values and tryed to loop the mysql query.

this is the class i have written to insert the data `

public function Table($userid, $rdate, $rday, $rpresence, $rway, $rkm, $rexpence, $rfile, $report){

$query  = $this->dbh->prepare(\'INSERT INTO time (rdate, rday, rway, rkm, rexpence, rfile, rprecence, report) VALUES'
                    for ($i = 1; $i <= $countdate; $i++) {
                        $query.=($_POST['rdate'][$i]
                            .', '.$_POST['rday'][$i]
                            .', '.$_POST['rway'][$i]
                            .', '.$_POST['rkm'][$i]
                            .', '.$_POST['rexpence'][$i]
                            .', '.$_POST['rfile'][$i]
                            .', '.$_POST['rpresence'][$i]
                            .', '.$_POST['report'])\')';
                        if ($i>$countdate) {
                           $query .= ',';
                        }
                    }
                    }
`

this is the table code

<?php
//require_once 'inc.php';
?>



<?php
if(isset($_POST['submit'])){
$month = $_POST['month'];
$year = $_POST['year'];
}

if(isset($_POST['adken'])){
$month = $_POST['month'];
$year = $_POST['year'];
$userid = $_SESSION['id']; 
$report = $_POST['month']['year'];
$rdate = $_POST['rdate'][$i]; 
$rday = $_POST['rday'][$i];  
$rway = $_POST['rway'][$i];
$rkm = $_POST['rkm'][$i];
$rexpence = $_POST['rexpence'][$i];
$rfile = $_FILES['rfile']['name'][$i];
$rpresence = $_POST['rpresence'][$i];
echo var_dump($_POST);

$update = new User();
$update->Table($rdate, $rday, $rpresence, $rway, $rkm, $rexpence, $rfile, $report);

}



$this_year = date( "Y", strtotime($year)); //
$next_year = date( "Y", strtotime($year." +1 year")); //
$this_month = date( "m", strtotime($month)); // 
$next_month = date( "m", strtotime($month." +1 month")); // 
$previous_month = date('m')-1; // 
$this_day = date('D'); // 
$num_days = cal_days_in_month(CAL_GREGORIAN, $this_month, $this_year); //כ
$jan = date( "m", strtotime('January'));

?>



<html>

    <head>
    </head>

    <body>
         <form name="adken" action="" method="POST" enctype="multipart/form-data"  > 
            <table class="table table-striped table-bordered table-hover" id="dataTables-example">
                <thead>
                    <tr>
                        <th>date</th>
                        <th>day</th>
                        <th>presence</th>
                        <th>way</th>
                        <th>km</th>
                        <th>expence</th>
                        <th>add file</th>
                    </tr>
                </thead>
                <tbody>
                    <tr>    
                        <?php//  echo $this_month;?><br />
                        <?php//      echo $next_month;?><br />
                        <?php//     echo $_SESSION['id'];?>

                        <?php
                            if($month == 'December'){
                                for ($i=21; $i<=$num_days ; $i++)
                                    echo' 
                                        <td><input type="text" name="rdate['.$i.']" class="readonly" value="'.$i.'/'.$this_month.'/'.$this_year.'" readonly></td>
                                        <td><input type="text" name="rday['.$i.']" class="readonly" value="'.date('D').' " readonly></td>
                                        <td><input name="rpresence['.$i.']" type="checkbox" value="1"/></td>
                                        <td><textarea name="rway['.$i.']" cols="20" rows="2"></textarea></td>
                                        <td width="10%"><textarea name="rkm['.$i.']" cols="3" rows="2"></textarea></td>
                                        <td><textarea name="rexpence['.$i.']" cols="20" rows="2"></textarea></td>
                                        <td><input class="form-control" type="file" name="rfile['.$i.']" id="file" value="בחר קובץ"></td>
                                        <td class="hidden"><input type="text" name="report" value="'.$year.' '.$month.'" readonly></td> 
                    </tr>';
                                for ($i=1; $i<=20 ; $i++)
                                     echo '
                                        <td><input type="text" name="rdate['.$i.']" class="readonly" value="'.$i.'/'.$jan.'/'.$next_year.'" readonly></td>
                                        <td><input type="text" name="rday['.$i.']" class="readonly" value="'.date('D').'" readonly> </td>
                                        <td><input name="rpresence['.$i.']" type="checkbox" value="1"/></td>
                                        <td><textarea name="rway['.$i.']" cols="20" rows="2"></textarea></td>
                                        <td width="10%"><textarea name="rkm['.$i.']" cols="3" rows="2"></textarea></td>
                                        <td><textarea name="rexpence['.$i.']" cols="20" rows="2"></textarea></td>
                                        <td><input class="form-control" type="file" name="rfile['.$i.']" id="file" value="בחר קובץ"></td>
                                        <td class="hidden"><input type="text" name="report" value="'.$year.' '.$month.'" readonly></td>  
                    </tr>';

                            }else{
                                for ($i=21; $i<=$num_days ; $i++)
                                    echo '
                                        <td><input type="text" name="rdate['.$i.']" class="readonly" value="'.$i.'/'.$this_month.'/'.$this_year.'" readonly></td>
                                        <td><input type="text" name="rday['.$i.']" class="readonly" value="'.date('D').'" readonly> </td>
                                        <td><input name="rpresence['.$i.']" type="checkbox" value="1"/></td>
                                        <td><textarea name="rway['.$i.']" cols="20" rows="2"></textarea></td>
                                        <td width="10%"><textarea name="rkm['.$i.']" cols="3" rows="2"></textarea></td>
                                        <td><textarea name="rexpence['.$i.']" cols="20" rows="2"></textarea></td>
                                        <td><input class="form-control" type="file" name="rfile['.$i.']" id="file" value="בחר קובץ"></td>
                                        <td class="hidden"><input type="text" name="report" value="'.$year.' '.$month.'" readonly></td>   
                    </tr>';
                                for ($i=1; $i<=20 ; $i++)
                                     echo '
                                        <td><input type="text" name="rdate['.$i.']" class="readonly" value="'.$i.'/'.$next_month.'/'.$this_year.'" readonly></td>
                                        <td><input type="text" name="rday['.$i.']" class="readonly" value="'.date('D').' " readonly></td>
                                        <td><input name="rpresence['.$i.']" type="checkbox" value="1"/></td>
                                        <td><textarea name="rway['.$i.']" cols="20" rows="2"></textarea></td>
                                        <td width="10%"><textarea name="rkm['.$i.']" cols="3" rows="2"></textarea></td>
                                        <td><textarea name="rexpence['.$i.']" cols="20" rows="2"></textarea></td>
                                        <td><input class="form-control" type="file" name="rfile['.$i.']" id="file" value="בחר קובץ"></td>
                                        <td class="hidden"><input type="text" name="report" value="'.$year.' '.$month.'" readonly></td>
                    </tr>';             
                                } ?>    
                </tbody>
            </table>
                <p>
                    <input name="password" type="text" value="Password" />
                    <br />
                    <input type="submit" name="send" value="Send" />
                    <input type="submit" name="adken" value="Update" /> 
                </p>
        </form>
    </body>
</html>

this is my DB

 - Database: `mop`

-- Table structure for table `time`
--

CREATE TABLE IF NOT EXISTS `time` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) DEFAULT NULL,
  `rdate` date DEFAULT NULL,
  `rday` varchar(10) DEFAULT NULL,
  `rpresence` int(1) DEFAULT NULL,
  `rway` longtext,
  `rkm` int(10) DEFAULT NULL,
  `rexpence` int(255) DEFAULT NULL,
  `rfile` mediumtext,
  `report` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `username` (`userid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Constraints for dumped tables

-- Constraints for table `time`
--
ALTER TABLE `time`
  ADD CONSTRAINT `time_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `users` (`id`) ON DELETE CASCADE;

Please, any assistance even the smallest one will be great!

i am stuck on this for the last five days.

other suggestions to insert the data will be great.

Thanx Dave

POST UPDATE

This is the current Table method that should insert all the data to the database.

public function Table($rdate, $rday, $rpresence, $rway, $rkm, $rexpence, $rfile, $report){

            $query = "INSERT INTO time (rdate, rday, rway, rkm, rexpence, rfile, rpresence, report) VALUES ";
            $values = "";

                foreach($rdate AS $key => $value) {
                    if(strlen($values) > 0) $values .= ", ";
                    $values .= "('".$value."', '".$rdate[$key]."', '".$rday[$key]."', '".$rway[$key]."', ".$rkm[$key].", ".$rexpence[$key].", '".$rfile[$key]."', ".$rpresence[$key].", '".$report[$key]."')";
                }

                $query .= $values;

                    try {
                            $query = $this->dbh->prepare($query);
                            return $query->execute();
                        } catch(PDOException $e) {
                            return $e;
                        }

}

when I run the script I receive the following error:

Warning: Invalid argument supplied for foreach() in C:\wamp\www\mop\inc\userclass.php on line 128 .

line 128 is foreach($rdate AS $key => $value) {

You get better chance for assistance by asking a concrete programming question with a self-containing, focused example written from scratch to explain how far you managed to understand your issue instead of pasting your live-code asking for help with debugging. — hakre, May 03 '14 at 08:19
Assistance #1: You prepare a query without using parameters. That is doing it wrong. Suggesting to close against how to prevent SQL injection in PHP now as it seems to be the most important issue you have esp. considering the case you have published the code here. This needs fixing. — hakre, May 03 '14 at 08:20
possible duplicate of [How can I prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — hakre, May 03 '14 at 08:21

Tanatos · Accepted Answer · 2014-05-05T05:10:44.300

-1

Try this :

public function Table($userid, $rdate, $rday, $rpresence, $rway, $rkm, $rexpence, $rfile, $report) {
    $query = "INSERT INTO time (userid,rdate, rday, rway, rkm, rexpence, rfile, rpresence, report) VALUES ";
    $values = "";
    foreach($rdate AS $key => $value) {
        if(strlen($values) > 0) $values .= ", ";
        $values .= "(".$userid.",'".$value."', '".$rdate[$key]."', '".$rday[$key]."', ".$rkm[$key].", ".$rexpence[$key].", '".$rfile[$key]['name']."', ".$rpresence[$key].", '".$report[$key]."')";
    }

    $query .= $values;

    try {
        $query = $dbh->query($query);
        return $query->execute();
    } catch(PDOException $e) {
        return $e;
    }
}

Another suggestion is that you should do move_uploaded_file before actually putting in the array the path's for the files (put them in a folder accessible to you later on for use in your application, something like /files/file_name.extension).

One more thing, the variable $userid had no use in your function (the value was not used inside the insert) so I didn't use it either.

Also make sure you change the following :

if(isset($_POST['adken'])){
  $month = $_POST['month'];
  $year = $_POST['year'];
  $userid = $_SESSION['id']; 
  $report = $_POST['month']['year'];
  function formatter_date($date) {
     return date('Y-m-d', strtotime($date));
  }
  $rdate = array_map("formatter",$_POST['rdate']);
  $rday = $_POST['rday'];  
  $rway = $_POST['rway'];
  $rkm = $_POST['rkm'];
  $rexpence = $_POST['rexpence'];
  $rfile = $_FILES['rfile'];
  $rpresence = $_POST['rpresence'];
  $update = new User();
  $update->Table($rdate, $rday, $rpresence, $rway, $rkm, $rexpence, $rfile, $report);
}

edited May 05 '14 at 05:10

answered May 03 '14 at 08:29

Tanatos

1,857
1
13
12

thank you very much for your attention, i still can't insert the data to the db maybe there is a problem with my db? I have edited my question and added the database mysql database code. i do not get any error, the data just doesn't enter the database. – DavSev May 03 '14 at 12:32
Try now. You've misspelled one of the column names (ie rprecence => rpresence). Also, when using PDO, try to put the query between a try/catch so that you can actually see what's wrong with the query :) – Tanatos May 03 '14 at 13:33
yes, i have seen the misspelling, after correcting it and adding the try/catch as you did above i receive the following errors `Warning: Invalid argument supplied for foreach() in C:\wamp\www\mop\inc\userclass.php on line 128` `Fatal error: Call to a member function execute() on a non-object in C:\wamp\www\mop\inc\userclass.php on line 137` i know that the first error means that there is no array actualy. can you please suggest me what to do? – DavSev May 03 '14 at 14:47
Instead of `$query = $dbh->query($query);` i wrote `$query = $this->dbh->query($query);` so that i will be able to connect to the db. i still don't get where the errors are coming from... which of the code do you want me to place and where? – DavSev May 03 '14 at 18:05
'public function Table($rdate, $rday, $rpresence, $rway, $rkm, $rexpence, $rfile){ $query = "INSERT INTO time (rdate, rday, rway, rkm, rexpence, rfile, rpresence, report) VALUES "; $values = ""; foreach($rdate AS $key => $value) { if(strlen($values) > 0) $values .= ", "; $values .= "('".$value."', '".$rdate[$key]."', '".$rday[$key]."', ".$rkm[$key].", ".$rexpence[$key].", '".$rfile[$key]."', ".$rpresence[$key].", '".$report[$key]."')"; } $query .= $values; try { $query = $this->dbh->query($query); return $query->execute(); } catch(PDOException $e) { return $e; } }` – DavSev May 03 '14 at 18:19
line 137 is `return $query->execute(); but solved the error in this line, the problem was that there was a missing value `$rkm[$key]` i have added it and aranged the values order and now i do not see an error from that line. now i need to resolve the error on line 128. line 128 is ` foreach($rdate AS $key => $value) {` – DavSev May 04 '14 at 04:57
Any suggestions what can be the problem on line 128? – DavSev May 04 '14 at 16:49
I have posted the full code at the main post under -post update-, I have also updated the table code at the main post. – DavSev May 05 '14 at 04:56
check both the code blocks above (both changed and need to be changed in yours). – Tanatos May 05 '14 at 05:12
thank you very much for your patience, i'm sorry but the code still dosen't insert the data to the sql db. now i receive the next error ` Notice: Undefined offset: 21 in C:\wamp\www\mop\inc\userclass.php on line 131` for every line of the array. the error refears to the next line ` $values .= "(".$value."', '".$rdate[$key]."', '".$rday[$key]."', '".$rway[$key]."', ".$rkm[$key].", ".$rexpence[$key].", '".$rfile[$key]['name']."', ".$rpresence[$key].", '".$report[$key]."')";` – DavSev May 05 '14 at 08:39
I'm sorry but my patience has run out. I'm not going to wrote the whole code for you, I just wanted to help out. Gave you multiple things that you needed to do before your code will actually work in my first post, try to follow those suggestions and best of luck ! – Tanatos May 05 '14 at 08:43
Your error basically means that not all of the array's you've passed to the insert function are equal. You need to make sure they are equal or fill the missing values in with some constants. – Tanatos May 05 '14 at 08:44

score -1 · Answer 2 · answered May 13 '14 at 18:01

Finaly i got an answer to my question. please have a look:

this is the table method:

public function Table($userid, $rdate, $rday, $rway, $rkm, $rexpence, $rfile, $rpresence, $report){
    $query = "INSERT INTO time (userid, rdate, rday, rway, rkm, rexpence, rfile, rpresence, report) VALUES ";
    $values = "";

        foreach($rdate as $key => $value) {
         if(strlen($values) > 0) $values .= ", ";

             $values .= "('".$userid."', '".$rdate[$key]."', '".$rday[$key]."', '".$rway[$key]."', '".$rkm[$key]."', '".$rexpence[$key]."', '".$rfile[$key]['name']."', '".$rpresence[$key]."', '".$report."')";
         }

    $query .= $values;
    try {
        $a = $this->dbh->prepare($query);                               
    return $a->execute();
    } catch(PDOException $e) {
    return $e;
     }
}

i had so many problems that i don't know where to start from...

Every thing else stayed the same. thanks to every one who helped

insert multiple rows via a php array into mysql

2 Answers2