My question now is how to do it smarter in the future? This is going to lead to a dumb database design if I need to avoid fields in different tables having the same name.
As you are implicitly joining the tables, the column name title is ambiguous and are in both tables. You will need to use a column alias preferably for both category.title and albums.title for clarify.
e.g.
SELECT
albums.title as album_title,
category.title as category_title
...etc
try this and do a dump of the return records.
Regards,
Duo
On Dec 19, 2009, at 7:02 PM, W Itch wrote:
I think I solved it by myself somehow :-D What I did was change the database table category.title to category.name so it wouldn't clash with the main table albums.title. Also I used a different SQL command.
My question now is how to do it smarter in the future? This is going to lead to a dumb database design if I need to avoid fields in different tables having the same name. 10 tables ok but If my database grows to 30 tables then I think I will get a headache.
Screenshot 3
http://i46.tinypic.com/2hrnbix.jpg
Code 3 - It works! But I think the database design is dumb :-(
http://pastebin.com/m3201c750
<p><a href="<?php echo $this->url(array('controller'=>'onemany', 'action'=>'add'));?>">Add new album</a></p> <?php $db = Zend_Registry::get('db'); //Zend_Debug::dump($db); $sql = 'SELECT A.id, A.title, A.artist, FROM albums AS A LEFT JOIN category AS C ON A.category_id = C.id'; //$sql = 'SELECT albums.title, category.title FROM albums, category WHERE category_id = category.id'; //$sql = 'SELECT * FROM albums'; $stmt = $db->query($sql); $tableAlbums = $stmt->fetchAll(); /* *** Display associative array *** */ ?> <table> <tr> <th>Title</th> <th>Artist</th> <th>Category</th> <th> </th> </tr> <?php foreach($tableAlbums as $AlbumID) : ?> <tr> <td> <a href="<?php echo $this->url(array('controller'=>'onemany', 'action'=>'edit', 'id'=>$AlbumID["id"]));?>">Edit</a> <a href="<?php echo $this->url(array('controller'=>'onemany', 'action'=>'delete', 'id'=>$AlbumID["id"]));?>">Delete</a> </td> </tr> <?php endforeach; ?> </table>
/Darth apprentice
Begone OLD text>>>On Sat, Dec 19, 2009 at 10:52 PM, W Itch <im.allergic.2.mailing.lists@gmail.com> wrote:Hi my code is 99% based on Akrabat's tutorial. And I wanted to do things with my database that Rob didn't cover in his book. Also the Zend manual doesn't explain things in a way that I can understand it. Also most of the one-to-many examples on the Internet is too fragmented for me to fully understand.
http://akrabat.com/zend-framework-tutorial/
Screenshot 1 and Code 1 shows you how things look like when everything is working.
Screenshot 1
http://i49.tinypic.com/15xs6ds.jpg
Code 1
http://pastebin.com/m49755d82
Screenshot 2 and Code 2 shows what happens when I implemented the MySQL code that I wanted to use.
I tried to use the associative array by writing $AlbumID["albums.title"] instead of just $AlbumID["title"] but the list breaks.
What am I doing wrong?
Screenshot 2
http://i49.tinypic.com/2rppjcn.jpg
Code 2
http://pastebin.com/m660ccf6a
/Darth apprentice
没有评论:
发表评论