Mobile Media

Shawn Van Every Shawn.Van.Every@nyu.edu

Week 2 - SMS + PHP/MySQL and MMS to Email

Last week we looked at SMS services and how they can work with a webserver using PHP. In order to truely harness what we can do with SMS in combination with a server we need to take things a bit further. One way is by using a database in combination with our PHP scripts.

PHP with MySQL

PHP and MySQL integrate very well together. A prime example of a PHP application working with the MySQL engine is phpMyAdmin.

Connecting to a pre-existing MySQL Database with PHP 
<?        
	$hostname = "localhost";
	$dbname = "your_database_name";
	$username = "your_username";
	$password = "your_password";
                
	$mySql = mysql_connect($hostname, $username, $password) or die (mysql_error());
	mysql_select_db($dbname, $mySql) or die(mysql_error());
?>
PHP MySQL Insert

PHP can be used to put information into a database. Let's do an example where when a message comes in from Textmarks, the message is logged in a database table: 
<?
	// This script will accept being run from the web with two query string variables:
	// $_GET['phone']
	// $_GET['message']

	// Database connect function
	$mySql = null;
	
	function sqlConnect() 
	{
		# Configuration Variables
		$hostname = "localhost";
		$dbname = "your_database_name";
		$username = "your_username";
		$password = "your_password";
		
		$mySql = mysql_connect($hostname, $username, $password) or die (mysql_error());
		mysql_select_db($dbname, $mySql) or die(mysql_error());
		
		return $mySql;
	}

	/* Database Table:
		mobile_media_messages
		message_id int(11) auto_increment
		message_text text 
		phone_number text
		
		SQL:
		
			CREATE TABLE `mobile_media_messages` (
			`message_id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
			`message_text` TEXT NOT NULL ,
			`phone_number` TEXT NOT NULL
			) TYPE = MYISAM ;

	*/
	
	// Connect to the database
	$mySql = sqlConnect();

	// Phone Number:
	$phone = $_GET['phone'];

	// Message:
	$message = $_GET['message'];

	// Save the message to the text file
	// No longer doing this
	//$fp=fopen('/home/sve204/public_html/mobilemedia_fall10/mobme/mobme.txt','w');
	//fwrite($fp,$message);
	//fclose($fp);
	
	// Instead, put it in the database
	// Construct the SQL
	$query = "insert into mobile_media_messages (message_text, phone_number) values ('" . $message . "', '" . $phone . "')";
	
	// Run the query to do the actual insert
	$result = mysql_query($query, $mySql);
	
	// Disconnect from the database
	mysql_close($mySql);
?>
Saving this as mobme_insert.php in a directory on ITP would yield a URL like this: http://itp.nyu.edu/~sve204/mobilemedia_fall10/mobme_insert.php?phone=\p&message=\0 for insertion into the textmarks configuration screen.

Now that we have the messages logged, we can build something that displays the messages. Here is a basic script for displaying all of the incoming messages.

PHP MySQL Select 
<?
	function sqlConnect() {
		# Configuration Variables
		$hostname = "localhost";
		$dbname = "your_database_name";
		$username = "your_username";
		$password = "your_password";
		
		$mySql = mysql_connect($hostname, $username, $password) or die (mysql_error());
		mysql_select_db($dbname, $mySql) or die(mysql_error());
		
		return $mySql;
	}

	function sqlQuery($query)
	{
		global $mySql;
		$data = null;
		$result = mysql_query($query, $mySql);

		# This set's up an associative array (key->value pair) for all of the data returned
		if (sizeof($result) > 0)
		{
			$num_fields = mysql_num_fields($result);
			$row_cnt = 0;
			while ($row_data = mysql_fetch_array($result)) 
			{
				for ($cnt = 0; $cnt < $num_fields; $cnt++) 
				{
					$field_name = mysql_field_name($result, $cnt);
					$data[$row_cnt][$field_name] = $row_data[$cnt];
				}
				$row_cnt++;
			}
		}
		return $data;
	}
	
	/* Database Table:
		mobile_media_messages
		message_id int(11) auto_increment
		message_text text 
		phone_number text
	*/

	$mySql = sqlConnect();

	$sql = "select * from mobile_media_messages";
	$data = sqlQuery($sql);	
?>

<html>
	<body>
	<?
		for ($i = 0; $i < sizeof($data); $i++)
		{
	?>
		
 
			<? 
				echo($data[$i]['message_id']);
				echo(": ");
				echo($data[$i]['phone_number']);
				echo(": ");
				echo($data[$i]['message_text']);
				echo("<br />\n");
			?>
		

	<?
		}
	?>
	</body>
</html>
Getting Fancier

It's all well and good to insert these messages into a database but what if you wanted to do something more, like act upon a command.

Here is an example which acts upon the command "weather" and then takes in a zip code and looks up the weather and returns that to the user.

It is probably a good idea to familiarize yourselves with the Strings functions in PHP: PHP: Strings - Manual 

<?
        // This script will accept being run from the web with two query string variables:
        // $_GET['phone']
        // $_GET['message']

        // Database connect function
        $mySql = null;

        function sqlConnect()
        {
                # Configuration Variables
				$hostname = "localhost";
				$dbname = "your_database_name";
				$username = "your_username";
				$password = "your_password";

                $mySql = mysql_connect($hostname, $username, $password) or die (mysql_error());
                mysql_select_db($dbname, $mySql) or die(mysql_error());

                return $mySql;
        }

        /* Database Table:
                mobile_media_messages
                message_id int(11) auto_increment
                message_text text 
                phone_number text
        */

        // Connect to the database
        $mySql = sqlConnect();

        // Phone Number:
        $phone = $_GET['phone'];

        // Message:
        $message = $_GET['message'];
        
       	// Save the message to the text file
        // No longer doing this
        //$fp=fopen('/home/sve204/public_html/mobilemedia_fall10/mobme/mobme.txt','w');
        //fwrite($fp,$message);
        //fclose($fp);

        // Instead, put it in the database
        // Construct the SQL
        $query = "insert into mobile_media_messages (message_text, phone_number) values ('" . $message . "', '" . $phone . "')";

        // Run the query to do the actual insert
        $result = mysql_query($query, $mySql);

        // Disconnect from the database
        mysql_close($mySql);

		// NEW STUFF STARTS HERE //

        // Turn the message into an array of individual words
        $message_array = explode(" ",$message);

        // Check to see if it has at least two elements
        if (sizeof($message_array) > 1)
        {
                // Make sure the first element is "weather", capatalized or not
                if (strtolower($message_array[0]) == "weather")
                {
                        // Call, "lookup_weather" passing in the second word, hopefully a zipcode
                        $weather = lookup_weather($message_array[1]);
                        return $weather;
                }
                else
                {
                        echo("Sorry, I didn't understand");
                }
        }
        else
        {
                echo("Sorry, I didn't understand");
        }

        function lookup_weather($zip)
        {

                $weather_data = file_get_contents("http://weather.yahooapis.com/forecastrss?p=".$zip);

                $stringStart = "temp=\"";
                $stringEnd = "\" ";

                $startPos = strpos($weather_data,$stringStart);
                $startPos += strlen($stringStart);
                $endPos = strpos($weather_data,$stringEnd,$startPos);
                $tempString = substr($weather_data,$startPos,$endPos-$startPos);
                echo("The temperature is: " . $tempString);
        }

?>
Try it via a web browser

Bonus: SQL for MySQL

A good friend is the Online MySQL Manual

MySql Command Line Tool

$ mysql -u <username> -p
Enter password: <password>
mysql> use <database name>

Select mysql> select * from mobile_media_messages;

mysql> select * from mobile_media_messages where phone_number = '7188096659';

mysql> select * from mobile_media_messages where phone_number = '7188096659' or message_text = 'hi';


Notes:
Always end line with ";".
Separate multiple items to select with comma
Put strings in single quote
Single "=" for comparison
Use "and" and "or" instead of "||" and "&&"

Insert mysql> insert into mobile_media_messages (message_text, phone_number) values ('a message', '7185551212',);

Update mysql> update mobile_media_messages set message_text = "from nobody" where phone_number = "7185551212";

phpMySQL

Use It.. It is easier ;-)

MMS to Email

MMS or Multimedia Messaging Service is a protocol for sending messages to and from mobile phones that include multimedia content. This can be audio, text, video, images and so on.

Although MMS isn't as popular as SMS and people aren't as comfortable with it, it does provide an easy way for us to start experimenting with media created on the mobile device.

More about MMS: Wikipedia: Multimedia Messaging Service

Although MMS isn't typically interchanged by carriers and SMS aggregators don't have the ability to deal with MMS messages, the US carriers have mostly implemented the ability for them to be sent to an email address. Also, as phones get "smarter" many of them include the ability to send photos and videos via email.

Exploiting those capabilities for our purposes is pretty straight forward.

Below is a script in PHP which can be setup to automatically receive an email and take action on that email. The version below which we will go over receives a message and then responds with an email back to the same address.

Download it here: PHP Mail Popper

Once you download this script to your desktop (and extract it) you should have a folder called "php_popper". Inside this folder there should be a file called "parseMailScript.php". You should open this file up in a text editor of your choice (TextWrangler on the Mac is a good choice, TextPad on Windows another good choice).

Once open, look in the script for the section that starts with this: 
        	/* User Configurable Variables */
Below this there are variables that need to be changed in order for this script to receive your email and parse it. You should setup an email address specifically for use with this script rather than reuse an existing address. This script takes action on any email that arrives in the account and therefore you don't want it mixing with other email that you may need or not want deleted.

The script is setup to work with POP3 mail servers. Gmail uses a slightly different protocol than most normal POP3 servers and requires the "ssl://" in front of the hostname variable. Also, in order for Gmail POP3 to work, you need to enable it on your Gmail account (Settings, Forwarding and POP/IMAP, Enable POP for mail that arrives from now on).

If you are using a mail server other than Gmail, you should put those settings in the variables under this line: // Normal POP settings Be sure to comment out the Gmail specific settings and uncomment the settings that you just made: 
	/* User Configurable Variables */

	// Gmail Settings
	//$mailbox_username = "xxx@gmail.com";
	//$mailbox_password = "xxx";
	//$mailbox_hostname = "ssl://pop.gmail.com";
	//$mailbox_port = 995; // SSL
	
	// Normal POP settings
	$mailbox_username = "xxx@xxx.com"; // CHANGE THIS LINE TO YOUR MAIL SERVER USERNAME
	$mailbox_password = "xxx";  // CHANGE THIS LINE TO YOUR MAIL SERVER PASSWORD
	$mailbox_hostname = "mail.xxx.com"; // CHANGE THIS LINE TO YOUR MAIL SERVER NAME
	$mailbox_port = 110; // Default
The next lines that you will need to change are the next three variables, $temp_folder, $attachment_output_folder and $attachment_output_folder_relative.

The first two expect the full path to the folder on the server. In the zip file and therefore in the folder on your desktop there are folders pre-created for these purposes. You are free to use those or to create your own folder on the server.

If you are using ITPs webserver for hosting this script, your variables would look something like this: 
        
	$temp_folder = "/home/sve204/public_html/mobilemedia/php_popper/tmp/";
	$attachment_output_folder = "/home/sve204/public_html/mobilemedia/php_popper/posts/";
	$attachment_output_folder_relative = "http://itp.nyu.edu/~sve204/mobilemedia/php_popper/posts/";
The next step is to upload the contents of the entire directory up to the server. You should use an SFTP program like Fetch on the Mac or WinSCP on the PC. You can download both of these applications for free from ITS here

Once these files are uploaded, you should log into the server using SSH. On the Mac you can do this by opening up Terminal (Applications, Utilities) and typing "ssh net-id@itp.nyu.edu" and on windows you can use one of the SSH software options made available by ITS.

Once connected, you will want to issue the following commands: 
[sve204@itp ~]$ cd public_html/mobilemedia/php_popper
[sve204@itp php_popper]$ chmod 700 parseMailScript.php 
[sve204@itp php_popper]$ ./parseMailScript.php
Here is a rundown: The "cd" command stands for change directory. The "chmod 700" command means make the parseMailScript.php file "read, write and execute" by you (the owner) and the "./" command means run the script.

Webmonkey has a good tutorial/article on Unix commands: Enough Unix for Your Resume as well as a nifty Unix Reference Guide.

Once you issue the command to execute the script, you should see some output like this: 
Connected to ssl://pop.gmail.com
Message number: 1
from: mfargo gmail.com
Message number: 2
from: angibabez gmail.com
Message number: 3
from: newnetherland gmail.com
That's it.. Now you are receiving messages into the script.

The parseMailScript that we started working with can also receive and save multimedia attachments that the carriers send through. Those attachments are saved onto the server that runs the script.

There are a couple of additional things in the script that we should set. The first is that the script is setup to recieve messages only from certain domains. In the script there is a partial list of domains that the carriers themselves use for the mail gateways, you can expand the list to add in domains that you want to be able to recieve content from. Careful though, the reason that this is limited is due to spam issues.

Here is the existing code: 
	// A list of domains that are allowed, put your own domain in this so you can post via email (iphone and other)
	$allowed_domains = array("mms.att.net","mobile.att.net","cingularme.com","messaging.sprintpcs.com","tmomail.net","vtext.com","mmode.com","alltel.net","vzwpix.com","mms.mycingular.com","nyu.edu","walking-productions.com","gmail.com");
To add a domain, you would simply add to this list: 
	// A list of domains that are allowed, put your own domain in this so you can post via email (iphone and other)
	$allowed_domains = array("mms.att.net","mobile.att.net","cingularme.com","messaging.sprintpcs.com","tmomail.net","vtext.com","mmode.com","alltel.net","vzwpix.com","mms.mycingular.com","nyu.edu","walking-productions.com","gmail.com","myadditionaldomain.com");
The next part is a list of attachments that the carriers might send through in an MMS message that we don't want saved. For instance, T-Mobile sends a T-Mobile graphic with each message. To tell the parser not to save those attachments, add them to this list: 
	// A list of attachment filename regular expressions that you don't want included in the output of the script
	$bad_attachments = array("masthead.jpg","dottedLine_600.gif","spacer.gif","video.gif","dottedLine_350.gif");
Related to this, there is a list of text that we don't want saved. This list is in the form of a regular expression. If you don't know regular expressions, have no fear, just substitue ".*" for whitespace: 
	// A list of text regular expressions that you don't want included
	$bad_text = array("nothingatall",
		"PIX.*FLIX.*Messaging",
		"To.*learn.*how.*you.*can.*snap.*pictures.*with.*your.*wireless.*phone",
		"To.*learn.*how.*you.*can",
		"www\.verizonwireless\.com",
		"To.*play.*video.*messages.*sent.*to.*email",						"process.*when.*asked.*to.*choose.*an.*installation.*type.*Minimum.*Recommended.*or.*Custom.*select",
		"If.*you.*can.*read.*this.*text",
		"^T-Mobile\$",
		"If.*you.*are.*having.*trouble.*playing.*this.*attachment",
		"This.*Video.*Message.*was.*sent.*from.*a.*T-Mobile.*video.*phone",
		"\.footer.*{",
		"font-family:.*Arial,.*;",
		"font-size.*;",
		"color:.*;",
		"text-decoration:.*;",
		"normal.*{",
		"This message was sent using service from Verizon Wireless!",
		"visit /getitnow/getflix.",
		", QuickTime 6.5 or higher is required. Visit www.apple.com/quicktime/download to download the free player or upgrade your existing QuickTime Player. Note: During the download",
		"Minimum for faster download."
	);

Automatically Running the script

*nix servers, like itp's server typically have a utility called cron. Cron gives us the means to run script or applications automatically based on time.

To run the parseMailScript.php automatically, you would first create a text file on your machine with the following lines: 
MAILTO="your@email.address"
0,5,10,15,20,25,30,35,40,45,50,55 * * * * /usr/bin/php /path/to/parseMailScript.php
This tells the cron application to run the PHP script parseMailScript.php every 5 minutes (:00, :05, :10 ..) every hour, of every day, of every month on every day of the week (each of the "*"s means "every").

The first line, the line starting with MAILTO means to send any output generated by the script to your email address. This means that if you leave $print_output = true that you will get an email every 5 minutes forever. I would suggest that when you are done testing that you turn off $print_output by setting it to "false".

To activate this cron script, you would first make sure that it is saved as a "unix" file with "unix" line breaks. Most text editors such as TextWrangler and TextPad have the capability built in.

You would then upload it to the server that is running the parseMailScript.php, log in via SSH and issue the following command on the command line: 
		crontab name_of_cron_script
if you then issue the "crontab -l" command you should see that the contents of the script are now set.

Any time you want to update the cron, just edit the file and issue the crontab command again.

MMS + MySQL

As with SMS messages coming from TextMarks, this PHP script can put the incoming messages into a database. In fact it is already setup to do so. Have a look for this code: 
					// Additionally let's put it in a database table
					/* Database Table:
						mobile_me_messages
						message_id int(11) auto_increment
						attachment varchar(255)
						subject varchar(255)
						body varchar(255)
						from_name varchar(255)
						from_domain varchar(255)					
					*/
					
					// Connect to the database
					$mySql = sqlConnect();
										
					// Insert Data
					$query = "insert into mobile_me_messages (attachment, subject, body, from_name, from_domain) values ('" . $attachments[0]['filename'] . "', '" . $subject . "', '" . $message_text . "', '" . $from['user'] . "', '" . $from['host'] . "')";
					$result = mysql_query($query, $mySql);
					
					// Disconnect from the database
					mysql_close($mySql);
as well as this code: 
	// Extra Database connect function
	$mySql = null;
	function sqlConnect() {
		# Configuration Variables
		$hostname = "localhost";
		$dbname = "xxxx";
		$username = "xxxx";
		$password = "xxxx";
		
		$mySql = mysql_connect($hostname, $username, $password) or die (mysql_error());
		mysql_select_db($dbname, $mySql) or die(mysql_error());
		
		return $mySql;
	}
both require you to update things, the top section shows the database table you should create, the bottom section should include your database information.

Now we can build a page, like a microblog that displays media sent in via email: 
<?
	function sqlConnect() {
		# Configuration Variables
		$hostname = "localhost";
		$dbname = "your_database_name";
		$username = "your_username";
		$password = "your_password";
		
		$mySql = mysql_connect($hostname, $username, $password) or die (mysql_error());
		mysql_select_db($dbname, $mySql) or die(mysql_error());
		
		return $mySql;
	}

	function sqlQuery($query)
	{
		global $mySql;
		$data = null;
		$result = mysql_query($query, $mySql);

		# This set's up an associative array (key->value pair) for all of the data returned
		if (sizeof($result) > 0)
		{
			$num_fields = mysql_num_fields($result);
			$row_cnt = 0;
			while ($row_data = mysql_fetch_array($result)) {
				for ($cnt = 0; $cnt < $num_fields; $cnt++) {
					$field_name = mysql_field_name($result, $cnt);
					$data[$row_cnt][$field_name] = $row_data[$cnt];
				}
				$row_cnt++;
			}
		}
		return $data;
	}
	
	$mySql = sqlConnect();
	
	/* Database Table:  Use phpMyAdmin to create
		mobile_me_messages
		message_id int(11) auto_increment
		attachment varchar(255)
		subject varchar(255)
		body varchar(255)
		from_name varchar(255)
		from_domain varchar(255)					
	*/

	$mySql = sqlConnect();

	$sql = "select * from mobile_me_messages";
	$data = sqlQuery($sql);	
?>

<html>
	<body>
	<?
		for ($i = 0; $i < sizeof($data); $i++)
		{
	?>
		
 
			<? 
				echo $data[$i]['message_id'] . " <a href=\"" . $data[$i]['attachment'] . "\">" . $data[$i]['attachment'] . "</a> " . $data[$i]['subject'] . " " . $data[$i]['body'] . " " . $data[$i]['from_name'] . " " . $data[$i]['from_domain'] . "
\n"; 
			?>
		

	<?
		}
	?>
	</body>
</html>
Example