Ok solved my problems with 2 workarounds.
I'm reporting here, maybe someone else will find this little guide useful.
Problem 1: First record (position) doesn't appear in the ingame hiscore table
1. Create a fake line in the db
Go the database and manually create a new line with score value as high as possible (2147483647 in my case). Name and other fields doesn't matter since the order is score based. This fake line will be the first one and won't be shown in the game hiscore table. The second record will be now shown as the first one.
2. Fix hiscore entries number in the ingame hiscore table
Ingame hiscore table will now report an extra record (the fake one) so we need to tell that number of rows = row - 1
send_data.php
change this line from:
$num_row = mysqli_num_rows($res0);
to
$num_row = mysqli_num_rows($res0) - 1;
New problem occurs: fake line appears in the web hiscore table
3. Hide the fake line in the hiscore web page via css
index.php
add this line in the <head> section to hide the second row of the table (first one is the header with icons):
<style>table tr:nth-child(2) { display: none !important; }</style>
4. Fix hiscore entries number in the web hiscore table
index.php
change this line from:
<div id="container_number_score"><i class="fa fa-star fa-lg" aria-hidden="true"> </i><?php echo $num_row ?> High Scores</div>
To:
<div id="container_number_score"><i class="fa fa-star fa-lg" aria-hidden="true"> </i><?php echo $num_row - 1 ?> High Scores</div>
New problem occurs: gold trophy disappear
5. Fix trophies assignment
index.php
fix trophies assignment increasing the position value by one, so the gold one will be correctly assigned to the second row (first is now hidden) and so on.
if ($position == 2){
$trophy = "<img src='./img/trophy1.png'>";
}
if ($position == 3){
$trophy = "<img src='./img/trophy2.png'>";
}
if ($position >= 4){
$trophy = "<img src='./img/trophy3.png'>";
}
Problem 2: input a special characters will report a blank name (empty field)
This is not a real solution, because it got rid of the special characters rather than fix the db.
I messed with utf-8 encoding stuff on the db (general-ci, unicode-ci), but no way to fix it.
This won’t return a blank name, at least:
Solution 1 (Universal)
Make the input field read only and use a custom entry system to write the name as in the old arcades or with a custom keyboard so you can exclude special characters.
Then using variables you can make the text of the input field = characters pressed.
Solution 2 (Windows only)
Make the input field read only and use the Substring Replace Object to replace any special characters with its standard character (eg.: replace è with e)
A tedious job, nonetheless, but I have a working online score system now.
EXTRA: show the position number
By default this solution doesn't show the position number. If you want to show it both in game and on the web:
Ingame
send_data.php
After
while (NULL !== ($check = $res0->fetch_array(MYSQLI_BOTH)) )
{
Add this
$position = $position+1;
echo $position;
Webpage
index.php
1. Add the position th to the table (first line in black with the hashtag icon)
echo"<table>
<tr>
<th><i class='fa fa-hashtag fa-2x' aria-hidden='true' ></i> </th>
<th><i class='fa fa-trophy fa-2x' aria-hidden='true' ></i> </th>
<th><i class='fa fa-list fa-2x' aria-hidden='true'></i>
</th>
<th><i class='fa fa-user-circle-o fa-2x' aria-hidden='true'></i></th>
<th><i class='fa fa-calendar fa-2x'aria-hidden='true'></i></th>
<th><i class='fa fa-clock-o fa-2x' aria-hidden='true'></i></th>
</tr>";
(WITH the above fix for the first line missing applied - go to the next red title if you don't have the first line missing problem)
2. Add this line before the line $position = $position+1;
$realposition = $position;
3. Add the position td to the html table (first line in black)
echo "
<tr>
<td>".$realposition."</td>
<td>".$trophy."</td>
<td>".$check['score']."</td>
<td><span>".$check['player_name']."</span></td>
<td>".$check['date_upload']."</td>
<td>".$check['heure_upload']."</td>
</tr>
";
(WITHOUT the above fix for the missing first line applied - hiscore table is ok by default)
If you doesn’t experience the problem of the first line missing, but want to add the position number:
index.php
2. Add the position td to the html table (first line in black)
echo "
<tr>
<td>".$position."</td>
<td>".$trophy."</td>
<td>".$check['score']."</td>
<td><span>".$check['player_name']."</span></td>
<td>".$check['date_upload']."</td>
<td>".$check['heure_upload']."</td>
</tr>
";
and while ps4 joypad works flawlessly, i'm experiencing some sort of lag with keyboard.