[fw-db] Re: One2Many MySQL associative array problems.

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

1. <p><a href="<?php echo $this->url(array('controller'=>'onemany', 'action'=>'add'));?>">Add new album</a></p>
2.  
3.  
4. <?php
5.  
6. $db = Zend_Registry::get('db');
7. //Zend_Debug::dump($db);
8.  
9.  
10. $sql =
11. 'SELECT
12.      A.id,
13.      A.title,
14.      A.artist,
15.      C.name
16. FROM
17.      albums AS A
18.      LEFT JOIN category AS C
19.      ON A.category_id = C.id';
20.  
21.  
22.  
23. //$sql = 'SELECT albums.title, category.title FROM albums, category WHERE category_id = category.id';
24. //$sql = 'SELECT * FROM albums';
25. $stmt = $db->query($sql);
26. $tableAlbums = $stmt->fetchAll();
27.  
28.  
29. /* *** Display associative array *** */
30. if(!$tableAlbums) echo "No records found!";
31. print_r($tableAlbums);
32.  
33. ?>
34.  
35.  
36. <table>
37. <tr>
38.     <th>Title</th>
39.     <th>Artist</th>
40.     <th>Category</th>
41.     <th>&nbsp;</th>
42. </tr>
43. <?php foreach($tableAlbums as $AlbumID) : ?>
44. <tr>
45.     <td><?php echo $this->escape($AlbumID["title"]);?></td>
46.     <td><?php echo $this->escape($AlbumID["artist"]);?></td>
47.     <td><?php echo $this->escape($AlbumID["name"]);?></td>
48.     <td>
49.         <a href="<?php echo $this->url(array('controller'=>'onemany', 'action'=>'edit', 'id'=>$AlbumID["id"]));?>">Edit</a>
50.         <a href="<?php echo $this->url(array('controller'=>'onemany', 'action'=>'delete', 'id'=>$AlbumID["id"]));?>">Delete</a>
51.     </td>
52. </tr>
53. <?php endforeach; ?>
54. </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