0

I'm trying to make a website to keep the best time records on my website. Currently I'm struggling to get the most popular levels.

I'm using MySQL in combination with phpMyAdmin (for the manual input) and PHP to manage my tables. Currently I'm using a table recordData to keep track of certain records. The table consists of a uniqueID(int) (A.I.), time(int), timeUsername(varchar) (the player with the time), timeLevelID(int) (the level played) and some other irrelevant data.

What I want is an output of the int data in timeLevelID that got used the most. Please see the following data to simplify this concept:

uniqueID timeLevelID
1 6
2 2
3 31
4 31
5 6
6 6

Where the desired output is a sorted count table, descending by count data:

timeLevelID count
6 3
31 2
2 1

What I've tried so far: First attempt, I tried messing around with the SQL query, but somehow I never got it to work.

    require_once "dbConnect.php";
    $allRecordsDataSQL="SELECT timeLevelID COUNT(timeLevelID) AS timeLevelIDFrequency FROM recordData GROUP BY timeLevelID ORDER BY timeLevelIDFrequency DESC";
    $allRecordsData = $conn->query($allRecordsDataSQL);
    print_r($allRecordsData);
    while($row=$allRecordsData->fetch_array(MYSQL_ASSOC)){
        echo $row["timeLevelID"];
    }

This creates the following error, and also doesn't return anything on the print_r - I assume $allRecordsData is false?

Fatal error: Uncaught Error: Call to a member function fetch_array() on boolean

In my second attempt I tried catching all the data in a new array. The new array would count the amount of levels each level has, After which I sort the array and output it's data.

require_once "dbConnect.php";
    $allRecordsDataSQL="SELECT timeLevelID FROM recordData";
    $allRecordsData = $conn->query($allRecordsDataSQL);
    
    
    $arrayCounter = array_fill(1, $allRecordsData->num_rows, 0);
    while($row = $allRecordsData->fetch_array(MYSQLI_ASSOC)){
         $arrayCounter[$row["timeLevelID"]]++; 
    }
    rsort($arrayCounter);
    foreach($arrayCounter as $key => $val){
        echo "<br>";
        echo "$key = $val\n";
    }

The second attempt does work PHP wise, but the output is the following, and I've got no clue what to do with this:

0 = 4
1 = 3
2 = 3
3 = 3 ..etc..

I assume my first attempt has a silly mistake but I just can't seem to spot it (I'm new to MySQL & PHP, sorry). Nevertheless, I do think the first attempt is the most efficient so I'd like to solve my issue this way.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Ruben Maatman
  • 1
  • A [quick demo](http://sqlfiddle.com/#!9/83ae3a/2) shows your first GROUP BY was correct, just a missing comma – Chris Haas Dec 02 '21 at 19:10
  • 1
    I generally recommend that everyone turn on [MySQL error reporting](https://stackoverflow.com/a/22662582/231316), at least while developing. – Chris Haas Dec 02 '21 at 19:11

1 Answers1

1

You have not included the source code of your dbConnect.php file. It appears to be suppressing the errors which is why you did not get an exception thrown for the error in your SQL query. There's a comma missing after timeLevelID in the SELECT list -

require_once "dbConnect.php";

$allRecordsDataSQL="SELECT timeLevelID, COUNT(timeLevelID) AS timeLevelIDFrequency FROM recordData GROUP BY timeLevelID ORDER BY timeLevelIDFrequency DESC";
$allRecordsData = $conn->query($allRecordsDataSQL);

Using var_dump instead of just print_r will often tell you more (boolean false perhaps) -

var_dump($allRecordsData);
while($row=$allRecordsData->fetch_array(MYSQL_ASSOC)){
    echo $row["timeLevelID"];
}
user1191247
  • 10,808
  • 2
  • 22
  • 32