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

Recently asked on Yahoo! Answers:

I need a script to track requests. Can you help me?

I need a script that can track requests that listeners submit for an online internet radio station.

Something like http://www10.asphost4free.com/bigbradio/radiorequest/

That script is a bit more complex than I’ll bother doing for free, but the one I’ll put together should suffice quite nicely.

We need four parts to this script:

  1. A few MySQL tables that will hold our data.
  2. A public interface to add a request. That’s the easy part.
  3. A simple admin backend for DJs to see their requests. That’s a bit more involved.
  4. A management backend to manage the DJs. We could eventually extend that with more features, such as reporting on what songs are most requested, which DJs play the highest percentage of requested tunes, etc. That’s far more involved, so we won’t do that for the simple part.
Feature image by RonPorter via Pixabay, in the public domain.
Feature image by RonPorter via Pixabay, in the public domain.

Part 1: Database Setup

First, we need to create a few database tables for this thing.

  • One will be a table that contains the listing of all DJ logins and passwords, plus their publicly viewable names.
  • One will store the names of admins allowed to add and delete DJ names.
  • One will store the requests members have made.
  • One will record which DJs are currently logged in to the system.
  • One records user IP addresses to prevent request flooding.
CREATE TABLE `djlist` (
`dj_id` INT( 4 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`dj_username` VARCHAR( 20 ) NOT NULL ,
`dj_password` VARCHAR( 20 ) NOT NULL ,
`dj_public_name` VARCHAR( 50 ) NOT NULL
) ENGINE = MYISAM ;

CREATE TABLE `adminlist` (
`admin_id` INT( 4 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`admin_username` VARCHAR( 20 ) NOT NULL ,
`admin_password` VARCHAR( 20 ) NOT NULL
) ENGINE = MYISAM ;

CREATE TABLE `songrequests` (
`request_id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`request_dj_id` INT( 4 ) NOT NULL ,
`request_time` INT( 12 ) UNSIGNED NOT NULL ,
`request_person_name` VARCHAR( 50 ) NOT NULL ,
`request_song_name` VARCHAR( 50 ) NOT NULL ,
`request_note` VARCHAR( 255 ) NOT NULL
) ENGINE = MYISAM ;

CREATE TABLE `currentdjlist` (
`current_id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`current_dj_id` INT( 4 ) NOT NULL ,
`current_dj_login_time` INT( 12 ) UNSIGNED NOT NULL ,
`current_dj_logout_time` INT( 12 ) UNSIGNED NOT NULL DEFAULT '0'
) ENGINE = MYISAM ;

CREATE TABLE `requestiplist` (
`userip_id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`user_ip_addy` VARCHAR( 20 ) NOT NULL ,
`user_ip_time` INT( 12 ) UNSIGNED NOT NULL
) ENGINE = MYISAM ;

It’s important to note here that these tables store passwords as plain text. A more secure method would be to store the MD5 hash of these passwords, or better yet, apply an encryption scheme to the passwords and store those in a BLOB. Again, this is free, so you get lots of shortcuts.

Part 2: The End User Interface Page

We’re going to make a simple end-user interface page for requests. It will figure out what DJs are currently logged in to the system and only allow users to make requests to logged in DJs.

<?php
require_once('conn.inc.php');
?>
<!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=iso-8859-1" />
<title>Request Page</title>
</head>
<body>
<h2>Requests</h2>
<?php
$REQUEST_INTERVAL = 300; //seconds between requests
$show = true; //show form boolean; form shown when true
$now = time();

if(isset($_POST['submit'])) {
	//set variables
	$uip = $_SERVER['REMOTE_ADDR'];
	$uname = htmlspecialchars(substr(trim(mysql_real_escape_string($_POST['person'])), 0, 50));
	$sname = htmlspecialchars(substr(trim(mysql_real_escape_string($_POST['song'])), 0, 50));
	$message = htmlspecialchars(substr(trim(mysql_real_escape_string($_POST['msg'])), 0, 255));
	$dj = $_POST['djid'];
	$flood = $now - $REQUEST_INTERVAL;

	if($message == "") {
		$message = " ";
	}

	// we need good data to proceed
	if($uname == "" || $sname == "" || !eregi('^[0-9]{1,4}$', $dj)) {
		echo "<p>Sorry, there was a problem with your request. Please <a href="request.php">try your request again.</a></p>n";
	}
	else {
	//check for flooding
		$sql = "SELECT * FROM requestiplist WHERE user_ip_addy = '$uip' AND user_ip_time > $flood";
		$rs = mysql_query($sql) or die('Cannot check flooding');
		if(mysql_num_rows($rs) > 0) {
			echo "<p>Sorry, but you've recently requested a song. Please wait a while, then submit a new request.</p>n";
			$show = false;
		}
		else {
			//add anti-flooding record
			$sql = "INSERT INTO requestiplist(user_ip_addy, user_ip_time) VALUES ('$uip', $now)";
			$rs = mysql_query($sql) or die('Cannot insert anti-flooding record');

			//add request
			$sql = "INSERT INTO songrequests (request_dj_id, request_time, request_person_name, request_song_name, request_note) VALUES ($dj, $now, '$uname', '$sname', '$message')";
			$rs = mysql_query($sql) or die('Could not enter song request.'.mysql_error().$sql);
			echo "<p>Thank you for your request! We have added it to our request list.</p>n";
			$show = false;
		}
	}
}

//get signed-in dj list; if none signed in, don't show form
$sql = "SELECT a.dj_public_name, b.* FROM currentdjlist b LEFT JOIN djlist a ON a.dj_id = b.current_dj_id WHERE b.current_dj_login_time <= $now AND b.current_dj_logout_time = 0 AND b.current_id IS NOT NULL";
$rs = mysql_query($sql) or die('Cannot get DJ list.'.mysql_error());
if(mysql_num_rows($rs) == 0) {
	echo "<p>Sorry, no DJs are available to take requests at this time. Please try again later.</p>";
	$show = false;
}

//show form
if($show == true) {
?>
<p>To make your request, provide the information requested below.</p>
<form method="post">
  <table border="1" cellspacing="0" cellpadding="5">
    <tr>
      <td><div align="right"><strong>Your name: </strong></div></td>
      <td><input name="person" type="text" id="person" value="<?php echo $_POST['name']; ?>" size="30" maxlength="50" /></td>
    </tr>
    <tr>
      <td><div align="right"><strong>To DJ: </strong></div></td>
      <td>
		  <select name="djid" id="djid">
<?php
			//dynamically create DJ list options
			while($row = mysql_fetch_array($rs)) {
				echo "<option value="$row[current_dj_id]"";
				if($_POST['djid'] == $row['current_dj_id']) {
					echo " selected";
				}
				echo ">$row[dj_public_name]</option>n";
			}
?>
		  </select>
	  </td>
    </tr>
    <tr>
      <td><div align="right"><strong>Song requested: </strong></div></td>
      <td><input name="song" type="text" id="song" value="<?php echo $_POST['song']; ?>"  /></td>
    </tr>
    <tr>
      <td><div align="right"><strong>Message to DJ: </strong></div></td>
      <td><trxtarea name="msg" cols="40" rows="3" id="msg"><?php echo $_POST['msg']; ?></textarea></td>
    </tr>
  </table>
	<br />
    <input name="submit" type="submit" id="submit" value="Place Request" />
</form>
<?php
}
?>
</body>
</html>

Some notes about the code above:

  • The form checks for some sort of input for person name and song name, and ensures there is a DJ ID that corresponds to a integer. It also escapes the inputs and makes sure they won’t overflow their columns. However, it doesn’t ensure the DJ ID requested is someone actually working, nor does it gracefully warn people about entering too much text. Again, free = limited security and lots of shortcuts.
  • The form also removes any HTML entities, to prevent HTML injection attacks; and also eliminates SQL injection attacks. It does not, however, attempt to trap mail injection attacks.
  • The code does prevent flooding by recording the IP address of persons making requests. If they request more songs within the Request Interval constant, they’re told to try again later. You may find that some persons object to this, especially those working behind a proxy / router who share an IP address but actually represent multiple listeners. That’s the problem with any kind of security; it locks out some people who shouldn’t be locked out but prevents an abuse that would be far worse.
  • You can only request songs with this script if there is a DJ logged in. If no DJ is logged in, you can’t request a song.

OK, that covers it for this entry. Tomorrow, we’ll make DJ’s backend.

2 Comments

  1. Hey thanks sooo much! I love you man! It really amazes me how much you’ve done just to answer my question on Yahoo Answers. I REALLY appreciate it. =D Big thanks.

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!