php multiple record insert to sql not working

Question

Hi all I have a form where I'm entering students marks for exams, what I am trying to do is enter multiple marks in at once...but it just is not working, could someone help please? It is adding only ONE result, doesn't enter all that I write in.

FORM Code:

Looks like you compiled this code from different sources and forgot the essential. You are using HTML input arrays which hold multiple values thus you need to loop through the data and save each. — AyB, Mar 17 '14 at 13:02
How does look multiple record which you are adding, coz there's no loop in form, or in inserting results!! — Michael, Mar 17 '14 at 13:03

score 2 · Answer 1 · answered Mar 17 '14 at 13:12

The script does not know what is $i in this case.

Use for loop:

$mark=$_POST['mark'];
$time=$_POST['time'];
$meID=$_POST['meID'];
$sID=$_POST['sID'];

for($i = 0; $i < count($_POST['mark']); $i++) {
    $_mark = mysql_escape_string($mark[$i]);
    $_time = mysql_escape_string($time[$i]);
    $_meID = mysql_escape_string($meID[$i]);
    $_sID = mysql_escape_string($sID[$i]);

    $sql = "INSERT INTO Marks (mark, time, meID, sID) VALUES ($_mark, $_time, $_meID, $_sID)";
    $result = mysql_query($sql);
}

Additionally you will add each mark twice, because of two mysql_query() invocations. I've already deleted one from $sql variable in my answer. Also you have wrong variable names in the insert query.

check var_dump($_POST['mark']) at the beginning of DB script. — michal.hubczyk, Mar 17 '14 at 13:39

echo_Me · Answer 2 · 2014-03-17T13:10:30.477

1

there is no mysql_escape_string . try replace this;

   $_mark = mysql_escape_string($mark[$i]);

to

   $_mark = mysql_real_escape_string($mark[$i]);

and put $_mark in the query instead of $mark

and replace all others also.

edited Mar 17 '14 at 13:10

answered Mar 17 '14 at 13:03

echo_Me

37,078
5
58
78

1

There is [mysql_escape_string](http://php.net/mysql_escape_string) although I agree the _real_ one does a better job, but that doesn't concern the OP's problem. – AyB Mar 17 '14 at 13:07

score 1 · Answer 3 · answered Mar 17 '14 at 13:08

I think the problem is with the variable names:

Following line:

$sql = "SELECT * FROM ModuleExamStudent WHERE mesID = '$mesID'";

But there is no $mesID. It should be $mes:

$sql = "SELECT * FROM ModuleExamStudent WHERE mesID = '$mes'";

Again:

$sql = mysql_query("INSERT INTO Marks (mark, time, meID, sID) VALUES ('$mark', '$time', '$meID', '$sID')");

Should be:

$sql = mysql_query("INSERT INTO Marks (mark, time, meID, sID) VALUES ('$_mark', '$_time', '$_meID', '$_sID')");

Looks like the body of the question is deleted. – Ambrish Mar 18 '14 at 05:59 — Ambrish, Mar 18 '14 at 05:59

score 0 · Answer 4 · edited May 23 '17 at 12:20

Yeah, a lot of mistakes in you're code. Fuse michal.hubczyk and Ambrish answer to obtain wath you want. If i can suggest another way to do it, i'll prefer to make only one query instead an elevated number of query succession inside a loop.

Try this method Inserting multiple rows in a single SQL query? and build your query in a way like this:

$sql = "INSERT INTO XX VALUES";
for($i=0;$i < count($_POST['mark']); $i++){
 $_mark = mysql_escape_string($mark[$i]);
 $_time = mysql_escape_string($time[$i]);
 $_meID = mysql_escape_string($meID[$i]);
 $_sID = mysql_escape_string($sID[$i]);
 $sql .= "(".$_mark.",".$_time",".$_meID.",".$sID.")";
 if($i<(count($_POST['mark']-1))){
  $sql .= ",";
}
$result = mysql_query($sql);

Another suggestion is to use mysqli libray instead mysql ( http://php.net/mysqli)

php multiple record insert to sql not working

4 Answers4