Introduction To Computational Media (ICM) : Week 12

Topics:

PHP with MySQL

MySQL is a very widely used open source database. It is typically available on servers run by web hosting companies and elsewhere.

Relational databases (like) MySQL are a way to store information in an organized fashion. I typically describe databases and multi-dimensional spreadsheets, each sheet a table in the database with links to the other tables.

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

Databases typically use a language called SQL (structured query language) for interacting with them. We can use PHP to send SQL to the database to store and retrieve data.

What follows are some examples of connecting to, pulling data out and putting data into a MySQL database with PHP.

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

<?
	// sqlConnect is a function I use so that I remember to do all of the steps together..
	function sqlConnect() {
		$hostname = "localhost";
		$dbname = "your_database_name";
		$username = "your_username";
		$password = "your_password";
		
		$mySql = mysql_connect($hostname, $username, $password) or die (mysql_error());
		// I always forget this when not using this function
		mysql_select_db($dbname, $mySql) or die(mysql_error());
		
		return $mySql;
	}

	// sqlQuery is a function I use to get data out of the database in a way that I like
	function sqlQuery($query)
	{
		global $mySql;
		$data = null;
		$result = mysql_query($query, $mySql);

		// This sets 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;
	}
	
	// Here is the rest of the program, connect and select data from the database
	$mySql = sqlConnect();
	
	$sql = "select id from sometable";
	$data = sqlQuery($sql);	
?>

<html>
	<body>
	<?
	// Loop through the array of results and print them out..
		for ($i = 0; $i < sizeof($data); $i++)
		{
	?>
		 
			<? 
				echo $data[$i]['id'] . "
\n"; 
			?>
		
	<?
		}
	?>
	</body>
</html>

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

	// Connect to the database
	$mySql = sqlConnect();
		
	// Insert Data
	$query = "insert into sometable (id) values (10)";
	
	$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 sometable;

mysql> select * from sometable where id = 0;

mysql> select * from sometable where name = "shawn" or id = 10;

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 sometable (id, name) values (15, "shawn");

Update mysql> update sometable set name = "Shawn" where id = 15;

phpMySQL

Use It.. It is easier ;-)

Mobile Processing

There are quite a few options for mobile application development. Most geared towards specific manufacturors and devices (C++ for Symbian, Python for Symbian Series 60, Java for Android, Objective C for iPhone, .Net for WIndows Mobile) and some geared towards hitting a wide range of devices (Flash Lite, Java ME) but more limiting.

Since we have been working in Processing, I wanted to give you a glimpse of something called Mobile Processing. Mobile Processing gives us a nice environment for development of mobile applications that run using JME (Java Micro Edition). While this doesn't hit all of the latest and greatest phones (iPhone, G1 and so forth) it does give us access to the most phones.

Getting Started with Mobile Processing

Prerequisites

We need three things, first is Java itself. Mac users should already have Java and therefore shouldn't need to download it. Windows users may need to. Furthermore, I would recommend the JDK (Java Development Kit) rather than just the runtime (which the above link will allow you to download).

The next thing that is required is a WTK (Wireless Toolkit). The WTK provides tools for building and emulating mobile apps. Sun makes a WTK for Windows machines.

Unfortunately, Sun does not have the WTK for the Mac but an independent developer has created one called the mpowerplayer SDK.

Last is Mobile Processing itself.

Configuration

After installing Java, the WTK and Mobile Processing the next step is to open up Mobile Processing and go to Preferences. In Preferences under "Mobile" you need to specify the location of your WTK install as well as choose a CLDC (Connected Limited Device Configuration) and MIDP (Mobile Information Device Profile) version. I recommend CLDC 1.1 and MIDP version 2.0. This configuration and profile specify the capabilities of the devices that you will be running the application you develop on.

More Information

Connected Limited Device Configuration - Wikipedia
Mobile Information Device Profile - Wikipedia
Mobile Processing Phones

Beginning Mobile Processing Development

A Simple App

Writing an application with Mobile Processing is very straight forward. Simply open up the application and simply start using the methods available.

Hello World:

  PFont font;
  
  void setup()
  {
    font = loadFont("HelveticaNeue-24.mvlw");
    textFont(font);
  }

  void draw()
  {
    text("Hello World",10,25);
  }

Testing and Installing

To test this application, you can simply hit the "Run" button in Mobile Processing which will compile the application and run it in your emulator (MPowerPlayer or Wireless Toolkit).

If it runs successfully there, you can choose "Export MIDlet" and transfer the application to your phone for testing. In order to test it, I typically send it to my phone through bluetooth on my Mac. On a PC you can do the same if you have bluetooth, otherwise you will have to connect via a cable and transfer it that way or upload it to the internet and download it through your phone's browser.