[RESOLVED] Updating multiple rows with different data

[dcjones]

Hi all,

I am trying to run an UPDATE query that updates multiple rows with different data.

This is what I am working with:

do {

$updateSQL = sprintf("UPDATE flight_client SET ClientAirline=%s, ClientFlightNo=%s, ClientFlightSuffix=%s, ClientFlightNoComplete=%s, ClientTerminalCode=%s, ClientGateNumber=%s, ClientFlightStatus=%s, ClientFlightId=%s, ClientRecordDate=%s, ClientStime=%s, ClientEtime=%s, ClientDepartLoc=%s, ClientArriveLoc=%s, ClientZone=%s WHERE ClientFlightDate =". $row_FlightData['FlightDate'],
                       GetSQLValueString($_POST['ClientAirline'] = $row_FlightData['CarrierCode'], "text"),
                       GetSQLValueString($_POST['ClientFlightNo'] = $row_FlightData['FlightNumber'], "text"),
                       GetSQLValueString($_POST['ClientFlightSuffix'] = $row_FlightData['FlightSuffix'], "text"),
                       GetSQLValueString($_POST['ClientFlightNoComplete'] = $row_FlightData['FlightNoComplete'], "text"),
                       GetSQLValueString($_POST['ClientTerminalCode'] = $row_FlightData['TerminalCode'], "text"),
                       GetSQLValueString($_POST['ClientGateNumber'] = $row_FlightData['GateNumber'], "text"),
                       GetSQLValueString($_POST['ClientFlightStatus'] = $row_FlightData['FlightStatus'], "text"),
                       GetSQLValueString($_POST['ClientFlightId'] = $row_FlightData['AODBUniqueID'], "int"),
                       GetSQLValueString($_POST['ClientRecordDate'] = $row_FlightData['CarrierCode'], "text"),
                       GetSQLValueString($_POST['ClientStime'] = $row_FlightData['ScheduledDateTime'], "text"),
                       GetSQLValueString($_POST['ClientEtime'] = $row_FlightData['EstimatedDateTime'], "text"),
                       GetSQLValueString($_POST['ClientDepartLoc'] = $row_FlightData['PublicLocationName'], "text"),
                       GetSQLValueString($_POST['ClientArriveLoc'] = $row_FlightData['PublicLocationName'], "text"),
                       GetSQLValueString($_POST['ClientZone'] = $row_FlightData['zone'], "text"));
print_r ($row_FlightData);
echo "<br /><br />";
} while ($row_FlightData = mysql_fetch_assoc($FlightData));

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

If I echo out the result I get:

Array ( [id] => 190739 [AODBUniqueID] => 4546434 [FIDSFlightActive] => Y [ArrivalOrDeparture] => D [CarrierCode] => EZS [FlightNumber] => 8474 [FlightSuffix] => [FlightNoComplete] => EZS8474 [FlightDate] => 2010-01-29 [ScheduledDateTime] => 2010-01-29T16:05:00+01:00 [EstimatedDateTime] => [IATALocationCode] => GVA [PublicLocationName] => Geneva [FlightStatus] => Check in Zone E [GateNumber] => [TerminalCode] => N [type] => [reg] => [run] => [stand] => 112M [MessageSentDateTime] => [zone] => [timeflag] => 2010-01-29 00:19:44 ) 

Array ( [id] => 190747 [AODBUniqueID] => 4545802 [FIDSFlightActive] => Y [ArrivalOrDeparture] => A [CarrierCode] => TCX [FlightNumber] => 121 [FlightSuffix] => L [FlightNoComplete] => TCX121 [FlightDate] => 2010-01-29 [ScheduledDateTime] => 2010-01-29T01:30:00+01:00 [EstimatedDateTime] => [IATALocationCode] => SSH [PublicLocationName] => Sharm El Sheikh [FlightStatus] => Baggage in Hall [GateNumber] => [TerminalCode] => S [type] => [reg] => [run] => [stand] => 20M [MessageSentDateTime] => [zone] => [timeflag] => 2010-01-29 00:20:08 ) 

But the rows are not getting updated.

Can anyone see why or what I am doing wrong.

[Weedpacket]

If any of those fields are strings (and some of them are) then they need to be quoted in the query. Does GetSqlValueString() do that?

Why are you putting all these values into $_POST?

There are some points I'm curious about: the arrival and departure locations are the same? The carrier code is stored in $_POST['ClientRecordDate']?

[dcjones]

Hi Weedpacket,

Many thanks for your reply. I have corrected the errors you pointed out apart from quoting the feilds. Not to sure what you mean. Below is the complete code which may make more sense. There are some "print and echo startements which are just for testing.

<?php 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;
}
}





$now = time ();
$_POST['ClientFlightDate'] = date("Y-m-d", $now);
print $_POST['ClientFlightDate'];

$colname_ClientData = "-1";
if (isset($_POST['ClientFlightDate'])) {
  $colname_ClientData = $_POST['ClientFlightDate'];
}


mysql_select_db($database_flightq, $flightq);
$query_ClientData = sprintf("SELECT * FROM flight_client WHERE ClientFlightDate = %s", GetSQLValueString($colname_ClientData, "date"));
$ClientData = mysql_query($query_ClientData, $flightq) or die(mysql_error());
$row_ClientData = mysql_fetch_assoc($ClientData);
$totalRows_ClientData = mysql_num_rows($ClientData);

$colname_FlightData = "-1";
if (isset($_POST['ClientFlightDate'])) {
  $colname_FlightData = $_POST['ClientFlightDate'];
}
mysql_select_db($database_flightq, $flightq);
$query_FlightData = sprintf("SELECT * FROM flightdata WHERE FlightDate = %s", GetSQLValueString($colname_FlightData, "text"));
$FlightData = mysql_query($query_FlightData, $flightq) or die(mysql_error());
$row_FlightData = mysql_fetch_assoc($FlightData);
$totalRows_FlightData = mysql_num_rows($FlightData);

$FlightDate = substr($row_FlightData['ScheduledDateTime'],0,10);
echo "<br />";
print $totalRows_ClientData;
echo "<br />";
print $totalRows_FlightData;
echo "<br />";
print $FlightDate;
echo "<br />";



do {

$updateSQL = sprintf("UPDATE flight_client SET ClientAirline=%s, ClientFlightNo=%s, ClientFlightSuffix=%s, ClientFlightNoComplete=%s, ClientTerminalCode=%s, ClientGateNumber=%s, ClientFlightStatus=%s, ClientFlightId=%s,  ClientStime=%s, ClientEtime=%s, ClientDepartLoc=%s, ClientArriveLoc=%s, ClientZone=%s WHERE ClientFlightDate =". $row_FlightData['FlightDate'],
                       GetSQLValueString($_POST['ClientAirline'] = $row_FlightData['CarrierCode'], "text"),
                       GetSQLValueString($_POST['ClientFlightNo'] = $row_FlightData['FlightNumber'], "text"),
                       GetSQLValueString($_POST['ClientFlightSuffix'] = $row_FlightData['FlightSuffix'], "text"),
                       GetSQLValueString($_POST['ClientFlightNoComplete'] = $row_FlightData['FlightNoComplete'], "text"),
                       GetSQLValueString($_POST['ClientTerminalCode'] = $row_FlightData['TerminalCode'], "text"),
                       GetSQLValueString($_POST['ClientGateNumber'] = $row_FlightData['GateNumber'], "text"),
                       GetSQLValueString($_POST['ClientFlightStatus'] = $row_FlightData['FlightStatus'], "text"),
                       GetSQLValueString($_POST['ClientFlightId'] = $row_FlightData['AODBUniqueID'], "int"),
                       GetSQLValueString($_POST['ClientStime'] = $row_FlightData['ScheduledDateTime'], "text"),
                       GetSQLValueString($_POST['ClientEtime'] = $row_FlightData['EstimatedDateTime'], "text"),
                       GetSQLValueString($_POST['ClientDepartLoc'] = $row_FlightData['IATALocationCode'], "text"),
                       GetSQLValueString($_POST['ClientArriveLoc'] = $row_FlightData['PublicLocationName'], "text"),
                       GetSQLValueString($_POST['ClientZone'] = $row_FlightData['zone'], "text"));
print_r ($row_FlightData);
echo "<br /><br />";
} while ($row_FlightData = mysql_fetch_assoc($FlightData));

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


mysql_free_result($ClientData);

mysql_free_result($FlightData);

[halojoy]

FlightDate is a string.
Puttting single quotes around '2010-01-15'
So this may work

".... WHERE ClientFlightDate ='". $row_FlightData['FlightDate']."'",

[dcjones]

Hi halojoy,

I have changed the code to:

do {

$updateSQL = "UPDATE flight_client SET 
ClientAirline 			= " . $row_FlightData['CarrierCode'] 		.",
ClientFlightNo			= " . $row_FlightData['FlightNumber'] 		.", 
ClientFlightSuffix		= " . $row_FlightData['FlightSuffix'] 		.",
ClientFlightNoComplete	= " . $row_FlightData['FlightNoComplete']	.", 
ClientTerminalCode		= " . $row_FlightData['TerminalCode'] 		.", 
ClientGateNumber		= " . $row_FlightData['GateNumber'] 		.", 
ClientFlightStatus		= " . $row_FlightData['FlightStatus']		.", 
ClientFlightId			= " . $row_FlightData['AODBUniqueID']		.",
ClientStime				= " . $row_FlightData['ScheduledDateTime']	.", 
ClientEtime				= " . $row_FlightData['EstimatedDateTime']	.",
ClientDepartLoc			= " . $row_FlightData['IATALocationCode']	.", 
ClientArriveLoc			= " . $row_FlightData['PublicLocationName'].",
ClientZone				= " . $row_FlightData['zone'] 				.", 
WHERE ClientFlightDate ='". $row_FlightData['FlightDate']."'"; 
print_r ($row_FlightData);
echo "<br /><br />";
} while ($row_FlightData = mysql_fetch_assoc($FlightData));

but i am getting the following error:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' ClientFlightStatus = Baggage in Hall, ClientFlightId = 4' at line 7"

Any ideas.

[halojoy]

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' ClientFlightStatus = Baggage in Hall, ClientFlightId = 4' at line 7"

You have to find out which fields and values are strings.
Integers, numbers does not need any quotes around in Query: $integer
But if the variable you try to put into database is a string you need: '$string'

It is impossible for me to know which data is numbers or text-strings.

One guess if ClientFlightStatus value is a string
you should add quotes ' and ' around.
Like this:

$updateSQL = "UPDATE flight_client SET
ClientAirline             = " . $row_FlightData['CarrierCode'] .",
ClientFlightNo            = " . $row_FlightData['FlightNumber'] .",
ClientFlightSuffix        = " . $row_FlightData['FlightSuffix'] .",
ClientFlightNoComplete    = " . $row_FlightData['FlightNoComplete'] .",
ClientTerminalCode        = " . $row_FlightData['TerminalCode'] .",
ClientGateNumber        = " . $row_FlightData['GateNumber'] .",

ClientFlightStatus        = '" . $row_FlightData['FlightStatus'] ."',

ClientFlightId            = " . $row_FlightData['AODBUniqueID'] .",
ClientStime                = " . $row_FlightData['ScheduledDateTime'] .",
ClientEtime                = " . $row_FlightData['EstimatedDateTime'] .",
ClientDepartLoc            = " . $row_FlightData['IATALocationCode'] .",
ClientArriveLoc            = " . $row_FlightData['PublicLocationName'] .",
ClientZone                = " . $row_FlightData['zone'] .",
WHERE ClientFlightDate ='". $row_FlightData['FlightDate']."'";

[dcjones]

Hi again,

I am still have problems with my code. For testing I have made it shorter:

CarrierCode is type "varchar".
$ClientFlightDate is type "varchar".

do {


$updateSQL = "UPDATE flight_client SET 
ClientAirline = '" . $row_FlightData['CarrierCode'] 		."',
WHERE ClientFlightDate  = '2010-01-29'";


print_r ($row_FlightData);
echo "<br /><br />";
} while ($row_FlightData = mysql_fetch_assoc($FlightData));

The error is:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE ClientFlightDate = '2010-01-29'' at line 4

[dcjones]

Hi all,

I have now rewritten the code and it runs withour error, but the table is not getting updated. Can anyone see where I am going wrong.

New code:

$FlightDate = $row_FlightData['FlightDate'];
print "Content of FlightDate variable used in the WHERE clause: " .$FlightDate;
echo "<br /><br /><br /><br />";
do {


$updateSQL = "UPDATE flight_client SET 
ClientFlightNo			= '" . $row_FlightData['FlightNumber']."', 
ClientFlightSuffix		= '" . $row_FlightData['FlightSuffix']."',
ClientFlightNoComplete	= '" . $row_FlightData['FlightNoComplete']."', 
ClientTerminalCode		= '" . $row_FlightData['TerminalCode']."', 
ClientGateNumber		  = '" . $row_FlightData['GateNumber']."', 
ClientFlightStatus        = '" . $row_FlightData['FlightStatus']."',  

ClientFlightId			= '" . $row_FlightData['AODBUniqueID']."',
ClientStime			   = '" . $row_FlightData['ScheduledDateTime']."', 
ClientEtime			   = '" . $row_FlightData['EstimatedDateTime']."',
ClientDepartLoc		   = '" . $row_FlightData['IATALocationCode']."', 
ClientArriveLoc		   = '" . $row_FlightData['PublicLocationName']."',
ClientZone                = '" . $row_FlightData['zone']."' 
WHERE ClientFlightDate    = " . $FlightDate;


print_r ($row_FlightData);
echo "<br /><br />";
} while ($row_FlightData = mysql_fetch_assoc($FlightData));

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

When out put to the browser I get:

Content of FlightDate variable used in the WHERE clause: 2010-01-29

Array ( [id] => 190739 [AODBUniqueID] => 4546434 [FIDSFlightActive] => Y [ArrivalOrDeparture] => D [CarrierCode] => EZS [FlightNumber] => 8474 [FlightSuffix] => [FlightNoComplete] => EZS8474 [FlightDate] => 2010-01-29 [ScheduledDateTime] => 2010-01-29T16:05:00+01:00 [EstimatedDateTime] => [IATALocationCode] => GVA [PublicLocationName] => Geneva [FlightStatus] => Check in Z [GateNumber] => [TerminalCode] => N [type] => [reg] => [run] => [stand] => 112M [MessageSentDateTime] => [zone] => [timeflag] => 2010-01-29 00:19:44 )

Array ( [id] => 190747 [AODBUniqueID] => 4545802 [FIDSFlightActive] => Y [ArrivalOrDeparture] => A [CarrierCode] => TCX [FlightNumber] => 121 [FlightSuffix] => L [FlightNoComplete] => TCX121 [FlightDate] => 2010-01-29 [ScheduledDateTime] => 2010-01-29T01:30:00+01:00 [EstimatedDateTime] => [IATALocationCode] => SSH [PublicLocationName] => Sharm El Sheikh [FlightStatus] => Baggage in [GateNumber] => [TerminalCode] => S [type] => [reg] => [run] => [stand] => 20M [MessageSentDateTime] => [zone] => [timeflag] => 2010-01-29 00:20:08 )

Any help would be great.