Can anyone help me figure out how to solve my inventory listing problem?
I am using php_5 and mysql_5.0 w/apache on fbsd.
I need to figure out a way to make a subtitle for every category (genre)
in the inventory so when I list the entire inventory on a sheet (at
client's request), it is organized by category (genre) and each category
(genre) has a title line above it. So the there is not just one big list
rather a neat list with titles for each category THEN all the rows in that
category etc. I can't figure out the loop to make the titles.
I have them sorted as you can by genre, the list is formatted fine There
are alternating colors on the rows to make it read easier. I just want to
keep from having to make a statement for EACH genre. I will eventually
make the genre list dynamic too, so I need to figure out how to
dynamically generate this inventory list.
This is the output I have now:
DVD ID TITLE GENRE 1 GENRE 2 GENRE 3 ACT QTY
BCK HLD INC OG USR OUT DATE OUT USR IN DATE IN USR
CY
20860003 Movie name action 1 1 0
10000000 0000-00-00 00:00:00 0000-00-00 00:00:00 0
20860020 Move Name COMEDY 1 1 0
10000000 0000-00-00 00:00:00 0000-00-00 00:00:00
0
20860006 Movie name COMEDY 1 1 0
10000000 0000-00-00 00:00:00 0000-00-00 00:00:00 0
What I WANT to see is:
I will fix the background colors, I just want to see the "GENRE: ACTION -
1 TITLES and GENRE: COMEDY - 2 TITLES"
DVD ID TITLE GENRE 1 GENRE 2 GENRE 3 ACT QTY
BCK HLD INC OG USR OUT DATE OUT USR IN DATE IN USR
CY
GENRE: ACTION - 1 TITLES
20860003 Movie name ACTION 1 1 0
10000000 0000-00-00 00:00:00 0000-00-00 00:00:00 0
GENRE: COMEDY - 2 TITLES
20860023 Movie name COMEDY 1 1
0 10000000 0000-00-00 00:00:00 0000-00-00 00:00:00
0
20860006 Movie name COMEDY 1 1 0
10000000 0000-00-00 00:00:00 0000-00-00 00:00:00 0
This is the code:
1. function invlistONE(){
2. dbconnect('connect');
3. $invlist = mysql_query("SELECT * FROM sp_dvd ORDER BY dvdGenre");
4.
5.
6. ?>
7. <table cellspacing="0" style="font-size:8pt;>
8. <tr>
9. <div style="font-size:8pt">
10. <td align="left" class="body"><b>DVD ID</b></td>
11. <td align="left" width="225"><b>TITLE</b></td>
12. <td align="left" class="body" width="75"><b>GENRE 1</b></td>
13. <td align="left" width="75"><b>GENRE 2</b></td>
14. <td align="left" class="body" width="75"><b>GENRE 3</b></td>
15. <td align="left" width="10"><b>ACT</b></td>
16. <td align="left" class="body" width="10"><b>QTY</b></td>
17. <td align="left" width="10"><b>BCK</b></td>
18. <td align="left" class="body" width="10"><b>HLD</b></td>
19. <td align="left" width="10"><b>INC</b></td>
20. <td align="left" class="body" width="50"><b>OG USR</b></td>
21. <td align="left"><b>OUT DATE</b></td>
22. <td align="left" class="body" width="55"><b>OUT USR</b></td>
23. <td align="left"><b>IN DATE</b></td>
24. <td align="left" class="body" width="50"><b>IN USR</b></td>
25. <td align="left" width=\"10\"><b>CY</b></td>
26. </div>
27. </tr>
28. <?
29.
30. $count = 0;
31. while($row = mysql_fetch_array($invlist)){
32.
33. $dvdId = $row['dvdId'];
34. $dvdGenre = $row['dvdGenre'];
35. $dvdGenre2 = $row['dvdGenre2'];
36. $dvdGenre3 = $row['dvdGenre3'];
37. $dvdTitle = $row['dvdTitle'];
38. $dvdOnHand = $row['dvdOnHand'];
39. $dvdOnHand = $row['dvdOnHand'];
40.
41. $active = $row['dvdActive'];
42. $back = $row['backordered'];
43. $hold = $row['dvdHoldRequests'];
44. $incoming = $row['incomingInventory'];
45.
46. $ogUserId = $row['ogUserId'];
47. $outDate = $row['outDate'];
48. $outUserId = $row['outUserId'];
49. $inDate = $row['inDate'];
50. $inUserId = $row['inUserId'];
51. $cycles = $row['cycles'];
52. $dvdLastUpdate = $row['dvdLastUpdate'];
53. $dvdLastAdminUpdate = $row['dvdLastAdminUpdate'];
54.
55. if ( $count == 1 ) { echo ("<tr bgcolor=\"#c1c1c1\">"); }
56. else { echo ("<tr>");}
57.
58. echo ("<div >");
59. echo ("<td class=\"body\" align=\"left\"> $dvdId </td>");
60. echo ("<td align=\"left\" width=\"225\">$dvdTitle</td>");
61. echo ("<td class=\"body\" align=\"left\"
width=\"75\">$dvdGenre</td>");
62. echo ("<td align=\"left\" width=\"75\">$dvdGenre2</td>");
63. echo ("<td class=\"body\" align=\"left\"
width=\"75\">$dvdGenre3</td>");
64. echo ("<td align=\"left\" width=\"10\">$active</td>");
65. echo ("<td class=\"body\" align=\"left\"
width=\"10\">$dvdOnHand</td>");
66. echo ("<td align=\"left\" width=\"10\">$back</td>");
67. echo ("<td class=\"body\" align=\"left\" width=\"10\">$hold</td>");
68. echo ("<td align=\"left\" width=\"10\">$incoming</td>");
69. echo ("<td class=\"body\" align=\"left\"
width=\"50\">$ogUserId</td>");
70. echo ("<td align=\"left\" width=\"75\">$outDate</td>");
71. echo ("<td class=\"body\" align=\"left\"
width=\"55\">$outUserId</td>");
72. echo ("<td align=\"left\" width=\"75\">$inDate</td>");
73. echo ("<td class=\"body\" align=\"left\"
width=\"50\">$inUserId</td>");
74. echo ("<td align=\"left\" width=\"10\">$cycles</td>");
75. echo ("</div>");
76. echo ("</tr>");
77.
78. $count++;
79. if ( $count == 2 ) { $count = 0; }
80. }
81. ?></table><?
82. }
HERE IS THE MYSQL TABLE:
CREATE TABLE sp_dvd(
dvdId int(8) UNSIGNED AUTO_INCREMENT NOT NULL UNIQUE PRIMARY KEY,
dvdActive smallint(1) NOT NULL,
backordered smallint(1) NOT NULL,
dvdHoldRequests int(4),
incomingInventory int(3),
dvdTitle varchar(50) NOT NULL UNIQUE,
dvdDescription text(500),
dvdActors varchar(200),
dvdGenre varchar(35),
dvdGenre2 varchar(35),
dvdGenre3 varchar(35),
dvdYear int(4),
dvdLength int(4),
dvdCover varchar(100),
dvdCover2 varchar(100),
dvdOnHand int(3),
firstInventoryDate TIMESTAMP,
ogUserId int(8),
outDate TIMESTAMP,
outUserId int(8),
inDate TIMESTAMP,
inUserId int(8),
cycles int(4),
dvdLastUpdate TIMESTAMP,
dvdLastAdminUpdate int(8),
rand varchar(150),
exchangeId int(8),
FULLTEXT (dvdTitle,dvdActors)
)TYPE=MyISAM AUTO_INCREMENT=20860000;
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
