Skip to content

Student Listing - Main Page

Start out with a Bootstrap template

I like to use the popular Bootstrap CSS template because it's responsive and allows me to focus mainly on the code while also creating a pretty decent looking web application. For more info on Bootstrap see https://getbootstrap.com

Let's create an index.php script using a Bootstrap template.

index.php - Student Listing: using Bootstrap Template

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
<html>
  <head>
    <link rel="stylesheet"
          href="https://stackpath.bootstrapcdn.com/bootstrap/4.2.1/css/bootstrap.min.css"
          integrity="sha384-GJzZqFGwb1QTTN6wy59ffF1BuGJpLSa9DkKMp0DgiMDm4iYMj70gZWKYbI706tWS"
          crossorigin="anonymous">
    <title>Student Listing</title>
  </head>
  <body>
    <script src="https://code.jquery.com/jquery-3.3.1.slim.min.js"
            integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo"
            crossorigin="anonymous"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.6/umd/popper.min.js"
            integrity="sha384-wHAiFfRlMFy6i5SRaxvfOCifBUQy1xHdJ/yoi7FRNXMRBu5WHdZYu1hA6ZOblgut"
            crossorigin="anonymous"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.2.1/js/bootstrap.min.js"
            integrity="sha384-B0UglyR+jN6CkvvICOB2joaf5I4l3gm9GU6Hc1og6Ls7i6U/mkkaduKaBhlAXv9k"
            crossorigin="anonymous"></script>
  </body>
</html>

Create a Separate Script for Storing Database Credentials

Create a separate file called dbconnection.php to hold our database credentials.

dbconnection.php - Student Listing: Database Credentials

1
2
3
4
5
6
7
8
<?php
    /* dbconnection.php */

    // Database connection constants
    define('DB_HOST', 'localhost');
    define('DB_USER', 'student');
    define('DB_PASSWORD', 'student');
    define('DB_NAME', 'Student');

Query the Database for All Students

Switching back to index.php, let's put everything inside what Bootstrap calls a card and add a header called Students within the card.

index.php - Student Listing: using Bootstrap Card to Display Content

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<html>
  <head>
    <link rel="stylesheet"
          href="https://stackpath.bootstrapcdn.com/bootstrap/4.2.1/css/bootstrap.min.css"
          integrity="sha384-GJzZqFGwb1QTTN6wy59ffF1BuGJpLSa9DkKMp0DgiMDm4iYMj70gZWKYbI706tWS"
          crossorigin="anonymous">
    <title>Student Listing</title>
  </head>
  <body>
    <div class="card">
      <div class="card-body">
        <h1>Students</h1>
      </div>
    </div>
    <script src="https://code.jquery.com/jquery-3.3.1.slim.min.js"
            integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo"
            crossorigin="anonymous"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.6/umd/popper.min.js"
            integrity="sha384-wHAiFfRlMFy6i5SRaxvfOCifBUQy1xHdJ/yoi7FRNXMRBu5WHdZYu1hA6ZOblgut"
            crossorigin="anonymous"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.2.1/js/bootstrap.min.js"
            integrity="sha384-B0UglyR+jN6CkvvICOB2joaf5I4l3gm9GU6Hc1og6Ls7i6U/mkkaduKaBhlAXv9k"
            crossorigin="anonymous"></script>
  </body>
</html>

Now let's add code for querying the database for all of our students:

index.php - Student Listing: Querying for all Students

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
  ...
  <body>
    <div class="card">
      <div class="card-body">
        <h1>Students</h1>

        <?php
            require_once('dbconnection.php');

            $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
                    or trigger_error('Error connecting to MySQL server for'
                    .  DB_NAME, E_USER_ERROR);

            $query = "SELECT id, first_name, last_name "
                    . "FROM studentListing ORDER BY last_name, first_name";

            $result = mysqli_query($dbc, $query)
                    or trigger_error('Error querying database studentListing', 
                    E_USER_ERROR);
      </div>
    </div>
    ...
  </body>
  ...

Depending upon the results, we'll either display a table listing all of the students' first and last name with a link to a studentdetails.php page, or that no students were found. So let's add the following code:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
        ...
        <?php
            require_once('dbconnection.php');

            $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
                    or trigger_error('Error connecting to MySQL server for'
                    .  DB_NAME, E_USER_ERROR);

            $query = "SELECT id, first_name, last_name "
                    . "FROM studentListing ORDER BY last_name, first_name";

            $result = mysqli_query($dbc, $query)
                    or trigger_error('Error querying database studentListing', 
                    E_USER_ERROR);

            if (mysqli_num_rows($result) > 0):
        ?>
                <table class="table table-striped">
                    <thead>
                    <tr>
                        <th scope="col">Student Name</th>
                    </tr>
                    </thead>
                    <tbody>
            <?php
                while($row = mysqli_fetch_assoc($result))
                {
                    echo "<tr><td>"
                        . "<a class='nav-link' href='studentdetails.php?id="
                        . $row['id'] . "'>" . 
                        $row['first_name'] . ' ' . $row['last_name']
                        . "</a></td></tr>";
                }
            ?>
                    </tbody>
                </table>        
        <?php
            else:
        ?>
                <h3>No Students Found :-(</h3>
        <?php
            endif;           
        ?>
      </div>
    </div>
    ...
  </body>
  ...

Notice the line that echos out table rows of each student's name linking to (the yet to be written) studentdetails.php along with the id field from the query:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
<?php
    while($row = mysqli_fetch_assoc($result))
    {
        echo "<tr><td>"
                . "<a class='nav-link' href='studentdetails.php?id="
                . $row['id'] . "'>" . 
                $row['first_name'] . ' ' . $row['last_name']
                . "</a></td></tr>";
    }
?>

Let's head back over to the lecture notes to discuss the difference between HTTP GET and POST and how to use GET in this case to send query parameters.

Complete Code Listing

index.php - Student Listing: Complete Code Listing

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
<html>
  <head>
    <link rel="stylesheet"
          href="https://stackpath.bootstrapcdn.com/bootstrap/4.2.1/css/bootstrap.min.css"
          integrity="sha384-GJzZqFGwb1QTTN6wy59ffF1BuGJpLSa9DkKMp0DgiMDm4iYMj70gZWKYbI706tWS"
          crossorigin="anonymous">
    <title>Student Listing</title>
  </head>
  <body>
    <div class="card">
      <div class="card-body">
        <h1>Students</h1>

        <?php
            require_once('dbconnection.php');

            $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
                    or trigger_error('Error connecting to MySQL server for' .  DB_NAME, E_USER_ERROR);

            $query = "SELECT id, first_name, last_name FROM studentListing ORDER BY last_name, first_name";

            $result = mysqli_query($dbc, $query)
                    or trigger_error('Error querying database studentListing', E_USER_ERROR);

            if (mysqli_num_rows($result) > 0):
        ?>
                <table class="table table-striped">
                    <thead>
                    <tr>
                        <th scope="col">Student Name</th>
                    </tr>
                    </thead>
                    <tbody>
            <?php
                while($row = mysqli_fetch_assoc($result))
                {
                    echo "<tr><td>"
                            . "<a class='nav-link' href='studentdetails.php?id="
                            . $row['id'] . "'>"
                            . $row['first_name'] . ' ' . $row['last_name']
                            . "</a></td></tr>";
                }
            ?>
                    </tbody>
                </table>        
        <?php
            else:
        ?>
                <h3>No Students Found :-(</h3>
        <?php
            endif;           
        ?>
      </div>
    </div>
    <script src="https://code.jquery.com/jquery-3.3.1.slim.min.js"
            integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo"
            crossorigin="anonymous"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.6/umd/popper.min.js"
            integrity="sha384-wHAiFfRlMFy6i5SRaxvfOCifBUQy1xHdJ/yoi7FRNXMRBu5WHdZYu1hA6ZOblgut"
            crossorigin="anonymous"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.2.1/js/bootstrap.min.js"
            integrity="sha384-B0UglyR+jN6CkvvICOB2joaf5I4l3gm9GU6Hc1og6Ls7i6U/mkkaduKaBhlAXv9k"
            crossorigin="anonymous"></script>
  </body>
</html>