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 3 at 8: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 3 at 8:20
possible duplicate of How can I prevent SQL injection in PHP? — hakre, May 3 at 8:21

Tanatos · Accepted Answer · 2014-05-05 05:10:44Z

up vote 0 down vote accepted

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 5 at 5:10

answered May 3 at 8:29

Tanatos
886137

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 3 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 3 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 3 at 14:47

$dbh is a pdo object, so you don't do dbh->execute. place your code again... – Tanatos May 3 at 15:11

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 3 at 18:05

show 11 more comments

DavSev · Answer 2 · 2014-05-13 18:01:59Z

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

asked	2 months ago
viewed	169 times
active	10 days ago

current community

your communities

more stack exchange communities

insert multiple rows via a php array into mysql

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged php mysql sql arrays datatable or ask your own question.

Linked

Hot Network Questions

current community

your communities

more stack exchange communities

insert multiple rows via a php array into mysql

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged php mysql sql arrays datatable or ask your own question.

Linked

Related

Hot Network Questions