0
There's still one problem with the integrated version though: it's unsecure. I tried to think of a solution myself, but I haven't been succesful and I really want this published, so does Dean I think. So maybe one of you guys has a solution for it?
Here's the code:
/*
Snippetname: MatchAdmin
Authors: Jelmer & Dean
Version 1.0, 28-02-06
MatchSuite allows you to manage a MySQL table
######## Installation ##########
Save the snippet in your Etomite installation. Call the snippet non-cachable ( that is [!MatchSuite!]) anywhere you want in your template. I suggest you use the PasswordProtect snippet or the EtoLoginOut snippet to protect your MatchSuite snippet from abuse.
MatchSuite requires two other snippets:
- PasswordProtect
- Xinha
Please download them in the Snippet Library on etomite.com.
For this snippet to work you need a table in your Etomite MySQL database named 'matches' which contains the following fields(type): id(auto-increment), date(date), time(time), finished(int), team1(varchar), team2(varchar), score1(char), score2(char), content(longtxt).
To set such a table up feed the following SQL statement to your MySQL manager (e.g. PHPMyAdmin):
------ START SQL
CREATE TABLE `matches` (
`id` INT NOT NULL AUTO_INCREMENT ,
`date` DATE NOT NULL ,
`time` TIME NOT NULL ,
`team1` VARCHAR( 50 ) NOT NULL ,
`team2` VARCHAR( 50 ) NOT NULL ,
`finished` INT DEFAULT '0',
`score1` CHAR( 2 ) ,
`score2` CHAR( 2 ) ,
`content` LONGTEXT ,
PRIMARY KEY ( `id` )
) TYPE = MYISAM;
-------- END SQL
Now you should be able to add your first matches!
######## End installation ##########
######## CSS Styling ##########
You can use the following CSS classes to make the menu work like it should:
.match_notice {
color:red;
padding-bottom: 10px;
}
.match_title {
padding-top: 10px;
padding-bottom: 5px;
}
.match_list {
margin:0;padding:0;
}
.match_list li {
display:inline;
margin:0;padding:0;
font-size:100%;
}
.match_list .match_active {
}
.match_list a {
margin:0;padding:2px 12px;
border-bottom:1px solid #AAA;
border-top:1px solid #EEE;
border-left:1px solid #AAA;
text-decoration:none;
background:#EEE;
}
.match_list .match_active a {
background:#FFF;
border:1px solid #AAA;
border-bottom-color:#FFF;
}
.match_list a:hover {background:#F60;color:#FFF;}
.match_list .match_active a:hover {background:#F90;}
######## End CSS Styling ##########
######## Configuration ##########
Change the following variables if necessary:
*/
$action = isset($action) ? $action : $_GET['action'];
$action = isset($action) ? $action : "overview";
//Title for the 'Overview' page
$overviewtitle="Overview";
//Title for the 'Add' page
$addtitle="Add a match";
//Title for the 'Edit' page
$edittitle="Edit a match";
$resultstitle="Latest results:";
$noresults="No results yet";
$nrofresults = 10;
$comingtitle="Next 5 games:";
$nogames="No more games...";
//number of upcoming games to show
$nrofgames = 5;
//For this moment, the rest of the variables can be edited below, in the code
//Set the password for the password-protected pieces of the snippet
$password="letmein";
/*
######## End configuration ##########
*/
//resetting output
$output="";
//fetching current id for navigation purposes
$id = $etomite->documentIdentifier;
//fetching display order
$order = ($_GET['order'] <> "") ? $_GET['order'] : $_SESSION['order'];
if($order == "") {$order = "asc";}
//putting $order in session
$_SESSION['order'] = $order;
//fetching the id of the game to be edited/deleted/viewed
$nr = $_GET['nr'];
//checking whether something has been posted
$gepost = $_GET['gepost'];
//determine a title
if($action=="overview") {$title = $overviewtitle; }
if($action=="add") {$title = $addtitle;}
if($action=="edit") {$title = $edittitle;}
//variables that might have been posted
$date = $_POST['date'];
//correcting the date to the standard scientific notation for entry in the database
$date = substr($date,0,4)."-".substr($date,4,2)."-".substr($date,6,2);
$time = $_POST['time'];
//correcting the time for entry in the database
$time = substr($time,0,2).":".substr($time,2,2);
//fetching team names and scores
$team1 = $_POST['team1'];
$team2 = $_POST['team2'];
$score1 = $_POST['score1'];
$score2 = $_POST['score2'];
$finished = $_POST['finished'];
$content = $_POST['content'];
//if the action is DELETE, delete the appropriate record. The id ($nr) is passed on in the URL
if($action=="delete") {
$output.="";
if($rs=$etomite->dbQuery("DELETE FROM ".$etomite->dbConfig['dbase'].".matches WHERE id='$nr'")) {
$notice="The record with id ".$nr." has been deleted";
$action="overview";
} else {
$notice="Delete Failed";
$action="failed";
}
}
//if a new row has been posted, insert it
if($gepost=="new") {
if ($rs=$etomite->dbQuery("INSERT INTO ".$etomite->dbConfig['dbase'].".matches (date,time,team1,team2,finished,score1,score2,content) VALUES ('$date','$time','$team1','$team2','$finished','$score1','$score2','$content')")) {
$notice="Record added";
$notice="The record with id ".$nr." has been added";
$action="overview";
} else {
$notice="Add failed";
$action="failed";
}
}
//if a modified row has been posted, update it
if($gepost=="edit") {
if ($rs=$etomite->dbQuery("UPDATE ".$etomite->dbConfig['dbase'].".matches SET date='$date',time='$time',team1='$team1',team2='$team2',finished='$finished',score1='$score1',score2='$score2',content='$content' WHERE id='$nr'")) {
$notice="The record with id ".$nr." has been changed";
$action="overview";
} else {
$notice="Edit failed";
$action="failed";
}
}
if($action=="failed") {
//dunno what to do with it yet. let's just give an overview for now.
$action="overview";
}
//building menu, title and a notice class
if($action=="add") {$highlightadd=" class='match_active'"; } else { $highlightadd=""; }
if($action=="overview") {$highlightoverview=" class='match_active'"; } else { $highlightoverview=""; }
$output .= "<ul class='match_list'>";
$output .= "<li".$highlightoverview."><a href='[~".$id."~]&action=overview&order=".$order."'>Overview</a></li>";
$output .= "<li".$highlightadd."><a href='[~".$id."~]&action=add'>Add a match</a></li>";
$output .= "</ul>";
$output .= "<h1 class='match_title'>".$title."</h1>";
$output .= "<span class='match_notice'>".$notice."</span>";
//form to add a new row
if($action=="add") {
$output .= "[[PasswordProtect?password=$password]]
<table><form method='post' action='[~".$id."~]&gepost=new'>
<tr><td>Date: (yyyymmdd)</td><td><input type='text' size='8' name='date'></td></tr>
<tr><td>Time: (hhmm)</td><td><input type='text' size='4' name='time'></td></tr>
<tr><td>Who against who:</td><td><input type='text' size='15' name='team1'> - <input type='text' size='15' name='team2'></td></tr>
<tr><td>Game played: (check)</td><td><input type='checkbox' name='finished' value='1'></td></tr>
<tr><td>Final score:</td><td><input type='text' size='2' name='score1'> - <input type='text' size='2' name='score2'></td></tr>
<tr><td>Summary:</td><td><textarea name='content' id='content' cols='40' rows='6'>".$row['content']."</textarea></td></tr>
<tr><td colspan='1'><input type='submit' value='Add match'></td></tr>
</form></table>[[xinha?areas='content'&width=500]]";
}
//form to edit a row
if($action=="edit") {
if ($query=$etomite->dbQuery("SELECT * FROM ".$etomite->dbConfig['dbase'].".matches WHERE id='$nr'")) {
$row = mysql_fetch_array($query);
$time = substr($row['time'],0,2).substr($row['time'],3,2);
$date = substr($row['date'],0,4).substr($row['date'],5,2).substr($row['date'],8,2);
//variabele finished is a true/false variable, if true then the checkbox is checked
if($row['finished']=="1"){$finished=" checked";} else {$finished="";}
$output .= "[[PasswordProtect?password=$password]]
<form method='post' action='[~".$id."~]&gepost=edit&nr=".$row['id']."'><table>
<tr><td>Date: (yyyymmdd)</td><td><input type='text' size='8' name='date' value='".$date."'></td></tr>
<tr><td>Time: (hhmm)</td><td><input type='text' size='4' name='time' value='".$time."'></td></tr>
<tr><td>Who against who:</td><td><input type='text' size='15' name='team1' value='".$row['team1']."'> - <input type='text' size='20' name='team2' value='".$row['team2']."'></td></tr>
<tr><td>Game played:</td><td><input type='checkbox' name='finished' value='1'".$finished."></td></tr>
<tr><td>Final score:</td><td><input type='text' size='2' name='score1' value='".$row['score1']."'> - <input type='text' size='2' name='score2' value='".$row['score2']."'></td></tr>
<tr><td>content:</td><td><textarea name='content' id='content' cols='40' rows='6'>".$row['content']."</textarea></td></tr>
<tr><td colspan='4'><input type='submit' value='Edit'></td></tr>
</table></form>[[xinha?areas='content'&width=500]]";
}
else {
$output .= "Can't find the data.";
}
}
if($action=="show") {
//action=show?matchid=x
$matchid = $_GET['matchid'];
$title="Match Summary";
$noresults="No results yet";
//number of results to show
$nrofresults = 10;
$output = "<h1 class='match_title'>$title</h1>";
//date of today, for comparison in the database
$today = date("Ymd");
if($result=$etomite->dbQuery("Select * FROM ".$etomite->dbConfig['dbase'].".matches WHERE id='$matchid' ORDER BY date DESC LIMIT $nrofresults")) {
$rows = mysql_num_rows($result);
if($rows == "0") { $output .= $noresults; }}
$i = 0;
while ($i < $rows) {
$id=mysql_result($result,$i,"id");
$date=mysql_result($result,$i,"date");
$team1=mysql_result($result,$i,"team1");
$team2=mysql_result($result,$i,"team2");
$score1=mysql_result($result,$i,"score1");
$score2=mysql_result($result,$i,"score2");
$content=mysql_result($result,$i,"content");
$tijd = substr($tijd,0,5);
if ($date==$lastdate) {$displaydate = "";}
else {
if(isset($lastdate)) { $displaydate = "<br />"; }
$jaar = substr($date,0,4);
$maand = substr($date,5,2);
$dag = substr($date,8,2);
$displaydate .= "<b>".$dag."-".$maand."-".$jaar."</b><br />";
}
$lastdate = $date;
$output .= $displaydate;
$output .= "<b>".$team1."</b> <i>versus</i> <b>".$team2."</b><br />".$content."<br /><br /><b><i>Final Score:</i></b><br /> ".$team1." : ".$score1." - ".$team2." : ".$score2."";
$i++;
}
$output .= "<br />";
}
if($action=="overview") {
//gives an overview of all the rows
$output .= "[[PasswordProtect?password=$password]]<span style='float: left;'><a href='[~".$id."~]&order=asc'>Sort ascending</a> || <a href='[~".$id."~]&order=desc'>Sort descending</a></span><br /><br />";
$output .= "<table>";
$output .= "<tr><td><strong>ID</strong></td><td><strong>time</strong></td><td><strong>Team1</strong></td><td> </td><td><strong>Team2</strong></td><td> </td><td></td><td colspan='3'><strong>Score</strong></td></tr>";
if ($result=$etomite->dbQuery("Select * FROM ".$etomite->dbConfig['dbase'].".matches ORDER BY date $order,time $order")) {
$rows = mysql_num_rows($result);
if($rows==0) { $output .= "<tr><td colspan='8'>No Records Given</td></tr>"; }
$i = 0;
while ($i < $rows) {
$nr=mysql_result($result,$i,"id");
$date=mysql_result($result,$i,"date");
$time=mysql_result($result,$i,"time");
$time=substr($time,0,5);
$team1=mysql_result($result,$i,"team1");
$team2=mysql_result($result,$i,"team2");
$finished=mysql_result($result,$i,"finished");
$score1=mysql_result($result,$i,"score1");
$score2=mysql_result($result,$i,"score2");
$content=mysql_result($result,$i,"content");
if($finished=="1") {$finished="<i>(finished)</i>";} else {$finished="";}
//display a new date only when it's different from the last date. this way rows get grouped by date
if ($date==$lastdate) {$displaydate = "";}
else {
$jaar = substr($date,0,4);
$maand = substr($date,5,2);
$dag = substr($date,8,2);
$displaydate = "<tr><td colspan='8'><br /><b>".$dag."-".$maand."-".$jaar."</b></td></tr>";
}
$lastdate = $date;
$output .= $displaydate;
$output .= "<tr><td>".$nr."</td><td>".$time."</td><td>".$team1."</td><td>-</td><td>".$team2."</td><td>".$finished."</td><td>".$score1."</td><td>-</td><td>".$score2."</td><td><a href='[~".$id."~]&action=edit&nr=".$nr."'>edit</a></td><td><a href='[~".$id."~]&action=delete&nr=".$nr."'>delete</a></td><td><a href='[~".$id."~]&action=show&matchid=".$nr."'>preview</a></td></tr>";
$i++;
}
}
else {
$output .= "<tr><td colspan='8'>Can't connect to the database</td></tr>";
}
}
$output .= "</table>";
if($action=="results") {
// show the latest results page
$output = "<h1 class='match_title'>$resultstitle</h1>";
//date of today, for comparison in the database
$today = date("Ymd");
if($result=$etomite->dbQuery("Select * FROM ".$etomite->dbConfig['dbase'].".matches WHERE finished='1' ORDER BY date DESC LIMIT $nrofresults")) {
$rows = mysql_num_rows($result);
if($rows == "0") { $output .= $noresults; }}
$i = 0;
while ($i < $rows) {
$id=mysql_result($result,$i,"id");
$date=mysql_result($result,$i,"date");
$team1=mysql_result($result,$i,"team1");
$team2=mysql_result($result,$i,"team2");
$score1=mysql_result($result,$i,"score1");
$score2=mysql_result($result,$i,"score2");
$tijd = substr($tijd,0,5);
if ($date==$lastdate) {$displaydate = "";}
else {
if(isset($lastdate)) { $displaydate = "<br />"; }
$jaar = substr($date,0,4);
$maand = substr($date,5,2);
$dag = substr($date,8,2);
$displaydate .= "<b>".$dag."-".$maand."-".$jaar."</b><br />";
}
$lastdate = $date;
$output .= $displaydate;
$output .= $team1." - ".$team2.": ".$score1." - ".$score2."<br />";
$i++;
}
$output .= "<br />";
}
if($action=="summary") {
//dunno what to do with it yet. let's just give an overview for now.
//resetting output
$output="";
$summarytitle="Summary";
//fetching current id for navigation purposes
$id = $etomite->documentIdentifier;
//fetching display order
$order = ($_GET['order'] <> "") ? $_GET['order'] : $_SESSION['order'];
if($order == "") {$order = "asc";}
//putting $order in session
$_SESSION['order'] = $order;
//fetching action to be taken
$action = $_GET['action'] ? $_GET['action'] : "summary";
//fetching the id of the game to be edited/deleted
$nr = $_GET['nr'];
//checking whether something has been posted
//$gepost = $_GET['gepost'];
//determine a title
if($action=="summary") {$title = $summarytitle; }
//variables that might have been posted
$date = $_POST['date'];
//correcting the date to the standard scientific notation for entry in the database
$date = substr($date,0,4)."-".substr($date,4,2)."-".substr($date,6,2);
$time = $_POST['time'];
//correcting the time for entry in the database
$time = substr($time,0,2).":".substr($time,2,2);
//fetching team names and scores
$team1 = $_POST['team1'];
$team2 = $_POST['team2'];
$score1 = $_POST['score1'];
$score2 = $_POST['score2'];
$finished = $_POST['finished'];
$content = $_POST['content'];
$output .= "<h1 class='match_title'>".$title."</h1>";
$output .= "<table style='width:100%;'>";
$output .= "<tr><td>Time</td><td><b>Team 1</b></td><td><i>v</i></td><td><b>Team 2</b></td><td>Completed?</td></tr>";
if ($result=$etomite->dbQuery("Select * FROM ".$etomite->dbConfig['dbase'].".matches ORDER BY date $order,time $order")) {
$rows = mysql_num_rows($result);
if($rows==0) { $output .= "<tr><td colspan='8'>Nothing Found</td></tr>"; }
$i = 0;
while ($i < $rows) {
$nr=mysql_result($result,$i,"id");
$date=mysql_result($result,$i,"date");
$time=mysql_result($result,$i,"time");
$time=substr($time,0,5);
$team1=mysql_result($result,$i,"team1");
$team2=mysql_result($result,$i,"team2");
$finished=mysql_result($result,$i,"finished");
$score1=mysql_result($result,$i,"score1");
$score2=mysql_result($result,$i,"score2");
$content=mysql_result($result,$i,"content");
if($finished=="1") {$finished="<font style='color:green;'><b>Yes</b> <a href='[~".$id."~]&action=show&matchid=".$nr."'>View</a></font>";} else {$finished="<font style='color:red;'><i>No</i></font>";}
//display a new date only when it's different from the last date. this way rows get grouped by date
if ($date==$lastdate) {$displaydate = "";}
else {
$jaar = substr($date,0,4);
$maand = substr($date,5,2);
$dag = substr($date,8,2);
$displaydate = "<tr><td colspan='4'><br /><b>".$dag."-".$maand."-".$jaar."</b></td></tr>";
}
$lastdate = $date;
$output .= $displaydate;
$output .= "<tr><td>".$time."</td><td><b>".$team1."</b></td><td><i>v</i></td><td><b>".$team2."</b></td><td>".$finished."</td></tr>";
$i++;
}
}
else {
$output .= "<tr><td colspan='8'>Can't connect to the database</td></tr>";
}
$output .= "</table>";
return $output;
}
if($action=="coming") {
// shows the coming page
$output = "<h1 class='match_title'>$comingtitle</h1>";
//fetch the date of today for comparison in the database
$today = date("Ymd");
if($result=$etomite->dbQuery("Select * FROM ".$etomite->dbConfig['dbase'].".matches WHERE date >= '$today' AND finished <> '1' ORDER BY date,time ASC LIMIT $nrofgames")) {
$rows = mysql_num_rows($result);
if($rows == "0") { $output .= $nogames; } }
$i = 0;
while ($i < $rows) {
$id=mysql_result($result,$i,"id");
$date=mysql_result($result,$i,"date");
$time=mysql_result($result,$i,"time");
$team1=mysql_result($result,$i,"team1");
$team2=mysql_result($result,$i,"team2");
$time = substr($time,0,5);
// if the first game of a new day is being displayed, print the date as well
if ($date==$lastdate) {$displaydate = "";}
else {
if(isset($lastdate)) { $displaydate = "<br />"; }
$jaar = substr($date,0,4);
$maand = substr($date,5,2);
$dag = substr($date,8,2);
$displaydate .= "<b>".$dag."-".$maand."-".$jaar."</b><br />";
}
$lastdate = $date;
$output .= $displaydate;
$output .= $time.": ".$team1." - ".$team2."<br />";
$i++;
}
$output .= "<br />";
}
if($action=="search") {
//search form page
$searchString =
isset($_POST['search']) &&
$_POST['search']!= "{{" &&
$_POST['search']!= "[[" &&
$_POST['search']!= "[(" &&
$_POST['search']!= "[~" &&
$_POST['search']!= "[*" ?
$_POST['search'] : "";
$SearchForm .= '<form name="SearchForm" action="" method="post">';
$SearchForm .= '<input type="text" name="search" class="text" value="'.$searchString.'"><br />';
$SearchForm .= '<input type="submit" name="sub" class="button" value="Search">';
$SearchForm .= '</form>';
if(isset($_POST['search']) && $_POST['search']!='') {
$search = explode(" ", $_POST['search']);
$tbl = $etomite->dbConfig['dbase'].".matches";
$sql = "SELECT id, team1, team2, score1, score2 FROM $tbl WHERE ($tbl.content LIKE '%".$search[0]."%'";
for ($x=1;$x < count($search); $x++) {
$sql .= " AND $tbl.content like '%$search[$x]%'";
}
$sql .= " OR $tbl.team1 LIKE '%".$search[0]."%' ";
for ($x=1;$x < count($search); $x++) {
$sql .= " AND $tbl.team1 like '%$search[$x]%'";
}
$sql .= " OR $tbl.team2 LIKE '%".$search[0]."%' ";
for ($x=1;$x < count($search); $x++) {
$sql .= " AND $tbl.team2 like '%$search[$x]%'";
}
$sql .= ") AND $tbl.finished = 1;";
$rs = $etomite->dbQuery($sql);
$limit = $etomite->recordCount($rs);
if($limit>0) {
$SearchForm .= "<p>The following results were found:</p><p><table cellspacing='0' cellpadding='0'>";
for ($y = 0; $y < $limit; $y++) {
$SearchFormsrc=$etomite->fetchRow($rs);
$SearchForm.="<tr><td style='padding: 1px'><a href='[~24~]&action=show&matchid=".$SearchFormsrc['id']."'><b>".$SearchFormsrc['team1']." v ".$SearchFormsrc['team2']."</b></a> </td><td style='padding: 1px'>";
$SearchForm.=" <small>".$SearchFormsrc['score1']." : ".$SearchFormsrc['score2']."</small>";
$SearchForm .= "</td></tr>";
}
$SearchForm .= "</table>";
} else {
$SearchForm.="<p>Sorry, couldn't find anything!</p>";
}
}
return $SearchForm;
}
return $output;
Problems:
- The snippet uses $_GET to determine the action of the snippet. But this way it can delete things without the password that's necessary for the admin section of the snippet.
- Code's still a bit sloppy, will correct that later.
Any ideas on the unsecure thing?
