List the items

Now that we got some posts, we would like to display them in a list. So we need a function in our class that fetches an array of items. And of course we want the same function to work for all out classes so we put it in the Db.php. We also need a count function, to return how many items we actually have.

    // Fetch an array of items in a table as objects
    // Sample: $filter  = ' WHERE id > 50 ';
    // If you provide a subset of columns as values the query might be faster, But remember that you do not get all values in your objects
    function fetchArray($filter=' ORDER BY id ', $values=' * ') {
        $ret = array();
        $vals = '';

        if ($values != ' * ') {
            $fields = array();
            $fields = explode(',', $values);
        } else {
            $fields = $this->fetchFields();
        }
        $first = true;
        foreach ($fields as $field) {
            if (!$first) {
                $vals .= ', ';
            }
            $first = false;
            $vals .= $field->columnName;
        }
        $sql = "SELECT " . $vals . " FROM `" . $this->table . '` ' . $filter;
        #print $sql.'<br>';
        $res = Db::query($sql);
        while($row = Db::fetch_assoc($res)) {
            $className = $this->className;
            $obj = new $this->className;
            $obj->fetchObject($row, $values);
            $ret[] = $obj;
        }
        return $ret;
    }
    
    function fetchCount($filter='') {
        $sql = "SELECT count(id) as cnt FROM `" . $this->table . '` ' . $filter;
        $res = Db::query($sql);
        while($row = Db::fetch_assoc($res)) {
            return $row['cnt'];
        }
        return 0;
    }

With our new function we can now create a new view in views/posts and call it items.php

<div>
  <?php $list = $obj->fetchArray(); ?>
  <?php $count = $obj->fetchCount(); ?>
    <h1>Posts</h1>
    <?php echo $count; ?> posts found<br>
    <table class="list">
        <tr><th>Title</th><th>Posted</th><th>Tags</th></tr>
        <?php foreach($list as $item) { ?>
        <tr>
            <td><?php echo $item->title; ?></td>
            <td><?php echo $item->posted; ?></td>
            <td><?php echo $item->tags; ?></td>
        </tr>
        <?php } ?>
    </table>
</div>

And since the list looks a bit cramped, we add some styling to the table.list structure as follows, in the main.css

table.list th, table.list td {
  padding: 10px;
}
table.list th {
  background: #a73;
  color: #fff;
  font-weight: normal;
  text-align: left;
  border: 1px solid #962;
}
table.list td {
  border: 1px solid #a73;
}
table.list tr:nth-child(odd) td {
  background: #ffe;
}

Now it looks more like a real list. And we can add the link it (index.php?module=post&view=items) to our menu in the main_template.php

        <div id="menu"><div>
            <a href="index.php?module=post&view=items">Posts</a>
            <a href="">Tags</a>
            <a href="index.php?module=page&view=links">Links</a>
            <a href="index.php?module=page&view=about">About</a>
        </div></div>

But since we also want to be able to click the posts to read the content we need to add a link in the list as this index.php?module=post&view=item&id=[post_number]. So now the list file looks like this:

<div>
  <?php $list = $obj->fetchArray(); ?>
  <?php $count = $obj->fetchCount(); ?>
    <h1>Post list</h1>
    <?php echo $count; ?> posts found<br>
    <table class="list">
        <tr><th>Title</th><th>Posted</th><th>Tags</th></tr>
        <?php foreach($list as $item) { ?>
        <tr>
            <td><a href="index.php?module=post&view=item&id=<?php echo $item->id; ?>"><?php echo $item->title; ?></a></td>
            <td><?php echo $item->posted; ?></td>
            <td><?php echo $item->tags; ?></td>
        </tr>
        <?php } ?>
    </table>
</div>

2015-09-24 01:02:00 - framework, php, database, software

Follow using RSS