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

Thanks Duo there's no disturbance in the *force* anymore ;-)

Screenshot 4
http://i48.tinypic.com/o7onk6.jpg

Code 4
http://pastebin.com/m2ea16ede

Please indicate why this post is abusive, and provide any other useful information.

Spam / advertising / junk
Personal details
Proprietary code
Other

comments (optional)


email (optional)

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

Begone OLD text>>>

On Sun, Dec 20, 2009 at 2:09 AM, Duo Zheng <duozheng@gmail.com> wrote:

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

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