How do I fetch SQL data with a timestamp logic

  • Hi all SQL/PHP pros,

    I've been trying to set up a leaderboard based on writing/reading from a SQL table.

    (I followed this tutorial https://www.scirra.com/tutorials/4839/creating-your-own-leaderboard-highscores-easy-and-free-php-mysql/page-2 )

    It works perfectly. I can post scores from the construct game and also fetch data from the sql table into the game with AJAX.

    Now I plan to make two lists within the game. One highscore table with "all time high scores" as well as one list with highest of the week/month or similar. So for the latter to work I need to be able to fetch db records based on some timestamp logic.

    In the SQL table I have added one column with auto generated TIMESTAMP.

    NOTE: the time is not sent from the Construct game, it is autogenerated in the SQL database. Here is the table:

    Here is an example record with data, autogenerated time stamp..

    This is how to post/get data from the sql table in construct, it is only linked to a PHP page:

    This is the PHP code to GET the data. I guess here is where you would put in some logic to only get records with timestamp from the last 30days or similar.

    <?php
    header('Access-Control-Allow-Origin: *');
    
    $host="localhost"; // Host name 
    $username="username"; // Mysql username 
    $password="password"; // Mysql password 
    $db_name="database"; // Database name 
    $tbl_name="scores"; // Table name
    
    // Connect to server and select database.
    mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
    mysql_select_db("$db_name")or die("cannot select DB");
    
    // Retrieve data from database 
    $sql="SELECT * FROM scores ORDER BY score DESC LIMIT 10";
    $result=mysql_query($sql);
    
    // Start looping rows in mysql database.
    while($rows=mysql_fetch_array($result)){
    echo $rows['name'] . "|" . $rows['score'] . "|";
    
    // close while loop 
    }
    
    // close MySQL connection 
    mysql_close();
    ?>[/code:17bhc8n1]
    
    Any one with some ideas?
    Maybe the SQL-king 

    korbaach has any ideas?

  • fredriksthlm I'm far away from SQL-anything..

    but .. here's an idea..instead of timestamp fild use simple INT fild

    and in your save score php script add new variable

    $time=time()[/code:absbjous]
    
    now you're going to store [url=http://www.unixtimestamp.com/]Unix Timestamp[/url] to table
    
    [img="http://lookpic.com/O/i2/1857/l8KtVWyq.png"]
    
    ok...now in your get score php
    [code:absbjous]// Connect to server and select database.
    mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
    mysql_select_db("$db_name")or die("cannot select DB");
    
    // Retrieve data from database 
    $sql="SELECT * FROM scores ORDER BY score DESC LIMIT 10";
    $result=mysql_query($sql);[/code:absbjous]
    
    you can add after [i]"mysql_select_db("$db_name")or die("cannot select DB");"[/i]
    
    [code:absbjous]$time = 0;
    $oneday = 86400;
    $days = $_POST['days'];
    
    if ($days > "0") { 
       $time=time()-( $oneday * $days);
    }
    // Retrieve data from database 
    $sql="SELECT * FROM scores  WHERE time>$time ORDER BY  score DESC LIMIT 10";
    $result=mysql_query($sql);[/code:absbjous]
    
    [img="https://media.giphy.com/media/3oriNQUBv7CaPkshWM/giphy.gif"]
    
    [url=https://app.box.com/s/kuj97nt7aea82krvp02rdapzoj9xwt9s]scoreDaysphp.capx[/url]
  • Try Construct 3

    Develop games in your browser. Powerful, performant & highly capable.

    Try Now Construct 3 users don't see these ads
  • Thank you korbaach , highly apprecieted! It works very well, I will provide a print screen soon with the result

Jump to:
Active Users
There are 1 visitors browsing this topic (0 users and 1 guests)