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

Well before I started with the alterations, Zend_Log which for me required a database was logging all the CRUD operations correctly in a text file.
And when I did this in the view page then I see all my database settings so it looks like I have database access.


$db = Zend_Registry::get('db');
Zend_Debug::dump($db);


When reading the ZFiA book there was mention about the great war between developers who believes in Database abstraction/Table abstraction vs. those who believe in working with the specific SQL.  At first I choose the Database/Table abstraction way because the author uses it and there was a lot of security features mentioned.  But after spending about 6 months with this and trying to read the Zend Manual then I just feel more stupid the more I read.  I can't find any practical code examples that I can follow and test for myself.

So now regardless of whether Dabase abstraction or pure SQL is the fastest and have the best performance.  For me it just boils down to just making it work.  I've been trying to ask for help for 6 months about how to do a certain 1-to-Many and Many-to-Many operation but I can't formulate my question to get an answer back.  If you weigh that against all the well written MySQL books out there then SQL to me seems like the easiest way.



If I reformulate my question instead.  If I want to test this SQL code on my ZF website.  Which file can I paste it into for a quick and dirty test?  I plan to move database code to the Model folder later, but for quick and dirty tests where is the best place?

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


And how can I make this code list all the results it has fetched from the database?


/Darth apprentice

On Mon, Dec 7, 2009 at 3:03 PM, pablofmorales@gmail.com <pablofmorales@gmail.com> wrote:

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