A Simple PHP Script (MySQL, Too) To Track Radio Station Song Requests, Part 2

In my last entry, I began a project to create a request system for an online radio station, using PHP and MySQL. I created the tables we’ll need, plus the front end for users.

With that done, we need to create a system for DJs to get their requests. This is a bit more complex, but in the grand scheme of projects, isn’t seriously heavy lifting.

This part has three steps:

  1. Create a login page that sets a cookie for authenticated users
  2. Provide a listing of current requests to authenticated users
  3. Provide a way for the DJ to log out / close out all requests for a given shift
Feature photo by ArtmoGraphicDesigner on Pixabay, in the public domain.
Feature photo by ArtmoGraphicDesigner on Pixabay, in the public domain.

Task 1: Log In The DJs (index.php)

This is a fairly traditional login script that simply sets a session variable with the current session ID for the DJ. It also records the fact that the DJ has a current session in the database.

It does do one twist: If the DJ logs in but hasn’t previously logged out, then the login page simply renews his old session.

Believe me, this will encourage your DJs to log in and out, since they don’t want to have request lists that are 10,000 songs long. As we’ll see later, when a DJ logs out, all his song requests for that session are no longer visible to him (they remain in the database, but the SQL we’re using doesn’t show them on the current request list, and new requests can’t be made if a DJ logs out).

<?php
session_start();
require_once("../conn.inc.php");

if(isset($_POST['submit'])) {
	//prepare text for SQL
	$un = trim(mysql_real_escape_string($_POST['user']));
	$pw = trim(mysql_real_escape_string($_POST['pass']));
	$now = time();

	//check credentials
	$sql = "SELECT * FROM djlist WHERE dj_username = '$un' AND dj_password = '$pw'";
	$rs = mysql_query($sql) or die('Cannot check login info');

	if(mysql_num_rows($rs) > 0) {
		$row = mysql_fetch_array($rs);

		//if credentials are good, make sure DJ is not already signed in
		$sql2 = "SELECT * FROM currentdjlist WHERE current_dj_id = $row[dj_id] AND current_dj_logout_time = 0 ORDER BY current_dj_login_time DESC LIMIT 1";
		$rs2 = mysql_query($sql2) or die('Cannot check current login status');

		//not logged in, insert record and set cookie
		//logged in, reset cookie to most recent login
		//redirect to menu page
		if(mysql_num_rows($rs2) == 0) {
			$sql3 = "INSERT INTO currentdjlist (current_dj_id, current_dj_login_time) VALUES ($row[dj_id], $now)";
			$rs3 = mysql_query($sql3) or die('Cannot record current DJ status');
			$_SESSION['sid'] = mysql_insert_id();
		}
		else {
			$row2 = mysql_fetch_array($rs2);
			$_SESSION['sid'] = $row2['current_id'];
		}

		header('Location: menu.php');
	}
	else {
		//bad credentials message
		$message = "<p>Sorry, your login did not work. Try again.</p>n";
	}
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>DJ Login</title>
</head>
<body>
<h1>DJ Login</h1>
<?php echo $message; ?>
<form id="form1" name="form1" method="post" action="">
  <table>
    <tr>
      <td>Username:</td>
      <td><input name="user" type="text" id="user" /></td>
    </tr>
    <tr>
      <td>Password:</td>
      <td><input name="pass" type="password" id="pass" /></td>
    </tr>
  </table>
	<br />
    <input name="submit" type="submit" id="submit" value="Submit" />
</form>
<p>&amp;nbsp;</p>
</body>
</html>

Some notes about this code:

  • This page does escape the inputs, but it doesn’t prevent someone from just flooding the login form and effectively crippling the server with DoS attacks. Again, free = somewhat insecure.
  • It is always possible for someone to forge a PHP session and therefore, be able to impersonate a DJ, view expired sessions, etc. Again, if you want bulletproof security, you need to go another route (namely, the “paid programming” route).

Task 2: Show The Requests (menu.php)

Once we have the login information, getting a list of current requests is just a trick of coming up with the right SQL. We also need to check if the user’s cookie is sent, so we have the proper key for the session we want to manage. If we can’t find a cookie, we direct the user back to the login page.

<?php
session_start();
require_once("../conn.inc.php");
if(!isset($_SESSION['sid']) || $_SESSION['sid'] == "") {
	header('Location: index.php');
}
else {
	$id = $_SESSION['sid'];
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Current Requests</title>
</head>
<body>
<h1>Current Requests</h1>
<?php
$sql = "SELECT a.*, b.* FROM songrequests a LEFT JOIN currentdjlist b ON a.request_dj_id = b.current_dj_id WHERE b.current_id = $id AND b.current_dj_login_time < a.request_time AND b.current_dj_logout_time = 0 ORDER BY a.request_time DESC";
$rs = mysql_query($sql) or die('Cannot get request records'.mysql_error());

if(mysql_num_rows($rs) == 0) {
	echo "<p>You have no requests.</p>n";
}
else {
	echo "<p>Your requests for this login session are listed below, from most recent to least recent.</p>n";
	echo "<table>n";
	echo "t<tr>n";
	echo "tt<th>Requester Name</th><th>Song Requested</th><th>Request Time</th><th>Note</th>n";
	echo "t</tr>n";
	while($row = mysql_fetch_array($rs)) {
		echo "t<tr>n";
		echo "tt<td>$row[request_person_name]</td><td>$row[request_song_name]</td><td>".date('m/d/y h:i a', $row['request_time'])."<td>$row[request_note]</td>n";
		echo "t</tr>n";
	}
	echo "</table>n";
}
?>
<br />
<p><a href="menu.php">Refresh list</a>.
<p>&amp;nbsp;</p>
<p><a href="logout.php">Log out</a> of the system. [<strong>Warning: </strong>Logging out will remove all your current requests; you will not be able to see your requests for this session.] </p>
<p>&amp;nbsp;</p>
</body>
</html>

Some notes about this code:

  • I’ve called this page menu.php to support future expansion of the end product. This way, you can make this a menu page if you ever add features.
  • I’ve intentionally not set any styles to these pages so you can fit the styles into your current Web styles.

Task 3: Logging Out (logout.php)

The final piece of the DJ puzzle is a way to log out. This entire system is driven on the concept that once a DJ logs in, he starts accepting requests; once he logs out, all his requests disappear. Therefore, logging out is just as important as logging in.

Fortunately, this script is really easy: We just update the logout time of the DJ and destroy his session. (The system assumes that DJs with a logout time of 0 are currently logged in; sessions with a logout time greater than 0 are considered ended).

<?php
session_start();
require_once("../conn.inc.php");
if(!isset($_SESSION['sid']) || $_SESSION['sid'] == "") {
	header('Location: index.php');
}
else {
	$sid = $_SESSION['sid'];
	$now = time();
	$sql = "UPDATE currentdjlist SET current_dj_logout_time = $now WHERE current_id = $sid";
	$rs = mysql_query($sql) or die('Cannot log out');
	session_destroy();
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Log Out</title>
</head>
<body>
<h1>Log Out</h1>
<p>You are now logged out of the system. <a href="index.php">Log in.</a></p>
</body>
</html>

That’s it for Part 2. Part 3 is next: Creating the admin backend.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  • Check out the Commenting Guidelines before commenting, please!
  • Want to share code? Please put it into a GitHub Gist, CodePen or pastebin and link to that in your comment.
  • Just have a line or two of markup? Wrap them in an appropriate SyntaxHighlighter Evolved shortcode for your programming language, please!