Re: [fw-db] Howto run an SQL query directly against a database?

You can not bring the model data directly from the view of the way that you're doing, is it the right way from a helper. Or directly from the action Controller

You're sure that the registry keeps a copy of the default_adapter?

Ideally, you use Zend_Db_Select This optimizes your querys for better performances

----------------------------------------
Pablo Morales
blog: http://blog.pablo-morales.com
linkedln: http://www.linkedin.com/pub/9/528/21
skype: pablofmorales
gtalk: pablofmorales@gmail.com
msn: pfm_mc@hotmail.com

On Mon, Dec 7, 2009 at 10:38 AM, W Itch <im.allergic.2.mailing.lists@gmail.com> wrote:

Hi I've been studying Akrabat's Tutorial with the Albums collection for one year now approximately along with his book ZFiA.
http://akrabat.com/zend-framework-tutorial/

Everything works well and all that but I prefer to work with the MySQL language directly.  I find the Internet documentations and books about SQL easier to understand than how ZF does things with the Table abstraction thingy.

So I'm trying to figure out how to fit things into my ZF MVC so I can begin coding in pure SQL while keeping some of the built-in ZF Database security thingys.
Here are two code snippets of the relevant files I'm working on.  I'm trying to replace the old standard ZF list function with pure SQL code but everything breaks.

$sql = 'SELECT * FROM albums';

What am I doing wrong?




class OnemanyController extends Zend_Controller_Action

    public function indexAction()
    {
        $this->view->title = "One2Many (N:M) My Albums";
        $this->view->headTitle($this->view->title, 'PREPEND');
 
        $albums = new Model_DbTable_Albums2();       // 0. This isn't necessary but I wanted to keep some of the old code before removing them completely.
        //$this->view->albums = $albums->fetchAll();    //  1. ZF standard.
        //$this->view->albums = $albums->listAlbum(); //  2. My custom SQL function which isn't in use.  I moved all the code to index.pthml for testing first.
    }




views/scripts/onemany/index.pthml

<p><a href="<?php echo $this->url(array('controller'=>'onemany', 'action'=>'add'));?>">Add new album</a></p>


<?php
$db = Zend_Registry::get('db');

$sql = 'SELECT * FROM albums';
$stmt = $db->query($sql);

$result = $stmt->fetchall();

$albums = $result;
?>


<table>
<tr>
    <th>Title</th>
    <th>Artist</th>
    <th>Genre</th>
    <th>&nbsp;</th>
</tr>
<?php foreach($albums as $album) : ?>
<tr>
    <td><?php echo $this->escape($album->title);?></td>
    <td><?php echo $this->escape($album->artist);?></td>
    <td>
        <a href="<?php echo $this->url(array('controller'=>'onemany', 'action'=>'edit', 'id'=>$album->id));?>">Edit</a>
        <a href="<?php echo $this->url(array('controller'=>'onemany', 'action'=>'delete', 'id'=>$album->id));?>">Delete</a>
    </td>
</tr>
<?php endforeach; ?>
</table>



/Darth apprentice