Creating A Real Website with php object oriented - part 3 - The Wallpapers Database and fetching Data

in this 3rd part of our series of tutorials about php programming language, now we are starting to fetch data from our database and we are getting closer to a functioning website.

Lets start by creating a class "Wallpapers" and make it extend or inherit the Database class we created in the previous tutorial, we will have the constructor of our class call its parent's constructor to make a server connection; then make a query to use the "Wallpapers" Database. like so:

    class Wallpapers extends Database {
        private $table = "wallpapers";
        private $tagtable = "tags";
        private $walltags = "wallpaperstags";
        public function __construct(){
            parent::__construct();
            $this->pdo->query("use Wallpapers");
            $this->pdo->query("set names 'utf8' ");
        }
    }

The second query is just to make sure we render the characters in our database correctly by using the UTF-8 character encoding. By now we are ready to start fetching data from our Database. We just have to think of the ways we will use this data. in our case. there are 3 ways we can use the Wallpapers Database:

• viewing a single wallpaper.
• browsing all the wallpapers
• searching for wallpapers by a keyword or a tag

We have to create 3 functions/methods in our Wallpapers class to do just that. like so:

    public function one($id){
        $query = "select * from $this->table where id = ? ";
        $sql = $this->pdo->prepare($query);
        $sql->execute([$id]);
        return $sql->fetch();
    }
    public function all($first,$nrows){
        $query = "select * from $this->table limit :first, :nrows";
        $sql = $this->pdo->prepare($query);
        $sql->bindValue(':first',(int) $first,PDO::PARAM_INT);
        $sql->bindValue(':nrows',(int) $nrows,PDO::PARAM_INT);
        $sql->execute();
        return $sql->fetchAll();
    }
    public function search($tag,$first,$nrows){
        $query = " select t1.* , ( (1.3 * (MATCH(t3.tagname) AGAINST ( :tag IN BOOLEAN MODE))) + (0.6 * (MATCH(t3.text) AGAINST ( :tag IN BOOLEAN MODE)))) AS relevance
            from $this->table t1
            join $this->walltags t2 on t1.id = t2.id
            join $this->tagtable t3 on t3.id = t2.tagid
            where  MATCH(t3.tagname,t3.text) AGAINST ( :tag IN BOOLEAN MODE)
            order by relevance
            limit :first, :nrows ";
        $sql = $this->pdo->prepare($query);
        $sql->bindValue(':first',(int) $first,PDO::PARAM_INT);
        $sql->bindValue(':nrows',(int) $nrows,PDO::PARAM_INT);
        $sql->bindValue(':tag',(string) $tag,PDO::PARAM_STR);
        $sql->execute();
        return $sql->fetchAll();
    }

the one() and all() methods are straightforward, we are either requesting one wallpaper by its id, or requesting all wallpapers and limiting the number of rows we select, as we intend to browse the wallpapers in pages.
But in the search() function we query the wallpapers by keywords defined as tags in our database. we also order the results by their relevance to the search keyword while giving a priority to the tagname over the tag text.

to make it easy for you to follow these tutorials, I offer you the 3 tables of the Wallpapers database. the tags table may prove valuable to you in other projects as it contains thousands of keywords and their related text:

The video as usual: