Update multiple records with distinctive field values

[PinkFloyd007]

Hey y'all,

In short : a page with results from an archery-match. Each archer is displayed with his score, #misses, #10's and #9's.

Goal : to change a lot of the scores, and to have the concerned records updated when SUBMIT is pressed.

This is what I've got so far :

<?php require_once('../Connections/URSTAB.php'); ?>
<?php

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

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "update_results")) {

for ($i = 0; $i < count($PID); $i++) {
	if (($RSCORE[$i]) > 0) {
	$values .="('$MYEAR[$i]','$MCODE[$i]',$PID[$i],$RSCORE[$i],$RMISS[$i],$R10S[$i],$R9S[$i]),";
	}
}
$values = substr($values,0,-1);

  $insertSQL = sprintf("UPDATE results SET (MYEAR, MCODE, PID, RSCORE, RMISS, R10S, R9S) VALUES $values");

  mysql_select_db($database_URSTAB, $URSTAB);
  $Result1 = mysql_query($insertSQL, $URSTAB) or die(mysql_error());

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

Now only including one of the records here (page is 1708 lines long, so...)

<?php if (isset($row_RESULTS_R_DEB['PNAME'])) {?>
  <tr>
    <td colspan="5"><b><?php print $lang['048'] ?></b></td>
  </tr>
  <tr>
    <td bgcolor="#999999" width="200"><?php print $lang['041'] ?></td>
    <td width="60" align="center" bgcolor="#999999"><?php print $lang['042'] ?></td>
    <td width="30" align="center" bgcolor="#999999"><?php print $lang['043'] ?></td>
    <td width="30" align="center" bgcolor="#999999"><?php print $lang['044'] ?></td>
    <td width="30" align="center" bgcolor="#999999"><?php print $lang['045'] ?></td>
  </tr>
    <?php do { 
	mysql_select_db($database_URSTAB, $URSTAB);
$query_RESULTS_R_DEB_SCORE = "SELECT * FROM results WHERE MCODE = '" . $_GET['MCODE'] . "' AND PID = " . $row_RESULTS_R_DEB['PID'];
$RESULTS_R_DEB_SCORE = mysql_query($query_RESULTS_R_DEB_SCORE, $URSTAB) or die(mysql_error());
$row_RESULTS_R_DEB_SCORE = mysql_fetch_assoc($RESULTS_R_DEB_SCORE);
$totalRows_RESULTS_R_DEB_SCORE = mysql_num_rows($RESULTS_R_DEB_SCORE);
?>
    <tr>
      <td><?php echo $row_RESULTS_R_DEB['PNAME']; ?></td>
      <td align="center"><input name="RSCORE[]" type="text" size="3" maxlength="3" value="<?php if(isset($row_RESULTS_R_DEB_SCORE['RSCORE'])) { echo $row_RESULTS_R_DEB_SCORE['RSCORE']; } else { print '0'; } ?>" /></td>
      <td align="center"><input name="RMISS[]" type="text" size="2" maxlength="2" value="<?php if(isset($row_RESULTS_R_DEB_SCORE['RMISS'])) { echo $row_RESULTS_R_DEB_SCORE['RMISS']; } else { print '0'; } ?>" /></td>
      <td align="center"><input name="R10S[]" type="text" size="2" maxlength="2" value="<?php if(isset($row_RESULTS_R_DEB_SCORE['R10S'])) { echo $row_RESULTS_R_DEB_SCORE['R10S']; } else { print '0'; } ?>" /></td>
      <td align="center"><input name="R9S[]" type="text" size="2" maxlength="2" value="<?php if(isset($row_RESULTS_R_DEB_SCORE['R9S'])) { echo $row_RESULTS_R_DEB_SCORE['R9S']; } else { print '0'; } ?>" /><input name="PID[]" type="hidden" value="<?php echo $row_RESULTS_R_DEB['PID']; ?>" /><input name="MCODE[]" type="hidden" value="<?php echo $_GET['MCODE']; ?>" /><input name="MYEAR[]" type="hidden" value="<?php echo $_GET['MYEAR']; ?>" /></td>
    </tr>
    <?php 
	mysql_free_result($RESULTS_R_DEB_SCORE);
	} while ($row_RESULTS_R_DEB = mysql_fetch_assoc($RESULTS_R_DEB)); ?>
  <tr>
    <td colspan="5">&nbsp;</td>
  </tr>
<?php } ?>

I know I've left out some of the DB selectors and other stuff, but I guess you get the general idea of how the data is passed (=> names of the form fields).

This solution doens't work off course, since the SQL statement is incorrect. I only started out like this because I'm using the same stuff to initially CREATE the records (INSERT INTO statement instead of UPDATE SET), and that works fine.

Problem is I've got no idea how to perform an UPDATE in this way...

Thanks for the help already!

Pink

[konsu]

an UPDATE changes values in rows that satisfy the test in the optional WHERE clause, or in all rows if there is no WHERE clause. what raws do you want to change?

[leatherback]

Update results
set COLUMNAME1 = 'VALUE1', COLUMNAME2 = 'VALUE2' WHERE MCODE = '" . $_GET['MCODE'] . "' AND PID = SOMETHING;

[PinkFloyd007]

Ah yes, that would work for only one record at a time, and I know this code, but my whole issue is that I want to extend this to a number of records in one go.

Like, say I've got 67 archers that already have a score logged.
They're all listed on my page, with their respective scores.

21 among them have errors in their scores, and these need to be corrected.

I want to correct those 21 scores, and then only submit the form once to update all of those changed values in their respective records.

So in this case, using the SET statement as marked by leatherback, I'll be only able to update one score (one archer).

With the code above, I simply demonstrated what way I went for the original creation of the records, all in one time, and that I looked for a way to be able to perform the same thing (all in one time) when updating instead of creating.

[PinkFloyd007]

Anyone ? Anyone ?

Oh, come on, I just KNOW there has to be a solution to this, I refuse to accept that no one ever came across a similar problem and left it unsolved...

[leatherback]

Depends on how you submite the data. But you can loop through them.

Assuming you have like the textarea's named as arrays
<input type=text name=highscore[0]> Player 1's highscore
<input type=text name=highscore[1]> Player 2's highscore

You can then in your post do something link:

$highscores = $_POST[highscore];
// this of course for all posted values

foreach($highscores sa $key=>value)
  {
   $thishighscore = $value;
   $thislowscore = $lowscore[$key];
    // etcetc for all submitted fields
// and update
   Update results
set COLUMNAME1 = 'VALUE1', COLUMNAME2 = 'VALUE2' WHERE MCODE = '" . $_GET['MCODE'] . "' AND PID = SOMETHING;
}

[Oersoep]

UPDATE table SET field = '1000' WHERE archer_id IN( 3, 5, 19, 21, 22)

But this one only works if they all have to change in the same way 🙂

[leatherback]

Like this logic:
create inputboxes:

      foreach($links as $link_id => $details)
        {
        $linklist .= "
        <tr><td class=\"inputbox\">

    <input  class=\"formel_2\" type=\"text\" name=\"link[$link_id][url]\" size=\"50\" value=\"$details[url]\"> url <br>
    <input  class=\"formel_2\" type=\"text\" name=\"link[$link_id][text]\" size=\"50\" value=\"$details[text]\"> name<br>
    <textarea class=\"formel_2\" name=\"link[$link_id][description]\" cols=37 rows=5>$details[description]</textarea> description<br>

    </td></tr>" ;
    }

evaluate them:

foreach($_POST['link'] as $key=>$value)
  {
    $key = test_key($key,0);
   $value = clean_array($value);
  $result = mysql_query("update $links_table set 
                                       L_url    = '$value[url]', 
                                       L_text     = '$value[text]', 
                                       L_description = '$value[description]' 
                                       where L_id = $key") or die('Could not update: '.mysql_error());


  }