{"id":22268,"date":"2018-10-29T09:14:53","date_gmt":"2018-10-29T09:14:53","guid":{"rendered":"https:\/\/www.knowband.com\/blog\/?p=22268\/"},"modified":"2020-11-11T17:08:51","modified_gmt":"2018-10-29T09:15:48","slug":"run-direct-sql-queries-in-magento","status":"publish","type":"post","link":"https:\/\/www.knowband.com\/blog\/magento-tutorials\/run-direct-sql-queries-in-magento\/","title":{"rendered":"How to run Direct SQL Queries in Magento?"},"content":{"rendered":"

Today we will learn how to run\u00a0direct SQL queries in the Magento 1.x. By default Magento usages data model to access and modify the database. The data model provides good abstraction which hides the tricky queries but they also make the operation slow. Direct SQL queries come very handily whenever we need to update\/insert a large number of data. For example, you have to update the price of all the products of a manufacturer and there are 15k products in that manufacturer. Doing this via loading product model in Magento will take a lot of time.<\/p>\n

We can also use Magento resource to directly run our SQL queries without loading the whole collection.<\/p>\n

Get the core resource model –<\/h2>\n
$resource = Mage::getSingleton('core\/resource');<\/pre>\n
After this we need to create read or write connection as per our need.<\/p>\n
\/**\r\n\r\n* Retrieve the read connection\r\n\r\n*\/\r\n\r\n$readConnection = $resource->getConnection('core_read');\r\n\r\n\/**\r\n\r\n* Retrieve the write connection\r\n\r\n*\/\r\n\r\n$writeConnection = $resource->getConnection('core_write');<\/pre>\n
Fetching the correct Table name –<\/h2>\n
In case you are unaware of the prefix of the table then you can use below code to fetch the correct table name.<\/p>\n
$_tableName = $resource->getTableName('catalog_product_entity');<\/pre>\n
If the prefix is mage_<\/strong> then the table name it will return will be mage_catalog_product_entity<\/em><\/strong><\/p>\n
Reading Data –<\/h2>\n
To read data from the table use the following code<\/p>\n
$query = \u201cselect * from {$_tableName}\u201d;\r\n\r\n$query_result = $readConnection->fetchAll($query);<\/pre>\n
Here, $query_result will return the result of select query. In our case it will return the array of all the product. In case you need data of only product then use following command.<\/p>\n
$query = \u201cselect * from {$_tableName} where entity_id={$_productId}\u201d;\r\n\r\n$query_result = $readConnection->fetchAll($query);<\/pre>\n
Here $_productId <\/strong>is the id of the product whose data we need.<\/p>\n
Writing Data –<\/h2>\n
For inserting data into the table use the following code.<\/p>\n
$insert_query = \u201cINSERT INTO {$_tableName} (\u2018COL_NAME1\u2019,\u2019COL_NAME2\u2019) VALUES                                                                 (\u2018COL_VALUE1\u2019,\u2019COL_VALUE2\u2019)\u201d;\r\n\r\n$writeConnection->query($insert_query);<\/pre>\n
Updated Data –<\/h2>\n
If we have to update the type_id of a product to \u201cconfigurable\u201d then we can use following code. Here $_productId is the product id.<\/p>\n
$update_query = \u201cUPDATE {$_tableName} SET type_id=\u201dconfigurable\u201d WHERE \t\t\t\t\t\tentity_id={$_productId}\u201d;\r\n$writeConnection->query($update_query);\r\n\r\nBut Magento has provided the concept of the model for database connection as they provide abstraction and they are more secure that the direct SQL queries.<\/pre>\n","protected":false},"excerpt":{"rendered":"
Today we will learn how to run\u00a0direct SQL queries in the Magento 1.x. By default Magento usages data model to access and modify the database. The data model provides good abstraction which hides the tricky queries but they also make the operation slow. Direct SQL queries come very handily whenever we need to update\/insert a […]<\/p>\n","protected":false},"author":14,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_kbui_enabled":"","footnotes":""},"categories":[6],"tags":[7483,1555,1573,7484,7485],"class_list":["post-22268","post","type-post","status-publish","format-standard","hentry","category-magento-tutorials","tag-directsqlqueries","tag-magento-extension","tag-magento-module","tag-run-sql-queries-in-magento","tag-sql-queries-in-magento"],"acf":[],"yoast_head":"\nHow to run Direct SQL Queries in Magento? - Knowband Blog<\/title>\n<meta name=\"description\" content=\"Want to know how to run direct sql queries in Magento extension? Find out the complete process in the write-up with the required codes.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.knowband.com\/blog\/magento-tutorials\/run-direct-sql-queries-in-magento\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to run Direct SQL Queries in Magento? - Knowband Blog\" \/>\n<meta property=\"og:description\" content=\"Want to know how to run direct sql queries in Magento extension? Find out the complete process in the write-up with the required codes.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.knowband.com\/blog\/magento-tutorials\/run-direct-sql-queries-in-magento\/\" \/>\n<meta property=\"og:site_name\" content=\"Knowband Blog | Ecommerce Modules\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/Knowband\" \/>\n<meta property=\"article:published_time\" content=\"2018-10-29T09:14:53+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-10-29T09:15:48+00:00\" \/>\n<meta name=\"author\" content=\"Prashant Kumar\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@KnowbandPlugin\" \/>\n<meta name=\"twitter:site\" content=\"@KnowbandPlugin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Prashant Kumar\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.knowband.com\\\/blog\\\/magento-tutorials\\\/run-direct-sql-queries-in-magento\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.knowband.com\\\/blog\\\/magento-tutorials\\\/run-direct-sql-queries-in-magento\\\/\"},\"author\":{\"name\":\"Prashant Kumar\",\"@id\":\"https:\\\/\\\/www.knowband.com\\\/blog\\\/fr\\\/#\\\/schema\\\/person\\\/04499d0d3f542a4f42182b91f772986c\"},\"headline\":\"How to run Direct SQL Queries in Magento?\",\"datePublished\":\"2018-10-29T09:14:53+00:00\",\"dateModified\":\"2018-10-29T09:15:48+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.knowband.com\\\/blog\\\/magento-tutorials\\\/run-direct-sql-queries-in-magento\\\/\"},\"wordCount\":290,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/www.knowband.com\\\/blog\\\/fr\\\/#organization\"},\"keywords\":[\"directsqlqueries\",\"Magento extension\",\"Magento module\",\"run sql queries in magento\",\"sql queries in magento\"],\"articleSection\":[\"Magento Tutorials\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.knowband.com\\\/blog\\\/magento-tutorials\\\/run-direct-sql-queries-in-magento\\\/\",\"url\":\"https:\\\/\\\/www.knowband.com\\\/blog\\\/magento-tutorials\\\/run-direct-sql-queries-in-magento\\\/\",\"name\":\"How to run Direct SQL Queries in Magento? - Knowband Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.knowband.com\\\/blog\\\/fr\\\/#website\"},\"datePublished\":\"2018-10-29T09:14:53+00:00\",\"dateModified\":\"2018-10-29T09:15:48+00:00\",\"description\":\"Want to know how to run direct sql queries in Magento extension? Find out the complete process in the write-up with the required codes.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.knowband.com\\\/blog\\\/magento-tutorials\\\/run-direct-sql-queries-in-magento\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.knowband.com\\\/blog\\\/magento-tutorials\\\/run-direct-sql-queries-in-magento\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.knowband.com\\\/blog\\\/magento-tutorials\\\/run-direct-sql-queries-in-magento\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.knowband.com\\\/blog\\\/fr\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"A cobran\u00e7a de fabricantes de smartphones na Europa levar\u00e1 o Google a quest\u00f5es antimonop\u00f3lio?\",\"item\":\"https:\\\/\\\/www.knowband.com\\\/blog\\\/pt-br\\\/noticia\\\/a-cobran%c3%a7a-de-fabricantes-de-smartphones-na-europa-levar%c3%a1-o-google-a-quest%c3%b5es-antimonop%c3%b3lio\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Como criar a API do servi\u00e7o Web do m\u00f3dulo Prestashop?\",\"item\":\"https:\\\/\\\/www.knowband.com\\\/blog\\\/pt-br\\\/prestashop-tutoriais\\\/como-criar-a-api-prestashop-module-web-service\\\/\"},{\"@type\":\"ListItem\",\"position\":4,\"name\":\"How to run Direct SQL Queries in Magento?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.knowband.com\\\/blog\\\/fr\\\/#website\",\"url\":\"https:\\\/\\\/www.knowband.com\\\/blog\\\/fr\\\/\",\"name\":\"Knowband Blog | Ecommerce Modules\",\"description\":\"Magento, Prestashop, Opencart, Shopify\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.knowband.com\\\/blog\\\/fr\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.knowband.com\\\/blog\\\/fr\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/www.knowband.com\\\/blog\\\/fr\\\/#organization\",\"name\":\"Knowband Blog | Ecommerce Modules\",\"url\":\"https:\\\/\\\/www.knowband.com\\\/blog\\\/fr\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.knowband.com\\\/blog\\\/fr\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/www.knowband.com\\\/blog\\\/wp-content\\\/uploads\\\/2016\\\/09\\\/cropped-knowband_logo-v2.png\",\"contentUrl\":\"https:\\\/\\\/www.knowband.com\\\/blog\\\/wp-content\\\/uploads\\\/2016\\\/09\\\/cropped-knowband_logo-v2.png\",\"width\":153,\"height\":61,\"caption\":\"Knowband Blog | Ecommerce Modules\"},\"image\":{\"@id\":\"https:\\\/\\\/www.knowband.com\\\/blog\\\/fr\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/www.facebook.com\\\/Knowband\",\"https:\\\/\\\/x.com\\\/KnowbandPlugin\"]},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.knowband.com\\\/blog\\\/fr\\\/#\\\/schema\\\/person\\\/04499d0d3f542a4f42182b91f772986c\",\"name\":\"Prashant Kumar\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.knowband.com\\\/blog\\\/wp-content\\\/uploads\\\/2018\\\/10\\\/10256082_607442326014813_1731639576902538685_o-150x150.jpg\",\"url\":\"https:\\\/\\\/www.knowband.com\\\/blog\\\/wp-content\\\/uploads\\\/2018\\\/10\\\/10256082_607442326014813_1731639576902538685_o-150x150.jpg\",\"contentUrl\":\"https:\\\/\\\/www.knowband.com\\\/blog\\\/wp-content\\\/uploads\\\/2018\\\/10\\\/10256082_607442326014813_1731639576902538685_o-150x150.jpg\",\"caption\":\"Prashant Kumar\"},\"description\":\"Prashant is a passionate Magento developer. He loves exploring e-commerce and reading books.\",\"url\":\"https:\\\/\\\/www.knowband.com\\\/blog\\\/author\\\/pkumar\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to run Direct SQL Queries in Magento? - Knowband Blog","description":"Want to know how to run direct sql queries in Magento extension? Find out the complete process in the write-up with the required codes.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.knowband.com\/blog\/magento-tutorials\/run-direct-sql-queries-in-magento\/","og_locale":"en_US","og_type":"article","og_title":"How to run Direct SQL Queries in Magento? - Knowband Blog","og_description":"Want to know how to run direct sql queries in Magento extension? Find out the complete process in the write-up with the required codes.","og_url":"https:\/\/www.knowband.com\/blog\/magento-tutorials\/run-direct-sql-queries-in-magento\/","og_site_name":"Knowband Blog | Ecommerce Modules","article_publisher":"https:\/\/www.facebook.com\/Knowband","article_published_time":"2018-10-29T09:14:53+00:00","article_modified_time":"2018-10-29T09:15:48+00:00","author":"Prashant Kumar","twitter_card":"summary_large_image","twitter_creator":"@KnowbandPlugin","twitter_site":"@KnowbandPlugin","twitter_misc":{"Written by":"Prashant Kumar","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.knowband.com\/blog\/magento-tutorials\/run-direct-sql-queries-in-magento\/#article","isPartOf":{"@id":"https:\/\/www.knowband.com\/blog\/magento-tutorials\/run-direct-sql-queries-in-magento\/"},"author":{"name":"Prashant Kumar","@id":"https:\/\/www.knowband.com\/blog\/fr\/#\/schema\/person\/04499d0d3f542a4f42182b91f772986c"},"headline":"How to run Direct SQL Queries in Magento?","datePublished":"2018-10-29T09:14:53+00:00","dateModified":"2018-10-29T09:15:48+00:00","mainEntityOfPage":{"@id":"https:\/\/www.knowband.com\/blog\/magento-tutorials\/run-direct-sql-queries-in-magento\/"},"wordCount":290,"commentCount":0,"publisher":{"@id":"https:\/\/www.knowband.com\/blog\/fr\/#organization"},"keywords":["directsqlqueries","Magento extension","Magento module","run sql queries in magento","sql queries in magento"],"articleSection":["Magento Tutorials"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.knowband.com\/blog\/magento-tutorials\/run-direct-sql-queries-in-magento\/","url":"https:\/\/www.knowband.com\/blog\/magento-tutorials\/run-direct-sql-queries-in-magento\/","name":"How to run Direct SQL Queries in Magento? - Knowband Blog","isPartOf":{"@id":"https:\/\/www.knowband.com\/blog\/fr\/#website"},"datePublished":"2018-10-29T09:14:53+00:00","dateModified":"2018-10-29T09:15:48+00:00","description":"Want to know how to run direct sql queries in Magento extension? Find out the complete process in the write-up with the required codes.","breadcrumb":{"@id":"https:\/\/www.knowband.com\/blog\/magento-tutorials\/run-direct-sql-queries-in-magento\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.knowband.com\/blog\/magento-tutorials\/run-direct-sql-queries-in-magento\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.knowband.com\/blog\/magento-tutorials\/run-direct-sql-queries-in-magento\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.knowband.com\/blog\/fr\/"},{"@type":"ListItem","position":2,"name":"A cobran\u00e7a de fabricantes de smartphones na Europa levar\u00e1 o Google a quest\u00f5es antimonop\u00f3lio?","item":"https:\/\/www.knowband.com\/blog\/pt-br\/noticia\/a-cobran%c3%a7a-de-fabricantes-de-smartphones-na-europa-levar%c3%a1-o-google-a-quest%c3%b5es-antimonop%c3%b3lio\/"},{"@type":"ListItem","position":3,"name":"Como criar a API do servi\u00e7o Web do m\u00f3dulo Prestashop?","item":"https:\/\/www.knowband.com\/blog\/pt-br\/prestashop-tutoriais\/como-criar-a-api-prestashop-module-web-service\/"},{"@type":"ListItem","position":4,"name":"How to run Direct SQL Queries in Magento?"}]},{"@type":"WebSite","@id":"https:\/\/www.knowband.com\/blog\/fr\/#website","url":"https:\/\/www.knowband.com\/blog\/fr\/","name":"Knowband Blog | Ecommerce Modules","description":"Magento, Prestashop, Opencart, Shopify","publisher":{"@id":"https:\/\/www.knowband.com\/blog\/fr\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.knowband.com\/blog\/fr\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.knowband.com\/blog\/fr\/#organization","name":"Knowband Blog | Ecommerce Modules","url":"https:\/\/www.knowband.com\/blog\/fr\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.knowband.com\/blog\/fr\/#\/schema\/logo\/image\/","url":"https:\/\/www.knowband.com\/blog\/wp-content\/uploads\/2016\/09\/cropped-knowband_logo-v2.png","contentUrl":"https:\/\/www.knowband.com\/blog\/wp-content\/uploads\/2016\/09\/cropped-knowband_logo-v2.png","width":153,"height":61,"caption":"Knowband Blog | Ecommerce Modules"},"image":{"@id":"https:\/\/www.knowband.com\/blog\/fr\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/Knowband","https:\/\/x.com\/KnowbandPlugin"]},{"@type":"Person","@id":"https:\/\/www.knowband.com\/blog\/fr\/#\/schema\/person\/04499d0d3f542a4f42182b91f772986c","name":"Prashant Kumar","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.knowband.com\/blog\/wp-content\/uploads\/2018\/10\/10256082_607442326014813_1731639576902538685_o-150x150.jpg","url":"https:\/\/www.knowband.com\/blog\/wp-content\/uploads\/2018\/10\/10256082_607442326014813_1731639576902538685_o-150x150.jpg","contentUrl":"https:\/\/www.knowband.com\/blog\/wp-content\/uploads\/2018\/10\/10256082_607442326014813_1731639576902538685_o-150x150.jpg","caption":"Prashant Kumar"},"description":"Prashant is a passionate Magento developer. He loves exploring e-commerce and reading books.","url":"https:\/\/www.knowband.com\/blog\/author\/pkumar\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/www.knowband.com\/blog\/wp-json\/wp\/v2\/posts\/22268","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.knowband.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.knowband.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.knowband.com\/blog\/wp-json\/wp\/v2\/users\/14"}],"replies":[{"embeddable":true,"href":"https:\/\/www.knowband.com\/blog\/wp-json\/wp\/v2\/comments?post=22268"}],"version-history":[{"count":4,"href":"https:\/\/www.knowband.com\/blog\/wp-json\/wp\/v2\/posts\/22268\/revisions"}],"predecessor-version":[{"id":22276,"href":"https:\/\/www.knowband.com\/blog\/wp-json\/wp\/v2\/posts\/22268\/revisions\/22276"}],"wp:attachment":[{"href":"https:\/\/www.knowband.com\/blog\/wp-json\/wp\/v2\/media?parent=22268"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.knowband.com\/blog\/wp-json\/wp\/v2\/categories?post=22268"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.knowband.com\/blog\/wp-json\/wp\/v2\/tags?post=22268"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}