0

So I have a SQL column that stores data like this.

"[1338,0,8523]"

I was wondering if I can then garb each one individually and because the value is minutes, if I could times it by 60 to get the hours and then display it, I've used the below for my other results but I'm stuck on this one.

<?php

$servername = "localhost";
$username = "Time";
$password = "fakepassword";
$dbname = "time";

$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
     die("Connection failed to Timebase Highscores: " . $conn->connect_error);
}

$sql = "SELECT name, time FROM players ORDER BY time DESC LIMIT 5";
$result = $conn->query($sql);
 
echo "Top 5 Life Wasters". "<br>";
if ($result->num_rows > 0) {
     while($row = $result->fetch_assoc()) {
          echo $row["name"]. " - $" . $row["time"] . "<br>";
     }
} else {
     echo "Error fetching any players from database.";
}
$conn->close();
?>
jarlh
  • 42,561
  • 8
  • 45
  • 63
Lone Dark
  • 1
  • Which dbms are you using? – jarlh May 13 '22 at 12:20
  • @jarlh MySQL Ran off of xampp – Lone Dark May 13 '22 at 12:23
  • Does this answer your question? [How to search JSON data in MySQL?](https://stackoverflow.com/questions/30411210/how-to-search-json-data-in-mysql) – Justinas May 13 '22 at 12:23
  • Treate each value as JSON array and use JSON_EXTRACT(). – Akina May 13 '22 at 12:40
  • 2
    Alternatively, normalise your database so you've got a secondary timings table listing each time entry in a separate row (with a foreign key back to the players table). Then you could easily use SQL to get the time for each user (by using SUM and GROUP BY), and also order the data properly by the highest time. See also [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – ADyson May 13 '22 at 12:48

0 Answers0