Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Page 1 of 2
showing query results in CMS pages - query table outside of magento store tables in same DB
 
moneeshot
Jr. Member
 
Total Posts:  21
Joined:  2010-06-13
 

What I am wanting to do is query a table and display the results on pages created in the CMS.

The table holds info and path to pdf’s, I have read a lot of possible answers to this but none have worked and most call for a module, that doesn’t seem correct to me but apparently I know very little about this program.

It would also be nice if the individual pages to request a different category; ei category=1 on op one page and category=2 on another page but just getting something would be a great start

Thanks for any help, Craig

 
Magento Community Magento Community
Magento Community
Magento Community
 
Brent W Peterson
Moderator
 
Avatar
Total Posts:  3217
Joined:  2009-02-26
Minneapolis MN
 

The easiest way would be to build a custom module. Is the table in a separate database or in the same one?

 
Magento Community Magento Community
Magento Community
Magento Community
 
moneeshot
Jr. Member
 
Total Posts:  21
Joined:  2010-06-13
 

same DB

 
Magento Community Magento Community
Magento Community
Magento Community
 
moneeshot
Jr. Member
 
Total Posts:  21
Joined:  2010-06-13
 

I\’ve tried to do that but there must be a flaw in my coding, out of frustration I have deleted everything so I\’m starting fresh
It may be the way I have tried to output the data , does the php have to be in a function, I assume yes?
The output seems to need to be in a certain format, it could be my lack of coding in OOP

 
Magento Community Magento Community
Magento Community
Magento Community
 
Brent W Peterson
Moderator
 
Avatar
Total Posts:  3217
Joined:  2009-02-26
Minneapolis MN
 

Could you post your query you are using?

 
Magento Community Magento Community
Magento Community
Magento Community
 
moneeshot
Jr. Member
 
Total Posts:  21
Joined:  2010-06-13
 

It was something like this

<?php

    
function _showFiles()
    
{    
        $query 
"SELECT * from uploads WHERE year = '2010'";
        
$files mysql_fetch_array($query);
        
$this->results $files;
    
}
    
    
public function getResults() {
        
return $this->results;
    
}

?>

And I’m not sure if I need to add $this->results; for every result that I want to show on the front end, meaning

$this->results['fileTitle'];
$this->results['fileDate'];

I figured it would store the whole array and I could declare this later but I’m not sure.
I also am not sure if I need to make a DB connection or how to do it if I had to.

 
Magento Community Magento Community
Magento Community
Magento Community
 
moneeshot
Jr. Member
 
Total Posts:  21
Joined:  2010-06-13
 

when I opened this post I said I wanted to request a different category on every page but that would actually be the year ,

same difference I guess

 
Magento Community Magento Community
Magento Community
Magento Community
 
MagePsycho
Mentor
 
Avatar
Total Posts:  1702
Joined:  2009-06-23
 

If you know how to include the .phtml files in cms pages / static blocks, then i think you can know to achieve the task.
Just a hint.

 
Magento Community Magento Community
Magento Community
Magento Community
 
moneeshot
Jr. Member
 
Total Posts:  21
Joined:  2010-06-13
 

This is what I have so far:

app/etc/modules/Journals.xml

<?xml version="1.0"?>
<config>
  <
modules>
    <
tacda_Journals>
      <
active>true</active>
      <
codePool>local</codePool>
    </
tacda_Journals>
  </
modules>
</
config>
app/code/local/tacda/Journals/etc/config.xml
<?xml version="1.0"?>
<config>
  <global>
    <
blocks>
      <
tacda_Journals>
        <class>
tacda_Journals_Block</class>
      </
tacda_Journals>
    </
blocks>
  </global>
</
config>
app/code/local/tacda/Journals/Block/Journals.php
<?php
class tacda_Journals_Block_Journals extends Mage_Core_Block_Abstract
{
    
protected $results;

    protected function 
_constructor()
    
{    
        $query 
"SELECT * from uploads WHERE year = '2010'";
        
$files mysql_fetch_array($query);
        
$this->results $files;
    
}
    
    
public function getResults() {
        
return $this->results;
    
}
}
?>
app/design/frontend/tacda/tacda-theme/template/Journals/journal.phtml
<?php $results $this->getResults(); ?>
<?php 
if(count($results)>0): ?>
    
<div>
        
            
<?php foreach($results as $result): ?>               
                <?php 
echo $result->['fileTitle']?>
                <?php 
echo $result->['fileDate']?>
            <?php 
endforeach; ?>
      
   
</div>
<?php endif; ?>

I’m getting nothing, I might have journal.phtml in the wrong place but does it matter as long as I refer to it correctly?
this is the call i’m using in the cms page

<block type="tacda/Journals" template="Journals/journal.phtml" />
 
Magento Community Magento Community
Magento Community
Magento Community
 
Brent W Peterson
Moderator
 
Avatar
Total Posts:  3217
Joined:  2009-02-26
Minneapolis MN
 

Why don’t you test it first on a php page in the root of your site. (i.e. mydomain.com/test.php) Use print_r() to make sure you are getting some results. After you have data you can easily move into the next stage. And yes you will have to get your connection.

 
Magento Community Magento Community
Magento Community
Magento Community
 
moneeshot
Jr. Member
 
Total Posts:  21
Joined:  2010-06-13
 

I tried using an array, I was able to get a stored array outside of Magento

<?php
class tacda_Journals_Block_Journals extends Mage_Core_Block_Abstract
{
    
protected $results;

    protected function 
_constructor()
    
{    
        $query 
"SELECT * from uploads WHERE fileDate = '2010'";
        
$files[] $query;
        
$this->results $files;
    
}
    
    
public function getResults() {
        
return $this->results;
    
}
}
?>
I’m still getting nothing but I don’t have a connection, any one know how to do this? And will the code work?
 
Magento Community Magento Community
Magento Community
Magento Community
 
moneeshot
Jr. Member
 
Total Posts:  21
Joined:  2010-06-13
 

so I’m tryin this now but still getting nothing

<?php
$w 
Mage::getSingleton('core/resource')->getConnection('core_write');

class 
tacda_Journals_Block_Journals extends Mage_Core_Block_Abstract
{
    
//protected $results;

    
public function _constructor()
    
{    
        $myQuery 
$w->query("SELECT * from uploads WHERE fileDate = '2010'");
        
$files[] $myQuery;
        
$this->results $files;
        return 
$this->results;
    
}
}
?>

If it’s the cuery which it good be , does anybody have any ideas, I’m struggling here and I’m out of ideas

 
Magento Community Magento Community
Magento Community
Magento Community
 
Brent W Peterson
Moderator
 
Avatar
Total Posts:  3217
Joined:  2009-02-26
Minneapolis MN
 

1) I am assuming you tested the query against your database in Query Analyzer or PhpMyAdmin?

2) Your colum fileDate contains only data that is ‘2010’?

 
Magento Community Magento Community
Magento Community
Magento Community
 
Brent W Peterson
Moderator
 
Avatar
Total Posts:  3217
Joined:  2009-02-26
Minneapolis MN
 

Here is an example of a select query

protected $_write;
    protected 
$_read;
    
    public function 
__construct()
    
{
        $this
->_write Mage::getSingleton('core/resource')->getConnection('orders_write');
        
$this->_read Mage::getSingleton('core/resource')->getConnection('orders_read');        
    
}
    


    
public function getOrderIdFromIncrementId($incrementId)
    
{
        $val 
0;
        
$row $this->_read->fetchRow("Select entity_id FROM sales_order where increment_id ='".$incrementId."'");
        if(
$row)
        
{
            $val 
$row['entity_id'];    
        
}
        
return $val;
    
}

For more that one row use fetchAll()

 
Magento Community Magento Community
Magento Community
Magento Community
 
moneeshot
Jr. Member
 
Total Posts:  21
Joined:  2010-06-13
 

the Filedate is by year and there will be quarterly reports. I actually just tried some static text <p>Show page</p>
and got nothing so apparently I have more issues then just the code.

I appreciate your time and will use your code as soon as I figure out why I\’m getting nothing returned

 
Magento Community Magento Community
Magento Community
Magento Community
 
Brent W Peterson
Moderator
 
Avatar
Total Posts:  3217
Joined:  2009-02-26
Minneapolis MN
 

So you fileData column only has “2010” in it and not something like 6.10.2010 correct? Also, you get results when you run your query directly against the database correct? (Can you post a couple of rows of results from your table?)

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top
Page 1 of 2