Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [sql]

Questions about using raw SQL queries. For questions about the database in general, use the database tag instead, for questions about the Magento database access layer use the appropriate tag, like: eav, model, resource-model, collections, install-script

0
votes
1answer
4 views

Bulk adding & updating product attribute

[M1] I have over 2000 products in my Magento store. I want to add a new attribute called 'Warranty Terms' to display it on the frontend. This is going to be the same for all products. I know how to ...
1
vote
2answers
26 views

How to check if column exist before trying to add it to the table?

We are using the Extension FollowUp E-Mail from AheadWorks. The Extension has bugs in the sql install scripts... they are trying to add a column to a table but don't check if the column does already ...
0
votes
1answer
14 views

Index sql error

When I run indexer:reindex I get the following SQL error SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your ...
0
votes
0answers
10 views

Magento 2 Data Migration SQL error 'Column not found' Column in m2 DB but not in m1 DB

im having this error: Its the first data migration step, the integrity step. Anyone knows what to do with that? Thanks!
0
votes
0answers
16 views

magento2 Sort is not working on PLP due to entity_id

We are working on Magento 2.2.7. On PLP, sort by price is not working. This is because in the query it first sorts the items by entity_id then only using the price field. Example query is given below ...
1
vote
0answers
25 views

How can I change multiple entries in DB?

In DB I have Rows: ID NAME PHONE Example: 1 Steave 33333333 2 Joe 77777 I can add and Edit any row, but I want when I click "save everyone" I rewrote all phones on DB example: 1 Steave 33333333 ...
0
votes
1answer
40 views

Insert, Update values in existing custom table and Select values from that table. Magento2

How can i Insert, Update values in existing custom table and Select values with Magento methods not sql requests ?
0
votes
1answer
20 views

Magento 2.3 introduced a view table which causes issue with import of mysql dump. What's it for?

If you mysql dump a magento 2.3 store and try to import it elsewhere you get an issue due the the format of the view import query. The problem part is this: CREATE ALGORITHM=UNDEFINED DEFINER=`...
2
votes
2answers
19 views

How get products URL in magento 2.2.5 sql database

I am using magento 2 enterprise edition i am trying to get products URL from sql database for this i have seen catalog_products_entity_varchar but in value attribute column products imgages are given ...
1
vote
1answer
23 views

How get products name magento 2 sql database

I am trying to get products name(value) and when i see my db table catalog_product_entity_varchar but in which in the column of value all base images are given so please tell accurate table or any ...
0
votes
1answer
19 views

Hiding products with £0.00

Magento 2.3, how can I hide products automatically via cron for products that have a £0.00 price? I use a 3rd party to calculate prices across my store and when some products cannot be matched in ...
0
votes
1answer
13 views

Magento 2 - Hide Products with no Images via PHP SQL Query

I have an SQL query that i run on the database manually which checks the catalogue for products that do not have an image and hides them, i want to run this via cron so have created a php with the ...
2
votes
0answers
41 views

Column not found: 1054 Unknown column 'search_result.score' in 'order clause'

I'm searching on the internet for an answer but no luck so far. I have 2 Magento 2 webshops. One is version 2.2.7 and one is 2.3.1. Both have the latest Porto theme installed and both work with the ...
0
votes
0answers
14 views

Magento2: Cardinality violation: 1241 Operand should contain 1 column(s)

When i delete the item from cart then got the SQLError below: SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s), query was: SELECT `catalog_category_entity`.* FROM `...
0
votes
0answers
20 views

Huge slow query for products, categories, filters

I run a Magento 1.9 based store with around 40 thousand active products. Using an FPC I am able to keep the server stable sometimes, and other unstable. While checking for highest hardware consumption ...
1
vote
1answer
53 views

Event Observer for import with magmi

I created an event observer to report the creation of new magento products, but the observer doesn't work when the products are created by importing Magmi. My magento version is 1.9.3.9 This is the ...
1
vote
1answer
91 views

SQL Query for custom attribute

I have create custom attribute in product upc I want custom query (sql) to get SKUs and UPCs Can anyone tell me the related tables from where I can get data, or if anyone can share query that would ...
1
vote
1answer
39 views

Magento 1: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'status' in where clause is ambiguous

I am getting the Error: Integrity constraint violation: 1052 Column 'status' in where clause is ambiguous SELECT `main_table`.*, CONCAT(soa.firstname,' ', soa.lastname,'', soa.street,'', soa....
2
votes
2answers
168 views

Custom report from SQL query

I want to get order total of the month January 2019 for complete orders I need to know tables related to this query, or if someone can share query that would be great.
2
votes
1answer
49 views

Error when importing configurable product programmatically

I am programming a custom import system with Magento libraries, but when I import configurable products I get an SQL error. This is my configurable product function (only a part of my code): ...
2
votes
1answer
40 views

Magento 2 delete search term through sql query

I want to delete search terms with 0 search results from magento 2 database by writing sql query in file. How can I do this?
1
vote
1answer
21 views

magento 1 query generated back tics are wrongly placed

I have an issue in Magento 1, where the SQL query that is getting generated has the back tics wrongly placed. This is a piece of the code that helps to provide the collection to the grid layout. ...
0
votes
0answers
21 views

Deadlock when updating products

I'm trying to update a few products in Magento with this code: Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID); $products = Mage::getModel('catalog/product') ->...
0
votes
0answers
24 views

Difference between Sales order and database

In Magento 1.9 when generating a Sales order report (Report -> Sales -> Orders) for February I expect the same value total in invoiced column compared to running below: select sum(base_total_invoiced)...
2
votes
2answers
32 views

how to calculate sum of selected entries of a column in Magento 2

I want to calculate sum of all total_purchase values whose ref_by value is 1. Table name is table_name. Can someone please help me. I want sql query which will return total (200 + 97 + 140)
2
votes
0answers
25 views

Magento Installation Error: SQL max_allowed_packet value

I am trying to install Magento 2.3.0 CE with Sample data, but the installer fails because my hosting have the max_allowed_packet set to 16M. In this post of someone with the same problem the issue is ...
2
votes
2answers
53 views

SQL Error when I try to reindex Magento 2

I have an issue in Magento 2.2.7 where my site has the message "One or more indexers are invalid." and looking at the indexers I see the following: However when I try to run php bin/magento indexer:...
0
votes
1answer
55 views

Clearing database logs in Magento 2

We've recently migrated from Magento 1 to Magento 2 and it seems that there is a large amount of information in our database files. We'd like to try to mitigate this by clearing the log files but ...
1
vote
1answer
22 views

SQL Query Newsletter subscription status with custom tables

I currently have the following script that someone else wrote for me that exports custom Magento data to a CSV file. It takes data from the two tables 'perfume_fragrance' & '...
1
vote
0answers
24 views

magento 1.9 CE error when duplicating a product

When I use the method to duplicate an object sometimes I get this error message, the product is created: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1-0-0-1-4-24884-0' for ...
1
vote
1answer
24 views

Magento 2.2.5 Delete Url Rewrites older than 1 Year

Is it possible to delete Url_rewrites older than one year? We have migrated from Magento1 to Magento 2 and kept all URLs for SEO reasons. BUT the table in Mage1 and Mage2 is really huge. Now we want ...
2
votes
1answer
36 views

Magento 1.9 - Change Tax in created invoice

Unfortunately somebody changed the tax class for many of our products. We needed 2 weeks to recognize that. Now I got 150 orders with wrong tax. I need to fix that and resend all invoices to the ...
1
vote
1answer
37 views

Generate Magento 2 Product Feed with MySQL Query

I hope you all having a nice day. I am wondering is it possible to generate below feed with SQL on Magento 2 Database? I want to run this query from Google Apps scripts using jdbc:mysql. Expected ...
1
vote
2answers
181 views

error on running raw sql query in upgradeData.php magento 2

I have created my own module and in UpgradeData.php script I am writing my own, mysql query but it gives me an error when I run bin/magento setup:upgrade to run the setup script files Recoverable ...
0
votes
1answer
28 views

how to enable disabled products via query?

Hi I want to enable all products I have in my Magento 2 store, is there any query to enable all products we have using a single sql query ? Thanks
0
votes
1answer
28 views

Magento Freezing because of SQL Query

Currently this query is slowing the entire site down: Spawns in irregular times: SELECT `main_table`.`entity_id`, main_table.`name`, main_table.`path`, `main_table`.`is_active`, `main_table`.`...
1
vote
0answers
34 views

Magento 2.2 - Want to implement blackfire for community edition

I have heard about blackfire php profiler and automated performance tool that can be used in three environments. If i wanted to implement in Magento 2.2 based website using nginx server then is it ...
0
votes
1answer
74 views

Error SQL[HY000] Error 1205 with import_products

I have a problem with a job which manage import products and customers every night. Randomly return an error when import products from SAP. If u see the picture u can see the material "SB-FHA125A-...
3
votes
1answer
94 views

Do bound parameters get escaped using the Core/Resource model?

Consider the following example: $connection = Mage::getSingleton('core/resource')->getConnection('core_read'); $email_address = $_GET['email']; $sql = 'SELECT * FROM `foo` WHERE `email_address` = ...
0
votes
1answer
46 views

Magento2 use Multiple LIKE operator in Join Query

I have used Join query,in which i'm able to fetch only one row by below query where $productId = 10 $collection->getSelect()->where('paproduct.product_id = (?)', $productId); I want to use ...
0
votes
1answer
53 views

Deleted ShipperHQ module causing error in “All Customers” section of Magento 2

I'm currently trying to build a Magento 2 site to replace an existing Magento 1 site. As we currently use the MatrixRates Module we decided to give ShipperHQ a try but ultimately decided against using ...
0
votes
0answers
22 views

How to run query subdate(now(), INTERVAL 1 DAY) in Magento Helper

in Magento 1.9 Community, I have this query created_at >= subdate(now(), INTERVAL 1 DAY) Group By product_id order By sum(qty_ordered) desc How do I do this in Helper/Data.php? Currently this is ...
0
votes
0answers
35 views

How can I convert this raw sql segment into a function to use in magento 2?

This sql segment works fine but meqp validator reports the possible raw sql error 'select `review_id foreach ($post['cons'] as $key => $value) { $array[] = $key; } $inCond = $...
0
votes
1answer
26 views

Tax_class_id per product (through SQL)

hoping you are well, I need your help with the following topic: I want to make a report that extracts all the products in MAGENTO that does NOT have tax_class_id = '2' (taxable goods) and although I ...
0
votes
0answers
17 views

how can i change the value of state column in code(sales_order) table from new to processing ?

Hi i have made a relation between two order statuses in my magento 2.2 system , the first status of the order is "pending" and the second is "processing" , i goto (stores->settings->order statuses) ...
0
votes
1answer
23 views

Extension installed - SQLSTATE[42S02]: Base table or view not found

I have a rather odd problem I haven't encountered before. I installed the Premium MatrixRate extension on Magento 1.9 according to their guide. When I go to Configuration > Shipping Methods (to try ...
0
votes
1answer
133 views

Delete All products

I have recently performed a data migration from my M1 store to my Magento 2.3 store. Along with the migration i carried over all products, somewhere around 5,000. A lot of these products are old and ...
-1
votes
1answer
18 views

Magento 1.x addFieldToFilter using AND instead of OR (how can I join search using AND)

I am trying to use AND and OR based on user input. OR = success But I an unable to setup AND queries for addFieldToFilter question: how can I add the second (looped filter) using AND to join $...
0
votes
2answers
129 views

How to mass update custom dropdown customer attribute to all customers? PHP or SQL

I created a new customer attribute (Drop Down) and added it to the registration form. Attribute details found in eav_attribute table: Everything work as expected, attribute is added to the customer ...
2
votes
1answer
57 views

Remove all customers in a certain group

I have just migrated my M1 site to a newer M2 site, however, this new site will be for wholesale only customers, each of whom are assigned to a different group to reflect their prices. how can I ...