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

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”. This tutorial is geared toward someone who has never done databases or web services.

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

This tutorial will walk through the steps to create a simple to-do list. Our to-do list will hold text of what it is we need to do and a value indicating if the task has been done. In this tutorial, we will create a MySQL table to hold our data, a PHP service to call the data and a Silverlight application to display and interact with the data.

Part 2: PHP

Part 3: Silverlight

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

Part 1: The MySQL Database

First, let’s create the table we need for our data.

If you don’t have or don’t like phpMyAdmin, the MySQL query to create the table described below is:

CREATE TABLE `[your_database_name]`.`to_do_data` (
`index_key` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`is_done` TINYINT( 4 ) NOT NULL DEFAULT ‘0’,
`to_do_text` TEXT NOT NULL ,
FULLTEXT ( `to_do_text` )
) ENGINE = MYISAM

If you have phpMyAdmin, I highly suggest using it if you’re a MySQL novice. In phpMyAdmin, go to your database and enter the name of the table you want to create in the “Create new table on database [your_db_name]” section.

We’ll call our table “to_do_data” and give it 3 fields.

• index_key – identifies the to-do item uniquely
• is_done  – true/false value indicates the status of the to-do item
• to_do_text – a short text to describe what needs to be done

In phpMyAdmin, it will look like this:

A couple of notes about the fields:

index_key

This is the primary key of the table and is used to uniquely identify the given row. As such, it cannot be null and it auto-increments as rows are added to the table.

is_done

We use “TINYINT” type for this value because using BOOLEAN is basically the same thing. It is not null because every item must either be “done” or “not done”. “Done” = true = 1 and “not done” = false = 0. We are going to default to “0” (false) because we’re assuming that users aren’t going to make a to do list of stuff that is already complete.

to_do_text

We have artificially limited the text size to 1024 because we assume that this will be a set of short to-dos to, not a set of journal entries. We’ve also turned on “Fulltext” which lets the MySQL database index our entries for quick searching.

Now we just click the “Save” button at the bottom and we have our table.

Now our database system is in place and we’re ready to write a PHP webservice to implement all the CRUD (create, read, update, delete) capabilities our system will need.

If you want to learn more about MySQL, I highly recommend “A Visual Introduction to SQL“. This is the book I have, a left-over of my grad school years and it is an exceptional book for picking your way through various database scenarios.

If you’re looking for something a bit less costly, “MySQL Crash Course” is a fine book on the subject as well.