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

1911131415

Comments

  • App_MakerApp_Maker Member, PRO Posts: 64

    Does anyone know how to allow the player to search the mysql database and return the results they ask for? Example below how can I have the $searchTerm variable be filled in with what the player wants to search for.

    $query = mysql_query("SELECT * FROM gsTest WHERE playerName LIKE '$searchTerm' ");

    So if the player wants to search for "James" it will change the variable $searchTerm to "James" and then filter all the results that have James in the playerName.

    Thanks

  • kpluongkpluong Member, PRO Posts: 11

    Hello everybody. I'm trying to follow these steps using the new Mac Gamesalad version (0.13.25 beta), but I cannot get it to send or receive. Does anybody know how to solve this problem? It looks like @App_Maker had a similar problem, but I couldn't find how he resolved it.

  • gingagaminggingagaming FREELANCE GS DEV Member Posts: 1,685

    Hey @jonmulcahy Ive got this sending data but receiving doesn't seem to come in. Ive checked the logs and I have the following error:

    [19-Oct-2015 21:18:28 Europe/Berlin] PHP Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in /Applications/MAMP/bin/mamp/asyncTest.php on line 114

    Any Ideas what went wrong here?

  • tatiangtatiang Member, Sous Chef, PRO, Senior Sous-Chef Posts: 11,948
    edited December 2015

    Any updates in terms of using this with MAMP 3.4 and PHP 5.6.10? I'm getting the following error in phpMyAdmin when attempting to go to http://localhost:8888/MAMP/:

    Invalid authentication method set in configuration: ‘cookie’

    I'm guessing my config file is not properly set up. Anyone willing to share their /Applications/MAMP/bin/phpMyAdmin/config.inc.php file with me? Thanks.

    Edit: I was able to get access to phpMyAdmin by restoring an original config file I had. I guess some of the changes I made to it to fix an earlier problem were incorrect.

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

  • tatiangtatiang Member, Sous Chef, PRO, Senior Sous-Chef Posts: 11,948
    edited December 2015

    GET is working but SEND isn't. The php script isn't generating json.txt or sqlErrors.txt. The apache and php logs aren't showing any errors. I decided to revert back to the original script you posted. Here are the errors I'm getting with the original script:

         PHP Notice: Undefined index: playerName in /Applications/MAMP/bin/mamp/asyncTest_orig.php on line 129
         PHP Notice: Undefined index: playerStats in /Applications/MAMP/bin/mamp/asyncTest_orig.php on line 130

    And from the script (lines 127-130):

         // tables we are capturing
         $playerID = $rows[$i]['playerID'];
         $playerName =$rows[$i]['playerName'];
         $playerStats = $rows[$i]['playerStats'];

    And the table structure:

    I know this thread is almost two years old -- and I went back and read every single post... phew! -- but I'm hoping someone can help out as I try to get this working.

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

  • jonmulcahyjonmulcahy Member, Sous Chef, PRO Posts: 10,406

    @tatiang I'm not positive but I Think it is case sensitive. Change your code to Have a capital P. I'm away right now best I could think of right now

  • The_Gamesalad_GuruThe_Gamesalad_Guru Member Posts: 9,919

    Ah you may be right about that. If it's unix then definitely case sensitive.

  • tatiangtatiang Member, Sous Chef, PRO, Senior Sous-Chef Posts: 11,948

    @jonmulcahy Thanks for the response. I'm not sure how I missed that! No wonder I never completed my Computer Science degree. :|

    But even with case sensitive table column names (my test project had matching table/php script names but still couldn't send), I can't get the TableSendStatus to change from 0 to anything else when clicking the send actor.

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

  • colandercolander Member Posts: 1,610

    @tatiang I had a problem with this last time I looked at my project. I contacted support and it was caused by using a text attribute to store the url address and they advised me to hard code it. That worked for me, if it fixes your issue then they still haven't fixed it on their end. @CodeWizard

  • tatiangtatiang Member, Sous Chef, PRO, Senior Sous-Chef Posts: 11,948
    edited December 2015

    @colander Good to know and I appreciate the suggestion but that didn't seem to affect it for me. Plus, I'm using a text attribute for both Send and Get and one is working while the other isn't.

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

  • colandercolander Member Posts: 1,610

    @tatiang Send worked for me too, I could see it update on the server but Get wouldn't unless I hard coded the url.

    When you fix your problem can test it with a text attribute and without to see if this is still an issue. I have changed computers and it is a lot of work for me to it set it up and test it.

  • tatiangtatiang Member, Sous Chef, PRO, Senior Sous-Chef Posts: 11,948

    @Colander It's the opposite for me. I can Get from the server but not Send. I tried hard coding the Send URL both as a quoted expression and after removing the expression and pasting it in. I'll play around with it some more...

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

  • zweg25zweg25 Member Posts: 738

    what is your callback attribute?

  • tatiangtatiang Member, Sous Chef, PRO, Senior Sous-Chef Posts: 11,948
    edited December 2015

    @zweg25 I'm using @jonmulcahy's posted script and project file on my local machine. The callback attribute is game.sendTable (an integer).

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

  • tatiangtatiang Member, Sous Chef, PRO, Senior Sous-Chef Posts: 11,948

    Not sure if it matters but in the Networking Behaviors info from the Cookbook, it suggests that JSON data from the Network Send Table to URL behavior is saved in /var/sandbox/params.txt. I don't have a /var/sandbox directory.

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

  • jonmulcahyjonmulcahy Member, Sous Chef, PRO Posts: 10,406

    @tatiang said:
    @Colander It's the opposite for me. I can Get from the server but not Send. I tried hard coding the Send URL both as a quoted expression and after removing the expression and pasting it in. I'll play around with it some more...

    ok, I'm back at a computer today. Are you still having trouble with this? I rewrote the get function to create a new log file called getErrors.txt and it will log every major piece of the function to it. Can you try running with it and see what you get?

        // start GET data
        if ($_SERVER['REQUEST_METHOD'] === 'GET') {
    
    
            $errorCheckingLog = 'getErrors.txt';
    
            file_put_contents($errorCheckingLog, "entered get function", FILE_APPEND);
    
            // initialize the JSON body variable
            $jsonBody="";
    
            // get table contents
            $query = mysql_query("SELECT * FROM ".$tableName); 
    
            // drop a copy of the table contents into the error log:
            file_put_contents($errorCheckingLog, $query, FILE_APPEND);
            // construct an array to hold the data we pull from mySQL
            $rows = array();
    
            // loop through the table and drop the data into the array
            while($row = mysql_fetch_assoc($query)) {
                // make sure it is looping properly
                file_put_contents($errorCheckingLog, $row, FILE_APPEND);
                $rows[] = $row;    
            }
    
            // get the number of rows in the array. We need this in the JSON return
            $arrlength = count($rows);
    
            // log array length
            file_put_contents($errorCheckingLog, $arrlength, FILE_APPEND);
    
            // set while loop index
            $i = 0;
    
            //loop through array node and get row values
            while ($i < $arrlength ) {
    
                // tables we are capturing
                $playerID = $rows[$i]['playerID'];
                $playerName =$rows[$i]['playerName'];
                $playerStats = $rows[$i]['playerStats'];
    
                // table row numbers. our index starts at 0, so we want to increment it by 1 to get valid row numbers.
                $tableRow = $i+1;
    
                // construct the JSON return from our data
                $jsonString = '{"Name":"'.$tableRow .'","Value":"|'.$playerID.'|'.$playerName.'|'.$playerStats.'|"},';
    
                // append the JSON return with the new data
                $jsonBody=$jsonBody.$jsonString;
    
                // log json body
                file_put_contents($errorCheckingLog, $jsonBody, FILE_APPEND);
                // increase index and loop again if not at end of array.
                $i++;           
            }
    
            // construct the JSON response
    
            // this is the header of the JSON return. It will have to be adjusted to match whatever your app is expecting. We have to define this here to get the row count above.
            $jsonHeadher='{"Properties":[],"Name":"","Children":[{"Properties":[{"Name":"rowCount","Value":'.$arrlength.'},{"Name":"columnCount","Value":3},{"Name":"0-1-name","Value":"playerID"},{"Name":"0-1-type","Value":1},{"Name":"0-2-name","Value":"playerName"},{"Name":"0-2-type","Value":1},{"Name":"0-3-name","Value":"playerstats"},{"Name":"0-3-type","Value":2}],"Name":"id911451_headers","Children":[]},{"Properties":[';
    
            // this is the footer of the JSON return. Again it will have to be adjusted to match whatever your app is expecting.
            $jsonFooter='],"Name":"id911451","Children":[]}]}';
    
            // removes an extra comma that the loop above leaves behind
            $jsonBody=rtrim($jsonBody, ",");
    
            // constructing the full JSON return
            $returnedJson=$jsonHeadher.$jsonBody.$jsonFooter;
    
    
            // write the JSON data so the app can read it.
            echo $returnedJson; 
    
            file_put_contents($errorCheckingLog, "              ", FILE_APPEND);
            file_put_contents($errorCheckingLog, $returnedJson, FILE_APPEND);
        } // end of get
    
  • Hi, could someone help me? Byethost is blocked ?? I have tried with this, and a personal host but it does not work. This is my table and its ok I think,

    http://pachopregunta.byethost7.com/getpingo.php

    But, "-1" persists.

    Thanks.

  • jonmulcahyjonmulcahy Member, Sous Chef, PRO Posts: 10,406

    @jonathanoviedoc1 said:
    Hi, could someone help me? Byethost is blocked ?? I have tried with this, and a personal host but it does not work. This is my table and its ok I think,

    http://pachopregunta.byethost7.com/getpingo.php

    But, "-1" persists.

    Thanks.

    first think you wanna check is if the web server is receiving the data, can you attach the log files I have the code generate? lines 32 and 82 in the code.

  • J.PharaohJ.Pharaoh Member, PRO Posts: 32

    Hello, I try to follow instruction, but could get it done. Send and get callback are 1.There are few things I don't understand.
    1) I could not find playstogther
    2) the network behavior change to send and get only
    3) Does the php file(post in 2014) still work ?

    I did change the dbName and tableName.
    I may miss some post. Pls give me some advise.

    Many thanks:)

  • jonmulcahyjonmulcahy Member, Sous Chef, PRO Posts: 10,406

    @simbig said:
    Hello, I try to follow instruction, but could get it done. Send and get callback are 1.There are few things I don't understand.
    1) I could not find playstogther
    2) the network behavior change to send and get only
    3) Does the php file(post in 2014) still work ?

    I did change the dbName and tableName.
    I may miss some post. Pls give me some advise.

    Many thanks:)

    1. Playstogether was removed until Windows was ready
    2. The name may have changed but the functions do the same thing
    3. The PHP file works!

    Where are you hosting this? What doesn't work? Check your server logs to see if you get any error msgs

  • J.PharaohJ.Pharaoh Member, PRO Posts: 32

    Thank a lot Jon for your reply. I follow your instruction and use the MAMP to create a local server. I fix the dbname and table name in the asyncTest.php file (only find 1 place to change for each one). When I run the project, the send callback get -1 and get callback is 0. There is only one thing I did not follow your instruction. That is the Disable Caching. I could not find the php5.5.3/conf/php.ini. I find the php5.5.26/conf/php.ini instead. Pls give me some advise. Thanks.

  • jonmulcahyjonmulcahy Member, Sous Chef, PRO Posts: 10,406
    edited March 2016

    @simbig said:
    Thank a lot Jon for your reply. I follow your instruction and use the MAMP to create a local server. I fix the dbname and table name in the asyncTest.php file (only find 1 place to change for each one). When I run the project, the send callback get -1 and get callback is 0. There is only one thing I did not follow your instruction. That is the Disable Caching. I could not find the php5.5.3/conf/php.ini. I find the php5.5.26/conf/php.ini instead. Pls give me some advise. Thanks.

    can you send me your asyctest.php file? I want to take a look

  • J.PharaohJ.Pharaoh Member, PRO Posts: 32
    edited March 2016
    <?php /* Sever Side PHP processing for gamesalad tables written by Jonathan Mulcahy http://www.icebergapps.com version 1.0.1 1/27/2013 */ // connect to SQL include_once 'php/functions.php'; $port = '3306'; $link = @mysql_connect(':/Applications/MAMP/tmp/mysql/mysql.sock', 'root', 'root'); // database connection strings. change these to your DB and Table names. $dbName = "asyncTest"; $tableName = "testData"; if (!$link) { exit('Error: Could not connect to MySQL server!'); } // connect to the table mysql_select_db($dbName)or die("cannot select DB"); // lets prepare some files to capture what is going on. $incomingJson = 'json.txt'; //$fullArray = 'fullArray.txt'; // needed if you enable the debugging secton below $sqlErrorLog = "sqlErrors.txt"; // initialize the string with a blank value $string = ""; // start SEND data if ($_SERVER['REQUEST_METHOD'] === 'POST') { //capture incoming data error_reporting(1); $sig = $_POST["sig"]; $jsondata = $_POST["params"]; // this line captures the sent data so you can figure out what you need to send back. file_put_contents($incomingJson,$jsondata); // this line tells the application that the data send was successful. echo '{"Status":"Success"}'; // convert JSON to an array $array = json_decode($jsondata, TRUE); /* // formats the array to view it easier $results = print_r($array,true); file_put_contents($fullArray,$results); */ //get the total number of objects in the array $arrlength = count($array['Children']['1']['Properties']); // set while loop index $i = 0; //loop through array node and get row values while ($i < $arrlength ) { // get row value $value = $array['Children']['1']['Properties'][$i]['Value']."\n"; // convert delimited string to an array $arrayPieces = explode("|", $value); // 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. $playerID = $arrayPieces[1]; $playerName =$arrayPieces[2]; $playerStats = $arrayPieces[3]; // construct SQL statement $sql="INSERT INTO ".$tableName."(playerID, playerName, playerStats)VALUES('$playerID', '$playerName', '$playerStats')"; // insert SQL statement $result=mysql_query($sql); // catch any errors if($result){ // if successful do nothing for now. } else { // if failure, write to custom log $sqlError = "Error writing to database\n"; file_put_contents($sqlErrorLog, $sqlError, FILE_APPEND); } $i++; } } // end of POST // start GET data if ($_SERVER['REQUEST_METHOD'] === 'GET') { // initialize the JSON body variable $jsonBody=""; // get table contents $query = mysql_query("SELECT * FROM ".$tableName); // construct an array to hold the data we pull from mySQL $rows = array(); // loop through the table and drop the data into the array while($row = mysql_fetch_assoc($query)) { $rows[] = $row; } // get the number of rows in the array. We need this in the JSON return $arrlength = count($rows); // set while loop index $i = 0; //loop through array node and get row values while ($i < $arrlength ) { // tables we are capturing $playerID = $rows[$i]['playerID']; $playerName =$rows[$i]['playerName']; $playerStats = $rows[$i]['playerStats']; // table row numbers. our index starts at 0, so we want to increment it by 1 to get valid row numbers. $tableRow = $i+1; // construct the JSON return from our data $jsonString = '{"Name":"'.$tableRow .'","Value":"|'.$playerID.'|'.$playerName.'|'.$playerStats.'|"},'; // append the JSON return with the new data $jsonBody=$jsonBody.$jsonString; // increase index and loop again if not at end of array. $i++; } // construct the JSON response // this is the header of the JSON return. It will have to be adjusted to match whatever your app is expecting. We have to define this here to get the row count above. $jsonHeadher='{"Properties":[],"Name":"","Children":[{"Properties":[{"Name":"rowCount","Value":'.$arrlength.'},{"Name":"columnCount","Value":3},{"Name":"0-1-name","Value":"playerID"},{"Name":"0-1-type","Value":1},{"Name":"0-2-name","Value":"playerName"},{"Name":"0-2-type","Value":1},{"Name":"0-3-name","Value":"playerstats"},{"Name":"0-3-type","Value":2}],"Name":"id911451_headers","Children":[]},{"Properties":['; // this is the footer of the JSON return. Again it will have to be adjusted to match whatever your app is expecting. $jsonFooter='],"Name":"id911451","Children":[]}]}'; // removes an extra comma that the loop above leaves behind $jsonBody=rtrim($jsonBody, ","); // constructing the full JSON return $returnedJson=$jsonHeadher.$jsonBody.$jsonFooter; // write the JSON data so the app can read it. echo $returnedJson; } // end of get // close the SQL connection mysql_close($link); ?>
  • J.PharaohJ.Pharaoh Member, PRO Posts: 32

    Sorry, I don't know why the message become like this when I post the code.

  • jonmulcahyjonmulcahy Member, Sous Chef, PRO Posts: 10,406

    @simbig said:
    Sorry, I don't know why the message become like this when I post the code.

    just attach the file to the post

  • J.PharaohJ.Pharaoh Member, PRO Posts: 32

    Pls don't mind. I don't know how to attach the file in the post. I find the image URL textfield. But I have no idea what to do with it.

  • jonmulcahyjonmulcahy Member, Sous Chef, PRO Posts: 10,406

    @simbig said:
    Pls don't mind. I don't know how to attach the file in the post. I find the image URL textfield. But I have no idea what to do with it.

    maybe you have to be pro to attach. Put it on dropbox or google dive or something, too difficult to read in that format

  • J.PharaohJ.Pharaoh Member, PRO Posts: 32
    edited March 2016

    Oh I see. If I am a pro user, I can attach the file then.

Sign In or Register to comment.