In this post, we will see how we can work with custom tables within our own custom modules.

Let us first create a table with following fields:

id           ,  integer       ,   auto-increment  ,  primary key
nameĀ         ,  varchar(50)
friend_type  ,  varchar(50) 
created_at   ,  date
status       ,  varchar(20)

We need to create a module. If you have not created a module yet, click here to create one.

We need to create following directory structure:

<Mymodule>
     <Test>
          <etc>
              <frontend>
                     routes.xml
              module.xml
          <Model>
              <Resource>
                     <Friend>
                           Collection.php
                     Friend.php
              Friend.php
     registration.php

Let’s add code to each file one by one.

module.xml ( /Mymodule/Test/etc/module.xml )

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../../../../lib/internal/Magento/Framework/Module/etc/module.xsd">
    <module name="Mymodule_Test" setup_version="0.0.1" active="true"/>
</config>

registration.php ( /Mymodule/Test/registration.php )

<?php
use \Magento\Framework\Component\ComponentRegistrar;
ComponentRegistrar::register(ComponentRegistrar::MODULE,'Mymodule_Test',__DIR__);

Friend.php ( /Mymodule/Test/Model/Friend.php )

<?php
namespace Mymodule\Test\Model;

use Magento\Framework\Model\AbstractModel;

class Friend extends AbstractModel
{
    /**
     * Define resource model
     */
    protected function _construct()
    {
        $this->_init('Mymodule\Test\Model\Resource\Friend');
    }
}

Friend.php ( /Mymodule/Test/Model/Resource/Friend.php )

<?php
namespace Mymodule\Test\Model\Resource;

use Magento\Framework\Model\ResourceModel\Db\AbstractDb;

class Friend extends AbstractDb
{
    /**
     * Define main table
     */
    protected function _construct()
    {
        $this->_init('friends', 'id');  // friends is name of table
    }
}

Collection.php ( /Mymodule/Test/Model/Resource/Friend/Collection.php )

<?php
namespace Mymodule\Test\Model\Resource\Friend;

use Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection;

class Collection extends AbstractCollection
{
    /**
     * Define model & resource model
     */
    protected function _construct()
    {
        $this->_init(
            'Mymodule\Test\Model\Friend',
            'Mymodule\Test\Model\Resource\Friend'
        );
    }
}

At this point, we are ready with our model. Now, we will create a controller to test our model.

Index.php ( /Mymodule/Test/Controller/Friend/Index.php )

<?php

namespace Mymodule\Test\Controller\Friend;

use Magento\Framework\App\Action\Action;
use Magento\Framework\App\Action\Context;
use Mymodule\Test\Model\FriendFactory;

class Index extends Action
{
    /**
     * @var \Mymodule\Test\Model\FriendFactory
     */
    protected $_modelFriendFactory;

    /**
     * @param Context $context
     * @param FriendFactory $modelFriendFactory
     */
    public function __construct(
        Context $context,
        FriendFactory $modelFriendFactory
    ) {
        parent::__construct($context);
        $this->_modelFriendFactory = $modelFriendFactory;
    }

    public function execute()
    {
        /**
         * When Magento get your model, it will generate a Factory class
         * for your model at var/generaton folder and we can get your
         * model by this way
         */
        $friendModel = $this->_modelFriendFactory->create();

        // Load the item with ID is 1
        $friend = $friendModel->load(1);
        if($friend){
            echo $friend->getName() . " found with Id 1";
            echo "<hr/>";
        }

        // Get friend collection
        $friendCollection = $friendModel->getCollection();
        // Load all data of collection
        foreach($friendCollection as $friend){
            echo $friend->getName() . " , " . $friend->getFriendType() . "<br/>";
        }
    }
}

routes.xml ( /Mymodule/Test/etc/frontend/routes.xml )

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../../../../../lib/internal/Magento/Framework/App/etc/routes.xsd">
    <router id="standard">
        <route id="hayo" frontName="hayo">
            <module name="Mymodule_Test" />
        </route>
    </router>
</config>

Now, clear your cache and visit the URL:

http://mag2.com.local/hayo/friend/index

You should be able to see table data.