Help/Suggestions Required - Working With Multiple Rows

[Y2Neil]

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 the users 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 and add or update their availability for it.

 

I had created something that lists all the upcoming events in the next week each with a button, but when clicking that button, it will only take the information from the last row of data, not the one the button is assigned to.

 

I'm sure I'm doing this wrong, there is probably a much better/easier way to do this, could any of you lend me a suggestion?

 

My current code is as below. Any help is greatly appreciated.

$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>";

[gristoi]

at a glance it looks loike you are not closing off your form after the else satement, but you are closing it off before:

</form></td>";
		} else {

so your last part of the code should be

<label><input name=\"ChaAv\" type=\"Submit\" id=\"ChaAv\" value=\"Change Availability\"></label></td>";

//changed to this

<label><input name=\"ChaAv\" type=\"Submit\" id=\"ChaAv\" value=\"Change Availability\"></label></form></td>";

[Y2Neil]

Thanks! Didn't spot that.

 

It only seemed to make the top button clickable, the others did nothing.

 

I think I need to rethink this whole thing...