Update multiple rows of data

[dcjones]

Hi all,

I am trying to write a script that will update multiple rows of data in a SQL table.

So far I have manage to complete the following:

I have one submit button which when clicked updates all the rows from the result of query1.

The code so far:

Table structure;

Fields: RecordID, FlightID, FlightNo, FlightStatus, FlightAck

// query1
mysql_select_db($database_test, $conn);
$sql = "SELECT * FROM table_sms";
$result = mysql_query($sql, $conn) or die(mysql_error());
$count=mysql_num_rows($result);

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

<tr>
<? $id[]=$rows['RecordID']; ?>
<input type="hidden" name="FlightID[]" id="FlightID" value="<?php print $rows['FlightID'];?>" />
<input type="text" name="FlightNo[]" id="FlightNo" value="<?php print $rows['FlightNo'];?>"/>
<input type="text" name="FlightStatus" id="FlightStatus" value="<?php print $rows['FlightStatus'];?>"/>
<input name="FlightAck[]" type="hidden" id="FlightAck" value="1" />
</tr>


}

if(isset($_POST['Submit'])){ 
 for($i=0;$i<$count;$i++){
 $sql1="UPDATE Flightx_sms SET FlightAck='FlightAck[$i]' WHERE RecordID='$id[$i]'";
 $result1=mysql_query($sql1);
 }
}

When the submit button is click the SQL UPDATE does not insert the content of FlightAck[$i] into the table.

Can anyone see where I am going wrong.

[Weedpacket]

You appear to have most of your PHP code outside the <?php...?> tags. That won't work for a start.

What does [font=monospace]FlightAck[$i][/font] mean? Don't you mean [font=monospace]$_POST[FlightAck][$i][/font] and haven't you learned about taking basic measures to prevent SQL injection yet, or the deprecation of the MySQL interface?

[dcjones]

Hi, Weedpacket,

Many thanks for your reply and additional comments concerning sql injection.

In my original post I tried to cut down on the amount of code which did leave out a lot of stuff, here is the complete script with the amendment from your reply:

<?php

if (!isset($_SESSION)) {
  session_start();
}

print_r($_POST);

require_once('../../Connections/flightq.php');

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    

    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

mysql_select_db($database_flightq, $flightq);
$query_sql = "SELECT * FROM FLIGHTx_sms WHERE FlightAck IS NULL";
$sql = mysql_query($query_sql, $flightq) or die(mysql_error());
$row_sql = mysql_fetch_assoc($sql);
$totalRows_sql = mysql_num_rows($sql);



?>


<link href="css/alert.css" rel="stylesheet" type="text/css" />
<form name="form1" method="post" action="" onSubmit="close_window()">

<table width="333" border="0" align="center" cellpadding="0" cellspacing="0">
  <tr>
    <td colspan="4" class="bodytext"><div align="center">
      <img src="images/popup_banner.png" width="310" height="66" border="0" />
      <table width="100%" border="0" align="center" cellpadding="0" cellspacing="0">
        <tr>
          <td class="bodytext">ID</td>
          <td class="bodytext">Flight No</td>
          <td class="bodytext">Status</td>
          <td class="bodytext">&nbsp;</td>
        </tr>
        <?php
while($row_sql = mysql_fetch_assoc($sql)){
?>
        <tr>

          <td><span class="popupfields">
            <input name="FlightID[]" type="text" class="bodytext2" id="FlightID" style="border:none" value="<?php print $row_sql['FlightID'];?>" size="12"/>
          </span></td>
          <td><span class="popupfields">
            <input name="FlightNo[]" type="text" class="bodytext2" id="FlightNo" style="border:none" value="<?php print $row_sql['FlightNo'];?>" size="9" readonly="readonly"/>
          </span></td>
          <td><span class="popupfields">
            <input name="FlightStatus" type="text" class="bodytext2" id="FlightStatus" style="border:none" value="<?php print $row_sql['FlightStatus'];?>" size="6" readonly="readonly"/>
          </span></td>
          <td><input name="FlightAck[]" type="hidden" id="FlightAck" value="1" /></td>
        </tr>
        <?php
		}
		?>
        <tr>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
          <td>&nbsp;</td>
        </tr>
      </table>

    </div></td>
  </tr>

  <tr>
    <td colspan="4" class="bodytext">        
      <div align="center">
        <input type="image" src="images/ack_button.png" name="Submit" value="Submit" />
      </div></td>
  </tr>
    </table>
</form>


<?php

if(isset($_POST['Submit'])){

for($i=0;
$i<$totalRows_sql;
$i++){
$updateSQL = sprintf("UPDATE FLIGHTx_sms SET  FlightAck=%s WHERE FlightID='".$row_sql['FlightID[$i]']."'",
                   GetSQLValueString($_POST['FlightAck[$i]'], "int"));

	mysql_select_db($database_flightq, $flightq);
	$Result1 = mysql_query($updateSQL, $flightq) or die(mysql_error());
}  
}

mysql_free_result($sql);
?>

When the form is submitted the result of the print_r($_POST) is:

Array ( [FlightID] => Array ( [0] => 273809636 [1] => 273863209 [2] => 273859571 [3] => 273863078 [4] => 273960304 [5] => 273913059 [6] => 273960521 [7] => 273842651 ) [FlightNo] => Array ( [0] => AC848 [1] => BA177 [2] => KU103 [3] => BA187 [4] => KL1022 [5] => KL1021 [6] => BA187 [7] => BA186 ) [FlightStatus] => CANX [FlightAck] => Array ( [0] => 1 [1] => 1 [2] => 1 [3] => 1 [4] => 1 [5] => 1 [6] => 1 [7] => 1 ) [Submit_x] => 25 [Submit_y] => 31 )

This leads me back to square one, can you or anyone see why the records are not getting updated.

Again many thanks.

[Weedpacket]

$_POST['FlightAck[$i]']

should be

$_POST['FlightAck'][$i]

[dcjones]

Hi Weedpacket,

Again thanks for your reply.

I have changed "$POST['FlightAck[$i]']" to "$POST['FlightAck'][$i]".

By commenting out this part of the script "(isset($_POST['Submit'])){"
and then running it I get the following error:

Notice: Undefined offset: 14 in /home/vhosts/mydomain.com/httpdocs/delay_dev/alert.php on line 120

Line 120 is: GetSQLValueString($_POST['FlightAck'][$i] , "int"));

Can you see why I am getting this and would that error stop the query from updating the table.