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?