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
WHEREscope
= ‘default’ ANDpath
= ‘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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | <?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.
1 2 3 4 5 6 | ->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,
1 2 3 4 5 6 7 8 9 10 11 | $select = $connection->select() ->from( ['c' => $tableName], ['*'] ) ->where( "c.path = ?", $path )->where( "c.scope = ?", $scope ); $records = $connection->fetchAll($select); |
Output
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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 ) ) |