List All Project

Let’s create a file called project_list.php at the root of our project, and add some basic HTML code into it. We’ll add our PHP code in the body tag.

First things first, let’s add the connection to the database and the try/catch block:

<?php
 try {
        require "./config.php";

        $connection = new PDO($dsn, $username, $password, $options);
       
    } catch (PDOException $err) {
        echo $err->getMessage();
    }
    ?>

Read from the database

Inside the try block, under the $connection variable, we’ll add our SELECT SQL query:

$sql =  'SELECT * FROM projects ORDER BY title';
$statement = $connection->query($sql);

We’ll execute the SQL query with the PDO::query and place the result into a $statement variable.

We’ll also count the number of records: $projectCount = $statement->rowCount();

Note: We can also use the fetchAll() method to return all the entrees of our query:

$result = $statement->fetchAll();

or, if we want to chain all the entrees in the same line:

$statement = $connection->query($sql)->fetchAll();

However, remember to be mindful of memory consumption.

Now, we have a run-down of the whole process that is used to retrieve all the projects from the database. The next step is to print the result on the screen.

Outside the try/catch block, we’ll create a simple container, for instance, a div tag.

We’ll add the following as the title of the page: <h1>Project list(<?php echo $projectCount ?>)</h1>

Then, we’ll create a foreach loop to iterate over the database result:

<?php foreach ($statement as $row) : ?>
   <ul><li><?php echo $row["title"] ?> </li></ul>
<?php endforeach; ?>

We’ll print each title on the screen, using the li tag.

Notes:

• In case you feel confused about the syntax used for the foreach loop, consider reading through the document linked here.
• If you are already familiar with the while loop and want to use it, below is how we will do it in the course:

while ($result = $statement->fetch) { ?>
<ul><li><?php echo $row["title"] ?> </li></ul>
<?php } ?>

In case a project is created, we’ll add an if statement to display a message:

 if ( $projectCount == 0 ) {
        echo "<p>You have not yet added any project </p>";
        echo "<p><a href='#'>Add project</a></p>";
 }

Run the code

The full code inside the body tag will look like this:

<?php
    try {
        require "./config.php";

        $connection = new PDO($dsn, $username, $password, $options);

        $sql =  'SELECT * FROM projects ORDER BY title';

        $statement = $connection->query($sql);
        // $result = $statement->fetchAll();
        $projectCount = $statement->rowCount();
    } catch (PDOException $err) {
        echo $sql . "<br>" . $err->getMessage();
    }
    ?>
    <div>
        <h1>Project list (<?php echo $projectCount ?>)</h1>
        <!-- If there's not yet data -->
        <?php
        if ($projectCount <= 0) {
            echo "<p>You have not yet added any project </p>";
            echo "<p><a href='#'>Add project</a></p>";
        }

        ?>
          <?php foreach ($statement as $row) : ?>
      <ul><li><?php echo $row["title"] ?> </li></ul>
<?php endforeach; ?>
    </div>

    <p><a href="./">Home</a></p>

Now, we can run the code and get this: