Jump to content

NiallAA

Members
  • Posts

    13
  • Joined

  • Last visited

NiallAA's Achievements

Newbie

Newbie (1/5)

0

Reputation

1

Community Answers

  1. Versions: Mysql 5.5, PHP 5.4 Evening folks, To simplify this request, I have a database of competition results. The table structure is as follows id (INT), date (DATE), pointsfor (TINYINT), pointsagainst (TINYINT) To summarise, if pointsfor > pointsagainst, the outcome on that date is considered a win. The result I'm looking to display is the 'winningest' run, i.e the start and end date of the most victories recorded in consecutive date order. I have no idea where to start with this, as I have yet to deal much with dates. Any help would be greatly appreciated. Thank you.
  2. Hi folks, Trying to run a MySQL UPDATE statement on a small table which basically has three fields. AppID, AppMatchID and AppPlayed. I have a form which sends two values via POST, which are app_played and matchid. app_played is a checkbox value. I am trying to update multiple rows at once using a submit button in the form, but coming into problems where it concerns where to check isset in the foreach loop. Here is my form: <form method="post" action="matchdata.php"> <input type="hidden" name="matchid" value="<?php echo $matchid ?>"> <?php // Get former players involved in opposition team $get_former_players = mysql_query(" SELECT L.OppID AS id, CONCAT(P.PlayerFirstName, ' ', P.PlayerLastName) AS name, L.OppPlayed AS played FROM db_opp_players L, tplss_players P WHERE L.OppMatchID = $matchid AND L.OppPlayerID = P.PlayerID ORDER BY P.PlayerLastName ASC, P.PlayerFirstName ASC",$connection) or die(mysql_error); while($opp_player_data = mysql_fetch_array($get_former_players)) { echo" <input type=\"hidden\" name=\"opp_id[]\" value=\"$opp_player_data[id]\"> $opp_player_data[name] "; if($opp_player_data['played'] == 1) echo"<input type=\"checkbox\" name=\"opp_played[]\" value=\"1\" CHECKED>"; else echo"<input type=\"checkbox\" name=\"opp_played[]\" value=\"1\">"; echo"Played?"; echo" <a href=\"matchdata.php?action=remove_from_opp_players&id=$opp_player_data[id]&matchid=$matchid\"> <img src=\"images/remove.gif\" border=\"0\" ALT=\"Remove\"></a> <br> "; } ?> <input type="submit" name="modify_opp_players" value="Update"> <? mysql_free_result($get_former_players); ?> </form> And here is my update command, after receiving POST information from the submit button. $opp_id = $_POST['opp_id']; $matchid = $_POST['matchid']; $opp_played = $_POST['opp_played']; foreach($opp_id as $key=>$played) { if(isset($opp_played['$key'])) { mysql_query(" UPDATE db_opp_players SET OppPlayed = '1' WHERE OppID = '$opp_id[$key]' ",$connection) or die(mysql_error()); } else { mysql_query(" UPDATE db_opp_players SET OppPlayed = '0' WHERE OppID = '$opp_id[$key]' ",$connection) or die(mysql_error()); } } Can anyone offer me a simpler solution, or better still something that works? At the moment the checkboxes either don't update, or only update the first entry.
  3. As quick as I posted this I sussed it out. $j = 7;
  4. Hi all, I am trying to display a set of results in a table, and use the MySQL row index as a numbered bullet as the first column in each row. However, I intend to use two sets of six results. The first set of results should be numbered 1 to 6, the second set 7 to 12. At the moment, it returns 1 to 6, followed by another 1 to 6, understandably. Is there something simple I could be doing to the second query to change the index so that it starts at 7? Here is the code I am using to display the result of the second query: $j=1; $i=0; while($i< $qty) { if(isset($draw_line)) { for($k = 0 ; $k < sizeof($draw_line) ; $k++) { if($draw_line[$k] == $i) { $templine_width = $tb_width-20; echo" <tr> <td height=\"5\" colspan=\"20\" align=\"center\" valign=\"middle\"> <img src=\"images/line.gif\" width=\"$templine_width\" height=\"5\" ALT=\"\"><br> </td> </tr> "; } } } echo" <tr style=\"border-top: 0.5px;\"> <td align=\"left\" valign=\"middle\" bgcolor=\"$bg1\"><b>$j.</b></td> <td align=\"left\" valign=\"middle\" bgcolor=\"$bg1\"> "; if($team[$i] == "Dundee United") echo"<b>UNITED</b>"; else echo"<a href=\"opponent.php?opp=$teamid[$i]\">$team[$i]</a>"; echo" </td> <td align=\"center\" valign=\"middle\" bgcolor=\"$bg2\">$pld[$i]</td> <td align=\"center\" valign=\"middle\" bgcolor=\"$bg1\">$wins[$i]</td> <td align=\"center\" valign=\"middle\" bgcolor=\"$bg1\">$draws[$i]</td> <td align=\"center\" valign=\"middle\" bgcolor=\"$bg1\">$loses[$i]</td> <td align=\"center\" valign=\"middle\" bgcolor=\"$bg1\">$goals_for[$i]</td> <td align=\"center\" valign=\"middle\" bgcolor=\"$bg1\">$goals_against[$i]</td> <td align=\"center\" valign=\"middle\" bgcolor=\"$bg1\">$diff[$i]</td> <td align=\"center\" valign=\"middle\" bgcolor=\"$bg2\">$points[$i]</td> </tr> "; $i++; $j++; } Niall
  5. Thank you very much, that's a great help. I shall look into the use of mysql_real_escape_string once again. Whatever I did was not successful the first time. These textareas are to contain basic html, in particular <a href=""> tags in the middle of several paragraphs. The inverted commas seem to almost always inherit a \ when using mysql_real_escape_string, and add a further \ each time the box is saved. It'll take a bit of trial and error I suppose, but needs must, as I won't be on PHP 5.3 forever and I shall need to look into moving with the times. Thank you very much for your assistance.
  6. Thanks. I have done so Sanitising (I guess this should also be done using nl2br? ): $matchdata['report'] = str_replace('<br>',"\r\n", $matchdata['report']); User input field: <textarea name="report" cols="45" rows="15"><?php echo $matchdata['report'] ?></textarea>
  7. Hi all, This is a bit of a confusing one. I have been using a script for a while now, very basic, which submits a textarea via a form to a MySQL database using UPDATE. It's never been a problem on the GoDaddy servers I'm using which run on PHP 5.3 and MySQL 5.5. I am trying to use the same script on a 1&1 internet hosted package, which uses PHP 5.4 and MySQL 5.1. It throws up MySQL syntax errors when I submit the exact same form. Before the UPDATE statement, I define a variable using $_POST info and strip the white space and replace the line breaks. $report = str_replace("\r\n",'<br>', trim($_POST['report'])); Is this just a case of differing MySQL versions? Niall
  8. print(Date("F jS Y")); will display: August 29th 2014 http://php.net/manual/en/function.date.php
  9. Thanks ginerjm. This is where I may need the advice. My knowledge of JOINs is very underdeveloped. I think I went round in circles somewhat just coming to the point I'm at with this particular one. If I may, could I give you the structure of the tables I am working with, and then a description of what I am trying to achieve? tplss_matches MatchID, MatchDateTime tplss_players PlayerID, PlayerName tplss_appearances AppearanceID, AppearancePlayerID, AppearanceMatchID tplss_substitutions SubstitutionID, SubstitutionPlayerIDIn, SubstitutionMatchID tplss_goals GoalID, GoalPlayerID, GoalMatchID In the context of the PHP coding thus far, $id is the current MatchID, which has been requested from the URL earlier in the code. What I want to display is a column of the players who have been selected to start the match in question. I then want to display, in the next column, a corresponding list of each player's total appearances up to and including that match (i.e. prior to and including the MatchDateTime). Total appearances are the sum of any entries for the PlayerID and the current MatchID, found in either tplss_appearances or tplss_substititions. Likewise, in the next corresponding column I want to show the player's goals scored, the sum of all entries match the PlayerID and the current MatchID in tplss_goals. It would look a little like this:
  10. This seems to be the bit that is causing the most delay: $get_starters = mysql_query(" SELECT P.PlayerID AS playerid, CONCAT(P.PlayerFirstName, ' ', P.PlayerLastName) AS name, P.PlayerNationID AS nation, P.PlayerPositionID as pos FROM tplss_players P, tplss_appearances A WHERE A.AppearancePlayerID = P.PlayerID AND A.AppearanceMatchID = '$id' ORDER BY P.PlayerPositionID ASC ",$connection) or die(mysql_error()); while($combstarters = mysql_fetch_array($get_starters)) { echo"<tr>"; echo"<td><a href=\"player.php?id=$combstarters[playerid]\">$combstarters[name]</a>"; if($combstarters['pos'] == 1) { echo" (GK)"; } echo"</td>"; $combpid = $combstarters['playerid']; echo" <td align=\"left\" style=\"vertical-align: middle;\"> <img src=\"images/flag_$combstarters[nation].jpg\" border=1> </td> "; $get_comb_apps = mysql_query("SELECT COUNT(A.AppearancePlayerID) AS apps FROM tplss_appearances A, tplss_matches M WHERE A.AppearancePlayerID = '$combpid' AND A.AppearanceMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate' ",$connection) or die(mysql_error()); $get_comb_ins = mysql_query("SELECT COUNT(S.SubstitutionPlayerIDIn) AS ins FROM tplss_substitutions S, tplss_matches M WHERE S.SubstitutionPlayerIDIn = '$combpid' AND S.SubstitutionMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate' ",$connection) or die(mysql_error()); while($combdata = mysql_fetch_array($get_comb_apps)) { while($idata = mysql_fetch_array($get_comb_ins)) { $totalapps = $combdata['apps'] + $idata['ins']; if($totalapps == 1) { echo"<td>$totalapps app (debut)</td>"; } else { echo"<td>$totalapps apps</td>"; } } } $get_goals_all = mysql_query(" SELECT COUNT(G.GoalPlayerID) AS total_goals FROM tplss_goals G, tplss_matches M WHERE G.GoalPlayerID = '$combpid' AND G.GoalMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate' AND G.GoalOwn != 1 GROUP BY G.GoalPlayerID ",$connection) or die(mysql_error()); if(mysql_num_rows($get_goals_all) == 0) { echo"<td> - </td>"; } while($combgoals = mysql_fetch_array($get_goals_all)) { if($combgoals['total_goals'] == 1) { echo"<td>$combgoals[total_goals] goal</td>"; } else { echo"<td>$combgoals[total_goals] goals</td>"; } } echo"</tr>"; } Is there a way I can do this without executing queries within loops? Thank you in advance to anyone who can help. What this page is, is a list of football players selected to play in a match. Next to them in a column is a list of their combined appearances (full appearances (apps) and partial appearances (ins)) and goals scored to date.
  11. Apologies if the title is quite vague, I suppose I am looking for some general advice on why some pages I write in PHP, which contain MySQL queries, might be running a bit slow. The following page takes up to 3 seconds to display: <?php session_start(); include('admin/user.php'); $connection = mysql_connect("$host","$user","$password") or die(mysql_error()); mysql_select_db("$txt_db_name",$connection) or die(mysql_error()); $id = $_REQUEST['id']; // MATCH INFO $get_details = mysql_query(" SELECT MatchDateTime AS date, DATE_FORMAT(MatchDateTime, '%Y-%m-%d') AS formatdate FROM tplss_matches WHERE MatchID = '$id' LIMIT 1",$connection) or die(mysql_error()); $factsdata = mysql_fetch_array($get_details); mysql_free_result($get_details); $matchdate = $factsdata['date']; $matchdate2 = $factsdata['formatdate']; // -----------SHOW FACTS ABOUT THE STARTING LINEUP--------- echo" <h5>Appearances & Goals To Date</h5> <table width=100%>"; $get_starters = mysql_query(" SELECT P.PlayerID AS playerid, CONCAT(P.PlayerFirstName, ' ', P.PlayerLastName) AS name, P.PlayerNationID AS nation, P.PlayerPositionID as pos FROM tplss_players P, tplss_appearances A WHERE A.AppearancePlayerID = P.PlayerID AND A.AppearanceMatchID = '$id' ORDER BY P.PlayerPositionID ASC ",$connection) or die(mysql_error()); $get_subbies = mysql_query(" SELECT P.PlayerID AS playerid, CONCAT(P.PlayerFirstName, ' ', P.PlayerLastName) AS name, P.PlayerNationID AS nation, P.PlayerPositionID as pos FROM tplss_players P, tplss_substitutions S WHERE S.SubstitutionPlayerIDIn = P.PlayerID AND S.SubstitutionMatchID = '$id' ORDER BY P.PlayerPositionID ASC ",$connection) or die(mysql_error()); while($combstarters = mysql_fetch_array($get_starters)) { echo"<tr>"; echo"<td><a href=\"player.php?id=$combstarters[playerid]\">$combstarters[name]</a>"; if($combstarters['pos'] == 1) { echo" (GK)"; } echo"</td>"; $combpid = $combstarters['playerid']; echo" <td align=\"left\" style=\"vertical-align: middle;\"> <img src=\"images/flag_$combstarters[nation].jpg\" border=1> </td> "; $get_comb_apps = mysql_query("SELECT COUNT(A.AppearancePlayerID) AS apps FROM tplss_appearances A, tplss_matches M WHERE A.AppearancePlayerID = '$combpid' AND A.AppearanceMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate' ",$connection) or die(mysql_error()); $get_comb_ins = mysql_query("SELECT COUNT(S.SubstitutionPlayerIDIn) AS ins FROM tplss_substitutions S, tplss_matches M WHERE S.SubstitutionPlayerIDIn = '$combpid' AND S.SubstitutionMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate' ",$connection) or die(mysql_error()); while($combdata = mysql_fetch_array($get_comb_apps)) { while($idata = mysql_fetch_array($get_comb_ins)) { $totalapps = $combdata['apps'] + $idata['ins']; if($totalapps == 1) { echo"<td>$totalapps app (debut)</td>"; } else { echo"<td>$totalapps apps</td>"; } } mysql_free_result($get_comb_ins); } mysql_free_result($get_comb_apps); $get_goals_all = mysql_query(" SELECT COUNT(G.GoalPlayerID) AS total_goals FROM tplss_goals G, tplss_matches M WHERE G.GoalPlayerID = '$combpid' AND G.GoalMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate' AND G.GoalOwn != 1 GROUP BY G.GoalPlayerID ",$connection) or die(mysql_error()); if(mysql_num_rows($get_goals_all) == 0) { echo"<td> - </td>"; } while($combgoals = mysql_fetch_array($get_goals_all)) { if($combgoals['total_goals'] == 1) { echo"<td>$combgoals[total_goals] goal</td>"; } else { echo"<td>$combgoals[total_goals] goals</td>"; } } mysql_free_result($get_goals_all); echo"</tr>"; } while($combsubbies = mysql_fetch_array($get_subbies)) { echo"<tr>"; echo"<td><a href=\"player.php?id=$combsubbies[playerid]\">$combsubbies[name]</a> (sub)"; if($combsubbies['pos'] == 1) { echo" (GK)"; } echo"</td>"; $combpid = $combsubbies['playerid']; echo" <td align=\"left\" style=\"vertical-align: middle;\"> <img src=\"images/flag_$combsubbies[nation].jpg\" border=1> </td> "; $get_comb_apps = mysql_query("SELECT COUNT(A.AppearancePlayerID) AS apps FROM tplss_appearances A, tplss_matches M WHERE A.AppearancePlayerID = '$combpid' AND A.AppearanceMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate' ",$connection) or die(mysql_error()); $get_comb_ins = mysql_query("SELECT COUNT(S.SubstitutionPlayerIDIn) AS ins FROM tplss_substitutions S, tplss_matches M WHERE S.SubstitutionPlayerIDIn = '$combpid' AND S.SubstitutionMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate' ",$connection) or die(mysql_error()); while($combdata = mysql_fetch_array($get_comb_apps)) { while($idata = mysql_fetch_array($get_comb_ins)) { $totalapps = $combdata['apps'] + $idata['ins']; if($totalapps == 1) { echo"<td>$totalapps app (debut)</td>"; } else { echo"<td>$totalapps apps</td>"; } } mysql_free_result($get_comb_ins); } mysql_free_result($get_comb_apps); $get_goals_all = mysql_query(" SELECT COUNT(G.GoalPlayerID) AS total_goals FROM tplss_goals G, tplss_matches M WHERE G.GoalPlayerID = '$combpid' AND G.GoalMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate' AND G.GoalOwn != 1 GROUP BY G.GoalPlayerID ",$connection) or die(mysql_error()); if(mysql_num_rows($get_goals_all) == 0) { echo"<td> - </td>"; } while($combgoals = mysql_fetch_array($get_goals_all)) { if($combgoals['total_goals'] == 1) { echo"<td>$combgoals[total_goals] goal</td>"; } else { echo"<td>$combgoals[total_goals] goals</td>"; } } mysql_free_result($get_goals_all); echo"</tr>"; } echo"</table>"; // -----------SHOW FACTS ABOUT THE STARTING LINEUP--------- echo"<br> <h5>Starting Lineup</h5> <table width=100%>"; // GET YOUNGEST PLAYER IN STARTING LINEUP $get_youngest_player = mysql_query(" SELECT P.PlayerDOB AS dob, DATE_FORMAT(P.PlayerDOB, '%d/%m/%Y') AS birth, CONCAT(P.PlayerFirstName, ' ', P.PlayerLastName) AS name, P.PlayerID AS id FROM tplss_players P, tplss_appearances A, tplss_matches M WHERE P.PlayerID = A.AppearancePlayerID AND A.AppearanceMatchID = M.MatchID AND M.MatchDateTime = '$matchdate' ORDER BY dob DESC LIMIT 0,1 ",$connection) or die(mysql_error()); while($youngest = mysql_fetch_array($get_youngest_player)) { echo"<tr>"; $dob = $youngest['dob']; echo"<td width=30%>Youngest Player:</td><td width=70%><a href=\"player.php?id=$youngest[id]\">$youngest[name]</a> ("; $now = strtotime("$matchdate"); $your_date = strtotime("$dob"); $datediff_days = $now - $your_date; $datediff_years = ($now - $your_date) / 365; $days = floor($datediff_days/(60*60*24)); $years = floor($datediff_years/(60*60*24)); $remainder = floor($datediff_days/(60*60*24)) - (floor($datediff_years/(60*60*24)) * 365); echo"$years years $remainder days)</td> </tr> "; } mysql_free_result($get_youngest_player); // GET OLDEST PLAYER IN STARTING LINEUP $get_oldest_player = mysql_query(" SELECT P.PlayerDOB AS dob, DATE_FORMAT(P.PlayerDOB, '%d/%m/%Y') AS birth, CONCAT(P.PlayerFirstName, ' ', P.PlayerLastName) AS name, P.PlayerID AS id FROM tplss_players P, tplss_appearances A, tplss_matches M WHERE P.PlayerID = A.AppearancePlayerID AND A.AppearanceMatchID = M.MatchID AND M.MatchDateTime = '$matchdate' ORDER BY dob ASC LIMIT 0,1 ",$connection) or die(mysql_error()); while($oldest = mysql_fetch_array($get_oldest_player)) { echo"<tr>"; $dob = $oldest['dob']; echo"<td width=30%>Oldest Player:</td><td width=70%><a href=\"player.php?id=$oldest[id]\">$oldest[name]</a> ("; $now = strtotime("$matchdate"); $your_date = strtotime("$dob"); $datediff_days = $now - $your_date; $datediff_years = ($now - $your_date) / 365; $days = floor($datediff_days/(60*60*24)); $years = floor($datediff_years/(60*60*24)); $remainder = floor($datediff_days/(60*60*24)) - (floor($datediff_years/(60*60*24)) * 365); echo"$years years $remainder days)</td>"; echo"</tr>"; } mysql_free_result($get_oldest_player); // GET AVERAGE DOB OF STARTING XI $get_average_dob = mysql_query(" SELECT FROM_DAYS(AVG(TO_DAYS(P.PlayerDOB))) AS dob FROM tplss_players P, tplss_appearances A, tplss_matches M WHERE P.PlayerID = A.AppearancePlayerID AND A.AppearanceMatchID = M.MatchID AND M.MatchDateTime = '$matchdate' ",$connection) or die(mysql_error()); while($average = mysql_fetch_array($get_average_dob)) { echo"<tr>"; $dob = $average['dob']; $now = strtotime("$matchdate"); $your_date = strtotime("$dob"); $datediff_days = $now - $your_date; $datediff_years = $datediff_days / 365; $days = floor($datediff_days/(60*60*24)); $years = floor($datediff_years/(60*60*24)); $remainder = floor($datediff_days/(60*60*24)) - (floor($datediff_years/(60*60*24)) * 365); echo"<td width=30%>Average Player Age:</td><td width=70%>$years years $remainder days</td>"; echo"</tr>"; } mysql_free_result($get_average_dob); $get_players = mysql_query(" SELECT COUNT(P.PlayerID) AS players FROM tplss_players P, tplss_appearances A, tplss_matches M WHERE P.PlayerID = A.AppearancePlayerID AND A.AppearanceMatchID = M.MatchID AND M.MatchDateTime = '$matchdate' GROUP BY M.MatchID ",$connection) or die(mysql_error()); $get_scots = mysql_query(" SELECT COUNT(P.PlayerID) AS scots FROM tplss_players P, tplss_appearances A, tplss_matches M WHERE P.PlayerID = A.AppearancePlayerID AND A.AppearanceMatchID = M.MatchID AND M.MatchDateTime = '$matchdate' AND P.PlayerNationID = 1 GROUP BY M.MatchID ",$connection) or die(mysql_error()); while($players = mysql_fetch_array($get_players)) { echo"<tr>"; while($scots = mysql_fetch_array($get_scots)) { $average = ($scots['scots'] / $players['players']) * 100; $average = number_format((float)$average, 2, '.', ''); echo"<td width=30%>Domestic Players:</td><td width=70%>$scots[scots] ($average % of starting eleven)</td>"; } echo"</tr>"; } mysql_free_result($get_players); echo"</table>"; ?> <? // -----------SHOW FACTS ABOUT THE MATCHDAY SQUAD-------------- echo"<br> <h5>Matchday Squad</h5> <table width=100%> "; // GET YOUNGEST PLAYER IN SQUAD $get_youngest_player_all = mysql_query(" SELECT P.PlayerDOB AS dob, CONCAT(P.PlayerFirstName, ' ', P.PlayerLastName) AS name, P.PlayerID AS id FROM tplss_players P INNER JOIN ( SELECT AppearancePlayerID as PlayerID , AppearanceMatchID as MatchID FROM tplss_appearances UNION SELECT SubstitutePlayerID as PlayerID , SubstituteMatchID as MatchID FROM tplss_substitutes ) as total USING (PlayerID) INNER JOIN tplss_matches M USING (MatchID) WHERE M.MatchDateTime = '$matchdate' ORDER BY dob DESC LIMIT 0,1 ",$connection) or die(mysql_error()); while($youngest_all = mysql_fetch_array($get_youngest_player_all)) { echo"<tr>"; $dob = $youngest_all['dob']; echo"<td width=30%>Youngest Player:</td><td width=70%><a href=\"player.php?id=$youngest_all[id]\">$youngest_all[name]</a> ("; $now = strtotime("$matchdate"); $your_date = strtotime("$dob"); $datediff_days = $now - $your_date; $datediff_years = ($now - $your_date) / 365; $days = floor($datediff_days/(60*60*24)); $years = floor($datediff_years/(60*60*24)); $remainder = floor($datediff_days/(60*60*24)) - (floor($datediff_years/(60*60*24)) * 365); echo"$years years $remainder days)</td>"; echo"</tr>"; } // GET OLDEST PLAYER IN SQUAD $get_oldest_player_all = mysql_query(" SELECT P.PlayerDOB AS dob, CONCAT(P.PlayerFirstName, ' ', P.PlayerLastName) AS name, P.PlayerID AS id FROM tplss_players P INNER JOIN ( SELECT AppearancePlayerID as PlayerID , AppearanceMatchID as MatchID FROM tplss_appearances UNION SELECT SubstitutePlayerID as PlayerID , SubstituteMatchID as MatchID FROM tplss_substitutes ) as total USING (PlayerID) INNER JOIN tplss_matches M USING (MatchID) WHERE M.MatchDateTime = '$matchdate' ORDER BY dob ASC LIMIT 0,1 ",$connection) or die(mysql_error()); while($oldest_all = mysql_fetch_array($get_oldest_player_all)) { echo"<tr>"; $dob = $oldest_all['dob']; echo"<td width=30%>Oldest Player:</td><td width=70%><a href=\"player.php?id=$oldest_all[id]\">$oldest_all[name]</a> ("; $now = strtotime("$matchdate"); $your_date = strtotime("$dob"); $datediff_days = $now - $your_date; $datediff_years = ($now - $your_date) / 365; $days = floor($datediff_days/(60*60*24)); $years = floor($datediff_years/(60*60*24)); $remainder = floor($datediff_days/(60*60*24)) - (floor($datediff_years/(60*60*24)) * 365); echo"$years years $remainder days)</td>"; echo"</tr>"; } // GET AVERAGE DOB OF WHOLE SQUAD $get_average_dob_all = mysql_query(" SELECT FROM_DAYS(AVG(TO_DAYS(P.PlayerDOB))) AS dob FROM tplss_players P INNER JOIN ( SELECT AppearancePlayerID as PlayerID , AppearanceMatchID as MatchID FROM tplss_appearances UNION SELECT SubstitutePlayerID as PlayerID , SubstituteMatchID as MatchID FROM tplss_substitutes ) as total USING (PlayerID) INNER JOIN tplss_matches M USING (MatchID) WHERE M.MatchDateTime = '$matchdate' ",$connection) or die(mysql_error()); while($average_all = mysql_fetch_array($get_average_dob_all)) { echo"<tr>"; $dob = $average_all['dob']; $now = strtotime("$matchdate"); $your_date = strtotime("$dob"); $datediff_days = $now - $your_date; $datediff_years = ($now - $your_date) / 365; $days = floor($datediff_days/(60*60*24)); $years = floor($datediff_years/(60*60*24)); $remainder = floor($datediff_days/(60*60*24)) - (floor($datediff_years/(60*60*24)) * 365); echo"<td width=30%>Average Player Age:</td><td width=70%>$years years $remainder days</td>"; echo"</tr>"; } $get_players_all = mysql_query(" SELECT COUNT(P.PlayerID) AS allplayers FROM tplss_players P INNER JOIN ( SELECT AppearancePlayerID as PlayerID , AppearanceMatchID as MatchID FROM tplss_appearances UNION SELECT SubstitutePlayerID as PlayerID , SubstituteMatchID as MatchID FROM tplss_substitutes ) as total USING (PlayerID) INNER JOIN tplss_matches M USING (MatchID) WHERE M.MatchDateTime = '$matchdate' GROUP BY M.MatchID ",$connection) or die(mysql_error()); $get_scots_all = mysql_query(" SELECT COUNT(P.PlayerID) AS scots FROM tplss_players P INNER JOIN ( SELECT AppearancePlayerID as PlayerID , AppearanceMatchID as MatchID FROM tplss_appearances UNION SELECT SubstitutePlayerID as PlayerID , SubstituteMatchID as MatchID FROM tplss_substitutes ) as total USING (PlayerID) INNER JOIN tplss_matches M USING (MatchID) WHERE M.MatchDateTime = '$matchdate' AND P.PlayerNationID = 1 GROUP BY M.MatchID ",$connection) or die(mysql_error()); while($players_all = mysql_fetch_array($get_players_all)) { while($scots_all = mysql_fetch_array($get_scots_all)) { echo"<tr>"; $average = ($scots_all['scots'] / $players_all['allplayers']) * 100; $average = number_format((float)$average, 2, '.', ''); echo"<td width=30%>Domestic Players:</td><td width=70%>$scots_all[scots] ($average % of matchday squad)</td>"; echo"</tr>"; } } echo"</table>"; ?> <? //--------------CHECK FOR ANY DEBUTS---------------- // GET STARTING XI FOR DEBUTS $get_debuts = mysql_query(" SELECT CONCAT(P.PlayerFirstName, ' ', P.PlayerLastName) AS name, P.PlayerID AS id, DATE_FORMAT(P.PlayerSigned, '%M %D, %Y') AS signed FROM tplss_players P, tplss_appearances A WHERE A.AppearanceMatchID = '$id' AND P.PlayerID = A.AppearancePlayerID ORDER BY name",$connection); // GET SUBS FOR DEBUTS $get_sub_debuts = mysql_query(" SELECT CONCAT(P.PlayerFirstName, ' ', P.PlayerLastName) AS name, P.PlayerID AS id, DATE_FORMAT(P.PlayerSigned, '%M %D, %Y') AS signed FROM tplss_players P, tplss_substitutions S WHERE S.SubstitutionMatchID = '$id' AND P.PlayerID = S.SubstitutionPlayerIDIn ORDER BY name",$connection); echo"<br><h5>First Team Debuts</h5> <table width=100%>"; // SHOW ANY DEBUTS FOR PLAYERS IN STARTING XI while($appdata = mysql_fetch_array($get_debuts)) { $appplayerid = $appdata['id']; $get_starts = mysql_query(" SELECT COUNT(A.AppearancePlayerID) AS total FROM tplss_appearances A, tplss_matches M WHERE A.AppearancePlayerID = $appplayerid AND A.AppearanceMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate' GROUP BY A.AppearancePlayerID ",$connection) or die(mysql_error()); $starts = mysql_fetch_array($get_starts); $get_subst = mysql_query(" SELECT COUNT(S.SubstitutionPlayerIDIn) AS total FROM tplss_substitutions S, tplss_matches M WHERE S.SubstitutionPlayerIDIn = $appplayerid AND S.SubstitutionMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate' GROUP BY S.SubstitutionPlayerIDIn ",$connection) or die(mysql_error()); $subst = mysql_fetch_array($get_subst); $total_apps = $starts['total'] + $subst['total']; $head_url = "images/heads/" . $appplayerid . ".jpg"; if($total_apps == 1) { echo"<tr> <td width=20%> <img src=\""; if(file_exists($head_url)) { echo"images/heads/$appplayerid.jpg"; } else { echo"images/heads/none.jpg"; } echo"\" width=\"50\" style=\"border:0px solid; border-radius:25px;\"> </td> <td width=40%><a href=\"player.php?id=$appplayerid\">$appdata[name]</a></td><td width=40%>(Signed $appdata[signed])</td>"; } else { echo""; } } mysql_free_result($get_debuts); // SHOW ANY DEBUTS FOR PLAYERS COMING OFF BENCH while($appdatas = mysql_fetch_array($get_sub_debuts)) { $appplayerid = $appdatas['id']; $get_starts = mysql_query(" SELECT COUNT(A.AppearancePlayerID) AS total FROM tplss_appearances A, tplss_matches M WHERE A.AppearancePlayerID = $appplayerid AND A.AppearanceMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate' GROUP BY A.AppearancePlayerID ",$connection) or die(mysql_error()); $starts = mysql_fetch_array($get_starts); $get_subst = mysql_query(" SELECT COUNT(S.SubstitutionPlayerIDIn) AS total FROM tplss_substitutions S, tplss_matches M WHERE S.SubstitutionPlayerIDIn = $appplayerid AND S.SubstitutionMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate' GROUP BY S.SubstitutionPlayerIDIn ",$connection) or die(mysql_error()); $subst = mysql_fetch_array($get_subst); $total_apps = $starts['total'] + $subst['total']; $head_url = "images/heads/" . $appplayerid . ".jpg"; if($total_apps == 1) { echo"<tr> <td width=20%> <img src=\""; if(file_exists($head_url)) { echo"images/heads/$appplayerid.jpg"; } else { echo"images/heads/none.jpg"; } echo"\" width=\"50\" style=\"border:0px solid; border-radius:25px;\"> </td> <td width=40%><a href=\"player.php?id=$appplayerid\">$appdatas[name]</a></td><td width=40%>(Signed $appdatas[signed])</td>"; } else { echo""; } } mysql_free_result($get_sub_debuts); echo"</table>"; ?> <?php // --------------------CHECK FOR ANY MILESTONES---------------- // GET STARTING XI FOR MILESTONES $get_milestones = mysql_query(" SELECT CONCAT(P.PlayerFirstName, ' ', P.PlayerLastName) AS name, P.PlayerID AS id FROM tplss_players P, tplss_appearances A WHERE A.AppearanceMatchID = '$id' AND P.PlayerID = A.AppearancePlayerID ORDER BY name",$connection); // GET SUBS FOR MILESTONES $get_sub_milestones = mysql_query(" SELECT CONCAT(P.PlayerFirstName, ' ', P.PlayerLastName) AS name, P.PlayerID AS id FROM tplss_players P, tplss_substitutions S WHERE S.SubstitutionMatchID = '$id' AND P.PlayerID = S.SubstitutionPlayerIDIn ORDER BY name",$connection); // GET SCORERS FOR GOAL CHECKS $get_goals = mysql_query(" SELECT CONCAT(P.PlayerFirstName, ' ', P.PlayerLastName) AS name, P.PlayerID AS id FROM tplss_players P, tplss_goals G WHERE G.GoalMatchID = '$id' AND P.PlayerID = G.GoalPlayerID AND G.GoalOwn != 1 ORDER BY name",$connection); echo"<Br><h5>Milestones</h5> <table width=100%>"; // SHOW MILESTONES FOR STARTING XI while($appdatam = mysql_fetch_array($get_milestones)) { $appplayerid = $appdatam['id']; $get_starts = mysql_query(" SELECT COUNT(A.AppearancePlayerID) AS total FROM tplss_appearances A, tplss_matches M WHERE A.AppearancePlayerID = $appplayerid AND A.AppearanceMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate' GROUP BY A.AppearancePlayerID ",$connection) or die(mysql_error()); $starts = mysql_fetch_array($get_starts); $get_subst = mysql_query(" SELECT COUNT(S.SubstitutionPlayerIDIn) AS total FROM tplss_substitutions S, tplss_matches M WHERE S.SubstitutionPlayerIDIn = $appplayerid AND S.SubstitutionMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate' GROUP BY S.SubstitutionPlayerIDIn ",$connection) or die(mysql_error()); $subst = mysql_fetch_array($get_subst); $total_apps = $starts['total'] + $subst['total']; echo""; if($total_apps == '50') { echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$appdatam[name]</a> played his 50th major competitive game for the Club.</td></tr>"; } elseif($total_apps == '100') { echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$appdatam[name]</a> played his 100th major competitive game for the Club.</td></tr>"; } elseif($total_apps == '200') { echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$appdatam[name]</a> made his 200th competitive appearance for the Club.</td></tr>"; } elseif($total_apps == '250') { echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$appdatam[name]</a> played his 250th major competitive game for the Club.</td></tr>"; } elseif($total_apps == '300') { echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$appdatam[name]</a> made his 300th competitive appearance for the Club.</td></tr>"; } elseif($total_apps == '400') { echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$appdatam[name]</a> played his 400th major competitive game for the Club.</td></tr>"; } elseif($total_apps == '500') { echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$appdatam[name]</a> made his 500th competitive appearance for the Club.</td></tr>"; } else { echo""; } echo""; } mysql_free_result($get_milestones); // SHOW MILESTONES FOR SUBS while($appdatams = mysql_fetch_array($get_sub_milestones)) { $appplayerid = $appdatams['id']; $get_starts = mysql_query(" SELECT COUNT(A.AppearancePlayerID) AS total FROM tplss_appearances A, tplss_matches M WHERE A.AppearancePlayerID = $appplayerid AND A.AppearanceMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate' GROUP BY A.AppearancePlayerID ",$connection) or die(mysql_error()); $starts = mysql_fetch_array($get_starts); $get_subst = mysql_query(" SELECT COUNT(S.SubstitutionPlayerIDIn) AS total FROM tplss_substitutions S, tplss_matches M WHERE S.SubstitutionPlayerIDIn = $appplayerid AND S.SubstitutionMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate' GROUP BY S.SubstitutionPlayerIDIn ",$connection) or die(mysql_error()); $subst = mysql_fetch_array($get_subst); $total_apps = $starts['total'] + $subst['total']; if($total_apps == '50') { echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$appdatams[name]</a> played his 50th major competitive game for the Club.</td></tr>"; } elseif($total_apps == '100') { echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$appdatams[name]</a> played his 100th major competitive game for the Club.</td></tr>"; } elseif($total_apps == '200') { echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$appdatams[name]</a> made his 200th competitive appearance for the Club.</td></tr>"; } elseif($total_apps == '250') { echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$appdatams[name]</a> played his 250th major competitive game for the Club.</td></tr>"; } elseif($total_apps == '300') { echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$appdatams[name]</a> made his 300th competitive appearance for the Club.</td></tr>"; } elseif($total_apps == '400') { echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$appdatams[name]</a> played his 400th major competitive game for the Club.</td></tr>"; } elseif($total_apps == '500') { echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$appdatams[name]</a> made his 500th competitive appearance for the Club.</td></tr>"; } else { echo""; } } mysql_free_result($get_sub_milestones); // SHOW MILESTONES FOR STARTING XI while($goaldata = mysql_fetch_array($get_goals)) { $appplayerid = $goaldata['id']; $get_goal_totals = mysql_query(" SELECT COUNT(G.GoalPlayerID) AS total FROM tplss_goals G, tplss_matches M WHERE G.GoalPlayerID = $appplayerid AND G.GoalMatchID = M.MatchID AND M.MatchDateTime <= '$matchdate' GROUP BY G.GoalPlayerID ",$connection) or die(mysql_error()); $goals = mysql_fetch_array($get_goal_totals); $total_goals = $goals['total']; if($total_goals == '1') { echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$goaldata[name]</a> scored his first goal for the Club.</td></tr>"; } elseif($total_goals == '10') { echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$goaldata[name]</a> reached 10 goals for the Club.</td></tr>"; } elseif($total_goals == '25') { echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$goaldata[name]</a> scored for the 25th time for the Club.</td></tr>"; } elseif($total_goals == '30') { echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$goaldata[name]</a> reached 30 goals for the Club.</td></tr>"; } elseif($total_goals == '50') { echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$goaldata[name]</a> scored his 50th goal for the Club.</td></tr>"; } elseif($total_goals == '75') { echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$goaldata[name]</a> reached 75 goals for the Club.</td></tr>"; } elseif($total_goals == '100') { echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$goaldata[name]</a> scored his 100th goal for the Club.</td></tr>"; } elseif($total_goals == '200') { echo"<tr><td width=100%><a href=\"player.php?id=$appplayerid\">$goaldata[name]</a> scored his 200th goal for the Club.</td></tr>"; } else { echo""; } } echo"</table>"; mysql_free_result($get_goals); ?> <hr> Any suggestions or general advice would be greatly appreciated.
  12. The following piece of code updated rows of data when my database was on MySQL 4.0 Now on MySQL 5.0, it does not work. Can anyone offer some help as to why? foreach($_POST['mid'] as $key=>$val) { $percent = $_POST['percent']; $detail = $_POST['detail']; $qty = $_POST['qty']; $rate = $_POST['rate']; mysql_query("UPDATE val_mworks SET mworks_percent = '$percent[$key]', mworks_detail = '$detail[$key]', mworks_qty = '$qty[$key]', mworks_rate = '$rate[$key]' WHERE mworks_id = '$val' ") or die(mysql_error()); }
  13. First of all, I must admit I know nothing about Javascript. If anyone can point me in the right direction with a correction or working example I would be hugely grateful. I am using the following scripts (one which cycles images in a header, the other which adds a caption to an image). The image cycle one works fine regardless, but the caption one doesn't work while the header is there. Can anyone suggest why they might be fighting? Script 1 (image cycler): $(function() { $('#fixed').capty({ animation: 'fixed' }); }); Script 2 (image captioner): ;(function($) { $.fn.capty = function(settings) { var options = $.extend({}, $.fn.capty.defaults, settings); if (this.length == 0) { debug('Selector invalid or missing!'); return; } else if (this.length > 1) { return this.each(function() { $.fn.capty.apply($(this), [settings]); }); } var $this = $(this), name = $this.attr('name'), $caption = $('<div class="' + options.cCaption + '"/>'), $elem = $this; if ($this.parent().is('a')) { $elem = $this.parent(); } var $image = $elem.wrap('<div class="' + options.cImage + '"/>').parent(), $wrapper = $image.wrap('<div class="' + options.cWrapper + '"/>').parent(); $wrapper.css({ height: $this.height(), overflow: 'hidden', position: 'relative', width: $this.width() }); $caption.css({ height: options.height, opacity: options.opacity, position: 'relative' }) .click(function(evt) { evt.stopPropagation(); }) .appendTo($wrapper); if (name) { var $content = $(name); if ($content.length) { $content.appendTo($caption); } else { $caption.html('<span style="color: #F00;">Content invalid or missing!</span>'); } } else { $caption.html($this.attr('alt')); } if (options.prefix) { $caption.prepend(options.prefix); } if (options.sufix) { $caption.append(options.sufix); } if (options.animation == 'fixed') { $caption.css('top', (-1 * options.height) + 'px'); } else { debug($this.attr('id') + ': invalid animation!'); } return $this; }; function debug(message) { if (window.console && window.console.log) { window.console.log(message); } }; $.fn.capty.defaults = { animation: 'slide', cCaption: 'capty-caption', cImage: 'capty-image', cWrapper: 'capty-wrapper', height: 30, opacity: .7, prefix: '', speed: 200, sufix: '' }; })(jQuery);
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.