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

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 2: PHP

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 wrote a PHP service to deliver the items in our to-do list in JSON format. In Part 3, we’ll create a Silverlight application that can utilize this web service to retrieve, display, and edit the to-do list.

Part 3: Connecting Silverlight To Our PHP Web Service

First, let’s start a new Silverlight project. (You can download the full project here.) In the interest of time, I’m not going to go into the details of the “ideal” implementation for this. I’m just going to show the parts necessary to retrieve, display and interact with the to-do data.

Before we start with the UI, let’s build a model so that we can appropriately bind our data. Add a new folder to the Silverlight project and name it “Models”. Add a new class to it and name the class “ToDoItem.cs”. In this instance, we’re just going to make the model look just like our MySQL table.

ToDoItem.cs

public class ToDoItem
{
public bool isDone { get; set; }
public string toDoDescription {get; set;}
public int toDoID {get; set;}

public
ToDoItem() { }
}

Now, we’ll add to our MainPage.xaml a ListBox named “ToDoList” that will hold the to-do items and a Grid to house the UI to add a new to-do. We’ll work on gathering and displaying our items first.

Right-click on the ListBox and go to “Edit Additional Templates –> Edit Generated Items (ItemTemplate) –> Create Empty…
clip_image001
You’ll love this part… Just add a checkbox to it. That’s all we’ll need, so that’s all we’ll add. Then, go into the XAML and make the checkbox look like this:

<CheckBox Content=”{Binding toDoDescription}”
IsChecked=”{Binding isDone}”
Tag=”{Binding toDoID}” />

This binding is all we’ll need once we gather our to-dos from the web service and attach it to the ListBox. Let’s go ahead and get that data into this ListBox.

Open MainPage.xaml.cs and, at the top of the class, add:

WebClient wc = new WebClient();
ObservableCollection<ToDoItem> myToDoList = new ObservableCollection<ToDoItem
>();
string baseURI = “http://<Web_address_holding_your_php_files>”;

You may have to add “using System.Net;” and “using System.Collections.ObjectModel;” to the top of the file. While we’re adding references, right-click on the “References” folder and find the “System.Json” component and add it to the project. Then add “using System.Json;” to the references section in the top. It’ll come in handy in a minute.

In the MainPage() method under InitializeComponent(), type “wc.DownloadStringCompleted += “ and hit TAB twice. This will add an event handler for when the WebClient has finished connecting to the web service we wrote.

The resulting event handler (which should be named “wc_DownloadStringCompleted”), is the code that our application will go to whenever it makes a call to our web service and gets a result. In it, we will do the following things:

  1. Check to make sure we got a result without error
  2. Check to see what kind of result we got (did we get all to-do items? or add a new to-do item?)
  3. Walk through the result, extracting the data we need
  4. Apply that data to the UI

Let’s add the code to do that for getting all the items. Make your event handler look like this:

wc_DownloadStringCompleted
  1. void wc_DownloadStringCompleted(object sender, DownloadStringCompletedEventArgs e)
  2. {
  3. if (e.Error == null && e.Result!= “”)
  4. {
  5. JsonValue completeResult = JsonPrimitive.Parse(e.Result);
  6. string resultType = completeResult[“returnType”].ToString().Replace(‘”‘, ‘ ‘).Trim();
  7. if (resultType == “todoItems”)
  8. {
  9. myToDoList.Clear();
  10. JsonArray toDoItemsJson = (JsonArray)completeResult[“results”];
  11. foreach (JsonValue todoItemJson in toDoItemsJson)
  12. {
  13. ToDoItem thisTodo = new ToDoItem();
  14. if (todoItemJson[“ToDoIndex”] != null)
  15. thisTodo.toDoID = Convert.ToInt32(todoItemJson[“ToDoIndex”].ToString().Replace(‘”‘, ‘ ‘).Trim());
  16. if (todoItemJson[“TodoText”] != null)
  17. thisTodo.toDoDescription = todoItemJson[“TodoText”].ToString().Replace(‘”‘, ‘ ‘).Trim();
  18. if (todoItemJson[“IsDone”] != null)
  19. {
  20. int isDoneInt = Convert.ToInt32(todoItemJson[“IsDone”].ToString().Replace(‘”‘, ‘ ‘).Trim());
  21. if(isDoneInt == 0){
  22. thisTodo.isDone = false;
  23. } else if (isDoneInt == 1){
  24. thisTodo.isDone = true;
  25. }
  26. }
  27. myToDoList.Add(thisTodo);
  28. }
  29. ToDoList.ItemsSource = myToDoList;
  30. }
  31. }
  32. }

With this in place, all we need to do is make a call to the get_todo_items.php, which we can do by adding this code just below the wc.DownloadStringCompleted line in the MainPage() method.

wc.DownloadStringAsync(new Uri(baseURI + “get_todo_items.php”));

When we run the project, we should get all objects from our database and the results should show up just the way we want in our Silverlight application. We will follow the exact same model to add new to-do items and update the to-do items we already have.

I’m in the process of separating out the final part of this tutorial (adding and updating the items) into a supplemental post so that I can wrap this post up. I encourage anyone walking through this to try to complete those parts by yourself and refer to the last bit only if you get stuck.

Finally, a little bit of warning. I structured this project to get it working in as straightforward a manner as possible. It is not in any way the ideal architectural example for a data-driven Silverlight application. It’s just a good way to get started using Silverlight in an environment that isn’t 100% Microsoft technologies.

17 thoughts on “PHP, MySQL, and Silverlight: The Complete Tutorial (Part 3)

  1. Hi,

    Thanks for a great post!
    I would like to give this a go with my windows laptop and a MySql db on a remote linux server, using ssh connection.

    I novice question:
    Where am I supposed to store the php files on the remote server?
    Do I have to define a new site or do any other work on the server?

  2. Heads up, for newer versions of PHP the msql_ statements work best as msqli_ statements AND these often require an additional variable and or modified order too!
    Plus that function in the first PHP file, the $connection variable must be declared global!
    And yes you’ve got to change <? to <?php!
    And yes putting a clientaccesspolicy.xml at the web root makes all the difference!
    Newbie that I am TWO WEEKS of my life noodling through this!
    Make it easy on yourself, follow these tidbits of advice!
    There are no other tutorials out there for mysql, php, and silverlight so tough it out!
    Maybe someday this tutorial will be updated but until then make the best of it!

  3. good day
    Really liked your article.
    For a detailed study of all downloaded files.
    set all the parameters for the server and created a database
    If you run the application from VS 2010 that just says “Attempt to connect failed”
    After starting Expression Blend 4 application running but when sending data is not sent or received. In what may be a problem.
    If you run the php file is empty mysql_vars.php
    get_todo_items.php {“returnType”: “todoItems”, “results “:[]}
    change_status.php {“returnType”: “updateToDoItem”, “status”: “ERROR: No item updated. To do ID not specified or to do item status not specified”, “toDoID”: null, “isToDoComplete”: null}
    add_todo_item.php {“returnType”: “addToDoItem”, “status”: “ERROR: No item added, no to-do text specified”, “newToDoItemID”: null, “newToDoText “:””,” newToDoStatus”: “false “}
    Version of php 5.3
    Perhaps the problem is in clientaccesspolicy.xml
    If yes, please give an example of him and that I need to change that would have application to work
    And also, whether the application depends on the version of the database

  4. If you don’t get any results from the PHP files into your Silverlight app, create a “clientaccesspolicy.xml” file in the root of your domain.

    clientaccesspolicy.xml (should read as follows):

  5. when i point the program at your site it works, when i point it to mine,

    JsonValue completeResult = JsonPrimitive.Parse(e.Result);

    is causing an serialation error, any ideas?

  6. Under MainPage.xaml.cs, I get this error:

    ” is not a valid JSON primitive. This error can also occur when extraneous data is present after the JSON data.

    On this line:

    JsonValue completeResult = JsonPrimitive.Parse(e.Result);

    Any suggestions?
    Thanks.

  7. When I run the get_to_do.php directly, I get:

    $itemRow[‘index_key’], “IsDone” => $itemRow[‘is_done’], “TodoText” => $itemRow[‘to_do_text’] ); } mysql_close($connection); // … then encode the results as JSON Text… $returnItems = array( “returnType” => “todoItems”, “results” => $todoArray); $JSONResult = json_encode($returnItems); // … and print the results so that our app can read them echo $JSONResult; ?>

    On the web browser itself. Surely it shouldn’t show the PHP code? So does this mean the PHP code is faulty? (mysql_vars.php doens’t display text on browser so I assume it works).
    I simply just copied and pasted it from here.

  8. I can’t get this example to work; can someone tell me if it’s OK to host my .php files in a localhost (wampserver), as I think that may be causing the problem.

    And if it is OK, how do you do it? I change my base URI to “http://localhost/phpfiles/” and I keep getting a blank listbox despite the database having entries.

    Thanks for any assistance.

  9. The number of people doing silverlight/php/mysql are limited. I have a major problem with updating a mysql table from my silverlight program multiple times.
    Can you give a tutorial on that?
    I have a basic example which I can send you in a zip file. Contact me on my email supplied if you are interested.

  10. Hi all,
    PHP page uses Silverlight to display data extracted from a MySQL database.
    Here is very nice tutorials, how to use Silverlight in PHP-MySQL programming.
    thanks guys keep it up..

Comments are closed.