Skip navigation links

MySQL Forums :: PHP :: Using Selected Rows


Advanced Search

Using Selected Rows
Posted by: Neil Brazier ()
Date: August 14, 2013 03:53AM

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.

Options: ReplyQuote


Subject Written By Posted
Using Selected Rows Neil Brazier 08/14/2013 03:53AM
Re: Using Selected Rows Rick James 08/31/2013 06:04PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.