As you mention SQL I'll assume that you are using some form of relational database system (MySQL, MS-SQL, or something similar).

The first thing to consider is the design of your database tables. Off hand I'd start with something like this:

create table section (
   sectionId    int
,  sectionName  varchar(32)
,  dateCreated  date
-- maybe some other fields that describe the section
-- status
)

create table inventoryItem (
   itemId            int
,  itemName          varchar(32)
,  sectionId         int
,  description       varchar(255)
,  photoLocation     varchar(255)
,  dateCreated       date
,  dateLastModified  date
,  status            int
,  price             numeric
,  salePrice         numeric NULL
)
[download]

Note: I'm using sort-of generic datatype names, you'll have to adapt for your particular database.

If you want maximum flexibility for the pricing of your items you'd break out the price from the inventoryItem table and create a table just for the price:

create table itemPrice (
    itemId      int
,   price       numeric
,   type        int         -- 0 for normal, 1 for sale
,   validFrom   date        NULL
,   validTo     date        NULL
)
[download]

By using this price table you can store a default price for the item (with type = 0 and validFrom/validTo left NULL), and any sale price entered with the type == 1 and appropriate dates for validFrom and validTo.

Now to fetch the items for a section you'd have a query that would look somewhat like this:

select s.sectionName
     , i.itemId
     , i.itemName
     -- other inventoryItem columns as needed
     , p1.price
     , "sale_price" = p2.price
  from section s left join inventoryItem i 
        on s.sectionId = i.sectionId 
    left join itemPrice p1 
        on i.itemId = p1.itemId and p1.type = 0 
    left outer join itemPrice p2 
        on p1.itemId = p2.itemId and p2.type = 1 
       and p2.fromDate < getdate() and p2.toDate > getdate()
[download]

where getdate() returns the current date.

This will return the appropriate rows, like this:

            itemId             price                sale_price
 ---------- ------- ---------- -------------------- ------------------
+--
 section 1        1 item 1                24.990000                 NU
+LL
 section 1        2 item 2                19.990000            14.9900
+00
[download]

Once you have the data fetched it becomes relatively easy to format it for your web page.

Michael

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.