PHP, MySQL, and Silverlight: The Complete Tutorial (Part 2)

This is meant to be the one-stop-shop blog post for creating a very simple web service in PHP that pulls from a MySQL database and displaying the data in Silverlight. Emphasis on the “simple”.

Here is an example of the finished product (I reserve the right to clean up the data on a regular basis):

Part 1: MySQL

Part 3: Silverlight

Download all files (PHP & Silverlight)
Download PHP files only
Download Silverlight project only

In Part 1, we created the MySQL tables necessary to store data for a simple to-do list. In Part 2, we’ll write some PHP code that will give us the ability to grab the data out of the database and send it, in JSON format, to our Silverlight application.

Part 2: Writing the PHP Web Service

We’re going to create 4 PHP files (download php files):

• mysql_vars.php – holds all the information for connecting to the MySQL database
• get_todo_items.php – for getting all the to do items
• add_todo_item.php – adds a to do item
• change_status.php – changes a to-do item from “not done” to “done” or vice versa

Since all these projects will be using the information in mysql_vars.php, we’ll write that first.

mysql_vars.php

<?php
$dbUsername = “[my_database_username]“;
$dbPassword = “[my_database_password]“;
$db = “[my_database_name]“;
$server = “[my_database_server]“;

//To Do Table and Column Names
$mysql_todoTable  = “to_do_data“;
$mysql_todoIndexCol = “index_key“;
$mysql_isDoneCol = “is_done“;
$mysql_todoTextCol = “to_do_text“;

$connection = mysql_connect($server, $dbUsername, $dbPassword);

function formatInput($rawURLData)
{
$returnString = urldecode($rawURLData);
$returnString = mysql_real_escape_string($returnString);
return $returnString;
}
?>

We’ve added the to-do table and column names so that, if we decide to change anything later, we can just go to this file and update the table or column once.

Just for good measure, we’ve added a function we’ll want to use across our php files. The function “formatInput” will be used to make sure all our data is decoded from the URL that calls our web service (the urldecode method) and then try to block any SQL injections (the mysql_real_escape_string method).

Now, let’s write the basic “Get the data” file. What we’re going to do is write it so that the we can choose to get:

• all the to-do items
• all the to-do items that are “done”
• all the to-do items that are “not done”

This range of functionality isn’t even close to ideal. In a perfect world, we would want a wider range of options in gathering items (for example, items that contain a certain word or one item in particular or limit the number of items we call by a date range). However, for our very simple purposes, this will do.

The way our web service will work is that we have a URL that we’ll call from Silverlight when we want to get some data. When we calls this web service, we may want only the “not done” items or only the “done” items. We’ll handle that option by adding “?itemStatus=done” or “?itemStatus=notDone” to the end of the URL.

Example: if our base URL is

http://www.mywebsite.com/

the call to

http://www.mywebsite.com/get_todo_items.php

will get all items, regardless of their completed status while

http://www.mywebsite.com/get_todo_items.php?itemStatus=done

will get all the to do items that are complete. So we need to make sure that our web service responds appropriately to both calls.

There are comments in the code, but I’ll just explain the basic concept in picture form:

We take in a URL, extract the variables from it, create the MySQL query based on the variables, execute the MySQL query, extract the results, and then send back the php object encoded as a Json object. Each one of our files will follow this same pattern.

get_todo_items.php

<?
include ‘mysql_vars.php’;

//    set up the “itemStatus” URL option and build a query addition
//        to account for the itemStatus variable
$itemStatus = $_GET[‘itemStatus’];
$itemQueryAddition = “”;

if($itemStatus != NULL)
{
if($itemStatus == “done”)
{
$itemQueryAddition = “WHERE `$mysql_isDoneCol` = 1”;
} else if ($itemStatus == “notDone”)
{
$itemQueryAddition = “WHERE `$mysql_isDoneCol` = 0”;
}

// Construct our MySQL query
$todoQuery = “SELECT * FROM `$mysql_todoTable` $itemQueryAddition ;”;

// execute the query and gather the results…
mysql_select_db($db, $connection);
$todoResult = mysql_query($todoQuery);
$todoArray = array();

while($itemRow = mysql_fetch_array($todoResult))
{
$todoArray[] = array( “ToDoIndex” => $itemRow[‘index_key’],
“IsDone” => $itemRow[‘is_done’],
“TodoText” => $itemRow[‘to_do_text’] );
}
mysql_close($connection);

// … then encode the results as JSON Text…
// we’re using a “returnType” field so that our Silverlight application can differentiate between
// the kind of return values it recieves and parse the Json object appropriately

$returnItems = array( “returnType” => “todoItems”,
“results” => $returnItems);
$JSONResult = json_encode($todoArray);

// … and print the results so that our app can read them
echo $JSONResult;
?>

The other two files, add_todo_item.php and change_status.php use exactly the same structure to add a new item and change the status of an existing item (respectively). I won’t put all the code here in this post that is already too long, but you can download all the files here.

Update the mysql_vars.php file to fit your needs and you should be able to just upload these files and have your running to-do web service all ready for Silverlight to call it for data, which is something we’ll deal with in Part 3.