How do I fetch SQL data with a timestamp logic

Get help using Construct 2

Post » Sun Nov 20, 2016 6:35 pm

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:

Image

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


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

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.
Code: Select all
<?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();
?>


Any one with some ideas?
Maybe the SQL-king @korbaach has any ideas? ;)
B
16
S
4
Posts: 85
Reputation: 1,175

Post » Mon Nov 21, 2016 2:02 am

@fredriksthlm I'm far away from SQL-anything..
but .. here's an idea..instead of timestamp fild use simple INT fild

Image

and in your save score php script add new variable
Code: Select all
$time=time()


now you're going to store Unix Timestamp to table

Image

ok...now in your get score php
Code: Select all
// 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);


you can add after "mysql_select_db("$db_name")or die("cannot select DB");"

Code: Select all
$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);


Image


scoreDaysphp.capx
B
67
S
24
G
7
Posts: 1,518
Reputation: 11,072

Post » Tue Nov 22, 2016 10:27 pm

Thank you @korbaach , highly apprecieted! It works very well, I will provide a print screen soon with the result :)
B
16
S
4
Posts: 85
Reputation: 1,175


Return to How do I....?

Who is online

Users browsing this forum: draiza and 3 guests