Using Selected Rows

[Neil Brazier]

Hello all,

I run a website for my football (soccer) team and I've created a section where players can submit their availability for a match or training however it needs updating.

At the moment they can only register for the next upcoming fixture, but what I want is for them to be able to select any fixture within the next seven days.

I created this:

$current_user =  wp_get_current_user();
$playerid = $_POST['pid'];
$match = $_POST['Date'];
$curav = $_POST['CurrentA'] ;
$type = $_POST['Type'] ;

//insert data
if ($_POST['Type']=="Match")
{
$sql = "INSERT INTO `matchday` (`matchdate`,`player_id`,`available`) VALUES ('$match',$playerid,$_POST[available])";
	if (!empty($playerid)) {
		if (!mysql_query($sql,$con)) {
			die('Error: ' . mysql_error());
		} else {
			$r++;
			echo "<p>$r record(s) added. Your availability has been made known to the management.</p>";
		}
	
}
} 
if ($_POST['Type']=="Training") 
{
$sql = "INSERT INTO `training` (`traindate`,`player_id`,`available`) VALUES ('$match',$playerid,$_POST[available])";
	if (!empty($playerid)) {
		if (!mysql_query($sql,$con)) {
			die('Error: ' . mysql_error());
		} else {
			$r++;
			echo "<p>$r record(s) added. Your availability has been made known to the management.</p>";
		}
	
}
}
if ($_POST['ChaAv'])
{
	if($curav=="Yes" && $type=="Training") {
						//Change to no
						$sql = "UPDATE `training` SET `available`= 'No' WHERE `player_id` = '$playerid' and `traindate` = $TDate";
						if (!empty($playerid)) {
							if (!mysql_query($sql,$con)) {
								die('Error: ' . mysql_error());
							} else {
								$r++;
								echo "<p>$r record(s) amended. Your availability has been updated.</p>";
							}
						}
											} 
	elseif($curav=="No" && $type=="Training" {
							  //Change to yes
												}
	elseif($curav=="Yes" && $type=="Match" {
							//Change to no
											}
	elseif($curav=="No" && $type=="Match" {
							//Change to yes
											}
}

$availability = "SELECT distinct `match`.`date` as 'SysDate', DATE_FORMAT(`match`.`date`, '%d/%m/%Y') as 'Date', DAYNAME(`match`.`date`) as 'Day', case
when (select `matchday`.`player_id` from `matchday` join `player` on `player`.`player_id` = `matchday`.`player_id` where `player`.`wp_user` = 'Y2Neil' AND `matchday`.`matchdate` =
`match`.`date`) THEN (SELECT `matchday`.`available` from `matchday` join `player` on `player`.`player_id` = `matchday`.`player_id` where `player`.`wp_user` = 'Y2Neil' and `matchday`.`matchdate` = `match`.`date`)
ELSE 'Empty'
END AS 'Availability',
CASE
WHEN `match`.`date` IS NOT NULL THEN 'Match'
ELSE 'Training'
END AS 'Type'

FROM `match`

WHERE (`match`.`date` BETWEEN current_date() AND DATE_ADD(CURRENT_DATE, interval 7 day)) AND `match`.`match_id` NOT IN ('416','421')

UNION ALL

SELECT `training_day`.`training_date` as 'SysDate', DATE_FORMAT(`training_day`.`training_date`, '%d/%m/%Y') as 'Date', DAYNAME(`training_day`.`training_date`) as 'Day',
case
when (select `training`.`player_id` from `training` join `player` on `player`.`player_id` = `training`.`player_id` where `player`.`wp_user` = 'Y2Neil' AND `training`.`traindate` =
`training_day`.`training_date`) THEN (SELECT `training`.`available` FROM `training` join `player` on `player`.`player_id` = `training`.`player_id` WHERE `player`.`wp_user` = 'Y2Neil' and `training`.`traindate` = `training_day`.`training_date`)
ELSE 'Empty'
END AS 'Availability',
CASE
WHEN `training_day`.`training_date` IS NOT NULL THEN 'Training'
ELSE 'Match'
END AS 'Type'

FROM `training_day`

WHERE `training_day`.`training_date` BETWEEN current_date() AND DATE_ADD(Current_date, interval 6 day)";

$available = mysql_query($availability, $con) or die(mysql_error());

$players = "SELECT `player_id`, `player_name` FROM `player` WHERE `player`.`wp_user` = '$current_user->user_login' ORDER BY `surname` ASC";

$player = mysql_query($players, $con) or die(mysql_error());

echo "<table align='center' valign='top'>";
echo "<form name=\"insert_availability\" method=\"post\" action=\"\">";	
while($prow = mysql_fetch_array($player))
{
	while($row = mysql_fetch_array($available))
	  {
	  echo "<tr>";
		if($row['Availability']=="Empty") { 
		echo "<td width=\"75%\" align=\"center\" valign=\"middle\"><input type=\"hidden\" size=\"4\" readonly=\"readonly\" name=\"pid\" id=\"pid\" value=\"" . $prow['player_id'] . "\">
			<p>There is an upcoming " . $row['Type'] . " day on " . $row['Day'] . ", " . $row['Date'] . " - are you available?<input type=\"hidden\" size=\"4\" readonly=\"readonly\" name=\"Date\" id=\"Date\" value=\"" . $row['SysDate'] . "\">";
		echo "<label><select name=\"available\" id=\"available\"><option value=\"2\">Yes</option><option value=\"1\">No</option></label> <label><input name=\"SubAv\" type=\"Submit\" id=\"SubAv\" value=\"Submit Availability\"></label></p></form></td>";
		} else {
			echo "<td width=\"75%\" align=\"center\" valign=\"middle\"><b>" . $row['Type'] . ":</b> " . $row['Day'] . ", " . $row['Date'] . " - You have already made yourself available for this date. 
			<input type=\"hidden\" size=\"4\" readonly=\"readonly\" name=\"CurrentA\" id=\"CurrentA\" value=\"" . $row['Availability'] . "\"> 
			<input type=\"hidden\" size=\"4\" readonly=\"readonly\" name=\"Type\" id=\"Type\" value=\"" . $row['Type'] . "\">
			<input type=\"hidden\" size=\"4\" readonly=\"readonly\" name=\"TDate\" id=\"TDate\" value=\"" . $row['Date'] . "\">
			<label><input name=\"ChaAv\" type=\"Submit\" id=\"ChaAv\" value=\"Change Availability\"></label></td>";
				}
	  echo "</tr>";
	  }
}
echo "</table>";

But when you go to add or update availability, it will only update it for the last row, not the specific one.

Can anyone lend me a hand or suggest a better way of doing it? I want something simple, clean and concise!

I appreciate any support.