Mobile Me(dia)

Shawn Van Every Shawn.Van.Every@nyu.edu
Spring 2009
H79.2690.1

Week 3 - PHP/MySQL, Mobile Web and SMS Services

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

<?        
	$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 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;
	}
	
	$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>

Mysql Select Example

PHP MySQL Insert

<?
	// Extra 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;
	}

	// 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();
	
	/* This is assuming that the code is in the popper script.  If it is not, you need to set the values yourself */
	$attachments = array();
	$attachments[0]['filename'] = "some file name";
	$subject = "some subject";
	$message_text = "some text";
	$from = array();
	$from['user'] = "some user";
	$from['domain'] = "some domain";
	
	// 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);
?>

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_me_messages;

mysql> select * from mobile_me_messages where from_name = '7188096659';

mysql> select * from mobile_me_messages where from_name = '7188096659' or from_host = 'walking-productions.com';

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_me_messages (attachment, subject, body, from_name, from_domain) values ('some attachment', 'some subject', 'some text', 'some user', 'some host');

Update mysql> update mobile_me_messages set from_name = "Matt" where from_domain = "somehost";

phpMySQL

Use It.. It is easier ;-)

Mobile Web

WAP or Wireless Application Protocol is a protocol for accessing web like pages on mobile devices. It uses a similar language to that of HTML called WML (Wireless Markup Language) and can be delivered via HTTP (just like a webpage).

More Information:
Wireless Application Protocol - Wikipedia

More recent versions of WAP (WAP 2.0) use a language derived from XHTML called XHTML MP (XHTML Mobile Profile). We will go through both quickly.

Unfortunately, WAP has never really taken off due to several reasons: Carriers overstated it's capabilities, many devices can only see a few characters or words at a time, people were used to the normal web and this new limited web isn't very appealing.

To create a WAP page, you merely create a page with the extension ".wml" and put it on a web server.

Here is a simple WML page:

<?xml version="1.0"?> 
<!DOCTYPE wml PUBLIC "-//WAPFORUM//DTD WML 1.1//EN" "http://www.wapforum.org/DTD/wml_1.1.xml"> 
<wml> 
	<!--begin card--> 
	<card> 
		<do type="accept"> 
			<go href="#card2"/> 
		</do> 
		<p> 
			This is the first card.
			Press OK to go to the second card. 
		</p> 
	</card> 

	<!--begin card--> 
	<card id="card2"> 
			<p> 
				This is the second card. 
			</p> 
	</card> 
</wml>

You can see this by typing in this URL in your mobile browser: http://itp.nyu.edu/~sve204/wml/test.wml

More Information:
WML Tutorial: Wireless Markup Language Introduction

Fortunately or unfortunately, modern mobile browsers are no longer offering good support for WAP/WML (Safari on the iPhone doesn't even attempt it) and are moving towards regular HTTP with XHTML MP or straight XHTML.

XHTML MP is the start of an integration of technologies meant for desktop web browsers and those on mobile devices.

It supports a subset of tags available in XHTML: (<a> <abbr> <acronym> <address> <b> <base> <big> <blockquote> <body> <br> <caption> <cite> <code> <dd> <dfn> <div> <dl> <dt> <em> <fieldset> <form> <h1> <h2> <h3> <h4> <h5> <h6> <head> <hr> <html> <i> <img> <input> <kbd> <label> <li> <link> <meta> <object> <ol> <optgroup> <option> <p> <param> <pre> <q> <samp> <select> <small> <span> <strong> <style> <table> <td> <textarea> <th> <title> <tr> <ul> <var> )

Here is a simple XHTML MP page:

<?xml version="1.0"?>
<!DOCTYPE html PUBLIC "-//WAPFORUM//DTD XHTML Mobile 1.0//EN" "http://www.wapforum.org/DTD/xhtml-mobile10.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
	<head>
		<title>XHTML MP Test</title>
	</head>
	
	<body>
		<h1>Shawn's Cool Page</h1>
		<p>
		Wow!..  Super Cool, Yes?
		</p>
	
		<form method="get">
			<p>
				<input type="radio" name="cool" value="yes"/>Yes
				<input type="radio" name="cool" value="no"/>No<br/>
			</p>
			<p>
				<input type="submit"/>
				<input type="reset"/>
			</p>
		</form>
	</body>
</html>

Check it out here: http://itp.nyu.edu/~sve204/mobilemedia/mobile.html or SMS to 41411 keyword momed xhtml

Recently a spate of new web browsers mobile devides has entered the market. Many of these are taking the principle of the browser on the iPhone, Nokia S60 and Android (WebKit) and delivering the web on mobile devices instead of "the mobile web" (reformatted to fit on mobile devices).

Here are some you may be interested in checking out:
SkyFire
Firefox
Opera Mobile
Opera Mini

More Information:
XHTML MP Tutorial: Introduction
Mobile Browsers Wrapup
From the "mobile Web" to "the Web" on mobile

File Uploads

Another way to get media off of a device is to create a mobile page which allows the user to directly upload a file.

Below is a PHP page which enables that:

<? echo "<?xml version=\"1.0\"?>\n"; ?>
<!DOCTYPE html PUBLIC "-//WAPFORUM//DTD XHTML Mobile 1.0//EN" "http://www.wapforum.org/DTD/xhtml-mobile10.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
	<head>
		<title>XHTML MP Test</title>
	</head>
	
	<body>
		<h1>File Upload Test</h1>
<?

	ini_set('display_errors', true);
	ini_set('display_startup_errors', true);
	// You could also log them:
	//ini_set('log_errors', true);
	//ini_set('error_log', '/home/netid/php_errors.log');
	error_reporting(E_ALL);

	// Extra Database connect function
	$mySql = null;
	function sqlConnect() {
		# Configuration Variables
		$hostname = "xxxx";
		$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;
	}

	// Limit what people can upload for security reasons
	$allowed_mime_types = array("video/3gpp"=>"3gp", 
								"audio/x-wav"=>"wav", 
								"video/mp4"=>"mp4",
								"video/3gpp2"=>"3g2",
								"video/mpeg"=>"mpg",
								"video/quicktime"=>"mov",
								"video/x-quicktime"=>"mov",
								"video/x-msvideo"=>"avi",
								"image/jpg"=>"jpg",
								"image/jpeg"=>"jpg",
								"image/pjpeg"=>"jpg",
								"image/png"=>"png",
								"audio/vnd.wave"=>"wav"
								);

	// Make sure form was submitted
	if (isset($_POST['form_submitted']) && $_POST['form_submitted'] == "true")
	{
		// Check the mime type
		$allowed_types = array_keys($allowed_mime_types);
		$allowed = false;
		if (isset($_FILES['bytes']['type']))
		{		
			for ($i = 0; $i < sizeof($allowed_types) && !$allowed; $i++)
			{
				if (strstr($_FILES['bytes']['type'], $allowed_types[$i]))
				{
					$allowed = true;
				}
			}
		
			// If the mime type is good, save it..
			if ($allowed)
			{
				$uploadfilename = time() . "_" . rand(1000,9999) . "_" . basename($_FILES['bytes']['name']);
				// Make sure apache can write to this folder
				$uploaddir = '/home/sve204/public_html/mobilemedia/php_popper/posts/';
				$uploadfile = $uploaddir . $uploadfilename;
				$uploadrelativefile = 'http://itp.nyu.edu/~sve204/mobilemedia/php_popper/posts/' . $uploadfilename;
		
				if (move_uploaded_file($_FILES['bytes']['tmp_name'], $uploadfile))
				{
					// Make sure the file isn't executable and you can delete it if you need
					chmod($uploadfile, 0666);
					
					// Put it in the database
					/* 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)					
					*/
					
					$subject = "";
					$message_text = "";
					
					if (isset($_POST['subject']))
					{
						$subject = $_POST['subject'];
					}
					
					if (isset($_POST['message_text']))
					{
						$message_text = $_POST['message_text'];					
					}
					
					// Connect to the database
					$mySql = sqlConnect();
										
					// Insert Data
					$query = "insert into mobile_me_messages (attachment, subject, body, from_name, from_domain) values ('" . $uploadfile . "', '" . $subject . "', '" . $message_text . "', 'mobile web user', 'mobile web user')";
					$result = mysql_query($query, $mySql);
					
					// Disconnect from the database
					mysql_close($mySql);					
					
					// Tell the user
					echo "<p>Success <br /> <a href=\"" . $uploadrelativefile  . "\">" . $uploadrelativefile . "</a></p>";
				}
				else
				{
					echo "<p>Error on upload...!  Here is some debugging info:</p>";
					var_dump($_FILES);
				}
			}
			else
			{
				echo "<p>Type not allowed...! Here is some debugging info:</p>";
				var_dump($_FILES);
			}
		}
		else
		{
			echo "<p>Strange, file type not sent by browser...!  Here is some debugging info:</p>";
			var_dump($_FILES);
		}
	}
	else
	{
?>
		<form enctype="multipart/form-data" method="post" action="upload.php">
			<p>
				Title: <input type="text" name="subject" /><br />
				Description: <input type="text" name="message_text" /><br />
				<input type="file" name="bytes" />
				<input type="hidden" name="form_submitted" value="true" />
				<br />
				<input type="submit" name="submit" value="submit" />
			</p>
		</form>
		<hr />
		<p>
			Phone (iPhone) doesn't support file uploads?  <a href="mailto:test@mobvcasting.com">Email it instead</a>..
		</p>
<?
	}
?>
	</body>
</html>

Try it: http://itp.nyu.edu/~sve204/mobilemedia/upload.php

More information:
PHP - Upload Script Security

SMS Services

TextMarks provides free SMS keywords on the following shortcode: 41411

After you signup as a developer, you can choose the keyword that you would like.

For instance, I registered, "MOMED" (mobile media).

The Developer Center goes through the various options that are available, basically sending and receiving.

Sending is configured as a list of people who have subscribed.

Receiving can be setup to query a URL that you specify. It passes along the phone number along with the user's message (phone number, unique id) if you ask it to.

The Developer Docs: Receiving Text Messages describes how you set this up. Basically you specify a URL that will get hit when a message is received with a couple of special characters that will get replaced with the appropriate data.

Here is the URL I setup for the "MOMED" keyword:

http://itp.nyu.edu/~sve204/mome.php?phone=\p&text=\0

which if I send the following message:

MOMED hello world

would result in this URL being hit:

http://itp.nyu.edu/~sve204/mome.php?phone=+17088096659&text=hello%20world

I have the following PHP in mome.php on ITP's server:

<? 
echo "Hi " . $_GET['phone'] . " got your message: " . $_GET['text'];
?>

which results in sending me the following message in response:

Hi 17088096659 got your message: hello world

The "$_GET['xxx']" allows me to access the variables that were sent in via the URL "query string". In this case "$_GET['phone']" contains the value of the "phone" argument in the query string and "$_GET['text']" the value of the "text" argument in the query string.

Taking things a step further, I decided to query the database which is storing the latest media that was sent in through the popper and send back the URL in the text message if the user sends the text "last".

<? 
ini_set('display_errors', true);
ini_set('display_startup_errors', true);
// You could also log them:
//ini_set('log_errors', true);
//ini_set('error_log', '/home/netid/php_errors.log');
error_reporting(E_ALL);

// 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;
}

// Extra database query function
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;
}

// If they sent in "last"
if (isset($_GET['text']) && stristr("last", $_GET['text']))
{
	// Connect to the database
	$mySql = sqlConnect();
	
	// The definition of the mobile_me_messages table
						
	// Select the last entry in the mobile_me_messages
	$query = "select attachment from mobile_me_messages order by message_id desc limit 1";
	$results = sqlQuery($query);
		
	// Disconnect from the database
	mysql_close($mySql);
	
	// Check to make sure there are results
	if (sizeof($results) > 0)
	{
		// Send back the first result
		$url = str_replace("~","%7E",$results[0]['attachment']); // TextMarks doesn't like "~" char
		//echo "Ver 1: " . $results[0]['attachment'];
		echo "\nLast Submission: " . $url;
	}
	else
	{
		echo "Sorry, no results";
	}
}
else
{
	// If they didn't send in "last", respond with the default message..
	echo "Hi " . $_GET['phone'] . " got your message: " . $_GET['text'];
}
?>

A useful function in PHP is the isset function. You should also lookup the str_replace and stristr functions show in the code above.

You can also use TextMarks to send messages. A quick example is on our class Wiki. In order to use it, you need to download Snoopy which is a PHP library that makes making web requests easier. You also need to register for a TextMarks API Key.