Write a fetchAssoc mysql query in Magento 2.

Write a Mysql fetchAssoc() query using Magento standard way for Fetches all the SQL result rows as an associative array as an output.

You can write direct SQL query fetchAssoc() without worrying about Model operation using below code snippet.

Return Type: fetchAssoc() always return as an array with the first column is the key and the entire row array is the value.

Base Definition of function:

    /**
     * Fetches all SQL result rows as an associative array.
     *
     * @param string|\Magento\Framework\DB\Select $sql An SQL SELECT statement.
     * @param mixed $bind Data to bind into SELECT placeholders.
     * @return array
     */
    public function fetchAssoc($sql, $bind = []);

Let’s we are writing a query from sales_order table to accomplish fetchAssoc() query operation.

<?php
namespace Path\To\Class;

use Magento\Framework\App\ResourceConnection;

class fetchAssoc {

    const ORDER_TABLE = 'sales_order';

    /**
     * @var ResourceConnection
     */
    private $resourceConnection;

    public function __construct(
       ResourceConnection $resourceConnection
    ) {
       $this->resourceConnection = $resourceConnection;
    }

    /**
    * fetchAssoc Sql Query
    *
    * @return string[]
    */
    public function fetchAssocQuery()
    {
      $connection  = $this->resourceConnection->getConnection();
      $tableName = $connection->getTableName(self::ORDER_TABLE);

      $query = $connection->select()
        ->from($tableName,['entity_id','status','grand_total'])
        ->where('status = ?', 'pending');

      $fetchData = $connection->fetchAssoc($query);
      return $fetchData;
    }
}

You need to write a custom SQL select query and add that query into fetchAssoc() function same as above fetchAssocQuery() method.

Output:
Here in output, you can see key value is always the first-row value of the table. like 1, 10 and 14 are the entity_id of result and its a first key of sales_order table.

This is the difference between fetchAssoc() and fetchAll().
fetchAll shows the result as a key is 0 based value while fetchAssoc() shows the original key value of the first row in a table.

Array
(
    [1] => Array
        (
            [entity_id] => 1
            [status] => pending
            [grand_total] => 334.9900
        )

    [10] => Array
        (
            [entity_id] => 10
            [status] => pending
            [grand_total] => 69.0000
        )

    [14] => Array
        (
            [entity_id] => 14
            [status] => pending
            [grand_total] => 280.0000
        )

    ...
)

 

Check for other Direct SQL Query in Magento 2