How to write a fetchRow method of MySql query in Magento 2

In this article I will try cover How to write a fetchRow method of MySql query in Magento 2. Craft a MySQL fetchRow() query in the Magento standard approach to retrieve the first row of the SQL result as the output.

Obtaining the first row as a result from a list of a resultant array is achievable with this query.

You can execute a direct SQL query using fetchRow() without concerning yourself with Model operations. The fetchRow() function consistently returns an array based on your query conditions.

You may also like this : How to Write SQL Query in Magento 2 With Standard Way

Main Definition:

fetchRow function fetches the first row of the SQL result

Magento\Framework\DB\Adapter\AdapterInterface::fetchRow($sql, $bind = [], $fetchMode = null);

public function fetchRow($sql, $bind = [], $fetchMode = null);

Now, let’s consider fetching the first row from the customer_entity table.

<?php
/**
 * @Module         : Jrb Demomodule
 * @Package        : Jrb_Demomodule
 * @Description    : Testing custom script output
 * @Developer      : Jyotiranjan Biswal<biswal@jyotiranjan.in>
 * @Copyright      : https://www.jyotiranjan.in/
 */

namespace Jrb\DemoModule\Model;

use Magento\Framework\Model\AbstractModel;
use Magento\Framework\App\ResourceConnection;

class CustomScript extends AbstractModel
{

    protected $logger;

    /**
     * Resource
     *
     * @var ResourceConnection
     */
    protected $resourceConnection;

    /**
     * Constructor
     *
     * @param \Psr\Log\LoggerInterface $logger
     */
    public function __construct(
        \Psr\Log\LoggerInterface $logger,
        ResourceConnection $resourceConnection,
    )
    {
        $this->logger = $logger;
        $this->resourceConnection = $resourceConnection;
    }

    /**
     * Execute the custom script
     *
     * @return void
     */
    public function execute()
    {
        // get the list of record having store id 1
        $storeId = 1;
        $customerData = $this->getAllRecordsByFetchRow($storeId);
        print_r($customerData);

        $this->logger->info('customer_data',$customerData);
    }

    /**
     * @param int $id
     * 
     * @return array
     */
    public function getAllRecordsByFetchRow($id){
        $connection = $this->resourceConnection->getConnection();
        $tableName = $this->resourceConnection->getTableName('customer_entity');

        $bind = ['store_id' => $id];
        $query = $connection
            ->select()
            ->from(
                ['main_table' => $tableName],
                ['entity_id','email']
            )
            ->where('store_id = :store_id');

        return $connection->fetchRow($query, $bind);
    }
}

We are retrieving the first row from the customer_entity table with store_id number 1

The output is the first row of the SQL result represented as an array:

Array
(
    [entity_id] => 1
    [email] => roni_cost@example.com
)

You can also refer the some example from magento2 core files like Magento\Customer\Model\ResourceModel\Customer::findSessionCutOff(int $customerId): ?int

I hope this article on “How to write a fetchRow method of MySql query in Magento 2” clearly explains you. Feel free to comment if you have further insights or if I missed anything. I’ll respond with the appropriate solution.

Keep liking and sharing!