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

11011121416

Comments

  • buildbuild Member, PRO Posts: 45

    well if i import a csv file with 1 million or 1000 rows. Gamesalad still limits it to 100 rows.
    And what if the game had more than 10000 players with each 10000+ buildings? That would require a lot of rows.

    I use windows 8.

  • NNterprisesNNterprises Member, PRO Posts: 387

    Never tried windows 8 but windows 10 works fine.
    I imported a 250 row CSV and yes GS only lets me SEE 100 rows of it, but all 250 rows have been tested in GS and ad-hoc and works fine. I'm not sure what real limitations it has, like is 1000000 too much, who knows? But I do know you will only be able to see 100 of them and it works for 250 I can confirm.

    And 999 works according to @jonmulcahy

  • buildbuild Member, PRO Posts: 45
    edited August 2016

    i will test with a csv with 1000000 row containing the numbers 1-1000000. Then make it scroll up 5000 numbers a sec. too see how far it goes

  • MantoManto Member Posts: 796

    @build said:
    i will test with a csv with 1000000 row containing the numbers 1-1000000. Then make it scroll up 5000 numbers a sec. too see how far it goes

    Last time I tested, it worked with 30k rows. Though I used my own import script as it was taking a long time with gs.

    I don't think you need that many rows in the GS game. You'll need to have all rows on the server, but it is enough to have one or two tables in GS, that will be loaded based on what the player does in the game. For example when a player wants to see other player's buildings, you would use the get table behavior to load only that player's buildings. When they switch the player whose buildings they are looking at, the table can be overwritten.

  • The_Gamesalad_GuruThe_Gamesalad_Guru Member Posts: 9,922

    @build said:
    Then what if a player had more than 100 buildings?
    I mean the tables in gamesalad maxes out at 100 rows.

    And how do i call the ID specific placement data from a table with thousands of IDs?

    Im also not quite sure how to add multiple table values to the .php file.

    These are the only problems stopping me from creating my game.

    Execpt for the bugged custom collisions, but thats another topic

    I think you're asking gamesalad to do more than it can at this point. It's too bad they haven't released their playstogether multiplayer as this would be easier to do. Building a multiplayer game is super complex, even with their multiplayer system that handles the backend. Trust me I built a two player multiplayer with the together option.

  • buildbuild Member, PRO Posts: 45

    @The_Gamesalad_Guru said:

    I think you're asking gamesalad to do more than it can at this point. It's too bad they haven't released their playstogether multiplayer as this would be easier to do. Building a multiplayer game is super complex, even with their multiplayer system that handles the backend. Trust me I built a two player multiplayer with the together option.

    I think so too. The idea wasnt to create a multiplayer game though, It was for grabbing every attribute from a database table and then save it to the table afterwards.

    And im pretty sure that you can add several tables into the .php files, im just not entirely sure how to.

  • chaosmasterrochaosmasterro Member, PRO Posts: 51

    So I'm working with PHP 7. I changed most of the commands out to an updated mysqli version. I can use both the get and send functions. The get functions returns a 1 and transfer my data in. The send function returns a 1 but when it writes to my table it only writes null/0 in my fields when it should write a different value.

    Any idea why? Since it's not a syntax error my log is not updating and the sqlerror.txt file is not returning "Unable to write to database" obviously.

  • JScottJScott Member Posts: 143

    Hey all, I know this is an old thread, just wondering if someone can help me! I've spent days trying to get this figured out, very frustrating, but now I need to figure it out or die trying...
    So, I've got the "get" part working great (I've followed @jonmulcahy 's thread, plus the others I could find). This is funny because I wasn't really trying for "get" and it seems more complicated. But the "send" part is making me crazy! I've included the php and a couple of pics. I'm assuming the connect to SQL is correct as it's the same on my get.php (which works). I've left the password out, but you're welcome to it if you want to give it a try.
    I thought it might be due to ghetto free webhosts, but I've tried a few, even paid for this current one. And since it sort of works (for get) I think it must be in the code. The gs file is the same as Jon's asynctest, just changed the URL.

    I don't get any error logs(that I can find at least) and I don't know if there should be a .json file showing up somewhere?

    Please have a look!


    <?php // connect to SQL $servername = "localhost"; $username ="tysonbye_tyson"; $password = "xxxxxxx"; $link = mysql_connect($servername, $username, $password); // database connection strings. change these to your DB and Table names. $dbName = "tysonbye_tyson"; $tableName = "testdata"; if (!$link) { //exit('Error: Could not connect to MySQL server!'); echo mysql_errno($link) . ": " . mysql_error($link). "\n"; } // 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(stripslashes($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 databasen"; file_put_contents($sqlErrorLog, $sqlError, FILE_APPEND); } $i++; } } // end of POST // close the SQL connection mysql_close($link); ?>

  • JScottJScott Member Posts: 143

    That wall of text don't look so good, hopefully this is better...

  • MantoManto Member Posts: 796

    @JScott said:
    That wall of text don't look so good, hopefully this is better...

    Did you check the error file defined by the variable $sqlErrorLog ? It should write "Error writing to database" if there's an error.

  • JScottJScott Member Posts: 143

    Thanks @Manto. It looks like that variable is defined as "sqlErrors.txt", but I don't know where that file goes/is!

    The top of the php is cut off in the post above, so I'm attaching the top. It looks like debugging is not enabled due to the "//" in front of "$fullArray"? I've removed those and tried again, but not getting any error logs (or I don't know how to view them...).

  • jonmulcahyjonmulcahy Member, Sous Chef Posts: 10,408

    Uncomment line 43 and 44 and it will dump whatever is incoming to a file in the same directory as the php file.

  • jonmulcahyjonmulcahy Member, Sous Chef Posts: 10,408
  • Shenanigans StudioShenanigans Studio Member, PRO Posts: 73

    Anyone have a clue as to what the table limit for sql tables is in gamesalad? I'm using my own server for cross platform highscores and I need to Get Table when the person begins playing so that I can check to make sure their username is unique. However I'm worried that if I have 50k,100k,500k,1Mil users(rows in my table) then gamesalad wont be able to import the table. Or perhaps their a work around to check to unique usernames?

  • JScottJScott Member Posts: 143

    Thanks @jonmulcahy . I've uncommented lines 43 and 44. I also noticed line 12 was commented?

    But, uncommenting those, didn't seem to change anything, does that mean it's not getting that far? Not connecting?

  • JScottJScott Member Posts: 143

    @Shenanigans Studio sorry, as you can see I can't even figure out how to send data yet! But I followed your posts on the subject, it's helped me so far. Looks like you're doing some cool stuff with this feature.

  • jonmulcahyjonmulcahy Member, Sous Chef Posts: 10,408

    @Shenanigans Studio said:
    Anyone have a clue as to what the table limit for sql tables is in gamesalad? I'm using my own server for cross platform highscores and I need to Get Table when the person begins playing so that I can check to make sure their username is unique. However I'm worried that if I have 50k,100k,500k,1Mil users(rows in my table) then gamesalad wont be able to import the table. Or perhaps their a work around to check to unique usernames?

    Search on here for uuid. I have a post about generating a unique identifier which I use to make sure everything is unique. Only downside is if you delete the game and recreate, you get a new UID

  • jonmulcahyjonmulcahy Member, Sous Chef Posts: 10,408

    @JScott said:
    Thanks @jonmulcahy . I've uncommented lines 43 and 44. I also noticed line 12 was commented?

    But, uncommenting those, didn't seem to change anything, does that mean it's not getting that far?

    That would be my assumption. Who are you using as a web host? I use site5 for all my needs.

    Check your apache logs, which will be at the server level, probably in your websites cpanel or whatever they use. You should see incoming traffic from your phone.

    They might have a php error log too, in case it was a php problem.

  • MantoManto Member Posts: 796

    @JScott

    Tested your code on a local php 5.6.28 server and it worked. What version of php are you using? If you are using PHP 7, the code may not work because mysql_connect is removed. You can check the version by for example adding:
    echo 'Current PHP version: ' . phpversion();
    to your php file and opening the page in browser.

  • MantoManto Member Posts: 796
    edited January 2017

    @Shenanigans Studio said:
    Anyone have a clue as to what the table limit for sql tables is in gamesalad? I'm using my own server for cross platform highscores and I need to Get Table when the person begins playing so that I can check to make sure their username is unique. However I'm worried that if I have 50k,100k,500k,1Mil users(rows in my table) then gamesalad wont be able to import the table. Or perhaps their a work around to check to unique usernames?

    Why not let the database check if the username is unique? Send the username using the get table behavior and let the server only return whether the username is available or not. You should be able to send data to server in get table behavior if you add the username to the url, for example like this:

    http://someserver.com/checkusername.php?username=manto

    Then in the php file use $_GET["username"] to access username and a sql query to check if it's unique.

  • JScottJScott Member Posts: 143

    @Manto thanks, looks like the server is using PHP 5.4.45
    @jonmulcahy I am using ifastnet for a server, which I'm sure is not the best, but I paid for the super awesome deluxe version because I thought that might help. it didn't.
    So, could be them, but seems weird that I can get data, but not send?
    The cpanel error logs show nothing there. I can see traffic, don't know if that means anything?

  • MantoManto Member Posts: 796
    edited January 2017

    @JScott said:
    @Manto thanks, looks like the server is using PHP 5.4.45
    @jonmulcahy I am using ifastnet for a server, which I'm sure is not the best, but I paid for the super awesome deluxe version because I thought that might help. it didn't.
    So, could be them, but seems weird that I can get data, but not send?
    The cpanel error logs show nothing there. I can see traffic, don't know if that means anything?

    Are those request in the image clickable? Can you find more information about what data is sent to the server?

  • JScottJScott Member Posts: 143

    OK, some progress (I think). I now have a json.txt and fullArray.txt in my server folder. But they are both empty!

  • Shenanigans StudioShenanigans Studio Member, PRO Posts: 73
    edited January 2017

    Thanks for all the help guys! I'm having trouble searching the array. I've spent quite a bit more hours on trying to figure this out than I'd like to admit. for some reason in_array and array_search aren't giving an outout for me. Ive checked and $rows is an array and it does contain the information I need and the $_GET function is running fine(can see in code).

    //find user
    $user = $_GET[username];
    echo in_array($user, $rows);
    echo array_search($user, $rows);
    echo 'Testing Array:' . $rows[3]['username'];
    echo 'break' . 'Testing $_GET' . $user;
    echo 'break' . '$rows is array?' . gettype($rows);

    Here's the output link
    http://www.forgedhero.com/nameChecker.php?username=Jake

  • MantoManto Member Posts: 796

    @Shenanigans Studio said:
    Thanks for all the help guys! I'm having trouble searching the array. I've spent quite a bit more hours on trying to figure this out than I'd like to admit. for some reason in_array and array_search aren't giving an outout for me. Ive checked and $rows is an array and it does contain the information I need and the $_GET function is running fine(can see in code).

    //find user
    $user = $_GET[username];
    echo in_array($user, $rows);
    echo array_search($user, $rows);
    echo 'Testing Array:' . $rows[3]['username'];
    echo 'break' . 'Testing $_GET' . $user;
    echo 'break' . '$rows is array?' . gettype($rows);

    Here's the output link
    http://www.forgedhero.com/nameChecker.php?username=Jake

    So your array contains all usernames?

    If you just want to check if the username exists, it's better to use sql query (databases are good at searching), e.g:

    select 1
    from table
    where username = Jake;

  • Shenanigans StudioShenanigans Studio Member, PRO Posts: 73

    @Manto hmm I'm getting a HTTP 500error, could you check my syntax please:

    $query = mysql_query("SELECT * FROM ".$tableName WHERE username = $_GET[username]);

  • MantoManto Member Posts: 796

    @Shenanigans Studio said:
    @Manto hmm I'm getting a HTTP 500error, could you check my syntax please:

    $query = mysql_query("SELECT * FROM ".$tableName WHERE username = $_GET[username]);

    The part between $tableName and $_GET should be string:

    $query = mysql_query("SELECT * FROM ".$tableName." WHERE username = ".$_GET[username]);

  • Shenanigans StudioShenanigans Studio Member, PRO Posts: 73
    edited January 2017

    @Manto Well that takes care of the error, the page runs now, but unfortunately I'm getting the same problem as I was when trying in_array and array_search. when add ?username=Jake or any other username I know is in my array the new line of code selects 0 rows. And I'm certain Jake is in my array because I ran echo $rows[4]['username'] and it returned "Jake". Here is my code in its entirety if that helps:

    // connect to SQL
    include_once 'php/functions.php';
    
    $port = '3306';
    $link = @mysql_connect('143.95.234.90', 'forgedhe_wp217', '--------');
    
    // database connection strings. change these to your DB and Table names.
    $dbName = "forgedhe_leaderboard";
    $tableName = "leaderboard";
    
    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 GET data
    if ($_SERVER['REQUEST_METHOD'] === 'GET') {

        // initialize the JSON body variable
        $jsonBody="";
    
        // get table contents
        $query = mysql_query("SELECT * FROM ".$tableName." WHERE username = ".$_GET[username]);
    
        // 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;    
        }
    

    //find user
    $user = $_GET[username];
    echo in_array($user, $rows);
    echo array_search($user, $rows);
    echo 'Testing Array:' . $rows[3]['username'];
    echo "
    " . 'Testing $_GET' . $user;
    echo "
    " . '$rows is array?' . gettype($rows);

    } // end of get
    
    
    // close the SQL connection
    mysql_close($link);
    

    EDIT: all players have a unique ID and if I alter code to "WHERE PlayerID = 3" then it outputs a row(the player with ID 3). So I'm assuming my problem is on the sql end. Do I need to set up something in my sql structure perhaps? I can select any rows by any other values(integers), its just the text attributes Im having trouble selecting.

  • MantoManto Member Posts: 796

    EDIT: all players have a unique ID and if I alter code to "WHERE PlayerID = 3" then it outputs a row(the player with ID 3). So I'm assuming my problem is on the sql end. Do I need to set up something in my sql structure perhaps?

    Instead of select * use select 1. Then it'll output "1" if it exists and "" if it doesn't.

  • MantoManto Member Posts: 796

    @Shenanigans Studio said:
    // get table contents
    $query = mysql_query("SELECT * FROM ".$tableName." WHERE username = ".$_GET[username]);

    Actually $_GET[username] should be $_GET["username"]

Sign In or Register to comment.