0

I am building a program which takes the user through a series of questions and it returns with a number of products which suit the users answers. After each question the number is refreshed.

I use a MYSQL query to find out how many products are suited to the user, the query depends on how many of the answers the user has answered. However, the number count only works if the user only answers the first question, if the answer anymore than the first question it sends back a 503 with an error.

The error I get back if I complete only the 1st 2 questions is:

a:5:{i:0;s:274:"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Pressure' in 'where clause', query was: SELECT sku
                    FROM picker_Safety_Gates
                    WHERE (Maximum_Width >= 90
                    AND Minimum_Width ";i:1;s:2158:

and the code I use for the second query is:

$resource = Mage::getSingleton('core/resource');
$connectionRead = $resource->getConnection('core_read');
$qry = "SELECT sku
                    FROM picker_Safety_Gates
                    WHERE Maximum_Width >= $width
                    AND Minimum_Width <= $width
                    OR Extendable_Width = $width
                    AND Fixture_Type = $fix";
                $arr = $connectionRead->fetchAll($qry);

I have echoed the query and everything looks fine, I have checked the query in a MYSQL checker and it says everything is fine. Before I started to make this in Magento I made it outside of Magento using the exact same queries and I didn't get any errors from it at all.

The code for the only query which works (which is the first one) is:

$resource = Mage::getSingleton('core/resource');
$connectionRead = $resource->getConnection('core_read');
$qry = "SELECT sku
                FROM picker_Safety_Gates
                WHERE Maximum_Width >= $width
                AND Minimum_Width <= $width
                OR Extendable_Width = $width";
            $arr = $connectionRead->fetchAll($qry);

How comes this query works and the other one doesn't? I have reindexed my site as it said here: SQLSTATE[42S22]: Column not found: 1054 unknown column 'e.status' after 1.9.2 Upgrade and I have also tried to google if there is another way to write queries in magento but I couldn't find anything. What I am asking is, do you know what is wrong with my query and how can I fix it?

Thank you for reading this.

1

Magento caches database information so it does not need to check columns all the time. This is stored in the "Cache Storage". To clear it:

  1. Go to System -> Cache Management.
  2. Next click on "Flush Cache Storage"
  3. A dialog will pop-up. Click on "OK"

    I am not sure if this will help you or not, but it may.

Also, using the code you posted it looks like you are not really using the strengths of Magento's framework. If you use the framework properly, it will help you build your queries more easily. Try to use models and only the models' resource models to communicate with the database. It will make your code more usable throughout Magento.

  • Hi, thank you for the answer! It didn't work though, How should I be communicating to the database to make full use of magento's functionality? – Ben Apr 5 '16 at 16:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.