You can write a select query in Magento 2 to retrieve records from the table. The select query can be written with best practice to avoid security breaches.
Example, You are searching for records from the core_config_data table,
Row query is:
SELECT * FROM `core_config_data` WHERE `scope` = ‘default’ AND `path` = ‘general/locale/code’
You can direct use above string as query and pass it to the query() method but it’s not a secure way to write a select query. I will show you the standard Magento way to write database queries with the best secure standard Practice.
<?php namespace Jesadiya\SelectQuery\Model\ResourceModel; use Magento\Framework\App\ResourceConnection; class Data { /** * @var ResourceConnection */ private $resourceConnection; public function __construct( ResourceConnection $resourceConnection ) { $this->resourceConnection = $resourceConnection; } /** * Select query to fetch records * * @return array */ public function selectQuery() { $tableName = $this->resourceConnection->getTableName('core_config_data'); //Initiate Connection $connection = $this->resourceConnection->getConnection(); $path = 'general/locale/code'; $scope = 'default'; $select = $connection->select() ->from( ['c' => $tableName], ['*'] ) ->where( "c.path = :path" )->where( "c.scope = :scope" ); $bind = ['path'=>$path, 'scope'=>$scope]; $records = $connection->fetchAll($select, $bind); return $records; } }
Above Query [‘*’] indicates fetch all the field of the table. You can pass a specific field by comma-separated also if you want.
You need to pass each where conditions as separately in the query with bind parameter.
->where( "c.path = :path" )->where( "c.scope = :scope" ) $bind = ['path'=>$path, 'scope'=>$scope];
Pass $path and $scope Value to the bind array instead of directly passing the value to the given where conditions.
- An alternative of bind (Second Way)
If you don’t want to use $bind you can use an alternative way to pass the parameter in where conditions by the given way,
$select = $connection->select() ->from( ['c' => $tableName], ['*'] ) ->where( "c.path = ?", $path )->where( "c.scope = ?", $scope ); $records = $connection->fetchAll($select);
Output
Array ( [0] => Array ( [config_id] => 6 [scope] => default [scope_id] => 0 [path] => general/locale/code [value] => en_US [updated_at] => 2020-02-10 06:27:49 ) )