Attached, you will find an updated department screen for your perusal. I
used the great one submitted to the FAQ by C Walker. Thanks Chuck!
Anyway, this one prints the <Prev Next> links at top and bottom. It will
also check the SKUs for an item and list the lowest price in order to entice
the user to click the item link. Logic is as follows:
If only one item:
print Sale Price if there is one, otherwise print the list price.
If more than one item:
print the lower of the two (lowest sku sales price) or (lowest sku list
price)
It is not the best code. In fact, the logic to get the prices could be a
pig. But, the ideal way to handle the price info (IMHO) is to denormalize
the item table and put LOWESTSALE, LOWESTLIST, and SKUCOUNT fields on the
ITEM table. These fields could then be updated via trigger/stored. proc./ or
PHP during INSERT/DELETE/UPDATE of the SKU table. It would be more efficient
to keep track of that info while keying data rather than figuring it out on
the fly when the user(s) are querying the data.
However, I was not in the mood to change the db, so it is left as an
exercise for the reader :-)
Two things to do:
=======================
1) Insert the following clip into your language file. Sorry, only English is
provided.
// department
define('L_DEPT_PREV', 'Previous');
define('L_DEPT_NEXT', 'Next');
define('L_DEPT_LOWPRICE', 'Only');
define('L_DEPT_SALE', 'Sale!');
define('L_DEPT_ITEMS', 'Items');
define('L_DEPT_OF' , 'of');
define('L_DEPT_MORETHAN1', 'From');
2) Replace your current department file with everything below. Backup first,
of course.
<?php
/*
** File: department
** Description: show departments and items contained in them
** Version: $Revision: 1.11 $
** Created: 04/22/1999
** Author: Leon Atkinson
** Email: [EMAIL PROTECTED]
** CVS Author: $Author: leon $
** Last Revised: $Date: 2000/02/02 18:59:48 $
** Log : $Log: department,v $
** Log : Revision 1.11 2000/02/02 18:59:48 leon
** Log : Fixing bugs reported by Ted Henigson.
** Log :
** Copyright (c) 1999 Working Dogs. All rights reserved.
*/
$department = intval($department);
function printPrevNext($StartItem,$NumItemsPerPage,$TotalNumber,$department)
/* Print the <Prev and Next> links along with the item count */
{
print("<BR>\n<TABLE ROWS=1 COLS=3 WIDTH=100% BORDER=0>\n");
print("\t<TR><TD WIDTH=33% ALIGN=\"center\">");
/* CW - Prev Link if necessary */
if ($StartItem>0)
{
print("<A
HREF=\"".ScreenURL("department")."&department=$department&StartItem=" .
($StartItem-$NumItemsPerPage) . "\"><" . L_DEPT_PREV .
"</A>");
}
print("</TD>\n\t<TD WIDTH=33% ALIGN=\"center\">");
print(L_DEPT_ITEMS . ($StartItem + 1) . " - ");
if (($StartItem + $NumItemsPerPage) > $TotalNumber)
{
print($TotalNumber);
}
else
{
print($StartItem + $NumItemsPerPage);
}
print(" " . L_DEPT_OF . " $TotalNumber");
print("</TD><TD WIDTH=33% ALIGN=\"center\">");
/* CW - Next link if necessary */
if ($TotalNumber>($StartItem+$NumItemsPerPage) )
{
print("<A
HREF=\"".ScreenURL("department")."&department=$department&StartItem=" .
($StartItem+$NumItemsPerPage) . "\">" . L_DEPT_NEXT . "></a>");
}
print("</TD></TR>\n</TABLE>\n<BR>\n");
}
//show sub-departments
$Query = "SELECT ID, Name, Graphic, Description ";
$Query .= "FROM department ";
$Query .= "WHERE Parent=$department ";
$Query .= "ORDER BY DisplayPrecedence, Name ";
$DatabaseResult = mysql_query($Query, $DatabaseLink);
while($DatabaseRow = mysql_fetch_object($DatabaseResult))
{
print("<A HREF=\"" . ScreenURL("department") .
"&department=$DatabaseRow->ID\">");
if(($DatabaseRow->Graphic) AND file_exists(DEPARTMENT_IMAGES_DIR . '/' .
$DatabaseRow->Graphic))
{
$department_Graphic_size = getimagesize(DEPARTMENT_IMAGES_DIR . '/' .
$DatabaseRow->Graphic);
print("<IMG SRC=\"" . DEPARTMENT_IMAGES_DIR . '/' . $DatabaseRow->Graphic .
"\"");
print(" $department_Graphic_size[3] BORDER=\"0\" ALIGN=\"left\">\n");
}
print("<B>$DatabaseRow->Name</B>");
print("</A>");
print("<BR>\n");
print($DatabaseRow->Description);
print("<BR CLEAR=\"all\">\n");
print("<BR>\n");
} // while
//show items in this department
// Change the Rows and Cols variables to create any size table you desire
$Rows = 3;
$Cols = 3;
$NumItemsPerPage = $Rows * $Cols;
if (!isset($StartItem)) {
$StartItem = 0;
}
/* CW - made 3 query strings so I didn't have to rewrite the query for the
count :) */
$Query = "SELECT i.ID, i.Name, i.Thumbnail ";
$Query2 = "FROM item i, department_item di ";
$Query2 .= "WHERE di.Department = $department ";
$Query2 .= "AND i.ID = di.Item ";
$Query3 = "GROUP BY i.ID ORDER BY i.DisplayPrecedence, i.Name ";
/* CW - add limit */
$Query3 .= "LIMIT $StartItem, $NumItemsPerPage";
$DatabaseResult = mysql_query($Query . $Query2 . $Query3,$DatabaseLink);
/* CW - If there are no items in this department don't bother */
if (mysql_numrows($DatabaseResult)>0)
{
/* CW - get total number of items */
$Query = "SELECT count(i.ID) AS Number " . $Query2;
$Result = mysql_query($Query, $DatabaseLink);
$TotalNumber = mysql_result($Result, 0, "Number");
/* CW - print Numbers, and Next/Prev Links */
printPrevNext($StartItem,$NumItemsPerPage,$TotalNumber,$department);
/* CW - print a table rows x cols */
print("<TABLE ROWS=$Rows COLS=$Cols WIDTH=100% BORDER=0>\n");
$i = 1;
/* The ideal way to get the min list,min sale, and count would be to
denormalize the
** item table and add these three columns. Then, we could use a
trigger,stored proc, or
** PHP code to keep these fields up to date on INSERT,DELETE,UPDATE on
SKU. The following
** code was the proverbial quick and dirty solution. It doesn't seem
unbearably slow.
** If you go with the code below, you may want to create indexes on
listprice
** and sale price.
*/
while($DatabaseRow = mysql_fetch_object($DatabaseResult))
{
/* mjp - get total number of skus for this item */
$SKUQuery = "SELECT count(*) AS NumberSKU FROM sku s WHERE s.Item =
$DatabaseRow->ID";
$SKUResult = mysql_query($SKUQuery, $DatabaseLink);
$TotalSKU = mysql_result($SKUResult, 0, 0);
/* mjp - get minimum sku sale price for this item */
$SaleQuery = "SELECT MIN(SalePrice) as sale FROM sku s WHERE s.Item =
$DatabaseRow->ID ";
$SaleQuery .= "AND SalePrice > 0";
$SaleResult = mysql_query($SaleQuery, $DatabaseLink);
$MinSale = mysql_result($SaleResult, 0, 0);
/* mjp - get minimum sku list price for this item */
$ListQuery = "SELECT MIN(ListPrice) as list FROM sku s WHERE s.Item =
$DatabaseRow->ID ";
$ListResult = mysql_query($ListQuery, $DatabaseLink);
$MinList = mysql_result($ListResult, 0, 0);
if (($i % $Cols) == 1 )
{
/* CW - first item, left columm, start a new row */
print("\t<TR>\n");
}
print("\t\t<TD VALIGN=\"top\">");
print("<A HREF=\"".ScreenURL("item")."&item=$DatabaseRow->ID\">");
if(($DatabaseRow->Thumbnail) AND file_exists(ITEM_IMAGES_DIR . "/" .
$DatabaseRow->Thumbnail))
{
$Thumbnail_Graphic_size = getimagesize(ITEM_IMAGES_DIR . "/" .
$DatabaseRow->Thumbnail);
print("<IMG SRC=\"" . ITEM_IMAGES_DIR . "/"
.$DatabaseRow->Thumbnail . "\"");
print(" $Thumbnail_Graphic_size[3] BORDER=\"0\" ALIGN=\"left\"
hspace=\"0\">\n");
}
else
{
/* CW - print NoImage holder */
print("<IMG SRC=\"" . ITEM_IMAGES_DIR ."/NoImage.gif\" WIDTH=100
HEIGHT=99 BORDER=0 ALIGN=\"left\">\n");
}
print("<B>$DatabaseRow->Name</b><br>");
/* Print price info for item (i.e., it's sku(s)). If there is a
sale, list the normal
** price then give the sale price. If there is more than one sku,
use the wording "from..."
*/
if ($TotalSKU > 1)
$Prefix = L_DEPT_MORETHAN1 ;
else
$Prefix = L_DEPT_LOWPRICE ;
if (!empty($MinSale))
{
print("$Prefix <strike>" . formatMoney($MinList) .
"</strike><br>");
print(L_DEPT_SALE . " $Prefix " . formatMoney($MinSale));
}
else
print("$Prefix " . formatMoney($MinList));
print("</A>");
print("<BR CLEAR=\"all\">\n");
print("<BR>\n");
print("</TD>\n");
if (($i % $Cols) == 0 )
{
/* CW - even-numbered item, right columm, end row */
print("\t</TR>\n");
}
$i++;
} //end while
print("</TABLE>");
printPrevNext($StartItem,$NumItemsPerPage,$TotalNumber,$department);
} //end if
?>
------------------------------------------------------------
To subscribe: [EMAIL PROTECTED]
To unsubscribe: [EMAIL PROTECTED]
Site: http://www.working-dogs.com/freetrade/
Problems?: [EMAIL PROTECTED]
