1

I have two table tbl_magazine, tbl_page .... I need to select data form magazine like... if magazine is enabled then also check magazine page is enable or not... if magazine is enable and some pages of magazine is disable then only select acive table.

my table is
------------              

magazine  table

magazine_id
magazine_name
magazine_cover
status

page table


page_id
magazine_id
page_content
status

I musing this query..

   public function getMagazine() {
        $current_magazine_id = Mage::registry('current_magazine_id');
        $id = $current_magazine_id['o'];
        Mage::unregister('current_magazine_id');

        if ($id) {

            $collection = Mage::getModel('magazine/page')->getCollection()
                    ->addFieldToFilter('magazine_id', $id)
                    ->addFieldToSelect('status', array('eq' => 'ENABLED'))
                     ->setOrder('page_id', 'ASC');
        } else {
            $defaultMagazine = Mage::getModel('magazine/display')->getCollection();
            $data = $defaultMagazine->getData();
            $defaultMagazineId = $data[0]['magazine_id'];
            $collection = Mage::getModel('magazine/page')->getCollection()
                    ->addFieldToFilter('magazine_id', $defaultMagazineId)
                    ->addFieldToSelect('status', array('eq' => 'ENABLED'))
                     ->setOrder('page_id', 'ASC');
        }
        return $collection;
    }

my error

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM `magazine` AS `main_table` ORDER BY month ASC' at line 1
  • This question appears to be off-topic because it is about MySQL join queries. You might have more luck on Stackoverflow as this is not specifically Magento related – Sander Mangel Dec 9 '14 at 9:37
  • 1
    @SanderMangel i want to get this in custom magento module....this is not off topic – user3623126 Dec 9 '14 at 9:50
  • Ok so what do you have so far on code? Any snippets we could start answering the question from? What you're asking is quite broad – Sander Mangel Dec 9 '14 at 9:52
  • @Marius THis is not off topic question – user3623126 Dec 9 '14 at 10:06
  • @SanderMangel I edited my question – user3623126 Dec 9 '14 at 10:14
1

I used this and currently its working fine...

public function getMagazine() {
    $current_magazine_id = Mage::registry('current_magazine_id');
    $id = $current_magazine_id['o'];
    Mage::unregister('current_magazine_id');

    if ($id) {

        $collection = Mage::getModel('magazine/page')->getCollection()
                ->addFieldToSelect('*')
                ->addFieldToFilter('status', array('eq' => 'ENABLED'))
                ->addFieldToFilter('magazine_id', array('in' => $id));
        $collection->getSelect()->order('page_id ASC');
    } else {
        $defaultMagazine = Mage::getModel('magazine/display')->getCollection();
        $data = $defaultMagazine->getData();

        $defaultMagazineId = $data[0]['magazine_id'];
        $collection = Mage::getModel('magazine/page')->getCollection()
                ->addFieldToSelect('*')
                ->addFieldToFilter('status', array('eq' => 'ENABLED'))
                ->addFieldToFilter('magazine_id', array('in' => $defaultMagazineId));
        $collection->getSelect()->order('page_id ASC');
    }
    return $collection;
}
  • feel free to accept this answer if it is now working – David Manners Feb 24 '15 at 8:27
0

Taking the code from your answer what you could do is simply write a getId function that checks the current magazine and then gets the default if it is not set. Then I think you will remove your code duplication.

private function getMagazineId() {
    $current_magazine_id = Mage::registry('current_magazine_id');
    if ($current_magazine_id !== null) {
        $id = $current_magazine_id['o'];
        Mage::unregister('current_magazine_id');
    } else {
        $defaultMagazine = Mage::getModel('magazine/display')->getCollection();
        $data = $defaultMagazine->getData();

        $id = $data[0]['magazine_id'];
    }
    return $id;
}

public function getMagazine() {
    $collection = Mage::getModel('magazine/page')->getCollection()
        ->addFieldToSelect('*')
        ->addFieldToFilter('status', array('eq' => 'ENABLED'))
        ->addFieldToFilter('magazine_id', array('in' => $this->getMagazineId()));
    $collection->getSelect()->order('page_id ASC');
    return $collection;
}

Not really sure if this is the "best" way but will remove the duplication from your original answer.

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.