I’m creating a site for a local football team. To view the matches I’m using 2 tables vid. teams (all the information of a team) and matches.
To have an overview of all the matches I use this query:
SELECT matches.matchid, CONCAT( ELT( DAYOFWEEK( matches.matchdate ) ,'zon', 'maa', 'din', 'woe', 'don', 'vrij', 'zat' ) , ' ',
DAYOFMONTH( matches.matchdate ) , ' ',
ELT( MONTH( matches.matchdate ) , 'jan', 'feb', 'maa', 'apr', 'mei','jun', 'jul', 'aug', 'sep', 'okt', 'nov', 'dec' ) ,' ',
year( matches.matchdate )
) AS datum_NL, TIME_FORMAT( matches.matchtime, '%H:%i' ) AS UUR
matches.matchtype, matches.division, matches.season, teams.teamname AS thuisploeg, teamhomescore.teamname AS bezoekers, matches.homescore, matches.awayscore, matches.matchday
FROM teams AS teamhomescore
INNER JOIN (teams
JOIN matches ON teams.teamid = matches.teamhome) ON teamhomescore.teamid = matches.teamaway
ORDER BY matches.matchdate
To get the data in my editform I use next query :
SELECT matches.matchid, CONCAT( ELT( DAYOFWEEK( matches.matchdate ) , 'zon', 'maa', 'din', 'woe', 'don', 'vrij', 'zat' ) , ' ',
DAYOFMONTH( matches.matchdate ) ,
' ',
ELT( MONTH( matches.matchdate ) , 'jan', 'feb', 'maa', 'apr', 'mei', 'jun', 'jul', 'aug', 'sep', 'okt', 'nov', 'dec' ) ,' ',
year( matches.matchdate )) AS datum_NL,
TIME_FORMAT( matches.matchtime, '%H:%i' ) AS UUR,
matches.matchtype, matches.division, matches.season, teams.teamname AS thuisploeg, teamhomescore.teamname AS bezoekers, matches.homescore, matches.awayscore, matches.matchday
FROM teams AS teamhomescore
INNER JOIN (
teams
INNER JOIN matches ON teams.teamid = matches.teamhome
) ON teamhomescore.teamid = matches.teamaway
WHERE matchid=$matchid
So far so good. Now I want to update the data, so there I have a problem. Do I have to reverse the query when I update my record or how I have to do that? This is the basic query that I have
UPDATE matches SET matchdate='$matchdate', matchtime='$matchtime' , season='$season', division='$division', matchtype='$matchtype' , teamhome='$thuisploeg', teamaway='$teamaway', homescore=IF('$homescore'='',NULL,'$homescore') ,awayscore=IF('$awayscore'='',NULL,'$awayscore'), matchday=$matchday WHERE matchid='$matchid'