[RESOLVED] Updating multiple rows with different data

[dcjones]

Hi all.

I am working on a script which needs to do the following.

I have a table "flightData" (table1) which holds flight records.

I have another table which holds client requests (Table2) for flight data in advance of the flight data. On the day of the flight I need "Table1" to update "Table2".

The script runs withour error BUT only the last record in "Table2" gets updated.

Can anyone please help.

Below is my complete code:

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

$FlightDate = date("Y-m-d", time());

// Table2 Client data
mysql_select_db($database_flightq, $flightq);
$query_FlightClient = "SELECT * FROM flight_client";
$FlightClient = mysql_query($query_FlightClient, $flightq) or die(mysql_error());
$row_FlightClient = mysql_fetch_assoc($FlightClient);
$totalRows_FlightClient = mysql_num_rows($FlightClient);

// Table1 Flight data
mysql_select_db($database_flightq, $flightq);
$query_FlightData = "SELECT * FROM flightdata WHERE FlightDate = CURDATE()";
$FlightData = mysql_query($query_FlightData, $flightq) or die(mysql_error());
$row_FlightData = mysql_fetch_assoc($FlightData);
$totalRows_FlightData = mysql_num_rows($FlightData);


while ($row_FlightData = mysql_fetch_assoc($FlightData)){

$updateSQL = "UPDATE flight_client SET  ClientAirline='".$row_FlightData['CarrierCode']."', ClientFlightNo='".$row_FlightData['FlightNumber']."', ClientFlightSuffix='".$row_FlightData['FlightSuffix']."', 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 ClientFlightNoComplete ='". $row_FlightData['FlightNoComplete']."'"; 

} 

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

mysql_free_result($FlightClient);

mysql_free_result($FlightData);

[cahva]

You are just setting the $updateSQL in a while loop. It only updates the last because of that. You have to do a mysql_query for that sql in every iteration.

while ($row_FlightData = mysql_fetch_assoc($FlightData)){

$updateSQL = "UPDATE flight_c...";
mysql_query($updateSQL);

}

[bradgrafelman]

Or eliminate lots and lots of SQL queries and messy code on that page by doing this:

<?php

$query = <<<END_SQL
	UPDATE flight_client c, flightdata d
	SET
		c.ClientAirline=d.CarrierCode,
		c.ClientFlightNo=d.FlightNumber,
		c.ClientTerminalCode=d.TerminalCode,
		c.ClientGateNumber=d.GateNumber,
		c.ClientFlightStatus=d.FlightStatus,
		c.ClientFlightId=d.AODBUniqueID,
		c.ClientStime=d.ScheduledDateTime,
		c.ClientEtime=d.EstimatedDateTime,
		c.ClientDepartLoc=d.IATALocationCode,
		c.ClientArriveLoc=d.PublicLocationName,
		c.ClientZone=d.zone
	WHERE c.ClientFlightNoComplete = d.FlightNoComplete;
END_SQL;

$exec = mysql_query($sql);

?>

and that's it; no SQL queries inside of loops (a huge red flag that often indicates unoptimized use of SQL queries) or anything of the sort.

Now, humor me by reading this scenario: I show you a large stack of papers on the left side of my desk. I tell you that throughout the day, I may change something in that stack of papers and that it's very important that you give the updated version to anyone who asks you. Would you rather a) make photocopies of this large stack of papers every time I change one item, set this photocopied stack on the right side of my desk, and then go through this stack of papers anytime someone asks to see one, or b) just go through the original stack of papers?

I only ask such a silly question because it seems like you're doing the former of the two here. Why try to duplicate data in one table into another? It's fine that you have a table of requests, that way you know who needs the information. But why can't you just retrieve the information from the 'flightData' table instead of duplicating it?

[dcjones]

Hi Brad and cahva,

Thanks for your replies.

Brad, your reply cuts out as you say "a lot of messy code" and works perfectly.

Is there any security issues using the "$exec" command.

[cahva]

I put out my answer without analysing the code (as it was a bit messy), but fortunately bradgrafelman had the time to give some good advice 🙂

$exec is no command. Its a variable holding the result of that mysql_query. $exec is not the same as exec() function 🙂 It could happily been $jadadada = mysql_query($sql); and worked the same way.

[dcjones]

Hi cahva,

Thanks for your reply, I am learning, slow but getting tere.

Again many thanks.

[bradgrafelman]

dcjones: Did you also read the bottom portion of my post (after the SQL code)?

I still think it would be better to just use the original data. You could even create a VIEW in MySQL to pair client requests with flight data in whatever format you desire.

[dcjones]

Hi Brad,

I did read all your reply and I fully understand where your coming from. Under normal conditions I would not have duplicated data but in this case one set of data is exported to another server belonging to another organisation. I found it a lot less trouble to run two tables. The data in the first table gets delete every 24 hours so I am not storing large amounts.

With regard to your code example. It does exactly what I was trying to do. As you say the amount of code is dramatically reduced and it is more efficient.

You say I should go back to the original code, why do you say that?

[bradgrafelman]

dcjones wrote:

You say I should go back to the original code, why do you say that?

No - I meant the original data in the first table... definitely not the original code you started out with! :p

If you understand your application's needs and still think two tables is better, then that's your call - you're the expert as far as that goes. I just wanted to make sure you were aware of the tools available to you to make things easier/faster/more efficient/etc.

I also just wanted to make sure you weren't one of the ones who stops reading a person's post as soon as they find what they were looking for. :p