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

Reply via email to