update form array not working.

Report · Sep 09, 2010

Hi,

My update form array does not work.

I am trying to upload to two tables: matches and matchscores

I need to upload to matchsores twice as it must hold information for two teams per match_id

This is my HTML

        <table width="100%" border="0" cellspacing="0" cellpadding="0">
          <tr>
            <td><table width="100%" border="0" cellspacing="0" cellpadding="0">
              <tr>
                
                <td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Date</div></td>
                <td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Home</div></td>
                <td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Score</div></td>
                <td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Away</div></td>
                <td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Kick-Off</div></td>
                <td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Venue</div></td>
                <td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Referee</div></td>
                <td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Update</div></td>
              </tr>

              <?php do { ?>
 <form action="<?php echo $editFormAction; ?>" method="POST" name="fixtures"> 
                <tr>
                 
                  <td><div align="center"><span id="sprytextfield1">
                    <label>
                    <input name="date[]" type="text" class="date" size="8" style="text-align:center" value="<?php echo $row_match_fixtures['shortDate']; ?>" />
                    <span class="textfieldRequiredMsg">A value is required.</span>                            
                    </label>
                    </span></div></td>
                  <td><div align="center"><a href="match-player.php?id=<?php echo $row_match_fixtures['match_id']; ?>&team=<?php echo $row_match_fixtures['team1_id']; ?>"><?php echo $row_match_fixtures['team1_name']; ?></a></div></td>
                  <td><div align="center"><input name="s1[]" type="text" value="<?php echo $row_match_fixtures['score1']; ?>" size="1" style="text-align:center" /> 
                  v <input name="s2[]" type="text" value="<?php echo $row_match_fixtures['score2']; ?>" size="1" style="text-align:center" />
                  </div></td>
                  <td><div align="center"><a href="match-player.php?id=<?php echo $row_match_fixtures['match_id']; ?>&team=<?php echo $row_match_fixtures['team2_id']; ?>"><?php echo $row_match_fixtures['team2_name']; ?></a></div></td>
                  <td><div align="center"><span id="sprytextfield2">
                  <label>
                  <input name="time[]" type="text" size="3" value="<?php echo $row_match_fixtures['time']; ?>" style="text-align:center" />
                  </label>
                  <span class="textfieldInvalidFormatMsg">Invalid format.</span><span class="textfieldRequiredMsg">A value is required.</span></span></div></td>
                  <td><div align="center"><span id="spryselect1">
                    <label>
                    <select name="venue[]">
                      <?php
do {  
?>
                      <option value="<?php echo $row_venue['venue_id']?>"<?php if (!(strcmp($row_venue['venue_id'], $row_venue['venue_name']))) {echo "selected=\"selected\"";} ?>><?php echo $row_venue['venue_name']?></option>
                      <?php
} while ($row_venue = mysql_fetch_assoc($venue));
  $rows = mysql_num_rows($venue);
  if($rows > 0) {
      mysql_data_seek($venue, 0);
       $row_venue = mysql_fetch_assoc($venue);
  }
?>
                    </select>
                    </label>
                    <span class="selectRequiredMsg">Please select an item.</span></span></div></td>
                  <td><div align="center"><span id="spryselect2">
                    <label>
                    <select name="referee[]">
                      <?php
do {  
?>
                      <option value="<?php echo $row_referee['ref_id']?>"<?php if (!(strcmp($row_referee['ref_id'], $row_referee['sname']))) {echo "selected=\"selected\"";} ?>><?php echo $row_referee['fname']?> <?php echo $row_referee['sname']?></option>
                      <?php
} while ($row_referee = mysql_fetch_assoc($referee));
  $rows = mysql_num_rows($referee);
  if($rows > 0) {
      mysql_data_seek($referee, 0);
       $row_referee = mysql_fetch_assoc($referee);
  }
?>
                    </select>
                    </label>
                    <span class="selectRequiredMsg">Please select an item.</span></span></div></td>

                  <td><div align="center"><input name="Submit[]" type="submit" id="Submit" value="Save" /></div></td>

           
                </tr>
         <input name="match_id[]" type="hidden" value="<?php echo $row_match_fixtures['match_id']; ?>" />
        <input name="mscore1[]" type="hidden" value="<?php echo $row_match_fixtures['mscore1']; ?>" />
        <input name="mscore2[]" type="hidden" value="<?php echo $row_match_fixtures['mscore2']; ?>" />
        <input name="team1_id[]" type="hidden" value="<?php echo $row_match_fixtures['team1_id']; ?>" />
        <input name="score1[]" type="hidden" value="<?php echo $row_match_fixtures['score1']; ?>" />
        <input name="team2_id[]" type="hidden" value="<?php echo $row_match_fixtures['team2_id']; ?>" />
        <input name="score2[]" type="hidden" value="<?php echo $row_match_fixtures['score2']; ?>" />
        
        <input type="hidden" name="MM_update" value="fixtures" />                  
</form> 
                <?php } while ($row_match_fixtures = mysql_fetch_assoc($match_fixtures)); ?>
      
            </table></td>
          </tr>
        </table>

This is the SQL:

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

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "fixtures")) {
  $updateSQL = sprintf("UPDATE matches SET `date`=%s, `time`=%s, referee_id=%s, venue_id=%s WHERE match_id=%s",
                       GetSQLValueString($_POST['date'], "date"),
                       GetSQLValueString($_POST['time'], "text"),
                       GetSQLValueString($_POST['referee'], "int"),
                       GetSQLValueString($_POST['venue'], "int"),
                       GetSQLValueString($_POST['match_id'], "int"));
                            
  $updateSQL = sprintf("UPDATE matchscores SET match_id=%s, team=%s, score=%s WHERE matchscores_id=%s",
                       GetSQLValueString($_POST['match_id'], "int"),
                       GetSQLValueString($_POST['team1_id'], "int"),
                       GetSQLValueString($_POST['score1'], "int"),
                       GetSQLValueString($_POST['mscore1'], "int"));
                            
  $updateSQL = sprintf("UPDATE matchscores SET match_id=%s, team=%s, score=%s WHERE matchscores_id=%s",
                       GetSQLValueString($_POST['match_id'], "int"),
                       GetSQLValueString($_POST['team2_id'], "int"),
                       GetSQLValueString($_POST['score2'], "int"),
                       GetSQLValueString($_POST['mscore2'], "int"));

  mysql_select_db($database_db, $db);
  $Result1 = mysql_query($updateSQL, $db) or die(mysql_error());

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

The query to display the information is taken from this query:

mysql_select_db($database_db, $db);
$query_match_fixtures = "select m.match_id, date_format(m.date, '%W %D %M %Y') as mDate, date_format(m.date, '%d/%m/%Y') as shortDate, m.time, t1.team_id as team1_id, t1.division, m.report, t1.team_name as team1_name, s1.score as score1, t2.team_id as team2_id, t2.team_name as team2_name, s2.score as score2, v.venue_name, r.fname, r.sname, s1.matchscores_id as mscore1, s2.matchscores_id as mscore2 
from matches m left join (matchscores s1 left join team t1 on t1.team_id = s1.team) on (s1.match_id = m.match_id) left join (matchscores s2 left join team t2 on t2.team_id = s2.team) on (s2.match_id = m.match_id) 
LEFT JOIN referee r ON r.ref_id = m.referee_id LEFT JOIN venue v ON v.venue_id = m.venue_id
where s1.team <> s2.team 
group by match_id 
order by m.match_id";
$match_fixtures = mysql_query($query_match_fixtures, $db) or die(mysql_error());
$row_match_fixtures = mysql_fetch_assoc($match_fixtures);
$totalRows_match_fixtures = mysql_num_rows($match_fixtures);

Where am I going wrong?

Report · Sep 09, 2010

oops bad double post

Report · Sep 09, 2010

$updateSQL

this looks like it is defined three times before being executed, only the last one will get updated

of course i cant test this, but you would need to execute a query for each sql statement, change the statement... execute...change

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

  // make connection to db here
 mysql_select_db($database_db, $db);



  $updateSQL = sprintf("UPDATE matches SET `date`=%s, `time`=%s, referee_id=%s, venue_id=%s WHERE match_id=%s",
                       GetSQLValueString($_POST['date'], "date"),
                       GetSQLValueString($_POST['time'], "text"),
                       GetSQLValueString($_POST['referee'], "int"),
                       GetSQLValueString($_POST['venue'], "int"),
                       GetSQLValueString($_POST['match_id'], "int"));

  //do first query 
  mysql_query($updateSQL, $db) or die(mysql_error());

  // re define $updateSQL                           
  $updateSQL = sprintf("UPDATE matchscores SET match_id=%s, team=%s, score=%s WHERE matchscores_id=%s",
                       GetSQLValueString($_POST['match_id'], "int"),
                       GetSQLValueString($_POST['team1_id'], "int"),
                       GetSQLValueString($_POST['score1'], "int"), 
                      GetSQLValueString($_POST['mscore1'], "int"));

  //do second query 
  mysql_query($updateSQL, $db) or die(mysql_error());

   // again change the var                      
  $updateSQL = sprintf("UPDATE matchscores SET match_id=%s, team=%s, score=%s WHERE matchscores_id=%s",
                       GetSQLValueString($_POST['match_id'], "int"),
                       GetSQLValueString($_POST['team2_id'], "int"),
                       GetSQLValueString($_POST['score2'], "int"),
                       GetSQLValueString($_POST['mscore2'], "int"));
  //do third query 
  mysql_query($updateSQL, $db) or die(mysql_error());

 
/* no good
  mysql_select_db($database_db, $db);
  $Result1 = mysql_query($updateSQL, $db) or die(mysql_error());
*/

........

Report · Sep 09, 2010

It also look like you will over write the second query with the third. since they would hold the same match id

if the match id is known you should insert them instead, then you have two rows per match id

UPDATE and SET simply overwrite existing data

$sql = " INSERT INTO matchscores VALUES ('id', 'value1', 'value2'......);"

Report · Sep 13, 2010

Hi,

I am inserting the match_id

Report · Sep 13, 2010

hi that format you gave me says

Query was empty

Report · Sep 13, 2010

the insert i posted was more of an example, i dont know the

structure of the table to make it work.

I sent you a pm

Report · Sep 14, 2010

I have tried this now:

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

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "fixtures")) {
if($Submit){
for($i=0;$i<$count;$i++){

mysql_select_db($database_db, $db);
  $updateSQL1 = "UPDATE matches SET date='$date[$i]', time='$time[$i]', referee_id='$referee[$i]', venue_id='$venue[$i]' WHERE match_id=$match_id[$i]";
                            
  //do first query 
/*  mysql_query($updateSQL1, $db) or die(mysql_error());

                            
  $updateSQL2 = sprintf("UPDATE matchscores SET match_id=%s, team=%s, score=%s WHERE matchscores_id=%s",
                       GetSQLValueString($_POST['match_id'], "int"),
                       GetSQLValueString($_POST['team1_id'], "int"),
                       GetSQLValueString($_POST['score1'], "int"),
                       GetSQLValueString($_POST['mscore1'], "int"));
                            
  mysql_query($updateSQL2, $db) or die(mysql_error());
                            
  $updateSQL3 = sprintf("UPDATE matchscores SET match_id=%s, team=%s, score=%s WHERE matchscores_id=%s",
                       GetSQLValueString($_POST['match_id'], "int"),
                       GetSQLValueString($_POST['team2_id'], "int"),
                       GetSQLValueString($_POST['score2'], "int"),
                       GetSQLValueString($_POST['mscore2'], "int"));
               
  mysql_query($updateSQL3, $db) or die(mysql_error());*/

}
}

/*  mysql_select_db($database_db, $db);
  $Result1 = mysql_query($updateSQL, $db) or die(mysql_error());*/

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

And the html code:

<?php // Count table rows
$count=mysql_num_rows($match_fixtures);
?>
        <table width="100%" border="0" cellspacing="0" cellpadding="0">
          <tr>
            <td><table width="100%" border="0" cellspacing="0" cellpadding="0">
              <tr>
                
                <td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Date</div></td>
                <td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Home</div></td>
                <td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Score</div></td>
                <td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Away</div></td>
                <td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Kick-Off</div></td>
                <td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Venue</div></td>
                <td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Referee</div></td>
                <td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Update</div></td>
              </tr>

              <?php do { ?>
              <?php $id[]=$row_match_fixtures['match_id']; ?>
 <form action="<?php echo $editFormAction; ?>" method="POST" name="fixtures"> 
                <tr>
                 
                  <td><div align="center"><span id="sprytextfield1">
                    <label>
                    <input name="date[]" type="text" class="date" id="date" size="8" style="text-align:center" value="<?php echo $row_match_fixtures['shortDate']; ?>" />
                    <span class="textfieldRequiredMsg">A value is required.</span>                            
                    </label>
                    </span></div></td>
                  <td><div align="center"><a href="match-player.php?id=<?php echo $row_match_fixtures['match_id']; ?>&team=<?php echo $row_match_fixtures['team1_id']; ?>"><?php echo $row_match_fixtures['team1_name']; ?></a></div></td>
                  <td><div align="center"><input name="s1[]" type="text" value="<?php echo $row_match_fixtures['score1']; ?>" size="1" style="text-align:center" id="s1" /> 
                  v <input name="s2[]" type="text" value="<?php echo $row_match_fixtures['score2']; ?>" size="1" style="text-align:center" id="s2" />
                  </div></td>
                  <td><div align="center"><a href="match-player.php?id=<?php echo $row_match_fixtures['match_id']; ?>&team=<?php echo $row_match_fixtures['team2_id']; ?>"><?php echo $row_match_fixtures['team2_name']; ?></a></div></td>
                  <td><div align="center"><span id="sprytextfield2">
                  <label>
                  <input name="time[]" type="text" size="3" value="<?php echo $row_match_fixtures['time']; ?>" style="text-align:center" id="time" />
                  </label>
                  <span class="textfieldInvalidFormatMsg">Invalid format.</span><span class="textfieldRequiredMsg">A value is required.</span></span></div></td>
                  <td><div align="center"><span id="spryselect1">
                    <label>
                    <select name="venue[]" id="venue">
                      <?php
do {  
?>
                      <option value="<?php echo $row_venue['venue_id']?>"<?php if (!(strcmp($row_venue['venue_id'], $row_venue['venue_name']))) {echo "selected=\"selected\"";} ?>><?php echo $row_venue['venue_name']?></option>
                      <?php
} while ($row_venue = mysql_fetch_assoc($venue));
  $rows = mysql_num_rows($venue);
  if($rows > 0) {
      mysql_data_seek($venue, 0);
       $row_venue = mysql_fetch_assoc($venue);
  }
?>
                    </select>
                    </label>
                    <span class="selectRequiredMsg">Please select an item.</span></span></div></td>
                  <td><div align="center"><span id="spryselect2">
                    <label>
                    <select name="referee[]" id="referee">
                      <?php
do {  
?>
                      <option value="<?php echo $row_referee['ref_id']?>"<?php if (!(strcmp($row_referee['ref_id'], $row_referee['sname']))) {echo "selected=\"selected\"";} ?>><?php echo $row_referee['fname']?> <?php echo $row_referee['sname']?></option>
                      <?php
} while ($row_referee = mysql_fetch_assoc($referee));
  $rows = mysql_num_rows($referee);
  if($rows > 0) {
      mysql_data_seek($referee, 0);
       $row_referee = mysql_fetch_assoc($referee);
  }
?>
                    </select>
                    </label>
                    <span class="selectRequiredMsg">Please select an item.</span></span></div></td>

                  <td><div align="center"><input name="Submit" type="submit" id="Submit" value="Save" /></div></td>

           
                </tr>
         <input name="match_id[]" type="hidden" id="match_id" value="<?php echo $row_match_fixtures['match_id']; ?>" />
        <input name="mscore1[]" type="hidden" id="mscore1" value="<?php echo $row_match_fixtures['mscore1']; ?>" />
        <input name="mscore2[]" type="hidden" id="mscore2" value="<?php echo $row_match_fixtures['mscore2']; ?>" />
        <input name="team1_id[]" type="hidden" id="team1_id" value="<?php echo $row_match_fixtures['team1_id']; ?>" />
        <input name="score1[]" type="hidden" id="score1" value="<?php echo $row_match_fixtures['score1']; ?>" />
        <input name="team2_id[]" type="hidden" id="team2_id" value="<?php echo $row_match_fixtures['team2_id']; ?>" />
        <input name="score2[]" type="hidden" id="score2" value="<?php echo $row_match_fixtures['score2']; ?>" />
        
        <input type="hidden" name="MM_update" value="fixtures" />                  
</form> 
                <?php } while ($row_match_fixtures = mysql_fetch_assoc($match_fixtures)); ?>
      
            </table></td>
          </tr>
        </table>

But it still does not work, the table does not change.

None of the values change

Report · Sep 23, 2010

Trying this

$limit = count($match_id);

for($i=0;$i<$limit;$i++) {
if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "fixtures")) {
  $updateSQL = sprintf("UPDATE matches SET `date`=%s, `time`=%s, referee_id=%s, venue_id=%s WHERE match_id=%s",
                       GetSQLValueString($_POST['date[]'], "date"),
                       GetSQLValueString($_POST['time[]'], "text"),
                       GetSQLValueString($_POST['referee[]'], "int"),
                       GetSQLValueString($_POST['venue[]'], "int"),
                       GetSQLValueString($_POST['match_id[]'], "int"));

  mysql_select_db($database_db, $db);
  $Result1 = mysql_query($updateSQL, $db) or die(mysql_error());

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

Not working :'(

Report · Sep 09, 2010

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$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;
}
}

$MMColParam_Recordset1 = "-1";
if (isset(Request.QueryString("search"))) {
$MMColParam_Recordset1 = Request.QueryString("search");
}
$MMColParam2_Recordset1 = "-1";
if (isset(Request.QueryString("search"))) {
$MMColParam2_Recordset1 = Request.QueryString("search");
}
$MMColParam3_Recordset1 = "-1";
if (isset(Request.QueryString("search"))) {
$MMColParam3_Recordset1 = Request.QueryString("search");
}
$MMColParam4_Recordset1 = "-1";
if (isset(Request.QueryString("search"))) {
$MMColParam4_Recordset1 = Request.QueryString("search");
}
$MMColParam5_Recordset1 = "-1";
if (isset(Request.QueryString("search"))) {
$MMColParam5_Recordset1 = Request.QueryString("search");
}
$MMColParam6_Recordset1 = "-1";
if (isset(Request.QueryString("search"))) {
$MMColParam6_Recordset1 = Request.QueryString("search");
}
$MMColParam7_Recordset1 = "-1";
if (isset(Request.QueryString("search"))) {
$MMColParam7_Recordset1 = Request.QueryString("search");
}
$MMColParam8_Recordset1 = "-1";
if (isset(Request.QueryString("search"))) {
$MMColParam8_Recordset1 = Request.QueryString("search");
}
$MMColParam9_Recordset1 = "-1";
if (isset(Request.QueryString("search"))) {
$MMColParam9_Recordset1 = Request.QueryString("search");
}
mysql_select_db($database_bridesmaidsrack_db, $bridesmaidsrack_db);
$query_Recordset1 = sprintf("SELECT * FROM donations WHERE posting_title LIKE %s OR size LIKE %s OR dress_condition LIKE %s OR dress_length LIKE %s OR color LIKE %s OR city LIKE %s OR state LIKE %s OR email LIKE %s OR additional_details LIKE %s ORDER BY posting_title ASC", GetSQLValueString("%" . $MMColParam_Recordset1 . "%", "text"),GetSQLValueString("%" . $MMColParam2_Recordset1 . "%", "text"),GetSQLValueString("%" . $MMColParam3_Recordset1 . "%", "text"),GetSQLValueString("%" . $MMColParam4_Recordset1 . "%", "text"),GetSQLValueString("%" . $MMColParam5_Recordset1 . "%", "text"),GetSQLValueString("%" . $MMColParam6_Recordset1 . "%", "text"),GetSQLValueString("%" . $MMColParam7_Recordset1 . "%", "text"),GetSQLValueString("%" . $MMColParam8_Recordset1 . "%", "text"),GetSQLValueString("%" . $MMColParam9_Recordset1 . "%", "text"));
$Recordset1 = mysql_query($query_Recordset1, $bridesmaidsrack_db) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

This may help,