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?

There'd be too many examples of clashing names for that to be work. Seán and Sean, for example.

I have a search script which essentially just searches a list of soccer player's names and returns a list of those that match the search criteria. $get_players = mysql_query(" SELECT CONCAT(PlayerLastName, ', ', PlayerFirstName) AS name, PlayerID AS id, DATE_FORMAT(PlayerDOB, '%Y') AS dob FROM tplss_players WHERE CONCAT(PlayerFirstName, ' ', PlayerLastName) LIKE '%$result%' OR PlayerFirstName LIKE '%$result%' OR PlayerLastName LIKE '%$result%' ORDER BY PlayerLastName, PlayerFirstName ASC ", $connection); The problem is, if a player has an accented character in their name and the person searching isn't aware of it, it wont display them in the results. I.e. Nadir Çiftçi. If someone searches for "Nadir Ciftci", nothing will come up. Is there a way to configure your query so that accented characters are treated as normal characters? i.e. Ç is C? The fields in question (PlayerFirstName and PlayerLastName) are set to use Collation of "utf8_bin". Thanks in advance for any help you can offer, Niall

Thanks that works great! Many thanks for your help!

Thanks for the reply Barand. I've tried this, to the letter, and I get the error - " Every derived table must have its own alias" Any ideas what I could be doing wrong?

Hi all, I'm sure I'm missing something quite simple here. Basically, I have three tables. One for employees, one for dayshifts and one for nightshifts. The structure is as follows: employees - employeeID, employeename weeks - weekID dayshifts - dayshiftID, dayshiftemployeeID, dayshiftweekid nightshifts - nightshiftID, nightshiftemployeeID, nightshiftweekid I want to find a way to find out how many different employees I've used throughout a week. I can easily do this for just dayshifts or just nightshifts, using the following code: $get_employees_used = mysql_query("SELECT COUNT(DISTINCT DayShiftEmployeeID) as total FROM dayshifts WHERE DayShiftWeekID = $weekid ",$connection) However, I can't find a way to combine both dayshifts and nightshifts together and return the number of employees I've used across all shifts in the the week. Does anyone have any ideas?

I'll give it a go! At its most basic, it's something like: sum(Value of Works * Retention %) However that value of works figure needs to be taken from the most recent valuation for each contract, and I want the query to disregard anything older. I.e. Three contracts Contract 1: Valuation 1 (Sep 2009), Valuation 2 (Oct 2009), Valuation 3 (Nov 2009) Contract 2: Valuation 1 (Jul 2010), Valuation 2 (Aug 2010) Contract 3: Valuation 1 (Nov 2009), Valuation 2 (Dec 2009), Valuation 3 (Feb 2010), Valuation 4 (Mar 2010) The only ones I would want the value of works figure from are shown in bold: Contract 1: Valuation 1 (Sep 2009), Valuation 2 (Oct 2009), Valuation 3 (Nov 2009) Contract 2: Valuation 1 (Jul 2010), Valuation 2 (Aug 2010) Contract 3: Valuation 1 (Nov 2009), Valuation 2 (Dec 2009), Valuation 3 (Feb 2010), Valuation 4 (Mar 2010)

Using MySQL 5.0 This seems a very complicated one. Basic table structure: val_contracts - contract_id, contract_ret (the % of retention the client will take) val_valuations - valuation_id, valuation_con (corresponds with contract_id), valuation_worksvalue, valuation_date This is a database of contracts. Each contract has valuations sent to customers each month, and within that contract is a % figure of retention (monies held by the customer) that they are expected to hold. For example: Value of Work: £20000 Less Retention (5%): £1000 Payment Requested: £19000 As a job or contract progresses, the value of work will increase and also thereby the retention figure will increase, so therefore, at any one time, to gauge the amount of retention outstanding, the MOST RECENT Valuation for each contract is the only one I'm interested in. What I want to do is to be able to show a total figure for ALL outstanding retention on ALL jobs/contracts. To do this, I need to write a query that will first find the most recent valuation (i.e. MAX(valuation_date)) and then somehow add together the value of work less retention (this % changes from client to client) for each job. However, I am completely at a loss as how to do this. Can anyone offer some assistance?

Thank you. Have now resolved this issue!

I read something similar elsewhere, but I'm slightly confused re: syntax. Is this close to correct? if(isset('defaultseasonid_tplss') && isset('defaultmatchtypeid_tplss')) { $_SESSION['defaultseasonid_tplss'] = $pdata['DefaultSeasonID']; $_SESSION['defaultmatchtypeid_tplss'] = $pdata['DefaultMatchTypeID']; $seasonid = $_SESSION['defaultseasonid_tplss']; $compid = $_SESSION['defaultmatchtypeid_tplss']; } else { $seasonid = $_SESSION['defaultseasonid_tplss']; $compid = $_SESSION['defaultmatchtypeid_tplss']; }

I wondered if anyone could offer me some advice on how to solve this problem: I am using MySQL 5.0 and PHP 5 and getting the following error: "Deprecated: Function session_is_registered() is deprecated in" The code is at the very top of my php file and there is nothing being sent to the browser prior to this code. <? 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()); $scriptname = "matches.php?" . $_SERVER['QUERY_STRING']; $pref = mysql_query("SELECT * FROM tplss_preferences WHERE ID = '0'",$connection) or die(mysql_error()); $pdata = mysql_fetch_array($pref); mysql_free_result($pref); if(!session_is_registered('defaultseasonid_tplss') || !session_is_registered('defaultmatchtypeid_tplss')) { $_SESSION['defaultseasonid_tplss'] = $pdata['DefaultSeasonID']; $_SESSION['defaultmatchtypeid_tplss'] = $pdata['DefaultMatchTypeID']; $seasonid = $_SESSION['defaultseasonid_tplss']; $compid = $_SESSION['defaultmatchtypeid_tplss']; } else { $seasonid = $_SESSION['defaultseasonid_tplss']; $compid = $_SESSION['defaultmatchtypeid_tplss']; } ?>