You can write a select query in Magento 2 to retrieve records from the table.
The select query can be written with best practices to avoid security breaches.
For 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 directly use the above string as a 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 { private ResourceConnection $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]; return $connection->fetchAll($select, $bind); } }
Above Query [‘*’] indicates fetching all the fields of the table. You can pass a specific field by comma-separated also if you want.
You need to pass each where condition separately in the query with the 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 ) )