Update Multiple records by date

[stb74]

I enter matches into a form by date.  If I then click on the date I dislay that matches that are scheduled on that date.

I have the option of changing the date or scores in the matches.

The only part that is not working at the moment is the date field.  When I click to update the records the date field is being blanked out.

Here is the form for displaying the selected matches.

[code]
<?php

$date = $_REQUEST['date'];

$get_matches = mysql_query("
SELECT LM.matchDate AS matchdate,
DAYOFMONTH(LM.matchDate) AS dayofmonth,
MONTH(LM.matchDate) AS month,
YEAR(LM.matchDate) AS year,
LM.matchID AS mid,
LM.matchHomeID AS homeid,
LM.matchAwayID AS awayid,
LM.matchHomeGoals AS homegoals,
LM.matchAwayGoals AS awaygoals,
LM.matchInfo AS info
FROM leaguestats_matches LM
WHERE
LM.matchDivisionID = '$divisionid' AND
LM.matchDate = '$date'
", $dbconnect)
or die(mysql_error());

//
//Query to get date
//
$get_match = mysql_query("
SELECT DAYOFMONTH(LM.matchDate) AS dayofmonth,
MONTH(LM.matchDate) AS month,
YEAR(LM.matchDate) AS year
FROM leaguestats_matches LM
WHERE LM.matchDivisionID = '$divisionid' AND
LM.matchDate = '$date'
LIMIT 1
", $dbconnect)
or die(mysql_error());

$datedata = mysql_fetch_array($get_match);

mysql_free_result($get_match);

$get_teams = mysql_query("
SELECT teamID AS id,
teamName AS name
FROM leaguestats_teams
WHERE teamdivisionID = '$divisionid'
ORDER BY teamName
", $dbconnect)
or die(mysql_error());

?>

<form method="post" action="<?php echo "$PHP_SELF?sessioid=$sessio" ?>">
<h1>Modify matches</h1>
<?php echo "$date"; ?>
<table cellspacing="1" cellpadding="1" border="0">
  <tr valign="top">
<td align="left" valign="top">Date and time: </td>
<td align="left" valign="top"><select name="day">
<?php
//Print the days
for($i = 1 ; $i < 32 ; $i++)
{
if($i<10)
{
$i = "0".$i;
}
if($datedata['dayofmonth'] == $i)
echo "<option value=\"$i\" SELECTED>$i</option>\n";
else
echo "<option value=\"$i\">$i</option>\n";
}
?>
  </select>
  &nbsp;/&nbsp;
  <select name="month">
<?php
//Print the months
for($i = 1 ; $i < 13 ; $i++)
{
if($i<10)
{
$i = "0".$i;
}
if($datedata['month'] == $i)
echo "<option value=\"$i\" SELECTED>$i</option>\n";
else
echo "<option value=\"$i\">$i</option>\n";
}
?>
  </select>
  &nbsp;/&nbsp;
  <select name="year">
<?php
//Print the years
for($i = 2006 ; $i < 2020 ; $i++)
{
if($i<10)
{
$i = "0".$i;
}
if($datedata['year'] == $i)
echo "<option value=\"$i\" SELECTED>$i</option>\n";
else
echo "<option value=\"$i\">$i</option>\n";
}
?>
  </select>            </td>
  </tr>
</table>
<table cellspacing="1" cellpadding="1" border="0">
<tr>

<td align="left" valign="middle"><b>Hometeam</b></td>
<td align="left" valign="middle"><b>Awayteam</b></td>
<td align="center" valign="middle"><b>GH</b></td>
<td align="center" valign="middle"><b>GA</b></td>
<td align="center" valign="middle"><b>Info</b></td>
</tr>

<?php

//
//Lets get all the matches from selected date to the form
//
$i = 0;
while($matchdata = mysql_fetch_array($get_matches))
{
//
//Back to line 0 in the query if not the first loop
//
if($i>0)
mysql_data_seek($get_teams, 0);

echo'
<tr>
<td align="left" valign="middle">
';

echo"<select name=\"home[$i]\">";

while($data = mysql_fetch_array($get_teams))
{
if($matchdata['homeid'] == $data['id'])
echo"<option value=\"$data[id]\" SELECTED>$data[name]</option>\n";
}

echo'
</select>
</td>
<td align="left" valign="middle">
';

//
//Back to line 0 in the query
//
mysql_data_seek($get_teams, 0);

echo"<select name=\"away[$i]\">";

while($data = mysql_fetch_array($get_teams))
{
if($matchdata['awayid'] == $data['id'])
echo"<option value=\"$data[id]\" SELECTED>$data[name]</option>\n";
}

echo"
</select>
</td>
<td align=\"center\" valign=\"middle\">
<input type=\"text\" name=\"home_goals[$i]\" size=\"2\" value=\"$matchdata[homegoals]\"></td>
<td align=\"center\" valign=\"middle\">
<input type=\"text\" name=\"away_goals[$i]\" size=\"2\" value=\"$matchdata[awaygoals]\"></td>
<td align=\"center\" valign=\"middle\">
<input type=\"text\" name=\"info[$i]\" size=\"2\" value=\"$matchdata[info]\"></td>
</tr>
<input type=\"hidden\" name=\"mid[$i]\" size=\"2\" value=\"$matchdata[mid]\">
";
$i++;
}

mysql_free_result($get_matches);
mysql_free_result($get_teams);

?>
</table>

You can't change home or away team in this mode. <br>
Click the match to modify home/away team.<br>
<br>
<input type="hidden" name="qty" value="<?= $i ?>">
<br>
<input type="submit" name="modifyall_submit" value="Click here to modify the matches">
</form>

[/code]

Here is the code for updating the database.

[code]

<?php

//
//Updates the last_updated column in preferences
//
mysql_query("
UPDATE `leaguestats_divisions`
SET `last_updated` = CURRENT_TIMESTAMP
WHERE `divisionID` = '$divisionid'" , $dbconnect)
or die(mysql_error());

$year = $_POST['year'];
$month = $_POST['month'];
$day = $_POST['day'];
$qty = $_POST['qty'];

//
//Check the submitted form
//
$i = 0;

while($i < $qty)
{
$mid = $_POST['mid'];
$dateandtime = $year."-".$month."-".$day;
$home = $_POST['home'];
$away = $_POST['away'];
$home_goals = $_POST['home_goals'];
$away_goals = $_POST['away_goals'];
$info = $_POST['info'];

//
//Set default
//
$home_winner = -1;
$home_loser = -1;
$home_tie = -1;
$away_winner = -1;
$away_loser = -1;
$away_tie = -1;

//
//Home wins
//
if($home_goals[$i] > $away_goals[$i])
{
$home_winner = $home[$i];
$away_loser = $away[$i];
}
//
//Away wins
//
elseif($home_goals[$i] < $away_goals[$i])
{
$away_winner = $away[$i];
$home_loser = $home[$i];
}
//
//Draw
//
elseif($home_goals[$i] == $away_goals[$i])
{
$home_tie = $home[$i];
$away_tie = $away[$i];
}

if($home_goals[$i] == '' || $away_goals[$i] == '' || !is_numeric($home_goals[$i]) || !is_numeric($away_goals[$i]))
{
mysql_query("
UPDATE leaguestats_matches SET
matchDivisionID = '$divisionid',
matchSeasonID = '$seasonid',
matchDate = '$dateandtime',
matchHomeID = '$home[$i]',
matchAwayID = '$away[$i]',
matchHomeWin = '-1',
matchHomeLose = '-1',
matchAwayWin = '-1',
matchAwayLose = '-1',
matchHomeDraw = '-1',
matchAwayDraw = '-1',
matchHomeGoals = NULL,
matchAwayGoals = NULL,
matchInfo = '$info[$i]'
WHERE matchID = '$mid[$i]'
", $dbconnect)
or die(mysql_error());
}
else
{
mysql_query("
UPDATE leaguestats_matches SET
matchDivisionID = '$divisionid',
matchSeasonID = '$seasonid',
matchDate = '$dateandtime',
matchHomeID = '$home[$i]',
matchAwayID = '$away[$i]',
matchHomeWin = '$home_winner',
matchHomeLose = '$home_loser',
matchAwayWin = '$away_winner',
matchAwayLose = '$away_loser',
matchHomeDraw = '$home_tie',
matchAwayDraw = '$away_tie',
matchHomeGoals = '$home_goals[$i]',
matchAwayGoals = '$away_goals[$i]',
matchInfo = '$info[$i]'
WHERE matchID = '$mid[$i]'
", $dbconnect)
or die(mysql_error());
}

$i++;
}

header("Location: $PHP_SELF?sessioid=$sessio");

?>
[/code]

[blear]

The actual format that your mysql db requires for date/time fields is very tricky. If not received in the proper format it will just leave it blank, or set it to zeroes.

Best bet is to query the DB for a date/time field and dump it to the screen, that will give you a better idea on what the database is expecting to see.

Personally I just use unix (integer) timestamp for all my date/time values in all of my tables, and convert it with the date() function when I intend to output it.