PHP and MYSQL event(ADD/EDIT/DELETE) system

In this tutorial we will take a look at how to create a basic event system using PHP and MYSQL. The system will have an option to add, edit and delete events as desired. It will contain two files, one for manipulating events and the other to display the output. The former I will name addeditdel.php and the latter index.php. The style.css file will be added as a visual enhancement to the system which of course you may change according to your own likings. Regarding MYSQL table you will need to add the following to your database:


CREATE TABLE `events` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `date` date NOT NULL,
  `time` varchar(5) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `event` varchar(150) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf-8 COLLATE=utf8_bin;

You may have noticed that time will be added as a simple VARCHAR of 5 that will be complimented by the PHP preg_match() condition to target specific input pattern and return false, if not matched. The tutorial will be done in 3 parts, first shedding light on the system, second to display the output and third for defining the CSS.

PART 1 - Create the event system (addeditdel.php)

First thing that needs to be done is to start a session but this time we will call for the ob_start() function first to catch the output buffer and send it at the end of the script instead, to accomodate the PHP header() function. CSS file will also be included in the head tag. The code will be done in Procedural PHP so make sure to include the right database configuration file.


<?php
ob_start();
session_start();
?>
<head>
<title>Event system</title>
<link> rel="stylesheet" href="style.css" type="text/css" />
</head>
<body>

To avoid repeating the code in the process we will first create two functions to handle errors and process the user input before saving it to database which will be called upon in the condition statements.


<?php
include_once('db_config.php');

function errors() {

	// Create empty variable to which errors will be appended
	$errs = '';
	
	if(empty($_POST['date'])) {$dateerror = '<span class="error">Date field is empty!</span><br/>';}
	elseif(!strtotime($_POST['date'])) {$dateerror = '<span class="error">Invalid date!</span><br/>';}

	// Create time error if no input or if it does not match specific pattern(2 digits, colon, 2 digits)
	if(empty($_POST['time'])) {$timeerror = '<span class="error">Time field is empty!</span><br/>';}
	elseif(!preg_match("/\d{2}:\d{2}/", $_POST['time'])) {$timeerror = '<span class="error">Wrong time pattern! Try this example: 08:30</span><br/>';}
		
	// Create event error if no event input was provided
	if(empty($_POST['event'])) {$eventerror = '<span class="error">Event field is empty!</span><br/>';}

	// Append errors to the $errs variable
	$errs .= $daterror.$timeerror.$eventerror;

	// Return errors
	return $errs;
}

// Note that $mysqli variable is passed from db_config file
function safe_db_vars($mysqli) {

	// Sanitize the inputs, escaping to prevent SQL injection and encode special chars to prevent XSS
	// All before actually saving it to MYSQL so we won't have to worry about it later
	$date = htmlspecialchars(mysqli_real_escape_string($mysqli, $_POST['date']));	
	$time = htmlspecialchars(mysqli_real_escape_string($mysqli, $_POST['time']));
	$event = htmlspecialchars(mysqli_real_escape_string($mysqli, $_POST['event']));

	// return array that will be called on when needed
	return array($date, $time, $event);
}

Now the conditions will be created about what happens when the post is being edited, when the post is being deleted and when the post is being added. Only after, we will create input form and tables to display the entries with edit and delete buttons.


// Option that gets executed with the edit button in events table(see below). It is passed to url which then triggers this condition.
if($_GET['edit'] === 'entry') {

	// Get the id from url, escape it and save to variable
	// Create a query from events where id in DB matches to the one got from url
	// Fetch an array of the DB row and create variables from DB columns
	// Note that $mysqli variable is from db_config.php file
	$id = mysqli_real_escape_string($mysqli, $_GET['id']);
	$query = mysqli_query($mysqli, "SELECT * FROM events WHERE id = '$id'");
	$fetch = mysqli_fetch_array($query);
	$sqldate = $fetch['date'];
	$sqltime = $fetch['time'];
	$sqlevent = $fetch['event'];

	// If button edit is set, clicked on(to edit the post, see below), execute below...
	if(isset($_POST['edits'])) {

		// Save errors from errors function to a variable
		// If the $errors variable is empty, execute below...
		$errors = errors();
		if(empty($errors)) { 

			// Get the list of variables needed for the query from the function
			list($date, $time, $event) = safe_db_vars($mysqli);

			// Update columns in the row that's being updated in the events table
			mysqli_query($mysqli, "UPDATE events SET date = '$date', time = '$time', event = '$event' WHERE id = '$id'");

			// Refresh the page
			header("Location:addeditdel.php");
		}
	}
}


// Option that gets executed with the delete button in events table(see below). It is passed to url which then triggers this condition.
elseif($_GET['delete'] === 'entry') {

	// Get the id from url, escape it and save to variable
	// Then delete the row from the table where id from DB matches the one from url. Refresh the page after
	$id = mysqli_real_escape_string($mysqli, $_GET['id']);
	mysqli_query($mysqli, "DELETE FROM events WHERE id = '$id'");
	header("Location:addeditdel.php");
}

 Else wait for the add button to be clicked on
else {

	if(isset($_POST['add'])) {

		// Same process as with edit option
		$errors = errors();
		if(empty($errors)) { 

			list($date, $time, $event) = safe_db_vars($mysqli);
			
			// The difference is that now the row is being inserted instead of updated
			mysqli_query($mysqli, "INSERT INTO events(date, time, event) VALUES('$date', '$time','$event')");
			
			// Create a success variable to be displayed just above the output table(see below)
			$success = "<span style="color:green;">Data added successfully.</span><br/>";
		}
	}
}
?>

So the functionality is now set, all we need to do now is to create a form to process all the inputs and append buttons to it. We will do that with HTML tables.


<form action="" method="post" name="form">
<table width="440">
<tr>
<td>Date:</td>
<td>Time:</td>
<td>Event:</td>
</tr>
<tr> 

// The inputs will be used for both add and edit option, the reason PHP condition is added to the value
// The PHP variables are created from the columns of the row that's being edited
<td><input type="date" name="date" size="3" value="<?php if($_GET['edit'] === 'entry') {echo $sqldate;} ?>"></td>
<td><input type="text" name="time" size="3" maxlength="5" value="<?php if($_GET['edit'] === 'entry') {echo $sqltime;} ?>"></td>
<td><input type="text" name="event" size="28" value="<?php if($_GET['edit'] === 'entry') {echo $sqlevent;} ?>"></td>
</tr>
<tr> 

// If edit option is initiated, display edit and back button, else display add button
// Classes are added to buttons because of the styling
<td><?php if($_GET['edit'] === 'entry') {echo '<button type="submit" class="add" name="edits"/>Edit</button>'.'<a class="delete" href="addeditdel.php">Back</a>';} else {echo '<button type="submit" class="add" name="add"/>Add</button>';} ?></td>
</tr>
</table>
</form>

All that remains now is to display the already created rows in MYSQL events table and attach edit and delete buttons to each row. For this purpose we will create another HTML table with 5 columns, for date, time, event, edit button and delete button.


// Display errors or success
<?php echo $success.$errors; ?>
<table width="600">
<tr bgcolor='#dedede'>
<td>Date:</td>
<td>Time:</td>
<td>Event:</td>
<td width="70"></td>
<td width="70"></td>
</tr>
<?php
// Query to select all rows from events in date ascending and time ascending order
$query = mysqli_query($mysqli, "SELECT * FROM events ORDER BY date ASC, time ASC");

// Loop through the query, create an array and display the three columns for each row that exists
while($fetch = mysqli_fetch_array($query)) { 
        
	echo "<tr>";
	echo "<td class='lines'>".$fetch['date']."</td>";
	echo "<td class='lines'>".$fetch['time']."</td>";
	echo "<td class='lines'>".$fetch['event']."</td>";

	// Add edit and delete buttons to the rows, match them on id of the row.
	// Build a link that points on the same file but creates additional url elements that activate the conditions above
	echo "<td class='lines'><a class='edit' href='addeditdel.php?edit=entry&id=$fetch[id]'>Edit</a></td>";   
	echo "<td class='lines'><a class='delete' href='addeditdel.php?delete=entry&id=$fetch[id]'>Delete</a></td>";
	echo "</tr>";
}
?>
</table>
// Close the body
</body>

That's it. The script is now functional and would work even without the CSS file. However, this is only the backend part. The frontend, where the actual events will be displayed, still needs to be created.

PART 2 - Display the events (index.php)

In this part, we will create a script that will get the information from MYSQL database, specifically from events table and display them in date, time ascending order. All the events that have the same date will be grouped under that date and then sorted by time in ascending order. Since this is the frontend, we will avoid HTML tables and use simple headings and paragraphs to display the output


// Link to style.css
<head>
<title>Events</title>
<link> rel="stylesheet" href="style.css" type="text/css" />
</head>
<body>
<h1>Events</h1>
<?php
// Include the DB config file
include_once('db_config.php');

// Select all from events in ascending order
// Create an empty variable to be compared against the date from MYSQL table
$query = mysqli_query($mysqli, "SELECT * FROM events ORDER BY date ASC, time ASC");
$prevdate = '';

while($res = mysqli_fetch_array($query)) {

	// Create variables from columns of each row in the loop
	// Format the date to be displayed as day, month, year but still sorted as year, month, day. This can be changed according to your own likings
	$date = date("d.m.Y", strtotime($res['date'])); 
	$time = $res['time'];
	$event = $res['event'];

	// If each previous row's date is the same as current row's date, then display only time and event
	if($prevdate === $date) {

		echo "";
		echo "<p id='event'>".$time;
		echo "&nbsp;&nbsp;&nbsp;&nbsp;".$event."</p>\n";
	}

	// Else display all three columns
	else {

		echo "<p id='evtitle'>".$date."</p>\n";
		echo "<p id='event'>".$time;
		echo "&nbsp;&nbsp;&nbsp;&nbsp;".$event."</p>\n";
	}
	// After each row is processed, store its date as $previous date so the date condition can be checked
	$prevdate = $date;
}
?>
</body>

PART 3 - Visual enhancement (style.css)

To make things look a bit better, I have decided to include a CSS file so that the things we disscussed above are shown more distinct and apparent to the naked eye. Here is the content of the file:


a.delete {
	color:#fff;
	background:#940101;
	width:70px;
	height:21px;
	display:inline-block;
	text-align:center;
	text-decoration:none;
	border-radius:6px;
	padding:4px 0px 0px 0px;
	box-shadow:0px 2px 2px #000;
}
a.edit {
	color:#fff;
	background:#9b5b17;
	width:62px;
	height:21px;
	display:inline-block;
	text-align:center;
	text-decoration:none;
	border-radius:6px;
	padding:4px 0px 0px 0px;
	box-shadow:0px 2px 2px #000;
}
.add {
	color:#fff;
	background:#0a4f10;
	width:62px;
	height:25px;
	text-align:center;
	text-decoration:none;
	border-radius:6px;
	box-shadow:0px 2px 2px #000;
	border: none;
	padding:3px 0px 0px 0px;
	margin:0px 5px 0px 0px;
	font-size:14px;
}
.add:hover {
	background:#000;
	cursor:pointer;
}
a.edit:hover, a.delete:hover {background:#000;}
.error {color:red;}
.lines {padding:6px 0px 6px 0px;}
#event {
	font-size: 18px;
	padding-right: 10px;
}
#evtitle {
	font-size: 22px;
	border-bottom: 2px dotted #603d13;
	width: 35%;
	padding: 5px 0px 5px 0px;
}

Alternatively, the complete script, including all three files and MYSQL table info, can be downloaded here.

Comments:

Be the first to comment.

PHP and MYSQL event(ADD/EDIT/DELETE) system

PART 1 - Create the event system (addeditdel.php)

PART 2 - Display the events (index.php)

PART 3 - Visual enhancement (style.css)

Comments:

Add a comment: