Sending and Receiving data using your own SQL Server - PHP file and TestProject included

jonmulcahyjonmulcahy Member, Sous Chef Posts: 10,408
edited January 2014 in Working with GS (Mac)
This guide assumes you are using a Mac. I did all testing on Mavericks.

Step 1: Download and install MAMP (http://www.mamp.info/en/index.html)

Step 2: Configure MAMP
Browse to Applications -> MAMP and click on MAMP. In the MAMP window click on Preferences and click on the Start/Stop tab. Make sure both Start and Stop are checked and uncheck the check for pro flag. The open at start is up to you. Leave the ports page alone unless you want to configure that yourself. PHP should be running 5.5.3, 5.2.17 seems to have problems with the JSON. Apache, leave alone. Hit OK to close the preferences tab.

At this point start your servers if they are not already green.

Step 3: Disable Caching
PHP 5.5 has some caching enabled which really caused some pain in testing. I would make a change and it would take awhile before it was enabled. You'll need to browse to /Applications/MAMP/bin/php/php5.5.3/conf/php.ini and comment out the OPcache section. It should be at the bottom. You want to put a ; in front of every line in that section.

Step 4: Create and Configure MySQL Databases
Browse to http://localhost:8888/MAMP/ and click on the phpMyAdmin link in the top bar. This will bring you to phpMyAdmin, a web management area for MySQL. It's very powerful, but since we are running MAMP, if you break something you can just reinstall. This is all based on the test project, but if you are using your own data, you want to make sure the tables match what you have in GS.

To start, click on the Databases tab. In the text box type in a tame and hit create. I'm using asyncTest.

image

After it's created click on the database in the list below. This will allow you to create a table. You can create as many as you want, but for this test we only need one. Give it 3 columns and hit GO. I called mine testData.

image

Now we have to create the columns. the names should match what you have in GS for ease of use, but it doesn't have to. The type needs to match exactly. I'm dealing with two columns of text and one integer. Hit save when you are complete

image

We are now done with the MySQL Configuration. Let's move over to PHP.

Step 5: Configure PHP
We are going to be copying the template PHP file into /Applications/MAMP/bin/mamp/. I have it called asyncTest.php, but you can call it anything you want, even index.php. Before you go any further, we need to talk about security. This script is wide open, the default MySQL username and password are written in plain text and stored right in the file. When you push this to production you will NEED to secure this up. There are many methods of doing this, so I'll leave it up to you. I will be attaching the script in a separate post below.

Step 6: Configure the Test app
I will be posting my test app in a separate post as well, but at this point we need to configure it. If you are testing this locally and didn't deviate anything from above, you don't have to do anything. If you are testing remotely you will have to alter the URL attributes. I have two URL attributes in the app, a sendURL and receiveURL. My script handles both POST and GET so they both point to the same script.

At this point, the app should be fully functioning. Pretty simple huh? When you execute the script you must first click on the CONNECT button. This initializes the network features, but i hear in the future this will not be required. After that, hit the send button. When you see the send status turn green, your data has reached the server. At this point if you browse to the location of asyncText.php you will see a json.txt file. This is the output of the network call. You will need this later when you customize this for your data.

If you want to insert some records into the table manually do it now. Click on the database name in the left column, then again on the table name. Click on the Insert tab and input some values and hit GO. Do this 2 or 3 times.

image

Now go back and hit GET in the app, you should see the next three values populate in the game.

That's it! I will be posting the php script and test app next, they deserve their own post.
«13456716

Comments

  • jonmulcahyjonmulcahy Member, Sous Chef Posts: 10,408
    edited January 2014
    The script is below, as an image. good luck!

    Just kidding, the .php file is attached at the bottom of this post. Let's talk about the script below. First of all, I'm using Sublime Text 2 to edit it. It has a nice color scheme and makes it very easy to decipher what is going on. That is why I have the screenshot below. Second, in the script I'm using I was using gsText as the table/DB names.

    When you decide to branch off into custom tables and whatnot, there are a few sections you need to alter. First section is lines 62-68. This captures the contents of the array and then inserts it into SQL. The variables are just custom names, I find it easy to have them match the table names. The $arrayPieces[1] thing is directly referencing the value. 0 will always be the name of the row, 1 is column1, 2 is column2, etc.

    Next we construct the SQL Statement. It's pretty basic SQL code, should be easy enough to figure out how to alter that.

    That is all you have to alter to SEND data.

    RECEIVING data is another deal. This is where the JSON output from Step 6 above is helpful.

    First we need to query the database and get the values into an array. (Lines 96-104). Starting at line 113 we are looping through the array and constructing the proper JSON format. This is a place you will need to be careful about when altering for your data. It's pretty simple, but one mistake and formatting and nothing will work.

    On line 124 we take the values and construct the JSON format for each row.

    Line 136 is an important one. This contains all the JSON text that comes before your data. You can do a copy and paste from the JSON output in step 6 above, but be sure to keep the .$arrlength. section, that makes sure the JSON reflects the right number of rows.

    Line 139 is the footer information, I don't think this changes, but check anyways.

    After that we construct the JSON data and off it goes!

    Typing this up I noticed a bunch of places I could optimize, but this was quick and dirty, so it is what it is. First thing I would do is toss the DB and Table name into a variable at the top so if it changes you don't have to scan through the code.

    If you have any specific questions, please post and reference the line number.

    You can download the php.ini script here

    image
  • jonmulcahyjonmulcahy Member, Sous Chef Posts: 10,408
    edited January 2014
    The final piece of the puzzle!

    To start, you will need to have a playstogether account. @stevej mentioned this requirement MIGHT (edit: it WILL!) go away, I hope it does. In the meantime, you will need to put your three key values into the project info section of the app before this will work.

    There is one scene with 9 actors:

    Connect - calls the network connect behavior and initializes the network stuff.
    Send - sends the tblTestData contents to your server
    Get - downloads the data from your SQL Server inserts it into your tables. I still have not tested if this automatically saves the table or if we need an extra table save.
    Reset - resets the callback attributes
    tableSendStatus - displays the status of the send behavior
    tableReceiveStatus - displays the status of the receive behavior
    networkConnectStatus - displays the status of the connect behavior
    displayTableValue - displays a table value. The app currently displays the top 6 rows.
    tableCount - displays the total number of rows in the table

    There are a few attributes in use:

    sendTable - callback attribute for send table behavior
    sendURL - URL that the app will try and POST data to
    networkConnect - callback attribute for network connect behavior
    receiveTable - callback attribute for receive table behavior
    receiveURL - URL that the app will try to GET data from

    You can download the zipped project file here
  • StormyStudioStormyStudio United KingdomMember Posts: 3,989
    Outstanding...
  • jonmulcahyjonmulcahy Member, Sous Chef Posts: 10,408
  • EireStudiosEireStudios Member Posts: 451
    Awesome work Jon, thanks for sharing all your hard work! :D
  • GSdustinGSdustin Inactive, Chef Emeritus Posts: 16
    WOW! This is really stellar work. Keep it up! :)
  • stevejstevej Member, PRO, Chef Emeritus Posts: 435
    The final piece of the puzzle!

    To start, you will need to have a playstogether account. @stevej mentioned this requirement MIGHT go away, I hope it does. In the meantime, you will need to put your three key values into the project info section of the app before this will work.

    There is one scene with 9 actors:

    Connect - calls the network connect behavior and initializes the network stuff.
    Send - sends the tblTestData contents to your server
    Get - downloads the data from your SQL Server inserts it into your tables. I still have not tested if this automatically saves the table or if we need an extra table save.
    Reset - resets the callback attributes
    tableSendStatus - displays the status of the send behavior
    tableReceiveStatus - displays the status of the receive behavior
    networkConnectStatus - displays the status of the connect behavior
    displayTableValue - displays a table value. The app currently displays the top 6 rows.
    tableCount - displays the total number of rows in the table

    There are a few attributes in use:

    sendTable - callback attribute for send table behavior
    sendURL - URL that the app will try and POST data to
    networkConnect - callback attribute for network connect behavior
    receiveTable - callback attribute for receive table behavior
    receiveURL - URL that the app will try to GET data from

    You can download the zipped project file here
    FYI, the Playstogether requirement for the URL commands WILL go away. I will also be adding a new network option "my own server" (or words to that effect) which will only enable the URL commands.
  • tatiangtatiang Member, Sous Chef, PRO, Senior Sous-Chef Posts: 11,949
    @jonmulcahy Impressive work and dedication to this feature! It looks a little overwhelming but I'm going to hunker down and see if I can get this set up with your clear directions above.

    New to GameSalad? (FAQs)   |   Tutorials   |   Templates   |   Greenleaf Games   |   Educator & Certified GameSalad User

  • jonmulcahyjonmulcahy Member, Sous Chef Posts: 10,408

    FYI, the Playstogether requirement for the URL commands WILL go away. I will also be adding a new network option "my own server" (or words to that effect) which will only enable the URL commands.
    awesome, awesome awesome!
    this feature is the most exciting one to come to GS since universal builds!
  • jonmulcahyjonmulcahy Member, Sous Chef Posts: 10,408
    WOW! This is really stellar work. Keep it up! :)
    thanks! got to bust out some dormant PHP knowledge :)

  • RiffelRiffel Member Posts: 1,272
    Great! I follow the steps, but when I pressed play in the asynTest.gameproject nothing happens! I have to place the .gameproject at the mamp/php folder? server data is a hard thing to me. 8-X
  • jonmulcahyjonmulcahy Member, Sous Chef Posts: 10,408
    game project is seperate.

    check under MAMP\Logs to see if anything is showing up

    Make sure the URL attribute is hitting a valid page
  • jonmulcahyjonmulcahy Member, Sous Chef Posts: 10,408
    @jonmulcahy Hats off to you good sir, you deserve a medal of some sort...splendid work!
    thanks, using a custom server is something I've been looking forward to for a long time. those two little behaviors are so incredibly powerful it's crazy. There is so much that could be done with the right back end.
  • RiffelRiffel Member Posts: 1,272
    game project is seperate.

    check under MAMP\Logs to see if anything is showing up

    Make sure the URL attribute is hitting a valid page
    http://localhost:8888/MAMP/asyncTest.php
    returns "cannot select DB"
  • jonmulcahyjonmulcahy Member, Sous Chef Posts: 10,408
    I'm guessing your DB name in MySQL and what you have in the php.ini file don't match.

    that DB name occurs a few times throughout the file, make sure you change them all. In the PHP file I uploaded i had the db name set to GSTest instead of asyncTest.
  • tatiangtatiang Member, Sous Chef, PRO, Senior Sous-Chef Posts: 11,949
    @Riffel This happened to me before I replaced "GSTest" with "asyncTest" in several places in the asyncTest.php file.

    New to GameSalad? (FAQs)   |   Tutorials   |   Templates   |   Greenleaf Games   |   Educator & Certified GameSalad User

  • tatiangtatiang Member, Sous Chef, PRO, Senior Sous-Chef Posts: 11,949
    Yee haw! I was able to get the SEND button to work but when I click GET after adding table data in MySQL it gives me a callback of 1 but nothing changes in the scene so I don't think the table data has changed.

    New to GameSalad? (FAQs)   |   Tutorials   |   Templates   |   Greenleaf Games   |   Educator & Certified GameSalad User

  • tatiangtatiang Member, Sous Chef, PRO, Senior Sous-Chef Posts: 11,949
    Should I see the existing table data (which appears in json.txt) in the MySQL table view?

    image

    New to GameSalad? (FAQs)   |   Tutorials   |   Templates   |   Greenleaf Games   |   Educator & Certified GameSalad User

  • StormyStudioStormyStudio United KingdomMember Posts: 3,989
    edited January 2014
    Just hit the same problem as Riffel... I'd also missed the step of updating the php.ini file...

    I can now successfully send the file to the correct folder, and preview the json.txt file. However the testData table in the asyncTest database is'nt being populated with the table info. I can manually go in and add info in phpMyAdmin, but this does'nt update in the JSON.txt file either.

    I've used the same names as you. Also when I press 'Get' nothing new happens, but then there's nothing new in the json.txt file so I'm not expecting it to... (I did try manually typing in some new variables in the JSON.txt file but probably did that wrong anyway).

    I'll go through the process again and see what I've missed... I also need to read through your code so I soak up a little bit of knowledge.

    EDIT ... This is now fixed... see my next post down.
  • RiffelRiffel Member Posts: 1,272
    fixed. but when I try connect the top button is set to red and NetworkConnectStatus:-1
  • StormyStudioStormyStudio United KingdomMember Posts: 3,989
    edited January 2014
    SUCCESS.

    When changing the gsTest text in the php file from Jon Mulcahy

    Change the first one to 'asyncTest' as this is asking for the name of the database.

    From then on it knows which data base your looking at, and just needs the table name.

    So the other two instances of gsTest need changing to dataTest (or what ever your table is called inside the the database.

    I've earnt myself a tea. :-)

    (hold that tea... I seem to be losing the 2 columns info (playerName) when getting the table back... but the id and stats come through fine... I must have made a typo in the code.)...

    Oh I'm allowed the tea again. When going back through the process I'd named the table row 'playerNAME' not 'playerName' so the php code did'nt see it.

    All fixed and working beautifully.

    @jonmulcahy
    When I send the table from GameSalad it adds more rows to the table rather than replaces the data, is that working the same your end?... thanks Jon
  • StormyStudioStormyStudio United KingdomMember Posts: 3,989
    @Riffel I had the same problem.

    I went into SEND button actor, and changed the test info for logging in, into something else random. No reason for it, but it worked successfully after adding some fresh login info.

  • jonmulcahyjonmulcahy Member, Sous Chef Posts: 10,408
    wow lots of progress :)

    @tatiang, that json file is only what your app is sending, it doesn't update further. if you put this line at 146 you can get an output of the GET command

    file_put_contents('updatedjson.txt',$returnedJson);

    @stormystudio
    yes, this code just appends the new data. It would be pretty easy to update the MYSQL code to update it, but for now i just append.

    probably would have been better of me to make sure the tutorial and PHP file matched, but oh well :)
  • StormyStudioStormyStudio United KingdomMember Posts: 3,989
    wow lots of progress :)
    probably would have been better of me to make sure the tutorial and PHP file matched, but oh well :)
    Don't be silly... I'd of never earnt my tea if you'd done that.
  • The_Gamesalad_GuruThe_Gamesalad_Guru Member Posts: 9,922
    Awesome work. I have to download and copy all this info for future knowledge. Too busy with everything else right now.
  • tatiangtatiang Member, Sous Chef, PRO, Senior Sous-Chef Posts: 11,949
    When changing the gsTest text in the php file from Jon Mulcahy

    Change the first one to 'asyncTest' as this is asking for the name of the database.

    From then on it knows which data base your looking at, and just needs the table name.

    So the other two instances of gsTest need changing to dataTest (or what ever your table is called inside the the database.
    This was my problem. Aha! Thank you @StormyStudio!

    New to GameSalad? (FAQs)   |   Tutorials   |   Templates   |   Greenleaf Games   |   Educator & Certified GameSalad User

  • jonmulcahyjonmulcahy Member, Sous Chef Posts: 10,408
    updated the script in the original post, moved all the DB and Table names into variables at the top, so you only have to update a single location.
  • StormyStudioStormyStudio United KingdomMember Posts: 3,989
    edited January 2014
    @jonmulcahy

    Good afternoon… a question of syntax and format.

    I've been working on getting access to Facebook information, just to see how possible it would be to do stuff…. turns out very possible… and all running on my local MAMP server (thanks for that introduction, always ignored any idea of a local server before)

    So far….

    I've setup my Facebook app id and urls for the Facebook approval to work.
    The user opens a browser window from inside of a gamesalad game, logs in (if not already logged in on the device), approves any requested privileges i.e. access to friends list, an access token is granted and sent back to my server. I can then request the information I want, which is sent back to my local server. Where I can convert it into a JSON file.

    This has all been done by reading a lot of tips and tricks from various online posts. Piecing together code that I just about comprehend.

    It works so that's very cool.


    However I think the JSON file that Im currently generating needs sorting before it's turned into the Text file to send back to GameSalad, in much the same way that you have done with your asyncTest.php file.

    Now I'm going to try and figure out how to reformat so it is done in a way GameSalad like.. but wanted to get your opinion, help, advice first…

    I've attached a version of the JSON text file I'm creating with data from Facebook (I've gone in and changed all the id numbers and names so it's fine to share with the world.

    I would appreciate any insight you might have on this compared to what you did with your php script.

    Once I've got a working system I'll post a vid/how to for the Facebook bit.

    One problem I can see coming up is ideally the game would close the in game browser window as soon as the Facebook login and approval is complete. But I don't think we can trigger the closing of the browser window with gamesalad behaviours (maybe a 'while loop' will let me have the browser open until an attribute is true...).

    Thanks

    Jon
  • jonmulcahyjonmulcahy Member, Sous Chef Posts: 10,408
    @StormyStudio

    no problem. when working with the JSON data, i find converting it to an array first really helps.

    Do you have a sample format of the GS table you are trying to store it in? That will also help with the conversion.

    I loaded the file up into my test machine and converted it to an array, you can see it below. If you can send over a sample JSON coming out of GS I can take a look.

    Array ( [id] => 8345679096 [name] => Steve Paper [first_name] => Steve [last_name] => Paper [link] => https://www.facebook.com/paper4000 [birthday] => 03/15/1920 [hometown] => Array ( [id] => 198675897641883604 [name] => Kent, Kent, United Kingdom ) [location] => Array ( [id] => 124533723732263369 [name] => Maidstone ) [quotes] => life is like a box of chocolates [education] => Array ( [0] => Array ( [school] => Array ( [id] => 10854349232221 [name] => Grovewood School & Sixth Form College ) [type] => High School ) [1] => Array ( [school] => Array ( [id] => 118788887563124 [name] => Grovewood School & Sixth Form Centre ) [type] => High School ) [2] => Array ( [school] => Array ( [id] => 1490222939342 [name] => Bournemouth ) [concentration] => Array ( [0] => Array ( [id] => 17098899702935 [name] => Pre-Animation and Illustration ) ) [type] => College ) ) [gender] => male [email] => [email protected] [timezone] => 0 [locale] => en_GB [verified] => 1 [updated_time] => 2013-11-01T15:37:45+0000 [username] => Paper4000 )
Sign In or Register to comment.