Jump to content

MargateSteve

Members
 View Profile  See their activity

  • Posts

    240

  • Joined

  • Last visited

 Content Type 

Posts posted by MargateSteve

    Filter records from two joined views (In Full!)

    in MySQL Help

    Posted

    And that was exactly my question in the first post. I tried WHERE view1.date BETWEEN '2010-08-01' AND  '2011-05-01'

    in the query and although some results were returned there were fewer than there should have been.

    My assumption was that the date fields in each view needed joining but could not work out how to do it.

     

    Steve

    Filter records from two joined views (In Full!)

    in MySQL Help

    Posted

    Sorry, it always makes more sense in my head than on the screen!

     

    The views and query that I currently have successfully generates the records that it should but it takes all records in the database.

     

    However, I want to be able to use a WHERE clause on `games`.`date` in both views so I can show different records for each season. I have previously done this within the views by adding WHERE `games`.`date` BETWEEN x AND x but that then restricts the views to just those dates and cannot be used for any different dates.

     

    What I am hoping to do is use a similar WHERE clause but within the query and not the views, so that I can alter just the query for each season and not have to create new views for each season.

     

    Steve

    Filter records from two joined views (In Full!)

    in MySQL Help

    Posted

    Sorry for the delay in getting back but have had a major virus issue!

     

    The LEFT JOIN's are probably hangovers from when there was a WHERE clause on `games`.`date` to make sure only the relevant records where pulled for the view. If they are superfluous now I will happily have a go at re-writing it.

    However, at the moment I think that I am going to have create a version of each view for each season with a WHERE `games`.`date` BETWEEN x AND x as I still cannot find a way to filter the date via the query on the page.

     

    Steve

    Filter records from two joined views (In Full!)

    in MySQL Help

    Posted

    Firstly a huge apology for the double post. I accidentally hit post instead of preview midway through my first attempt and by the time I finished adding the rest of the data, the modify post time limit had expired.

     

    Quite some time back, I was given help on here with aggregating fields from two different tables. It still works fine but I failed to foresee a future problem in that I need to use posted data to filter the query.

     

    The data generated is statistics for football (soccer) players and is designed to show the number of minutes played per goal scored. Everything works fine but it only shows all data but I need to be able to filter the query between two dates at a time to some the statistics for separate seasons.

     

    The first view gets all the games that a player has featured in (from 'appearances') including the number of minutes that he has been on the pitch and counts them by separate competition ('lgemins', 'facmins' etc in the counts towards the end of the view).

    `view1` AS select 
`games`.`all_games_id` AS `match_id`
,`games`.`date` AS `date`
,`games`.`time` AS `time`
,`games`.`comp` AS `competition`
,`games`.`round` AS `round`
,`games`.`replay` AS `replay`
,`games`.`home_team` AS `home_team`
,`games`.`away_team` AS `away_team`
,`games`.`home_goals` AS `home_goals`
,`games`.`away_goals` AS `away_goals`
,`games`.`attendance` AS `attendance`
,`games`.`report_url` AS `report_url`
,`games`.`photo_url` AS `photo_url`
,`games`.`notes` AS `notes`
,`games`.`extratime` AS `extra_time`
,`appearances`.`app_id` AS `app_id`
,`appearances`.`match` AS `match`
,`appearances`.`number` AS `number`
,`appearances`.`player` AS `player`
,`appearances`.`type` AS `type`
,`appearances`.`on` AS `on`
,`appearances`.`off` AS `off`
,`appearances`.`yellows` AS `yellows`
,`appearances`.`red` AS `red`
,`appearances`.`replaced` AS `replaced`
,`players`.`player_id` AS `player_id`
,`players`.`surname` AS `surname`
,`players`.`firstname` AS `firstname`
,`players`.`date_of_birth` AS `date_of_birth`
,`players`.`date_joined` AS `date_joined`
,`players`.`date_left` AS `date_left`
,count((case when ((`games`.`comp` = 1) and (`appearances`.`type` = 1)) then 1 else NULL end)) AS `lgest`
,count((case when ((`games`.`comp` = 1) and (`appearances`.`type` = 2)) then 1 else NULL end)) AS `lgesub`
,count((case when ((`games`.`comp` = 1) and (`appearances`.`type` = 3)) then 1 else NULL end)) AS `lgebench`
,count((case when ((`games`.`comp` = 2) and (`appearances`.`type` = 1)) then 1 else NULL end)) AS `facst`
,count((case when ((`games`.`comp` = 2) and (`appearances`.`type` = 2)) then 1 else NULL end)) AS `facsub`
,count((case when ((`games`.`comp` = 2) and (`appearances`.`type` = 3)) then 1 else NULL end)) AS `facbench`
,count((case when ((`games`.`comp` = 3) and (`appearances`.`type` = 1)) then 1 else NULL end)) AS `fatst`
,count((case when ((`games`.`comp` = 3) and (`appearances`.`type` = 2)) then 1 else NULL end)) AS `fatsub`
,count((case when ((`games`.`comp` = 3) and (`appearances`.`type` = 3)) then 1 else NULL end)) AS `fatbench`
,count((case when ((`games`.`comp` = 4) and (`appearances`.`type` = 1)) then 1 else NULL end)) AS `kscst`
,count((case when ((`games`.`comp` = 4) and (`appearances`.`type` = 2)) then 1 else NULL end)) AS `kscsub`
,count((case when ((`games`.`comp` = 4) and (`appearances`.`type` = 3)) then 1 else NULL end)) AS `kscbench`
,count((case when ((`games`.`comp` = 5) and (`appearances`.`type` = 1)) then 1 else NULL end)) AS `lcst`
,count((case when ((`games`.`comp` = 5) and (`appearances`.`type` = 2)) then 1 else NULL end)) AS `lcsub`
,count((case when ((`games`.`comp` = 5) and (`appearances`.`type` = 3)) then 1 else NULL end)) AS `lcbench`
,count((case when (`appearances`.`type` = 1) then 1 else NULL end)) AS `tst`
,count((case when (`appearances`.`type` = 2) then 1 else NULL end)) AS `tsub`
,count((case when (`appearances`.`type` = 3) then 1 else NULL end)) AS `tbench`
,(sum((case when (`games`.`comp` = 1) then `appearances`.`off` else NULL end)) - sum((case when (`games`.`comp` = 1) then `appearances`.`on` else NULL end))) AS `lgemins`
,(sum((case when (`games`.`comp` = 2) then `appearances`.`off` else NULL end)) - sum((case when (`games`.`comp` = 2) then `appearances`.`on` else NULL end))) AS `facmins`
,(sum((case when (`games`.`comp` = 3) then `appearances`.`off` else NULL end)) - sum((case when (`games`.`comp` = 3) then `appearances`.`on` else NULL end))) AS `fatmins`
,(sum((case when (`games`.`comp` = 4) then `appearances`.`off` else NULL end)) - sum((case when (`games`.`comp` = 4) then `appearances`.`on` else NULL end))) AS `kscmins`
,(sum((case when (`games`.`comp` = 5) then `appearances`.`off` else NULL end)) - sum((case when (`games`.`comp` = 5) then `appearances`.`on` else NULL end))) AS `lcmins`
,(sum(`appearances`.`off`) - sum(`appearances`.`on`)) AS `tmins` 
from (`all_games` `games` join (`appearances` join `players` on((`appearances`.`player` = `players`.`player_id`)))) 
where ((`appearances`.`match` = `games`.`all_games_id`) 
and (`games`.`comp` <> 6)) 
group by `players`.`player_id`;

     

    The second view gets all the goals that a player has scored that a player has featured in (from 'goals') and counts them by separate competition (`lgegls`, `facgls` etc in the counts towards the end of the view).

    `view2` AS select 
`games`.`all_games_id` AS `match_id`
,`games`.`date` AS `date`
,`games`.`time` AS `time`
,`games`.`comp` AS `competition`
,`games`.`round` AS `round`
,`games`.`replay` AS `replay`
,`games`.`home_team` AS `home_team`
,`games`.`away_team` AS `away_team`
,`games`.`home_goals` AS `home_goals`
,`games`.`away_goals` AS `away_goals`
,`games`.`attendance` AS `attendance`
,`games`.`report_url` AS `report_url`
,`games`.`photo_url` AS `photo_url`
,`games`.`notes` AS `notes`
,`games`.`extratime` AS `extra_time`
,`goals`.`goal_id` AS `goal_id`
,`goals`.`match` AS `match`
,`goals`.`scorer` AS `scorer`
,`goals`.`goal_type` AS `goal_type`
,`goals`.`goal_time` AS `goal_time`
,`players`.`player_id` AS `player_id`
,`players`.`surname` AS `surname`
,`players`.`firstname` AS `firstname`
,`players`.`date_of_birth` AS `date_of_birth`
,`players`.`position` AS `position`
,`players`.`image` AS `image`
,`players`.`date_joined` AS `date_joined`
,`players`.`date_left` AS `date_left`
,count((case `games`.`comp` when 1 then 1 else NULL end)) AS `lgegls`
,count((case `games`.`comp` when 2 then 1 else NULL end)) AS `facgls`
,count((case `games`.`comp` when 3 then 1 else NULL end)) AS `fatgls`
,count((case `games`.`comp` when 4 then 1 else NULL end)) AS `kscgls`
,count((case `games`.`comp` when 5 then 1 else NULL end)) AS `lgecgls`
,count(`goals`.`goal_id`) AS `tgls`
from (`all_games` `games` join (`goals` join `players` on((`goals`.`scorer` = `players`.`player_id`)))) 
where ((`goals`.`match` = `games`.`all_games_id`) 
and (`games`.`comp` <> 6)) 
group by `players`.`player_id`;

     

    The query that brings it all together (showing just 'comp' = 1 data at the moment) with the actual minutes per game as 'lminpg' is

    SELECT 
view1.player_id as theRealId
, view1.surname as theRealSurname
, view1.firstname as theRealFirstname
, view1.lgemins/view2.lgegls as lminpg
, view1.date as theDate
, view1.*
, view2.* 
FROM view1 
LEFT JOIN view2 on view1.player_id=view2.player_id 
ORDER BY lminpg ASC

     

    All of the pages on the site use posted data to filter by season

    if (isset($_GET['season_id']) ? $_GET['season_id'] : 1);
$season_id = $_GET['season_id'];

    using the following in the query

    LEFT JOIN seasons as S ON all_games.date 
BETWEEN S.season_start AND S.season_end
WHERE S.season_id = $season_id

     

    I need to implement something similar with the query from the views but have come up against blanks! If I use something like

    WHERE view1.date BETWEEN '2010-08-01' AND  '2011-05-01'

    which is equivalent to 'S.season_start AND S.season_end' for the 2010-11 season, some results are returned but nowhere near the correct number. I am assuming that the date fields from both views need to be joined but everything I have tried just returns the 'die' error message.

     

    I am probably missing something very simple here but I just cannot get my head around it! If there is any more information required (such as test data) then just let me know.

     

    As always, any pointers would be gratefully received.

    Thanks in advance

    Steve

    Show Last N records per group

    in MySQL Help

    Posted

    Absolutely 100% perfect!!

     

    I made a couple of slight amendments in that I changed the ORDER BY in GROUP_CONCAT to date DESC to get the last 6 games for each team, and the overall ORDER BY is now on Pts then GD. I also joined the seasons table so I can show it season by season and my test run works exactly how it should - http://www.margate-fc.com/content/test/cform3.php.

     

    All that is left to do is to turn the case statements into reusable strings just to tidy the code up but massive, massive thanks to you. I would not like to think how many hours have been spent on this!

     

    I have put my (current) final query below for other peoples reference.

     

    Thanks again

    Steve

     

    SELECT
   last_games.team_id
, last_games.team_name
, COUNT(DISTINCT `all_games`.`all_games_id`) AS 'GP'
, SUM(CASE WHEN all_games.`home_team` = last_games.team_id AND all_games.`home_goals` > all_games.`away_goals` THEN 1 ELSE 0 END) +  SUM(CASE WHEN all_games.`away_team` = last_games.team_id AND all_games.`home_goals` < all_games.`away_goals` THEN 1 ELSE 0 END) AS 'W'
, SUM(CASE WHEN all_games.`home_team` = last_games.team_id AND all_games.`home_goals` < all_games.`away_goals` THEN 1 ELSE 0 END) +  SUM(CASE WHEN all_games.`away_team` = last_games.team_id AND all_games.`home_goals` > all_games.`away_goals` THEN 1 ELSE 0 END) AS 'L'
, SUM(CASE WHEN all_games.`home_team` = last_games.team_id AND all_games.`home_goals` = all_games.`away_goals` THEN 1 ELSE 0 END) +  SUM(CASE WHEN all_games.`away_team` = last_games.team_id AND all_games.`home_goals` = all_games.`away_goals` THEN 1 ELSE 0 END) AS 'D'
, (SUM(CASE WHEN all_games.`home_team` = last_games.team_id THEN `all_games`.`home_goals` ELSE 0 END) 
   + SUM(CASE WHEN all_games.`away_team` = last_games.team_id THEN `all_games`.`away_goals` ELSE 0 END)) 
   - (SUM(CASE WHEN all_games.`home_team` = last_games.team_id THEN `all_games`.`away_goals` ELSE 0 END) 
   + SUM(CASE WHEN all_games.`away_team` = last_games.team_id THEN `all_games`.`home_goals` ELSE 0 END)) AS 'GD'
, ((SUM(CASE WHEN (all_games.`home_team` = last_games.team_id AND all_games.`home_goals` > all_games.`away_goals`)OR(all_games.`away_team` = last_games.team_id AND all_games.`home_goals` < all_games.`away_goals`) THEN 3 ELSE 0 END)
   + SUM(CASE WHEN (all_games.`home_team` = last_games.team_id OR all_games.`away_team` = last_games.team_id) AND all_games.`home_goals` = all_games.`away_goals` THEN 1 ELSE 0 END)))  AS P
, SUM(CASE WHEN all_games.`away_team` = last_games.team_id THEN 1 ELSE 0 END) AS 'AWAY_GAMES'
, SUM(CASE WHEN all_games.`home_team` = last_games.team_id THEN 1 ELSE 0 END) AS 'HOME_GAMES'
, SUM(CASE WHEN all_games.`away_team` = last_games.team_id THEN COALESCE(`all_games`.`away_goals`, 0) ELSE 0 END) AS 'GOALS_WHEN_AWAY'
, SUM(CASE WHEN all_games.`home_team` = last_games.team_id THEN COALESCE(`all_games`.`home_goals`,0) ELSE 0 END) +  SUM(CASE WHEN all_games.`away_team` = last_games.team_id THEN COALESCE(`all_games`.`away_goals`, 0) ELSE 0 END) AS 'GF'
, SUM(CASE WHEN all_games.`home_team` = last_games.team_id THEN COALESCE(`all_games`.`away_goals`,0) ELSE 0 END) +  SUM(CASE WHEN all_games.`away_team` = last_games.team_id THEN COALESCE(`all_games`.`home_goals`, 0) ELSE 0 END) AS 'GA'

FROM
  all_games
INNER JOIN 
(
SELECT
  	  DISTINCT
  	  b.`team_id`
	, b.`team_name`
	, SUBSTRING_INDEX(
      GROUP_CONCAT(a.`all_games_id` ORDER BY a.`date` DESC)
    ,',',6
  	) AS 'last_games_ids'
	FROM
  		all_games a
	 INNER JOIN teams b ON
  		  a.`away_team` = b.`team_id`
	  OR a.`home_team` = b.team_id
	 LEFT JOIN seasons as S ON a.date BETWEEN S.season_start AND S.season_end
  		WHERE a.comp = '1' 
   		AND S.season_id = 104
    	GROUP BY b.team_id
)
last_games ON
   	FIND_IN_SET(all_games.`all_games_id`, last_games.last_games_ids) >= 1
GROUP BY last_games.team_id
ORDER BY P DESC, GD DESC , GF DESC;

    Show Last N records per group

    in MySQL Help

    Posted

    In answer to both of you, in no particular order..........

     

    ebmigue - It is getting close but I am still having a problem!

     

    If I use your query leaving out WHERE last_games.`team_name` LIKE '%SUTTON%' I get a table that shows each teams name 6 times so that part of it works (http://www.margate-fc.com/content/test/cform2.php).

     

    If I add GROUP BY last_games.team_id just before the ORDER BY statement, it only shows each teams name once so the grouping is fine (http://www.margate-fc.com/content/test/cform3.php).

     

    However, no matter where I put the aggregate statement, it results in an empty table but I think that the problem is just my confusion and maybe I am using the wrong aliases.

     

    If I change my strings to

    $ht = "a.home_team = b.team_id";
$at = "a.away_team = b.team_id";

     

    And add the aggregate to find the number of games played

     ) AS 'last_games_ids'
  , count(CASE WHEN (".$ht." OR ".$at.") THEN 1 ELSE 0 END) as GP
FROM
  all_games a

    the theory should be that the 'P' column on the page should have '6' in it for every team but the column is showing up blank. If I can work out how to get that part working correctly at least, then I can move on with all the other aggregates and sorting by date descending (which all should be fairly simple once I have it working).

     

    fenway -  I revisited that link and have tried to implement it but I have got myself confused again.

    As I understand it, the grouper would be t.team_id as it is the teams that need to be grouped. The t_limiter would be all_games as that is where the dates are contained and the date is vital (IMO) to get the last 6 games by team.

    In the WHERE statement, I have translated 

    l.grouper = dl.grouper

    to

    WHERE  l.team_id IN  (dl.home_team,dl.away_team)

    and

    l.id = mid

    to

    l.date= dl.date

     

    But once again, it just returns an empty table. I even stripped the whole thing bare and started again with just the all_games table and still couldn't get that to work.

    It is frustrating as looking at what I am trying to do seems fairly simple and is used on soccer sites everywhere and looking at that example you posted I can understand the logic and how it works, but for the life of me, I just cannot get it to work the way I need.

     

     

    Thanks to both of you for your continuing patience and help.

     

    Steve

    Show Last N records per group

    in MySQL Help

    Posted

    Thanks also Fenway.

     

    I think the problem I am having is that all the instructions that have been offered, or i have found, have dealt with just one table but this one involves three.

     

    Below is a trimmed version of the query to make it easier to work with and I have attached a dump of the tables and data.

     

    Thanks again

    Steve

     

    $i = 1;

/*ADD th, st, rd TO THE POSITION NUMBERS*/
/*
* @return number with ordinal suffix
* @param int $number
* @param int $ss Turn super script on/off
* @return string
*/
function ordinalSuffix($i, $ss=0)
{
    /*** check for 11, 12, 13 ***/
    if ($i % 100 > 10 && $i %100 < 14)
    {
        $os = 'th';
    }
    /*** check if number is zero ***/
    elseif($i == 0)
    {
        $os = '';
    }
    else
    {
        /*** get the last digit ***/
        $last = substr($i, -1, 1);

        switch($last)
        {
            case "1": 
            $os = 'st';
            break;

            case "2":
            $os = 'nd';
            break;

            case "3":
            $os = 'rd';
            break;

            default:
            $os = 'th';
        }
    }

    /*** add super script ***/
    $os = $ss==0 ? $os : '<sup>'.$os.'</sup>';

    /*** return ***/
    return $i.$os;
};

/*GET THE CORRECT SEASON*/
if (isset($_GET['season_id']) ? $_GET['season_id'] : 1);
$season_id = $_GET['season_id'];

/*COMMON STANDINGS QUERY STRINGS*/
$ht = "g.home_team = t.team_id";
$at = "g.away_team = t.team_id";
$hw = "g.home_goals > g.away_goals";
$aw = "g.home_goals < g.away_goals";
$d = "g.home_goals = g.away_goals";
$hg ="g.home_goals";
$ag ="g.away_goals";

/*THE STANDINGS QUERY*/ 
$table = mysql_query("SELECT *,
t.team_name as Tm
, @rownum := @rownum+1 AS rank
, ((sum(CASE WHEN (".$ht." AND ".$hw.")OR(".$at." AND ".$aw.") THEN 3 ELSE 0 END)
+ sum(CASE WHEN (".$ht." OR ".$at.") AND ".$d." THEN 1 ELSE 0 END)))  AS P
, count(CASE WHEN (".$ht." OR ".$at.") THEN 1 ELSE 0 END) as GP
, sum(CASE WHEN (".$ht." AND ".$hw.") OR (".$at." AND ".$aw.") THEN 1 ELSE 0 END) AS W
, sum(CASE WHEN (".$ht." AND ".$d.") OR (".$at." AND ".$d.") THEN 1 ELSE 0 END) AS D
, sum(CASE WHEN (".$ht." AND ".$aw.") OR (".$at." AND ".$hw.") THEN 1 ELSE 0 END) AS L
, SUM(CASE WHEN (".$ht.") THEN ".$hg." WHEN (".$at.") THEN ".$ag." END) as GF
, SUM(CASE WHEN (".$ht.") THEN ".$ag." WHEN (".$at.") THEN ".$hg." END) as GA
, (SUM(CASE WHEN (".$ht.") THEN ".$hg." WHEN (".$at.") THEN ".$ag." END)
- SUM(CASE WHEN (".$ht.") THEN ".$ag." WHEN (".$at.") THEN ".$hg." END)) as GD
from teams t
left join all_games g 
on t.team_id in (g.home_team,g.away_team)
LEFT JOIN seasons as S ON g.date BETWEEN S.season_start AND S.season_end
LEFT JOIN deductions d ON ( d.team = t.team_id) AND (d.season = S.season_id)
WHERE comp = '1' AND home_goals IS NOT NULL 
AND S.season_id = $season_id 
GROUP BY t.team_id
ORDER BY P desc, GD desc, GF desc 
");

     

    [attachment deleted by admin]

    Show Last N records per group

    in MySQL Help

    Posted

    Apologies for the double quick post. I realised that I had put more code in the html file than was needed and also thought that it may be easier if code for just the bit that shows all games may be easier to work with so I have attached another version if that is better.

     

    Steve

     

    [attachment deleted by admin]

    Show Last N records per group

    in MySQL Help

    Posted

    Thanks ebmigue.

     

    I have attached an sql dump of the relevant tables and data, a text file with the the query and bits I have put into strings and the html of the bit that is being output. All have been the sql and html files have been given a .txt extension as it wouldn't let me attach them otherwise!

    An online test version (ignore the formatting) of the page can be found at http://follyball.co.uk/oss/standings.php.

     

    Basically, the page currently shows the standings of a soccer league with three sections, games where the team was the home side, games where the team was the visiting (away) side and all of the teams games. The home and away parts are just for reference and it is the 'All' games part where any sorting is done (firstly by points descending).

    What I am trying to achieve is to show the standings for just the last 6 (or any number) games for each team.

    It cannot be filtered by date as the games are not always played on the same day so some teams may have their 6 games go back over the last 6 weeks and some may have played 6 games in the last 3 weeks. In the table the 'P' column is games played and as you can see some teams by this point had played 20+ games while others had only played 15.

     

    In a nutshell I need to get each team, find the 6 most recent games for each of those teams, and then sort the standings based on those results.

     

    The nearest I ever got to making it work was for it to select the most recent 6 games from the database and not the most recent for each of the teams. My guess is that it would need to get the last 6 games for each team before doing any grouping but I am probably miles out!

     

    Thanks in advance for any suggestions.

     

    Steve

     

     

     

     

    [attachment deleted by admin]

    Looking for login and checkbox tutorials

    in Miscellaneous

    Posted

    Could anyone point me in the direction of a couple of tutorials? I have searched around but as they often give conflicting advice, I wondered if there were any definitive guides?

     

    Login System - with simple registration, admin backend, multiple user levels and restrictive access to pages or sections of pages (for example, guest may be blocked from seeing certain pages while only admins would see an 'edit this page' button. One complication I would need to overcome would be that, in addition to user level restrictions, there may be other restrictions such as certain members of different levels may need to access a downloads area.

     

    PhpAdmin style checkboxes - the ability to add, delete or edit multiple records based on checkbox selections.

     

    Thanks in advance

    Steve

    Date Created and Date Updated fields

    in MySQL Help

    Posted

    Can anyone point me in the direction of a good tutorial on Date Created and Date Updated fields?

     

    Quite simply they would need to do what it says on the tin, ie. one field automatically updates with the date the record was created and the other automatically updates with the date the record was updated.

     

    As a further complication, as it will be used on an existing site, I would ideally like to be able to manually update the Date Created field if required, although an additional field where a date could be manually entered and supercede the Date Created in queries would be a way around this.

     

    Thanks in advance

    Steve

    Initial steps of a 'On This Day in History' script

    in PHP Coding Help

    Posted

    A friend of mine is looking at relaunching a site he used to run and I am going to try to persuade him to move over to a dynamic site.

     

    The site is a Football Clubs history site and contains around 2,000 games and several hundred players. The obvious advantage of a dynamic site is that even the bits I just mentioned run to around 2,500 static pages, which could be reduced to just 2 pages if dynamically driven.

     

    I am fairly confident that I can do the conversion, but there is one thing that I already know I may struggle with and that is the page which shows game, births and deaths on this day in history. A rough example (using a very heavy Javascript code) can be found at http://follyball.co.uk/jeff/.

     

    In a simplified form (ignoring for now the foreign keys to relationships that these tables actually have) there will be two tables

    GAMES

    game_id | date | competition | opponent | score | attendance

     

    PLAYERS

    player_id | surname | firstname | date of birth | date of death

     

    What I would be looking for is a way to take todays date, and find any corresponding records in games.date, players.date of birth, players.date of death on the same date in previous years. This would be simple to do as three different queries but as I would like to actually mix the three and show them in date order, I would like to do it in one query, presumably using UNIONS.

     

    On top of this, I would also like a slightly different output for each different set, for example, if it was a game I would like

    year, competition, opponent, score, attendance,

     

    Whereas if it is a players birth or death, I would like it to show something like

    firstname surname was born in year

     

    Before I go delving too deep into how it may be done, I would like to know if it can be done! I cannot see why it would not be able to generate this but if it is going to be way out of my ability, then I will look for another option!

     

    Thanks in advance

    Steve

    Using PHP to update config file

    in PHP Coding Help

    Posted

    in case you rather use a textfile, maybe try this out I adjusted a little script at php.net to do the following.

    ...snip...

    That looks like it may well be just what I was after but will have to wait until after work tomorrow to try it as it is time for bed!

    Thanks

    Steve

    Using PHP to update config file

    in PHP Coding Help

    Posted

    Why don't you store the credentials in a database? Isn't that much easier, and maybe even more secure?

    That is the plan eventually (in fact I will hit it as soon as I next get some time of work), but for now I just need to get something simple set up that he can try in the next couple of days and be happy with, with very little time to spend on it.

     

    Having said that, if it is just as easy to put it in a database then I will happily do it that way. If I had 2 fields ('Username' and 'Password') would it be as simple as changing  

    $username = "user";
$password = "pass";

    to 

    $username = "SELECT username from table";
$password = "SELECT password from table";

    or would there be a bit more to it than that? All of the examples I have seen are more complex and involve separate log-in pages but I would rather (for now) have it all in the one page.

     

    Thanks

    Steve

×
×
  • 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.