Monday, April 20, 2015

Creating a Q Sort with LiveCode

I'm currently attending the annual conference of the American Educational Research Association (AERA) in Chicago. One of my presentations was about "understanding university faculty perceptions about innovation in teaching and technology." A tall order, to say the least. My research colleagues (TJ Kopcha and Brandy Walker) and I used something called the Q methodology for the research project. The interesting part of this methodology is the use of an activity called a Q Sort to collect data. The cool thing about this activity is that people seem to find it an intriguing and fun thing to do. It is not a game, but it has some game-like qualities due to some of its intrinsic challenges. I have built an initial prototype of the Q Sort activity using LiveCode. Before I show that, let me provide some brief background about the Q Sort activity itself.

What is a Q Sort?

To explain how a Q Sort works, let's use an example that is near and dear to my heart - pizza. So, consider for a moment how much you like pizza. Think about how important certain kinds of toppings are to you. Are you a meat lover, a vegetarian, or are you someone who just wants as many toppings that will fit on top? Consider the cost of pizza. Are you a gourmet who doesn't care about cost, or someone on a budget who is always looking for those "2 for 1" deals.

OK, now imagine being asked to complete a survey about your pizza preferences. It's easy to imagine a typical survey that asks you to rate the importance of statements like "The crust is thick and chewy" using a scale from 1 (least important) to 5 (most important). Now, if you are a pizza lover, you might not think too much about the question and just answer "4" or "5" for any particular question.

In contrast, a Q Sort uses a special "game board" that resembles something like this:

All of the survey questions are then presented on separate cards, such as these:

The challenge in a Q Sort is that you have to move each statement card to one and only one slot on the Q Sort board. So, you are forced to choose which items are truly the most and least important to you, at least in comparison to the other items. That can be difficult, but in an intriguing sort of way. When you are done, you have a measure of your subjective point of view of pizza, at least as represented by the arrangement of these particular statements.

By the way, it is not a coincidence that the Q Sort board kind of looks like an inverted normal curve, that is, the classic bell curve (but upside down). This is actually an important part of the theory behind the Q Methodology, but we won't get into that here.

The Q Sort activity we used in our research had 33 statements, a rather typical number for a Q Sort, so it was much more involved and difficult for the participants to complete. Yet, they all seemed to enjoy the activity, with a few people reporting really liking it.

The Downside to Q Sorts

The biggest disadvantage of the Q Sort activity is that it is a bear to administer and score. You have to prepare a bunch of Q sorting boards and sets of cards. Participants have to accurately write down on their sorting board which statement is where before they hand it in. And then, you have the tedious task ahead of you of manually entering the data into a spreadsheet or statistical software package. This just cries out for an electronic version.

Creating a Q Sort Activity with LiveCode

Yes, it would be great to have an electronic version of the Q Sort activity. Several are available, the most notable is Q-Assessor. This looks like an excellent tool, at least as shown in a YouTube video demonstration of it. However, it is costly. They charge researchers $500 a month for a subscription to use it. We have not found any suitable free or low cost versions to use. Producing a fully supported electronic version is challenging because the limited amount of space available on a computer screen makes it very difficult to design the activity appropriately. Indeed, when completing the paper version participants typically need a large table surface available for laying out and sorting their statements. There is the possibility to redesign the activity so as to promote a multi-step Q sort procedure, which could easily allow 30-50 statements. Q-Assessor uses a two-step sorting procedure and it looks very effective. However, their design also requires that not all statements are fully visible to participants at the same time. They report that their design approach has been fully validated, but I think this remains an issue. I have certainly not solved this problem, but I recently had a design breakthrough that has helped tremendously. More about that shortly.

First, check out this brief video demonstration of the current prototype:

My Design Breakthrough

You will obviously notice in the video how different the Q Sort board is in my prototype from the example illustrated above. Here's a partial screen shot for those who did not watch the video:

This change in the visual layout of the board was my design breakthrough. At first, I had it stuck in my head that the gameboard had to be exactly like that used in the paper versions. Then, I was introducing the survey application Qualtrics to my doctoral students. If you have never used or seen Qualtrics, suffice it say that there are some very creative question types available. One of question types allows the user to sort a list of items using a drag and drop interface. This triggered the insight that I could use a more linear visual layout using indentations to represent the different rating levels. This visual design allows for a much more compact visual arrangement of the game board. One reason is that the game board slots become "docking locations" for the statements. This means that the slots don't have to be the same size as the statements. I was also able to use LiveCode to dynamically resize the statements based on their lengths. Doing so opens up yet more screen space. My early try-outs of this design convince me that this is the way to go for an electronic version.

But before I congratulate myself too much, let me quickly also point out that my prototype only currently allows for about 20 total statements, which is far below what would be needed for serious research using the Q methodology. Yet, I think I'm on the right track and may be able to squeeze out more space to allow for a few more statements. I may also try the multi-step approach in later versions, similar to that used in Q-Assessor.

Using a Web-Based Approach for Populating the Q Sort Activity and Collecting Data

As shown in the video, I designed the prototype in a very special way so as to facilitate the dynamic design and delivery of the Q Sort activity. I'll explain the details of this in a subsequent post. Suffice it to say that this approach allows for an unlimited number of Q Sorts to be designed using the same client software. For example, although designed as a research tool, I see some tremendous potential of the Q Sort activity as a teaching tool. A teacher could use the Q Sort activity throughout a course to engage students and provoke discussion and reflection. The topic of the Q Sort can be changed as often as one wishes just by changing the contents of the download URL entered into the software. To realize this potential, I still need to figure out an easy way to score the activity so as to make the results available more or less right away. I think there are some ways to do that without doing a full Q analysis. I'll be working on that.

Closing Thoughts

Yes, I am quite smitten with the Q Sort activity. One thing I like about it is that it promotes "mindfulness" in completing the activity. That is, you really have to think about what you value as you complete it. You can't just quickly click a bunch of 4s or 5s on a survey. You are fully engaged in activity as you complete it. The other thing, and this is not trivial, is again the fact that people seem to really enjoy doing it. I know I'm repeating myself, but I think it is because it has a game-like feeling about it. Also, I think the engaging, mindful nature of it is just naturally motivating. I also find the instructional potential of Q Sorts very, very exciting. I think a Q sort would be an excellent classroom activity leading to motivating and reflective discussions.

There is much more to explain and talk about here, but I hope this gives you a good sense of what a Q Sort is and the advantages of creating an electronic version. This project really needs a web-only delivered solution, so this will be a great candidate for exporting LiveCode to HTML5 when that feature becomes available in the (hopefully) not too distant future. On that note, I send my best wishes and vibes to the LiveCode team currently working on this.

Wednesday, April 8, 2015

Desperately Seeking Middleware: Connecting LiveCode to a mySQL Database with PHP

I think I have finally figured something out after about two years of false starts and false hopes. When I first started using LiveCode, one of the features that interested me most was its ability to connect to databases stored on Web servers. I quickly found the following tutorial which led me to believe that doing so would almost be child's play:

But, I quickly ran into a problem and submitted a request for help to the LiveCode's user forum. I quickly learned from other LiveCode users that this "easy way" to have LiveCode connect to a database was only possible if LiveCode has a "direct connection" to the database. However, for security reasons, few web servers allow direct connections. The solution was to use "middleware" code -- code that resides on the web server that acts as an intermediary between the database (stored also on the server) and my standalone LiveCode app (residing on my computer, my iPhone, etc.). I learned that I could write this middleware in PHP. Another approach I was encouraged to consider was installing a special version of LiveCode on the web server with special server functionality - called (not surprisingly) LiveCode Server - with which I could easily write this middleware. Unfortunately, the server and the database I wanted to connect to was administered by a commercial hosting company and so I did not have the ability to install LiveCode Server on it. Plus, I already had been working a little with PHP on other projects, so the PHP route seemed the way to go.

But, I really didn't have a firm conceptual understanding of what this middleware was supposed to do. Every now and then over the next two years I came back to this problem and each time I got stuck again. I usually chalked it up to just not doing a thorough enough search on Google or the LiveCode user forums. Finally, a few weeks ago, I redoubled my efforts and found a few intriguing leads. I also submitted a much more detailed and somewhat emotionally-laden request for help to the LiveCode user forum. Based on some very helpful replies, I realized that I was looking at the problem all wrong. As it turns out, I (more or less) knew all along how to solve this problem, but just didn't know it at the time. That realization opened the door for me to solve the problem.

So, the purpose of this blog post, and perhaps a few more to come, is to share what I've learned with some concrete examples, code and all. Put another way, the purpose of this post is to provide to others exactly what I needed two years ago.

But I must alert anyone who stumbles upon this posting that understanding the solution that follows requires not only some understanding of LiveCode, but at least a cursory understanding of PHP, a touch of SQL and HTML, and a very good understanding of how to set up and manage a mySQL database on a typical server installation (such as with the use of an interface such as phpMyAdmin).

Setting the Context: Imagine the World's Most Boring Game

I have created a very simple sample project to demonstrate how all this works. Imagine a game that generates a final score, except that we'll skip the game part and let users choose their own score. Next, imagine that you want the people playing your game to have the option to upload their score along with a user name of their choice. That way, we will have a record of all game scores tied to players. This would allow (later) the option to give a single user feedback on how well they are doing in relation to all other players. Similarly, although I don't do it here, it would also be straightforward to generate a list of the ten top high scores for the players to aim for. But, let's keep the task as simple as possible for right now.

So, yes, I created the world's most boring game for demonstration purposes here. Basically, you just enter whatever game score you would like along with a user name of your choice, then click a button to submit that information to be added to the database on the server. We'll also see how to retrieve and view all of the game scores and user names.

Assembling the Software Puzzle Pieces: mySQL Database, PHP, and LiveCode

To do this, you need to first create a database on the server. I'm using mySQL on a web site maintained by a commercial web hosting company. I created a simple database with just one table called "players." The table has the following columns:

  • ID_players
  • username
  • score_recent

Writing the LiveCode Scripts

Here is a screenshot of the boring game I built with LiveCode:

After entering your user name and game score -- in fields titled "username" and "score_recent" --  you click the button "Submit to the Game Center." Here is the script for this button:

on mouseUp
   put "username="&field "username" & "&score_recent="&field "score_recent" into varSendData
   post varSendData to URL ""
   put it into varResults
   answer "Response from database:"&return&varResults
end mouseUp

The first line assembles a string containing the information the player entered into the two fields. Let's image that the person enter "Lloyd" for user name and "99" for the game score. The resulting string, put into the variable varSendData, would look like this:


I used variable names that matched the column labels in my database, but that was not necessary.

This is information is then posted to the URL shown - obviously you would enter the URL of the PHP file on your web site. POST and GET are the two standard ways to send data to a URL. The GET method will attach the information to the end of the URL and hence will be visible in the user's browser (not recommended for sensitive information, such as passwords), whereas the POST method keeps the information hidden from view.

The PHP file "insert_newscore.php" takes that information and performs the database function of inserting the information into the database as a new record, then returns the message "Information entered into database." This message is put into the variable "varResults" which is displayed in a pop-up message.

OK, let's take a quick look at that PHP file.

Writing the PHP File "insert_newscore.php"

Here is the entire code for the PHP file "insert_newscore.php" that has been waiting on the web server:

//1. Create a database connection

# FileName="Connection_php_mysql.htm"
# Type="MYSQL"
# HTTP="true"
$hostname_connLivecode = "yourhostname";
$database_connLivecode = "demo_high_score";
$username_connLivecode = "yourusername";
$password_connLivecode = "yourpassword";
$connLivecode = mysql_pconnect($hostname_connLivecode, $username_connLivecode, $password_connLivecode) or trigger_error(mysql_error(),E_USER_ERROR);

//2. Perform database query

$username = $_POST["username"];
$score_recent = $_POST["score_recent"];

mysql_select_db($database_connLivecode, $connLivecode);
$query = "INSERT INTO players (";
$query .= " username, score_recent ";
$query .= ") VALUES (";
$query .= " '{$username}', '{$score_recent}'";
$query .= ")";

$result = mysql_query($query, $connLivecode) or die(mysql_error());

//3. Return feedback
echo "Information entered into database";

This file is pure PHP. In fact, it is a single PHP code block. No HTML is included or needed. Adding HTML tags of any sort would just get in the way, as I'll explain later. The PHP code performs three steps. First, it establishes a database connection. You obviously need to enter your own hostname, database name, user name, and password for the database.

Second, it takes the two bits of information we posted (i.e. sent) to the page (shown in red) and puts each into PHP variables with the same names (again, not necessary, but a good habit to get into), followed by a SQL script to insert this information into the players table in the database.

Third, it returns the feedback "Information entered into database" to the LiveCode app using the PHP echo command (which I equate to a print-to-screen command). Indeed, if this PHP file were viewed in a browser at this moment, all you would see on the screen is the sentence "Information entered into database."

If you've never worked with PHP, please don't beat yourself for not understanding the syntax or command structure (and just be thankful that LiveCode isn't written this way). But, I do hope you can at least get the gist of what's going on here.

Using LiveCode to Trigger Database Queries

OK, what if I want to know all the games scores saved so far. The two buttons "Show All Users (sort by username)" and "Show All Users (no sort)" are used to initiate a process that will trigger the database to perform these queries then return this information from the database and put it in the field at the bottom of the card. The scripts for both buttons are identical except for one thing. Let's look at the script of the first button:

on mouseUp
   put empty into field "players"
   put URL "" into varResults
   put varResults into field "players"
   if line 1 of field "players" is empty then delete line 1 of field "players" 

end mouseUp

The first line just empties out the field "players," which is the name of the field at the bottom of the card. The second line goes to the PHP file on the Internet and puts whatever it finds into the variable "varResults," which in turn is put into the field "players." (The last line is a hack -- for some reason, I get a blank line as the first line every time, so this just deletes it.)

The code for the other button is exactly the same, except that it goes to this URL:

OK, let's take a look at each of these two PHP files

Writing PHP Files to Query the Database and Return the Results to LiveCode

Here's the code for the PHP file "show_all_scores_username.php":

//1. Create a database connection
# FileName="Connection_php_mysql.htm"
# Type="MYSQL"
# HTTP="true"
$hostname_connLivecode = "yourhostname";
$database_connLivecode = "demo_high_score";
$username_connLivecode = "yourusername";
$password_connLivecode = "yourpassword";
$connLivecode = mysql_pconnect($hostname_connLivecode, $username_connLivecode, $password_connLivecode) or trigger_error(mysql_error(),E_USER_ERROR); 

//2. Perform database query

mysql_select_db($database_connLivecode, $connLivecode);
$query_rsUser = ("SELECT * FROM players ORDER BY username ASC");

$rsUser = mysql_query($query_rsUser, $connLivecode) or die(mysql_error());
$row_rsUser = mysql_fetch_assoc($rsUser);
$totalRows_rsUser = mysql_num_rows($rsUser);

if ($totalRows_rsUser > 0) {
    echo "#Data begins here"."\n";
if ($totalRows_rsUser == 0) {
    echo "no data found";

do {
  echo $row_rsUser['username'].",".$row_rsUser['score_recent']."\n";
   } while ($row_rsUser = mysql_fetch_assoc($rsUser)); 

As you can see, this script has some similarities to that in the other PHP file. The main difference is the following SQL statement:

SELECT * FROM players ORDER BY username ASC

This says to select all of the records found in the table "players" and return and display them in alphabetical order of the column "username."

The other key command is the "do" command at the very end which also includes an "echo" command (show in red). This creates a loop where the database finds and shows each record in the database. The loop repeats for as many records in the database. As before, if we were to enter this URL in a browser, you would see the information appear on the screen. All LiveCode does is take this ouput and put it into the field "players."

It also merits pointing out that this PHP file does not need any input to work, so there is no need to POST or GET any information from the LiveCode file.

The script in the PHP file "show_all_scores.php" is identical except for the SQL statement:

SELECT * FROM players ORDER BY ID_players ASC

This just says to show the records in order of ID_players. ID_players is an integer that auto-increments by 1 every time a new record is created. It guarantees that every record will have a unique number assigned to it. Indeed, even if a record is deleted the number for that deleted record will not be reused. By ordering the output of the records by this number in ascending order, it will show the records in the order of which they were created.

Again, if you don't know PHP, then don't get stressed out over not understanding the ins and outs of every line. However, just to show that it really is just another programming language like LiveCode with the same sorts of functions and options, let's take a look at the line I highlighted above in red:

echo $row_rsUser['username'].",".$row_rsUser['score_recent']."\n";

Notice in the middle these characters: .",".

The period is the concatenation symbol in PHP, whereas we use the & symbol in LiveCode. So, I've merely joined the two variables with a comma in-between. Notice also at the end these characters: ."\n"

"Backslash n" is the PHP code for a line return, so I've simply concatenated this to the end of the string that is being "echoed" (printed to the screen). This ensures that each record in the database will be shown on a separate line.

So, let's imagine that this is the list of all scores in the database which gets returned to the LiveCode app:

#Data begins here

First, you might wonder why it starts with "#Data begins here." It's a little something I added. You will find this sentence in the script above. It's part of an if/then statement and is triggered only if there is data found in the database. Otherwise, the statement "no data found" is return." (I thought it would be a good idea to include the # sign, as I often use that symbol to aid in my list processing scripts because it's a unique and easy symbol to search for.)

Next comes the username and score for each record in the database separated by a comma, with each record on a new line. I decided to display it this way because this is a convenient form for LiveCode to handle given that the comma is the default itemDelimiter. So, item 1 of each line is the user name and item 2 of each line is the game score. If this were a "real" game, I would probably just keep the field hidden from view or alternatively store this list of data in a variable. Then I would display the information in a more friendly form to the user.

Why No HTML?

I'll bet you know some HTML and you are just dying to add a <p> here and a <br> there. Don't do it. The reason is that these tags will appear in their original text form in the field "player" along with the raw data, which is obviously something we don't want.

Short Summary of What I Learned

In the end, the PHP files above are really nothing special for LiveCode. If you were designing a Web site with a database backend, these are exactly the sort of pages you would be writing. Instead of having LiveCode send a user name with a game score, or asking for a list of scores to be shown, you would build an HTML page to do pretty much the same thing. That, in essence, was my conceptual breakthrough. I thought, incorrectly, that I needed to write a custom PHP script -- specialized for the needs of LiveCode -- that would allow LiveCode to "talk" to the database. This misconception blocked my ability to see the solution for two years.

Just so you know, I'm not convinced any of the above is the "best way" for LiveCode to communicate with a database, but it is definitely "one way" to do it. As always, I look forward to the more experienced LiveCode programmers out there to give feedback and guidance to me.

The best thing I can say is that I'm glad I persevered. I now have a whole new LiveCode world open to me.