Proper way to select data from Magento2 Databases

Question

I have created a PHP function to display data from database,
and i'm triyng to include it in my megento2 block function,

here is my code for Select data

$dates = date("Y-m-d");
$phone = $_POST["phone"];

 $sql = "SELECT * FROM otpp WHERE phone = '$phone' AND dates = '$dates'";

    $result = mysqli_query($conn, $sql);
    $count = mysqli_num_rows($result);
    // if exists ?
    if ($count > 0) {

        if ($result->num_rows > 0) {
            $row = $result->fetch_assoc();
            $otppass = $row["otp"];
            echo $otppass;
        } else {
            echo "0 results";
   }

Can I Proceed with this way to get datas from database or any other ways use datas from databases?

Magento2 has a well built orm. Y use raw sql queries? – Rohit Rai Oct 24 '17 at 22:11 — Rohit Rai, Oct 24 '17 at 22:11

score 8 · Accepted Answer · answered Apr 17 '18 at 07:58

Magento2 has an object-oriented way to utilize data from Database . That models and collections

Step 1 create a model

<?php
namespace <CompanyName>\<ModuleName>\Model;

use Magento\Framework\Model\AbstractModel;

    class Data extends AbstractModel
    {   
        protected function _construct()
        {
            $this->_init('<CompanyName>\<ModuleName>\Model\ResourceModel\Data');
        }
    }

Step 2 Creat Resource model

<?php

namespace <CompanyName>\<ModuleName>\Model\ResourceModel;


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

class Data extends AbstractDb
{
    protected function _construct()
    {
        $this->_init('TableName', 'id'); //id is a primary key 
    }
}

Step 3 Create Collection

namespace <CompanyName>\<ModuleName>\Model\ResourceModel\Data;


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


class Collection extends AbstractCollection
{
    protected function _construct()
    {
        $this->_init(
        '<CompanyName>\<ModuleName>\Model\Data',
        '<CompanyName>\<ModuleName>\Model\ResourceModel\Data'
    );
    }
}

Step 4 then we can simply use this collection in our block file

namespace <CompanyName>\<ModuleName>\Block;


use Magento\Framework\View\Element\Template\Context;
use <CompanyName>\<ModuleName>\Model\Data;
use Magento\Framework\View\Element\Template;


class Hello extends Template
{

    public function __construct(Context $context, Data $model)
    {
                $this->model = $model;
        parent::__construct($context);

    }


    public function getDatas()
    {
        $Datas = $this->model->getCollection();
        return $Datas;
    }

}

score 0 · Answer 2 · answered Oct 24 '17 at 09:10

You can get data from database using below code

$objectManager = \Magento\Framework\App\ObjectManager::getInstance();
$resource = $objectManager->get('Magento\Framework \App\ResourceConnection');
$connection = $resource->getConnection();
$tableName = $resource->getTableName('yourtablename');
$dates = date("Y-m-d");
$phone = $_POST["phone"];
$sql = "SELECT * FROM otpp WHERE phone = '$phone' AND dates = '$dates'";
$result = $connection->fetchAll($sql); 
echo '<pre>'; print_r($result); echo '</pre>';

You can add condition for empty array using

if(!empty($result)){

}

Thanks

score 0 · Answer 3 · answered Oct 24 '17 at 09:13

//Try with below code.

$objectManager = \Magento\Framework\App\ObjectManager::getInstance(); 
$resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
$connection = $resource->getConnection();

//Select Data from table
$dates = date("Y-m-d");
$phone = $_POST["phone"];
$sql = "SELECT * FROM otpp WHERE phone = '$phone' AND dates = '$dates';
//echo $sql; //uncomment this line and check sql query.
$result = $connection->fetchAll($sql);

More details click me

Proper way to select data from Magento2 Databases

3 Answers3