Let's Get Started with an Advanced PHP/MySQL Table Page!

  1. Open Notepad++
  2. Choose: File -> New
  3. Copy the PHP Table Template (PHP) code (below)
  4. Paste that code into your new file
  5. Change the put-title-of-this-page-here
  6. Change each of the <th> lines to add these lines:
    <th>
    <a href="$PHP_SELF?orderBy=dish_name">
    <i class="material-icons">arrow_drop_up</i></a>
    Dish
    <a href="$PHP_SELF?orderBy=dish_name DESC">
    <i class="material-icons">arrow_drop_down</i></a>
    </th>
  7. For the first column add these lines between the <td> and </td> tags:
    $table .= " <a href=\"mysqlform2.php?action=mod&id=$id\">";
    $table .= " <i class=\"material-icons\">create</i></a>";
    $table .= " <a href=\"mysqlform2.php?action=del&id=$id\">";
    $table .= " <i class=\"material-icons\">delete_sweep</i></a>";
  8. Add these lines to add an anchor tag for adding a record:
    $table .= "<p><a href=\"mysqlform2.php?action=add\">";
    $table .= " <i class=\"material-icons\">add_box</i></a> new record</a>";
  9. Click: File -> Save As
  10. Name your file: /public_html/table.php
  11. Change the Save as type: to: PHP Hypertext Preprocessor (*.php)
  12. Click: Save
  13. Visit your page (http://bscacad3.buffalostate.edu/~your-BSC-username/table.php) in your favorite web browser
  14. Make any changes
  15. Save your changes

PHP Table Template (PHP)

<!doctype html>
<html>
  <head>
    <title>put-title-of-this-page-here</title>
    <!-- make the Google Icon Fonts available for your code -->
    <link rel="stylesheet" href="https://fonts.googleapis.com/icon?family=Material+Icons">
  </head>
  <body>
  <h1>My Dish Items Advanced Table</h1>
  <?php
  $debug = 0;
  $PHP_SELF = $_SERVER['PHP_SELF'];
  $dbUsername = "root";
  $dbPassword = "";
  $dbDatabase = "wnylrc";
  $dbServer   = "localhost";
  // Create a new server connection
  $dbConn = new mysqli($dbServer, $dbUsername, $dbPassword, $dbDatabase);
  if (isset($_GET['orderBy'])) {
    $orderBy = " ORDER BY " . $_GET['orderBy'];
  } else {
    $orderBy = "";
  }
  // Create the query
  $query  = "SELECT dish_id, dish_name, price, is_spicy ";
  $query .= "FROM dishes ";
  $query .= $orderBy;
  if ($debug) { echo("$query<br/>"); }
  // Send the query to MySQL
  $result = $dbConn->query($query,MYSQLI_STORE_RESULT);
  $row_cnt = $result->num_rows;
  printf("<h2>My Dish Table Has %d Rows!</b><br>\n", $row_cnt);
  $table = <<< _END
     <table>
       <thead>
         <tr style="background-color: #F0F; color: #FFF; padding: 4px; 
         text-align: center; vertical-align: bottom;">
           <th>
             Action
             </th>
           <th>             
             <!-- add anchor tags and icons for sorting up here -->
             Dish
             <!-- add anchor tags and icons for sorting down here -->
           </th>
           <th>            
             <!-- add anchor tags and icons for sorting up here -->
             Price            
             <!-- add anchor tags and icons for sorting down here -->
           </th>
           <th>            
             <!-- add anchor tags and icons for sorting up here -->
             Spicy            
             <!-- add anchor tags and icons for sorting down here -->
           </th>
         </tr>
       </thead>
       <tbody>
_END;
  // Iterate through the result set
  $cnt = 0;
  while (list($id, $name, $price, $isSpicy) = $result->fetch_row()) {
    $price = "$" . number_format($price, 2);
    if ($cnt++ % 2 == 0) { 
      $table .= "    <tr style='background-color: #CCC;'>\n";
    } else { 
      $table .= "    <tr  style='background-color: #DDD; padding: 4px; text-align: center;'>\n"; 
    }
    $table .= "      <td style='padding: 4px; text-align: left;'>";            
    // add anchor tags and icons for modifying and deleting the record here
    $table .= "      </td>\n";
    $table .= "      <td style='padding: 4px; text-align: left;'>";
    $table .= "        $name</td>\n";
    $table .= "      <td style='padding: 4px; text-align: right;'>";
    $table .= "        $price</td>\n";
    $table .= "      <td style='padding: 4px; text-align: center;'>";
    if ($isSpicy == 0) {
      $table .= "Yes";
    } else {
      $table .= "No";
    }
    $table .= "      </td>\n";
    $table .= "    </tr>\n";
  }
  $table .= "  </tbody>\n</table>\n";           
  // add anchor tag and icon for adding a record here
  echo($table);
  $dbConn->close();
?>
  </body>
</html>

Output