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:

http://lessons.runrev.com/m/4071/l/7003-connecting-to-a-mysql-database

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 "http://yourwebsite.com/gamecenter/insert_newscore.php"

   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:

"username=Lloyd&score_recent=99"

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:

---

<?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

$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 "http://yourwebsite.com/gamecenter/show_all_scores_username.php" 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:

http://yourwebsite.com/gamecenter/show_all_scores.php

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":

---

<?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
Mark,88
William,91
Lloyd,99

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.