NiallFH

This feels like it's a complicated one, I'll try to explain it as best I can. I have a table of players - tplss_players - fields PlayerID, PlayerName I have a table of players who have made an appearance for their team = fields AppearanceID, AppearancePlayerID, AppearanceMatchID I have a table of matches that have been played - MatchID, MatchDate On the page I am working with, $matchdate is a variable already declared using a $_GET. To clarify, PlayerID and AppearancePlayerID are related, as are MatchID and AppearanceMatchID. I want to present one result which tells me the TOTAL amount of appearances made in the past by everyone who appears on that matchdate. In other words, a team of 11 players played on 13th September 2013, I want to show the total of amount of games all eleven players have played prior to that date. As a combined figure. If anyone can offer some help, I'd be extremely grateful.

Yes! That seems to have done it. Need to double check the accuracy, but I think it has. Thank you once again Barand!

Ha! Okay, it seems to work fine when the players involved are born AFTER 1969? But any time there are players involved who were born before that date, the result seems to default to 1969-12-31.

Am trying this now. Okay, something strange is happening. Have tested this against a variety of matches and combinations. Every single test returns "1969-12-31" as the average.

I had tried this: $get_average_dob = mysql_query(" SELECT FROM_UNIXTIME(avg(unix_timestamp(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()); However, the result it presents seems wildly inaccurate. To the point where a bunch of players who were born between 1958 and 1963 returns an average DOB of some time in 1969??

On further reflection, the year that appeared was a different print test. The code I posted actually returns nothing at all. No error though either?

Here it is: -- phpMyAdmin SQL Dump -- version 2.11.11.3 -- http://www.phpmyadmin.net -- -- Host: ### -- Generation Time: May 22, 2014 at 03:04 PM -- Server version: 5.0.96 -- PHP Version: 5.1.6 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; -- -- Database: `###` -- -- -------------------------------------------------------- -- -- Table structure for table `tplss_players` -- CREATE TABLE `tplss_players` ( `PlayerID` int(10) unsigned NOT NULL auto_increment, `PlayerFirstName` varchar(32) character set utf8 NOT NULL, `PlayerLastName` varchar(64) character set utf8 NOT NULL, `PlayerFriendlyName` varchar(200) collate latin1_general_ci NOT NULL, `PlayerNickName` varchar(128) collate latin1_general_ci NOT NULL default '', `PlayerSeasonID` int(11) unsigned NOT NULL default '0', `PlayerPositionID` tinyint(1) unsigned NOT NULL default '0', `PlayerNumber` tinyint(3) unsigned NOT NULL default '0', `PlayerDescription` text collate latin1_general_ci NOT NULL, `PlayerProfile` text collate latin1_general_ci NOT NULL, `PlayerPublish` tinyint(1) unsigned NOT NULL default '1', `PlayerShowStats` tinyint(1) NOT NULL default '1', `PlayerAllData` tinyint(1) NOT NULL default '1', `PlayerTask` varchar(128) collate latin1_general_ci NOT NULL default '', `PlayerFullName` varchar(128) collate latin1_general_ci NOT NULL, `PlayerHeight` varchar(128) collate latin1_general_ci NOT NULL default '', `PlayerDead` tinyint(1) NOT NULL default '0', `PlayerDOB` date default NULL, `PlayerDied` date default NULL, `PlayerBirthplace` varchar(220) collate latin1_general_ci NOT NULL, `PlayerDeathplace` varchar(220) collate latin1_general_ci NOT NULL, `PlayerPos` varchar(128) collate latin1_general_ci NOT NULL default '', `PlayerYouthID` tinyint(3) NOT NULL default '9', `PlayerSigned` date default NULL, `PlayerFee` int(13) NOT NULL default '0', `PlayerNationID` tinyint(3) NOT NULL default '0', `PlayerCapped` tinyint(1) default NULL, `PlayerCaps` tinyint(3) default NULL, `PlayerCapsGoals` tinyint(11) default NULL, `PlayerCapsNotes` text collate latin1_general_ci, `PlayerIntDebut` date default NULL, `PlayerIntOpponent` tinyint(11) default NULL, `PlayerHomegrown` tinyint(1) NOT NULL default '0', `PlayerExpiry` date default NULL, PRIMARY KEY (`PlayerID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1493 ;

Thanks Barand, Trying this just now but it only seems to return a year? No month or date showing. $get_average_dob = mysql_query(" SELECT CAST(ROUND(AVG(P.PlayerDOB),0) as DATE) 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' AND P.PlayerDOB IS NOT NULL ",$connection) or die(mysql_error());

Could someone please advise the best way to determine the average of a DATE field in a SELECT query?

Thank you very much, this works perfectly!

Good afternoon all, I'm looking for some help with joins and dates. Not something I have ever done before so hoping that someone can point me in the right direction. Here is the code I have just now which works absolutely fine in terms of find the person with the oldest date of birth: $get_oldest_player = mysql_query(" SELECT P.PlayerDOB AS dob, 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()); Problem is, I have two tables I'm wanting to include in this query, and not entirely sure how I combine them in the search. I have ONE table for the people (tplss_players) but TWO tables recorded instances of their involvement in matches (tplss_appearances and tplss_substitutions). Should I use a LEFT JOIN somehow ? Not sure where it sits in the layout of the query? tplss_appearances and tplss_substitutions are the same in their structure. Any help would be greatly appreciated. Niall

Good afternoon all. I am using MySQL 5.0 and wish to create a query which will allow me to display the following: I have three tables. One is a database of years. YearID - YearName The second is a database of registrations RegistrationID - RegistrationYear - RegistrationPerson The third is a database of people PersonID - PersonName (I have identified the personid as a variable earlier in my code). Ultimately, what I want to do, is show a list of years that a person is NOT registered to. I have tried a few different variations, but as yet have been unable to put together a query that will do this. I can however display a list of years to which a person IS registered, using this query: $get_registered_years = mysql_query(" SELECT Y.YearName AS yearname FROM lpro_years Y, lpro_registrations R WHERE R.RegisteredYear = Y.YearID AND R.RegisteredPerson = $personid ORDER BY Y.YearName ",$conn) or die(mysql_error()); It baffles me that I cannot do the reverse? Any help would be greatly appreciated.

Can anyone explain to me why this code updates the records fine, yet the code below it doesn't? To me, they are both the same in principle, yet one works and the other doesn't? Tearing my hair out! Example 1: <form method="post" action="<?php echo "$PHP_SELF?sessioid=$sessio&action=modify&id=$matchid" ?>"> <?php $get_goalscorers = mysql_query("SELECT CONCAT(tplss_players.PlayerFirstName, ' ', tplss_players.PlayerLastName) AS playername, tplss_goals.GoalMinute AS minute, tplss_goals.GoalID as id, tplss_goals.GoalPenalty AS pen, tplss_goals.GoalOwn AS own, tplss_goals.GoalOwnScorer AS ownscorer FROM tplss_players, tplss_goals WHERE tplss_players.PlayerID = tplss_goals.GoalPlayerID AND tplss_goals.GoalMatchID = '$matchid' AND tplss_goals.GoalSeasonID = '$seasonid' ORDER BY minute ", $connection) or die(mysql_error()); if(mysql_num_rows($get_goalscorers) == 0) { echo'No goals added.'; } else { while($gsdata = mysql_fetch_array($get_goalscorers)) { echo"<input type=\"hidden\" name=\"goal_id[]\" value=\"$gsdata[id]\">"; if($gsdata['own'] == 1) { echo"$gsdata[ownscorer] (o.g.) <input type=\"text\" name=\"minute[]\" size=\"2\" value=\"$gsdata[minute]\"><br>\n"; } elseif($gsdata['pen'] == 1) { echo"$gsdata[playername] (pen.) <input type=\"text\" name=\"minute[]\" size=\"2\" value=\"$gsdata[minute]\"><br>\n"; } else { echo"$gsdata[playername] <input type=\"text\" name=\"minute[]\" size=\"2\" value=\"$gsdata[minute]\"><br>\n"; } } } mysql_free_result($get_goalscorers); ?> <input type="submit" name="modify_goals" value="Modify goal times"> </form> elseif($modify_goals) { $goalid = $_POST['goal_id']; $goalminute = $_POST['minute']; foreach($goalid as $key=>$goaltime) { mysql_query(" UPDATE tplss_goals SET GoalMinute = '$goalminute[$key]' WHERE GoalID = '$goalid[$key]' ",$connection); } } Example #2 (doesn't update): <form method="post" action="<?php echo "$PHP_SELF?sessioid=$sessio&action=modify&id=$matchid" ?>"> <input type="hidden" name="matchid" value="<?php echo $matchid ?>"> <input type="hidden" name="season_id" value="<?php echo $seasonid ?>"> <?php $get_substitutions = mysql_query(" SELECT CONCAT(P.PlayerFirstName, ' ', P.PlayerLastName) AS playername, CONCAT(PL.PlayerFirstName, ' ', PL.PlayerLastName) AS playername2, S.SubstitutionMinute AS minute, S.SubstitutionID AS id FROM tplss_players P, tplss_players PL, tplss_substitutions S WHERE S.SubstitutionMatchID = '$matchid' AND S.SubstitutionSeasonID = '$seasonid' AND P.PlayerID = S.SubstitutionPlayerIDIn AND PL.PlayerID = S.SubstitutionPlayerIDOut ORDER BY minute ", $connection) or die(mysql_error()); if(mysql_num_rows($get_substitutions) == 0) { echo'No substitutions added.'; } else { while($subbydata = mysql_fetch_array($get_substitutions)) { echo"<input type=\"hidden\" name=\"subbyid[]\" value=\"$subbydata[id]\">"; echo"$subbydata[playername] for $subbydata[playername2] <input type=\"text\" name=\"minute[]\" size=\"2\" value=\"$subbydata[minute]\"><br>\n"; } } mysql_free_result($get_substitutions); ?> <input type="submit" name="modifysubs" value="Modify sub times"> </form> elseif($modifysubs) { $subbyid = $_POST['subbyid']; $subbyminute = $_POST['minute']; foreach($subbyid as $key=>$subbytime) { mysql_query(" UPDATE tplss_substitutions SET SubstitutionMinute = '$subbyminute[$key]' WHERE SubstitutionID = '$subbyid[$key]' ",$connection) or die(mysql_error()); } }

What I'm trying to do seems very basic, but having not done it before I think I've become quite lost along the way. I have a record that has a list of goals, each has its own ID and also a number next to it which is the minute the goal is scored. I have written an html form which posts information the code detailed below. Here is the only input box from my form: <input type=\"hidden\" name=\"goal_id\" value=\"$gsdata[id]\"> <input type=\"text\" name=\"minute\" size=\"2\" value=\"$gsdata[minute]\"> It is displayed using a mysql_fetch_array to display a list of all goals associated to one match, so naturally there could, 2, 3, 4, or any number of records displayed as input boxes shown above. When I click the Submit button, it posts to the page shown below: $goalid = $_POST['goal_id']; foreach($_POST['minute'] as $goaltime) { mysql_query(" UPDATE pro_goals SET GoalMinute = '$goaltime' WHERE GoalID = '$goalid' ",$connection); } Can anyone offer any help?

Thank you so much, that has worked to a degree for my searches. I can now find Çiftçi, for example, simply by searching Ciftci, but some of the accented consonants still don't match up, such as ć or Ł. In fact, it seems to be mainly consonants with accents that don't work. Also - it still doesn't sort the names in the correct order when displaying multiple results. It still orders Ç for Çiftçi after Z. Any ideas?