inserting multiple rows in 1 query...

[mcerveni]

I found a tutorial about updating multiple records at a time at  http://www.phpeasystep.com/mysqlview.php?id=10 .

 

So i used it to INSERT multiple records at one time...the problem is that it's not grabbing the arrays and inserting them all.

It's instead grabbing the very last row and inesrting it into the table. I'm not sure why...

 

code to display the form:

 

$getShift = "SELECT * FROM shiftbid"; 
$result = mysql_query($getShift);

//Count table rows 
$count=mysql_num_rows($result);
?>
<form action="include/process.php" method="post">
<table id="shiftTable"> 
<tr> <th> NO </th> <th> DAY </th> <th> SHIFT </th> <th> RANK </th> </tr>

<?php
while($row= mysql_fetch_array($result)) { 

?>

<tr> 
<td> <[b]input type="hidden" name="id[]" value="<?php echo $row['id'];?>">[/b]  <?php echo $row['no'];?> <input type="hidden" name="no[]" value="<?php echo $row['no'];?>"> </td> 
<td> <?php echo $row['day'];?>  <input type="hidden" name="day[]" value="<?php echo $row['day'];?>"> </td> 
<td> <?php echo $row['shift'];?>  <input type="hidden" name="shift[]" value="<?php echo $row['shift'];?>"></td> 
<td> <?php echo '<input type="text" name="rank[]" size="2" maxlength="2" > '; ?>  </td> </tr>
<?php
} //while
?>

<tr><td><input type="submit" name="finish" value="Done"> </td></tr> 

</table>
</form>
<?php }
?>

 

 

code to process the insert:

 

$no = $_POST['no'];
$day = $_POST['day'];
$shift = $_POST['shift'];
$rank = $_POST['rank'];
$id = $_POST['id'];

for($i=0;$i<3;$i++){


$insertShift_sql = "INSERT INTO user_req (id, no, day, shift, rank) VALUES('$id[$i]', '$no[$i]', '$day[$i]', '$shift[$i]', '$rank[$i]')";

}


   if( @mysql_query ($insertShift_sql) ){ 
           echo 'You\'re shift has been processed. Thank you. ';
           
       } 
       else {
       echo 'An error has been encountered. Please refresh the page. ';
       }
    

 

 

Any help appreciated.

[trq]

Your not executing mysql_query() within the loop.

[dezkit]

nvm

[mcerveni]

what nevermind?

[mcerveni]

where am i not executing mysql_query within the loop?

[dezkit]

i have no idea... thorpe is high on something

[mcerveni]

oh ok ..lol.

 

well im still stuck .. :-\

[dezkit]

can you post the whole code, just block out the username password and database with asterisks

[mcerveni]

nevermind..

lol it works..

 

i changed the for loop { to end at the end of the mysql_query() like he said.

 

it works..all the records go into the table at once now 

 

thanks.

[mcerveni]

however...

 

it will show "Your shift has been processed" 3 times lol ...but if there are about 97 rows which there will be...it will show Your shift has been processed 97 times haha..oh man.

 

i'll figure that out.

[mcerveni]

Now i'm trying to apply ajax into inserting multiple rows in 1 query...

the php code works fine..but how do i pass the arrays onto my ajax code that passes the variables to my process.php page..?

 

code that displays form:

 

$getShift = "SELECT * FROM shiftbid"; 
$result = mysql_query($getShift);

//Count table rows 
$count=mysql_num_rows($result);
?>
<table id="shiftTable"> 
<tr> <th> NO </th> <th> DAY </th> <th> SHIFT </th> <th> RANK </th> </tr>

<?php
while($row= mysql_fetch_array($result)) { 

?>

<tr> 
<td> <input type="hidden" name="id[]" value="<?php echo $row['id'];?>">  <?php echo $row['no'];?> <input type="hidden" name="no[]" id="no[]" value="<?php echo $row['no'];?>"> </td> 
<td> <?php echo $row['day'];?>  <input type="hidden" name="day[]" id="day[]" value="<?php echo $row['day'];?>"> </td> 
<td> <?php echo $row['shift'];?>  <input type="hidden" name="shift[]" id="shift[]" value="<?php echo $row['shift'];?>"></td> 
<td> <?php echo '<input type="text" name="rank[]" id="rank[]" size="2" maxlength="2" > '; ?>  </td> </tr>
<?php
} //while
?>

<tr><td><input type="submit" name="finish" value="Done" onclick="addShift();"> </td></tr> 

</table>

<?php }
?>

 

if i were using straight php the name="shift[]" , etc..would work because process.php can pick up those arrays and use them...however since i'm using ajax.. i used id="shift"  ..but how do i pass the arrays into the ajax code..??

 

ajax code:

 

function addShift() {
dimScreen();
document.getElementById('loaderBox').style.display="block";
document.getElementById('loaderBox').innerHTML = " <table> <tr><td> <img src='scripts/loader.gif'> </td><td> <b> Processing </b> </td></tr></table> "

var id = document.getElementsByName('id');
var no = encodeURI(document.getElementById('no').value);
var day = encodeURI(document.getElementById('day').value);
var shift = encodeURI(document.getElementById('shift').value);
var rank = encodeURI(document.getElementById('rank').value);


var url = "include/process.php";  
var params = "id=" + id + "&no=" + no + "&day=" + day + "&shift=" + shift + "&rank=" + rank;
http.open("POST", url, true);



//Send the proper header information along with the request
http.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
http.setRequestHeader("Content-length", params.length);
http.setRequestHeader("Connection", "close");

http.onreadystatechange = function() {//Call a function when the state changes.
if(http.readyState == 4 && http.status == 200) {
var response = http.responseText;
document.getElementById('content').innerHTML = ''+response+ '';

}

}
http.send(params);

} //function

 

 

process.php page:

 

$id = $_POST['id'];
$no = $_POST['no'];
$day = $_POST['day'];
$shift = $_POST['shift'];
$rank = $_POST['rank'];



for($i=0;$i<3;$i++){


$insertShift_sql = "INSERT INTO user_req (id, no, day, shift, rank) VALUES('$id[$i]', '$no[$i]', '$day[$i]', '$shift[$i]', '$rank[$i]')";




   if( @mysql_query ($insertShift_sql) ){ 
           echo 'You\'re shift has been processed. Thank you. ';
           
       } 
       else {
       echo 'An error has been encountered. Please refresh the page. ';
       }
    
}

 

Any help again appreciated.