Update multiple records depending on another query

[mrt003003]

Hi ive been stuck for a while now and i just cant get my head around it so if you could help i'd be well chuffed.

 

I'm trying to update multple rows in a table depending upon the results of another.

 

I have a form that enables me to update a table called fleet. Another table called ships is realted to the fleet table as each ship is in a fleet:

 

fleet

-----

Fleetname*

Location

Detected

 

ships

------

ShipID*

Fleetname *

Shipname

 

The particular fleet i am editing is parsed as a url parameter.

 

I made a query to search for all the ships that are in the fleet i am editing:

$colname_ShipsInfleet = "-1";
if (isset($_GET['recordID'])) {
  $colname_ShipsInfleet = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']);
}
mysql_select_db($database_swb, $swb);
$query_ShipsInfleet = sprintf("SELECT * FROM ships WHERE FleetName = %s", GetSQLValueString($colname_ShipsInfleet, "text"));
$ShipsInfleet = mysql_query($query_ShipsInfleet, $swb) or die(mysql_error());
$row_ShipsInfleet = mysql_fetch_assoc($ShipsInfleet);
$totalRows_ShipsInfleet = mysql_num_rows($ShipsInfleet);

 

It searched the ships WHERE the FleetName (which is the primary key of the fleet table) is parsed as a url parameter. The appropriate ship records that are generated i want to use so a field can be updated.

For example im updating a fleet record and and setting the location (Planet Name) to a different value. I want the Ship records that are of that particular fleet to update a field (PlanetName) in ship table when the fleet table is updated.   

 

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {
  $updateSQL = sprintf("UPDATE ships WHERE ShipID = $row_ShipsInfleet['ShipID'] SET PlanetName=%s",             
                       GetSQLValueString($_POST['select'], "text"));
  mysql_select_db($database_swb, $swb);

  $Result1 = mysql_query($updateSQL, $swb) or die(mysql_error());

  $updateSQL = sprintf("UPDATE fleet SET PlanetName=%s, Detected=%s, Faction=%s WHERE FleetName=%s",
                       GetSQLValueString($_POST['select'], "text"),
                       GetSQLValueString(isset($_POST['checkbox']) ? "true" : "", "defined","1","0"),
                       GetSQLValueString($_POST['hiddenField2'], "int"),
                       GetSQLValueString($_POST['hiddenField'], "text"));

  mysql_select_db($database_swb, $swb);
  $Result1 = mysql_query($updateSQL, $swb) or die(mysql_error());

 

 

I'm really not sure how to proceed here so please if you can help me out that would be ace.

 

Thank You

 

 

[mrt003003]

Ive been testing and have modified the $ShipsInfleet query to make it a join on with the fleet table. Ive also modfied the Update query:

 

$colname_ShipsInfleet = "-1";
if (isset($_GET['recordID'])) {
  $colname_ShipsInfleet = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']);
}
mysql_select_db($database_swb, $swb);
$query_ShipsInfleet = sprintf("SELECT s.ShipID, s.FleetName, s.PlanetName, f.FleetName, f.PlanetName, f.Detected, f.Faction FROM fleet f
LEFT JOIN Ships s ON (f.FleetName = s.FleetName)
WHERE f.FleetName = '$colname_Fleet'");
$ShipsInfleet = mysql_query($query_ShipsInfleet, $swb) or die(mysql_error());
$row_ShipsInfleet = mysql_fetch_assoc($ShipsInfleet);
$totalRows_ShipsInfleet = mysql_num_rows($ShipsInfleet);



$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {
  $updateSQL = sprintf("UPDATE ships , SET PlanetName=%s WHERE FleetName = %s", GetSQLValueString($_POST['select'], "text" ),             
                       GetSQLValueString($colname_Fleet, "text"));
   mysql_select_db($database_swb, $swb);
  $Result1 = mysql_query($updateSQL, $swb) or die(mysql_error());

  $updateSQL = sprintf("UPDATE fleet SET PlanetName=%s, Detected=%s, Faction=%s WHERE FleetName=%s",
                       GetSQLValueString($_POST['select'], "text"),
                       GetSQLValueString(isset($_POST['checkbox']) ? "true" : "", "defined","1","0"),
                       GetSQLValueString($_POST['hiddenField2'], "int"),
                       GetSQLValueString($_POST['hiddenField'], "text"));

  mysql_select_db($database_swb, $swb);
  $Result1 = mysql_query($updateSQL, $swb) or die(mysql_error());

  $updateGoTo = "fleet.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";
    $updateGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $updateGoTo));
}
?>

 

I get a syntax 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 'SET PlanetName='Mon Calamari' WHERE FleetName = 'Doom'' at line 1

 

This points to the first update query line

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {
  $updateSQL = sprintf("UPDATE ships , SET PlanetName=%s WHERE FleetName = %s", GetSQLValueString($_POST['select'], "text" ),             
                       GetSQLValueString($colname_Fleet, "text")); 

 

Any ideas whats wrong with the syntax??

 

Thanks

 

[mrt003003]

Yay ive got it working! The problem was the extra commas! Woohoo im well chuffed.