title: Using the mySQLi avg() function. What should my query be?
body: <p>I have a question, I hope somebody can help me make a query to get the avarage rating out of database table.</p>
  
  <p>My database is currently set up like this: (I hope it's clear enough to understand)</p>
  
  <p>The database name is 'beoordelingen'</p>
  
  <p>The first table in the database is 'beoordeling' where I store the filename (file) and title (Spel) in 3 columns:</p>
  
  <pre><code>[ id | file | Spel ]
  </code></pre>
  
  <p>The second table in the database is 'comments' with 3 columns:</p>
  
  <pre><code>[ id | comment | form_id ]
  </code></pre>
  
  <p>The third table in the database is 'game_rating' with 3 columns:</p>
  
  <pre><code>[ id | form_id | rating ]
  </code></pre>
  
  <p>What I would like to do, is echo the avarage rating from table game_rating but I'm having problems understanding what I need to say in the query to achieve this.</p>
  
  <p>I created a master page where all the game titles are displayed and made into a link on clicking this link you receive an ID and continue to the detail page.
  Here is the code from the master page:</p>
  
  <pre><code>&lt;?php
      //Place all data from this mySQL query in $result.
      $result = $conn-&gt;query("SELECT * FROM beoordeling");
  
      //While a row of data exists, put that row in $data as an associative array.
      while($data = $result-&gt;fetch_assoc()) {
  
          //Echo links to all the games in the MySQL database.
          echo "&lt;a href='detail.php?id=" . $data['ID'] . "'&gt;";
  
              //Echo the games name.
              echo $data['Spel'];
  
          echo "&lt;/a&gt;";
          echo "&lt;br /&gt;";
      }
  ?&gt;
  </code></pre>
  
  <p>And this is the detail page:</p>
  
  <pre><code>&lt;!DOCTYPE html&gt;
      &lt;?php
          include("dbconnect.php");
          error_reporting(E_ALL);
  
  
          if(isset($_GET['id'])) {
              $id = (int)$_GET['id'];
  
              //Place all data out of the database, with the ID number retrieved out of the url in $result.
              $game = $conn-&gt;query("SELECT * FROM beoordeling WHERE id = '" . $id . "'");
          }
  
          //While a row of data exists, put that row in $data as an associative array.        
          while($data = $game-&gt;fetch_assoc()) {
              $gameName = $data['Spel'];
              $fileName = $data['file']; 
          }
  
          //CommentList retrieves all comments with id = x.
          $commentList = $conn-&gt;query("SELECT * FROM comments WHERE form_id = '" . $id . "'"); 
      ?&gt;
  &lt;html&gt;
      &lt;head&gt;
          &lt;meta charset="UTF-8"&gt;
          &lt;link rel="stylesheet" type="text/css" href="style.css" media="screen" /&gt;
          &lt;title&gt;&lt;?php echo $gameName; ?&gt;&lt;/title&gt;
      &lt;/head&gt;
      &lt;body&gt;
  
      &lt;?php        
              /*  
               * 
               * @ToDo: Make point system show avarage rating
               * 
               * @ToDo: Create a webmaster page where you can add games to the list
               * and automaticly create game page.
               * 
               */
  
                  //Retrieve the file name from the database and place it in the &lt;embed&gt; tags as src="...".
                  echo "&lt;embed width='800' height='512' src='{$fileName}' type='application/x-shockwave-flash'&gt;&lt;/embed&gt;&lt;br /&gt;";
  
  
  
          //Echo the form with a text box and a rating box.
          echo '&lt;div id="game_form"&gt;&lt;form method="POST"&gt;
              &lt;a&gt;Leave a comment&lt;/a&gt;&lt;br /&gt;
            &lt;input type="text" name="comments" /&gt;
            &lt;br /&gt;&lt;a&gt;Rate'.$gameName.'&lt;/a&gt;&lt;br /&gt;  
          &lt;select name="rategame"&gt;
            &lt;option value=""&gt;Select...&lt;/option&gt;
            &lt;option value="1"&gt;1&lt;/option&gt;
            &lt;option value="2"&gt;2&lt;/option&gt;
            &lt;option value="3"&gt;3&lt;/option&gt;
            &lt;option value="4"&gt;4&lt;/option&gt;
            &lt;option value="5"&gt;5&lt;/option&gt;
          &lt;/select&gt;
              &lt;input type="submit" name="submit" value="submit" /&gt;   
          &lt;/form&gt;&lt;/div&gt;';
  
              //Create a table with all the comments  
              echo "&lt;div id='table_data'&gt;&lt;table&gt;";
  
              while($cdata = $commentList-&gt;fetch_assoc()) {
                  echo "&lt;tr&gt;";
                  echo "&lt;td&gt;" . $cdata["comment"] . "&lt;/td&gt; &lt;br /&gt;&lt;br /&gt;";
                  echo "&lt;/tr&gt;";
              }
              echo "&lt;/table&gt;&lt;/div&gt;";
  
          //Submit functionality    
          if (isset($_POST['submit'])) {
  
          // Check connection
          if ($conn-&gt;connect_error) {
              die("Connection failed: " . $conn-&gt;connect_error);
          }
  
          //Check if comment is entered else set it to an empty string
          if (isset($_POST['comments'])) {
              $commentText = $conn-&gt;real_escape_string($_POST['comments']);
          } else {
              $commentText = "";
          }
  
          $rate = $_POST['rategame'];
  
          echo $rate;
  
          //If user enters either rate or comment (or both) send a query to table.
          //Else only send the query with the users input which the user has entered.
          if (($rate &gt;= 1) &amp;&amp; ($rate &lt;= 5)) {
              if ($commentText === "") {
                  $sqlrate = "INSERT INTO game_rating (rating, form_id) VALUES ('{$rate}','{$id}')";
                  $sqlcomment = ""; //Initializing to null to avoid error.
              }
              else {
                  $sqlrate = "INSERT INTO game_rating (rating, form_id) VALUES ('{$rate}','{$id}')";
                  $sqlcomment = "INSERT INTO comments (comment, form_id) VALUES ('{$commentText}','{$id}')";
              }   
          }
          else {
              if ($commentText !== "") {
              $sqlcomment = "INSERT INTO comments (comment, form_id) VALUES ('{$commentText}','{$id}')";
              $sqlrate = ""; //initializing to null to avoid error.
              }
          }
  
          //Push query to database
          if($sqlcomment) {
               $conn-&gt;query($sqlcomment); 
          }
  
          if($sqlrate) {
               $conn-&gt;query($sqlrate); 
          }
  
  
          //Close connection to free up resources.
          $conn-&gt;close();
          }
      ?&gt;      
      &lt;/body&gt;
  &lt;/html&gt;
  </code></pre>
  
  <p>So on the detail page I would like to display avarage rating for the game.
  Can anyone help me make this happen ? :D Thanks alot!!</p>