Gathering articles from an articles database

Question

I'm new to PHP and OOP and am building methods in order to gather articles from an article database. My method will receive three parameters: $limit, $category, $order. I think this is self-explanatory, however, I have a few questions:

• What happens if one of the arguments is missing? Will the method return false? How can I avoid this?
• I don't know how to gather attributes from two related tables, that is, each article belongs to a category.
• Should I put each of the methods I am building inside a try-catch clause? Will this be beneficial for me or will my if-elses do the trick?
• Am I doing parameter binding correctly, or should I specify the third argument which I think is the type I should be expecting the variable to be? What if the var introduced is different from the expected type?

Here are my related tables. I think I set up their relationship correctly.

Articles table:

CREATE TABLE articles
(
  id                     SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,  -- Each article unique identifier:
  categoryId             SMALLINT UNSIGNED NOT NULL,                 -- The artcle category id:
  image                  VARCHAR(255) NULL DEFAULT 'images/img_default_article.jpg',                      -- Main image of the article:
  title                  TEXT NOT NULL,                              -- Full title of the article:
  summary                TEXT NOT NULL,                              -- The summary of the article:
  content                MEDIUMTEXT NOT NULL,                        -- The HTML content of the article:
  created                DATE NOT NULL,                              -- Day and Month the article entry was created
  modified               TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, --Timestamp the article was last edited
  visible                INT(11) NOT NULL,                           -- 1 - Visible, 0 - Not visible:


  PRIMARY KEY     (id)
  FOREIGN KEY     (categoryId) REFERENCES categories(id) ON UPDATE CASCADE ON DELETE CASCADE
)

ENGINE=innoDB DEFAULT CHARSET=utf8 COMMENT='all the articles available';

Categories table:

CREATE TABLE categories
(
  id              SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,         -- Category unique identifier
  image           TEXT NOT NULL DEFAULT "images/img_default_category.jpg",    -- Image of the article
  name            VARCHAR(255) NOT NULL,                             -- Name of the category:
  description     TEXT NOT NULL,                                     -- A short description of the category:

  PRIMARY KEY     (id)
)

ENGINE=innoDB DEFAULT CHARSET=utf8 COMMENT="Categories for all th articles";

PHP code:

<?php
class Article extends BaseEntity{
    private $id, $categoryid, $author, $image, $title, $summary, $content, $created, $modified, $visible;

    public function __construct($adapter) {
        $table="articles";
        parent::__construct($table, $adapter);
    }



    public function getArticlesAdv($limit, $category, $order){
        $sql = "SELECT *
                FROM articles
                ORDER BY created DESC";

        $stmt = $this->db->prepare($sql);
        $stmt->execute();
        if($stmt->execute()){

            $numrows = $stmt->rowCount();
            if(numrows>0){

                while ($row = $query->fetchAll(PDO::FETCH_OBJ))
                {
                $resultSet[]=$row;
                }
                return $resultSet;

            }
            else
            {   
            return false;
            }   
        }

        else
        {
        return false;   
        }
    }

    public function getAllArticles(){
        $sql = "SELECT *
                FROM articles
                ORDER BY created DESC";

        $stmt = $this->db->prepare($sql);

        $stmt->execute();

        $numrows = $stmt->rowCount();

        if(numrows>0){

        while ($row = $query->fetchAll(PDO::FETCH_OBJ)) {
           $resultSet[]=$row;
        }

        return $resultSet;

        }
        else{
            return false;
        }


    }

?>

Answer 1

What happens if one of the arguments is missing? Will the method return false? How can I avoid this?

You must add the default value of the argument. This makes it easier and clearer to use.

public function getArticlesAdv($category, $limit = 15, $order = 'desc')

You might also consider the positions of the arguments. E.g. what is least important?

This would allow you to call getArticlesAdv($id); or getArticlesAdv($id, 100) without a hitch.

I don't know how to gather attributes from two related tables, that is, each article belongs to a category.

Correct me if I'm wrong but isn't that database-related query? You might look into JOIN statements.

Should I put each of the methods I am building inside a try-catch clause? Will this be beneficial for me or will my if-elses do the trick?

I really do believe you need to abstract your getArticlesAdv() function way more. It is doing way too much. Either using try-catch or if-else, you'll end up with a large function containing lots of conditionals. This is very hard to maintain afterwards.

I'll explain about this point at the end of my answer.

Am I doing parameter binding correctly, or should I specify the third argument which I think is the type I should be expecting the variable to be? What if the var introduced is different from the expected type?

I don't see any parameter binding. The $sql statement is both the same for getArticlesAdv() and getAllArticles(). Though yes, it's risky and like the always say, never trust the user's input.

---

What you should essentially do is have a 'query builder' concept going on. Google about it and try to implement it in a way that you can do:

$articles = Article::orderBy('desc')->limit(15)->get();

That's called method chaining and helps quite a lot.

Here is an attempt:

<?php

    class Article extends BaseEntity
    {

        private $id, 
                $categoryid, 
                $author, 
                $image, 
                $title, 
                $summary, 
                $content, 
                $created, 
                $modified, 
                $visible;

        protected $limit;

        protected $orderByColumn;

        protected $direction;

        public function __construct($adapter)
        {
            $table = "articles";

            parent::__construct($table, $adapter);
        }

        public function limit(int $limit)
        {
             $this->limit = $limit;

             /*
              * We return $this so we can method-chain.
              * E.g. Article::limit(15)->get();
              *
              */

             return $this;
        }

        public function orderBy($columnName = 'created', $direction = 'desc')
        {
              $this->orderByColumn = $columnName;
              $this->direction = $direction;

              return $this;
        }

        public function get()
        {
            /**
             * TODO: Now, you can build your final db query
             * based on this class' vars $limit, $direction
             * $orderByColumn and whatever else you may
             * think is necessary.
             *
             * Finally, wrap up everything here.
             */


            $sql = "SELECT *
                    FROM " . $this->table  . "
                    ORDER BY " . $this->orderByColumn . "DESC";

            // TODO: Bind the variables to protect against SQL injection since
            // $this->orderByColumn could come from a user input.

            $stmt = $this->db->prepare($sql);
            $stmt->execute();
            if($stmt->execute()) {

                $numrows = $stmt->rowCount();
                if(numrows>0){

                    while ($row = $query->fetchAll(PDO::FETCH_OBJ))
                    {
                        $resultSet[] = $row;
                    }

                    return $resultSet;

                } else {   
                    return false;
                }   
            } else {
                return false;   
            }
        }

You can have many functions such as with('categories') that you can chain.

As a final note, I have noticed you have made the default value of the article image a link to the placeholder image. I advise against that because it's dealing with the database directly, instead, in your codebase, you should attempt to insert that default image when creating new articles instead of having the database layer to deal with it.

This way, you can change the default image name without a hassle.

That tip can be applied generally when it comes to default database values.