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

1679111216

Comments

  • scottharrrules43scottharrrules43 Tulsa, OklahomaMember, PRO Posts: 694
    edited December 2014

    here is my code (for this part)

    // construct SQL statement
    $sql="INSERT INTO" .$tableName. "(email, password, coins, high_score, slot_shield, red, green, black, white, gold, level, points, points_goal, coin_reward, level_cost, index) VALUES ('$email', '$password', '$coins', '$high_score', '$slot_shield', '$red', '$green', '$black', '$white', '$gold', '$level', '$points', '$points_goal', '$coin_reward', '$level_cost', '$index')";

    i got this error

    Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'index) VALUES ('test', 'fake', '5', '100', '0', '0', '0', '0', '0', '0', '0', '0' at line 1
    Whole query:

  • jonmulcahyjonmulcahy Member, Sous Chef Posts: 10,408
    edited December 2014

    @scottharrrules43‌

    change this

    // construct SQL statement $sql="INSERT INTO" .$tableName. "

    to this and try again

    // construct SQL statement $sql="INSERT INTO ".$tableName."

    I added a space after INTO and removed the space around the periods.

  • scottharrrules43scottharrrules43 Tulsa, OklahomaMember, PRO Posts: 694

    got same error

  • jonmulcahyjonmulcahy Member, Sous Chef Posts: 10,408

    @scottharrrules43‌
    actually the problem is your column called index. that's a reserved mysql keyword. change it to something else and you should be good.

  • scottharrrules43scottharrrules43 Tulsa, OklahomaMember, PRO Posts: 694

    i got this error randomly

    Invalid query: Unknown column 'slot_shield' in 'field list'
    Whole query:

    `// get array values. This section would have to be modified to capture each value you are interested in.
    $rowName = $arrayPieces[0]; // this variable will be blank if you don't name your rows.
    $email = $arrayPieces[1];
    $password =$arrayPieces[2];
    $coins = $arrayPieces[3];
    $high_score = $arrayPieces[4];
    $slot_shield = $arrayPieces[5];
    $red = $arrayPieces[6];
    $green = $arrayPieces[7];
    $black = $arrayPieces[8];
    $white = $arrayPieces[9];
    $gold = $arrayPieces[10];
    $level = $arrayPieces[11];
    $points = $arrayPieces[12];
    $points_goal = $arrayPieces[13];
    $coin_reward = $arrayPieces[14];
    $level_cost = $arrayPieces[15];
    $extrastuff = $arrayPieces[16];

            // construct SQL statement
            $sql="INSERT INTO ".$tableName."(email, password, coins, high_score, slot_shield, red, green, black, white, gold, level, points, points_goal, coin_reward, level_cost, extrastuff) VALUES ('$email', '$password', '$coins', '$high_score', '$slot_shield', '$red', '$green', '$black', '$white', '$gold', '$level', '$points', '$points_goal', '$coin_reward', '$level_cost', '$extrastuff')";
    

    `

  • jonmulcahyjonmulcahy Member, Sous Chef Posts: 10,408

    @scottharrrules43‌

    That's going to be something in your MySql implementation. Make sure that slot_shield actually exists as a table and can accept the value type you are inputting.

    a good way to test the mysql statements is to use phpmyadmin and copy and paste the full statement and execute it there.

  • scottharrrules43scottharrrules43 Tulsa, OklahomaMember, PRO Posts: 694

    there was a misspelling with it

    now it is fixed thanks

  • jonmulcahyjonmulcahy Member, Sous Chef Posts: 10,408

    over the past few days I leveraged these behaviors and my own back end server to code a feature that check's the game's version version the released version. If there is a difference (meaning a new version is released), I display an **update available ** button.

    I just ran it through some tests and it works great :)

  • scottharrrules43scottharrrules43 Tulsa, OklahomaMember, PRO Posts: 694
    edited December 2014

    if i wanted to replace the data in the database with some new values it should look like this right?

    $sql = "UPDATE asyncTest SET playerName=$playerName AND playerStats=$playerStats WHERE playerID=$playerID";
    it is not replacing, i am testing it with your project

  • scottharrrules43scottharrrules43 Tulsa, OklahomaMember, PRO Posts: 694
  • EireStudiosEireStudios Member Posts: 451

    I've been messing around with electronics lately using Arduino as my base, I think I'll try and see if I can control my Arduino using the network features in GS, if it doesn't work out I think it'll be great if @stevej‌ could add some new festures for this. I think Arduino and GS would be a great partnership because GS makes game creation simple and Arduino makes electronics projects simple, just an idea, what do you guys think? :smile:

  • jonmulcahyjonmulcahy Member, Sous Chef Posts: 10,408

    @EireStudios said:
    I've been messing around with electronics lately using Arduino as my base, I think I'll try and see if I can control my Arduino using the network features in GS, if it doesn't work out I think it'll be great if stevej‌ could add some new festures for this. I think Arduino and GS would be a great partnership because GS makes game creation simple and Arduino makes electronics projects simple, just an idea, what do you guys think? :smile:

    If you can control it using s web server I see no reason why this wouldn't work. You'll have to have some fancy server side code, and probably a table or url per function but it should work just fine. Once we can send individual table entries it will be much easier

  • HopscotchHopscotch Member, PRO Posts: 2,782
    edited December 2014

    Yep, the networking opens endless possibilities.

    I have a fitness treadmill of which the speed, inclination, etc. can be controlled via external signals.

    Now think endless runner where your success/failure or hardness of the stage translates to your workout in real time as you play. >:)

    Tearing downhill at breakneck pace, struggling uphill, little jolts on a bumpy road, that is real interactivity.

  • scottharrrules43scottharrrules43 Tulsa, OklahomaMember, PRO Posts: 694
    edited December 2014

    I replaced this
    // construct SQL statement
    $sql="INSERT INTO ".$tableName."(playerID, playerName, playerStats)VALUES('.$playerID.', '.$playerName.', '.$playerStats.')";

    with this
    $sql = "UPDATE asyncTest SET playerName=$playerName AND playerStats=playerStats WHERE playerID=playerID";

    it won't work for some reason it won't work.
    Is there any thing wrong with this?

    edit:it says can not write to database.

  • App_MakerApp_Maker Member, PRO Posts: 64

    I noticed that nightly version 13.2 has the network features to Get/Send rows from url. I tried using them instead of the Get/Send tables and they don't seem to work. I am using the asycTest template with the php file Jon included in the beginning of the thread. Do I need to make modifications to the php file to get the Gamesalad to work with the send/get individual rows?

    I was wondering how are the rest of you are working with just getting/sending one row at a time?

  • scottharrrules43scottharrrules43 Tulsa, OklahomaMember, PRO Posts: 694
    edited January 2015

    Deleted

  • App_MakerApp_Maker Member, PRO Posts: 64

    Do you have a example php file with the modification I can look at?

    Thanks

  • scottharrrules43scottharrrules43 Tulsa, OklahomaMember, PRO Posts: 694
    edited January 2015

    @App_Maker‌ I edited this above but I flip flopped on send and get

    Also I don't have a modification like this but should be able to help point and right direction hold on on minute.

  • scottharrrules43scottharrrules43 Tulsa, OklahomaMember, PRO Posts: 694
    edited January 2015

    @App_Maker‌ before I tell you this stuff I should tell you I am new with php so i may be wrong

    On send change this bit of code to what rows you want in arrlength attribute

    Here is a helpful link
    http://www.w3schools.com/php/php_looping.asp

    On get (bit more difficult) I would change the query to what you want rows you want select

    Here is link I would recommend.
    http://www.w3schools.com/php/php_mysql_select.asp

  • App_MakerApp_Maker Member, PRO Posts: 64

    For the send part how can I initialize the row value to match the row that I'm requesting from Gamesalad.

    For example,

    If I use the Network Send Table Row To URL (Action) Rule in Gamesalad and where it says Row: "Lets say I want row 7 to be the row it sends to database."

    How can I set the $arrlength to row 7 based on whatever row value I set in Gamesalad. So if I request row 3 it changes $arrlength to that. Any idea on how to do that?

    That way I can say while ($i = $arrlength)
    {
    // Then I can copy row info to database.
    }

  • scottharrrules43scottharrrules43 Tulsa, OklahomaMember, PRO Posts: 694

    You could just take the row and move it to a table and send it.> @App_Maker said:

    For the send part how can I initialize the row value to match the row that I'm requesting from Gamesalad.

    For example,

    If I use the Network Send Table Row To URL (Action) Rule in Gamesalad and where it says Row: "Lets say I want row 7 to be the row it sends to database."

    How can I set the $arrlength to row 7 based on whatever row value I set in Gamesalad. So if I request row 3 it changes $arrlength to that. Any idea on how to do that?

    That way I can say while ($i = $arrlength)
    {
    // Then I can copy row info to database.
    }

  • App_MakerApp_Maker Member, PRO Posts: 64

    That's is true, but then I wouldn't be able to use the network send row rule. Sending just the row you want could be beneficial by not having to create a new table to move the row to.

  • scottharrrules43scottharrrules43 Tulsa, OklahomaMember, PRO Posts: 694
    edited January 2015

    @App_Maker‌
    Oh I see the command daily builds sorry I am not. To fluent with that command.
    That's the way would do it and once it send just clean out the table. On tables on takes up like a kb or two. It is just an easy work around.

  • App_MakerApp_Maker Member, PRO Posts: 64

    So I just modified the php file to search for a specific name in the playerName column and it works great thanks. Below is where I made changes.

    // get table contents
    $query = mysql_query("SELECT * FROM gsTest WHERE playerName LIKE '%bob%'");

    The code above will search the database table and check the playerName Column for the name bob and pull back all matches. That's further than I've gotten before.

    Now how it was done isn't the best way since it was hardcoded into php file for the search term "bob". What I need to do is create a table that I can send to the database with search terms that a player enters into Gamesalad and then somehow input them into this php script as a variable in place of "bob". That way the player can search for whatever name they want and it would return the results back to them.

  • jonmulcahyjonmulcahy Member, Sous Chef Posts: 10,408

    @App_Maker said:
    I noticed that nightly version 13.2 has the network features to Get/Send rows from url. I tried using them instead of the Get/Send tables and they don't seem to work. I am using the asycTest template with the php file Jon included in the beginning of the thread. Do I need to make modifications to the php file to get the Gamesalad to work with the send/get individual rows?

    I was wondering how are the rest of you are working with just getting/sending one row at a time?

    cool, i missed those new network features. Once 13.2 hits RC I'll try and work those options into my example. It should be a pretty small modification. In the example we are looping through an array we created with all the table entries. With only sending a single row, we'd just be inserting (or updating) that row.

    In the game I'm testing this out with I'm only capturing when people play the game and their progress to see how far people actually get, so I track that in a single row table and just send that over.

    In the huge game I've been working on for almost a year it will be much more involved, and should use those new functions heavily.

  • App_MakerApp_Maker Member, PRO Posts: 64

    Hey Jon,

    Do you know how to let the Gamesalad player search something on the database?
    I assume I have to let the player type in a search word in Gamesalad and then save it to a table in Gamesalad. After it's saved send it to the database as a variable and somehow check the database. Where I'm lost is how I can set up the search word as a variable and to check and get back the info. In the example above I just hardcoded the search term into the php file.

    Thanks

  • App_MakerApp_Maker Member, PRO Posts: 64

    Does anyone know how to use the network send and get attribute rules in the latest nightly build? I was looking at the json.txt file and this is what it sends to the php file
    if set a text game attribute to "Vegas" and below is the info.

    {"Properties":[{"Name":"attributes.id348967","Value":Vegas}],"Name":"","Children":[]}

    Does anyone know how to turn this into a variable that php can read and then use it to get info from the database. Basically I'm trying to have the player send a attribute/search term "Vegas in this current example" to the database and get back all the info regarding the search term.

    Any help would be greatly appreciated. Thank you

  • scottharrrules43scottharrrules43 Tulsa, OklahomaMember, PRO Posts: 694
    edited January 2015

    Finally got my game to use network features. Out on the App Store. I still need to make some modification to the php script but database leaderboard works pretty great.

Sign In or Register to comment.