PDA

View Full Version : where to insert "SELECT DISTINCT" query


confucious
09-02-2008, 04:02 PM
Wondering if anyone can help me with the proper placement of a SELECT DISTINCT query. I have a "Master" page with repeating regions that is repeating ALL the records in my recordset. I want to make it skip all the records that have the same 'itemcode' (column) value. It seems like a simple enough task, but exactly where to insert it is a bit of a mystery to me. Any help would be greatly appreciated! I would imagine the select query would just be SELECT DISTINCT itemcode FROM 'jewelleryrings' Where itemcode is the field and jewelleryrings is the table. The code is:


<?php
$rsRings_endRow = 0;
$rsRings_columns = 4; // number of columns
$rsRings_hloopRow1 = 0; // first row flag
do {
if($rsRings_endRow == 0 && $rsRings_hloopRow1++ != 0) echo "<tr>";
?>
<td><table width="145" class="repeatingtablestyle">
<tr>
<th height="120" scope="col"><?php echo $row_rsRings['thumbnails'];?> </th>
</tr>
<tr>
<td><a href="productpage.php?<?php echo $MM_keepNone.(($MM_keepNone!="")?"&":"")."category=".urlencode($row_rsRings['category']) ?>"><?php echo $row_rsRings['product name']; ?></a></td>
</tr>
<tr>
<td><?php echo $row_rsRings['price']; ?></td>
</tr>
<tr>
<td><?php echo $row_rsRings['itemcode']; ?></td>
</tr>
</table></td>
<?php $rsRings_endRow++;
if($rsRings_endRow >= $rsRings_columns) {
?>
</tr>
<?php
$rsRings_endRow = 0;
}
} while ($row_rsRings = mysql_fetch_assoc($rsRings));
if($rsRings_endRow != 0) {
while ($rsRings_endRow < $rsRings_columns) {
echo("<td>&nbsp;</td>");
$rsRings_endRow++;
}
echo("</tr>");
}?>
</table><p align="center">&nbsp;

Records <?php echo ($startRow_rsRings + 1) ?> to <?php echo min($startRow_rsRings + $maxRows_rsRings, $totalRows_rsRings) ?> of <?php echo $totalRows_rsRings ?>
<table width="244" border="0" align="center">

THanks for looking!

davidj
09-02-2008, 06:38 PM
you need to post all the code

confucious
09-02-2008, 06:52 PM
After a bit more research, perhaps an 'if' statement would be more the way to go? Something like along the lines of <?php if (row_rsRings['itemcode'] == ['itemcode']) {omit duplicate records}?> ???

Here is the rest of the code:

<?php require_once('Connections/connburapa.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}

$currentPage = $_SERVER["PHP_SELF"];

$maxRows_rsRings = 8;
$pageNum_rsRings = 0;
if (isset($_GET['pageNum_rsRings'])) {
$pageNum_rsRings = $_GET['pageNum_rsRings'];
}
$startRow_rsRings = $pageNum_rsRings * $maxRows_rsRings;

mysql_select_db($database_connburapa, $connburapa);
$query_rsRings = "SELECT category, itemcode, colour, `size`, `description`, `shipping code`, `product name`, price, origin, thumbnails, `full images` FROM jewelleryrings ORDER BY itemcode ASC";
$query_limit_rsRings = sprintf("%s LIMIT %d, %d", $query_rsRings, $startRow_rsRings, $maxRows_rsRings);
$rsRings = mysql_query($query_limit_rsRings, $connburapa) or die(mysql_error());
$row_rsRings = mysql_fetch_assoc($rsRings);

if (isset($_GET['totalRows_rsRings'])) {
$totalRows_rsRings = $_GET['totalRows_rsRings'];
} else {
$all_rsRings = mysql_query($query_rsRings);
$totalRows_rsRings = mysql_num_rows($all_rsRings);
}
$totalPages_rsRings = ceil($totalRows_rsRings/$maxRows_rsRings)-1;

$queryString_rsRings = "";
if (!empty($_SERVER['QUERY_STRING'])) {
$params = explode("&", $_SERVER['QUERY_STRING']);
$newParams = array();
foreach ($params as $param) {
if (stristr($param, "pageNum_rsRings") == false &&
stristr($param, "totalRows_rsRings") == false) {
array_push($newParams, $param);
}
}
if (count($newParams) != 0) {
$queryString_rsRings = "&" . htmlentities(implode("&", $newParams));
}
}
$queryString_rsRings = sprintf("&totalRows_rsRings=%d%s", $totalRows_rsRings, $queryString_rsRings);

$MM_paramName = "";

// *** Go To Record and Move To Record: create strings for maintaining URL and Form parameters
// create the list of parameters which should not be maintained
$MM_removeList = "&index=";
if ($MM_paramName != "") $MM_removeList .= "&".strtolower($MM_paramName)."=";
$MM_keepURL="";
$MM_keepForm="";
$MM_keepBoth="";
$MM_keepNone="";
// add the URL parameters to the MM_keepURL string
reset ($_GET);
while (list ($key, $val) = each ($_GET)) {
$nextItem = "&".strtolower($key)."=";
if (!stristr($MM_removeList, $nextItem)) {
$MM_keepURL .= "&".$key."=".urlencode($val);
}
}
// add the Form parameters to the MM_keepURL string
if(isset($_POST)){
reset ($_POST);
while (list ($key, $val) = each ($_POST)) {
$nextItem = "&".strtolower($key)."=";
if (!stristr($MM_removeList, $nextItem)) {
$MM_keepForm .= "&".$key."=".urlencode($val);
}
}
}
// create the Form + URL string and remove the intial '&' from each of the strings
$MM_keepBoth = $MM_keepURL."&".$MM_keepForm;
if (strlen($MM_keepBoth) > 0) $MM_keepBoth = substr($MM_keepBoth, 1);
if (strlen($MM_keepURL) > 0) $MM_keepURL = substr($MM_keepURL, 1);
if (strlen($MM_keepForm) > 0) $MM_keepForm = substr($MM_keepForm, 1);
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<title>Untitled Document</title>

<link href="cssstyles/productlisttext.css" rel="stylesheet" type="text/css" />

<link href="cssstyles/productlinepagetwocolumn.css" rel="stylesheet" type="text/css" />
<!--[if IE 5]>
<style type="text/css">
/* place css box model fixes for IE 5* in this conditional comment */
.twoColFixLtHdr #sidebar1 { width: 230px; }
</style>
<![endif]--><!--[if IE]>
<style type="text/css">
/* place css fixes for all versions of IE in this conditional comment */
.twoColFixLtHdr #sidebar1 { padding-top: 30px; }
.twoColFixLtHdr #mainContent { zoom: 1; }
/* the above proprietary zoom property gives IE the hasLayout it needs to avoid several bugs */
</style>
<![endif]-->
<link href="spryassets/SpryMenuBarHorizontal.css" rel="stylesheet" type="text/css" />
<link href="spryassets/SpryMenuBarVertical.css" rel="stylesheet" type="text/css" />
<link href="cssstyles/greytextcentered.css" rel="stylesheet" type="text/css" />
<script src="SpryAssets/SpryMenuBar.js" type="text/javascript"></script>

<style type="text/css">
<!--
body {
background-color: #660066;
}
-->
</style>

<link href="cssstyles/papyrustext.css" rel="stylesheet" type="text/css" />
<link href="cssstyles/productlinepagefooter.css" rel="stylesheet" type="text/css" />
<link href="cssstyles/repeatingtablestyle.css" rel="stylesheet" type="text/css" />
</head>

<body class="twoColFixLtHdr">
Detail
<div id="container">
<div id="header">
<table width="975" height="83">
<tr class="greytextcentered">
<th width="271" height="79" align="left" scope="col"><img src="root folder/images/headerandfooter/burapalogo186x90.jpg" width="186" height="90" alt="logo" /></th>
<th width="359" scope="col"><span class="greytextcentered">B<span class="style3">urapa imports only the finest home and personal style products from Thailand, Laos, Nepal, &amp; Tibet</span></span></th>
<th width="71" scope="col">&nbsp;</th>
<th width="131" scope="col"><p align="center"><img src="root folder/images/headerandfooter/45x39transparent.gif" width="45" height="39" alt="shopping" /></p>
<p align="left" class="greytextcentered">my shopping bag</p></th>
<th width="119" valign="top" scope="col"><p><img src="root folder/images/headerandfooter/planesmallergreenback.jpg" width="50" height="37" alt="shipping" /></p>
<p class="greytextcentered style1">shipping faqs</p></th>
</tr>
</table>
<ul id="MenuBar1" class="MenuBarHorizontal">
<li><a href="index.html">home</a></li>
<li><a class="MenuBarItemSubmenu" href="#">products</a>
<ul>
<li><a href="productpageshtml/antiques/antiquesproductpage.html">antiques</a></li>
<li><a href="productpageshtml/artcarvingspaintings/artproductpage.html">art, carvings, prints</a></li>
<li><a href="productpageshtml/furniture/furnitureone.html">furniture</a></li>
<li><a href="productpageshtml/gifts/giftproductlinepage.html">gifts</a></li>
<li><a href="productpageshtml/homestyle/homestyleproductpage.html">home style</a></li>
<li><a href="productpageshtml/jewellery/jewelleryproductlinepage.html">jewellery</a></li>
<li><a href="productpageshtml/pashmina/pashminaproductspage.html">pashmina/cashmere</a></li>
<li><a href="productpageshtml/silk/silkproductlinepage.htm">silk</a></li>
</ul>
</li>
<li><a href="#">testimonials</a></li>
<li><a href="#">wholesale</a> </li>
<li><a href="#">about us</a></li>
</ul>
<p>&nbsp;</p>
<!-- end #header --></div>

<div id="sidebar1">
<h1 class="papyrustext">pashmina</h1>
<ul id="MenuBar2" class="MenuBarVertical">
<li><a href="#">__________</a></li>
<li><a href="#" class="MenuBarItemSubmenu">scarves</a>
<ul>
<li><a href="#">70 / 30% blend</a></li>
<li><a href="#">100% </a></li>
<li><a href="#">other</a></li>
</ul>
</li>
<li><a href="#" class="MenuBarItemSubmenu">stoles</a>
<ul>
<li><a href="#">70 / 30% blend</a></li>
<li><a href="#">100%</a></li>
<li><a href="#">other</a></li>
</ul>
</li>
<li><a href="#" class="MenuBarItemSubmenu">shawls</a>
<ul>
<li><a href="#">70 / 30% blend</a></li>
<li><a href="#">100%</a></li>
<li><a href="#">other</a></li>
</ul>
</li>
<li><a href="#">gloves</a> </li>
<li><a href="#">__________</a></li>
</ul>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<!-- end #sidebar1 -->
</div>
<div id="mainContent">
<p>&nbsp;</p>
<table >
<tr>

<?php
$rsRings_endRow = 0;
$rsRings_columns = 4; // number of columns
$rsRings_hloopRow1 = 0; // first row flag
do {
if($rsRings_endRow == 0 && $rsRings_hloopRow1++ != 0) echo "<tr>";
?>
<td><table width="145" class="repeatingtablestyle">
<tr>
<th height="120" scope="col"><?php echo $row_rsRings['thumbnails'];?> </th>
</tr>
<tr>
<td><a href="productpage.php?<?php echo $MM_keepNone.(($MM_keepNone!="")?"&":"")."category=".urlencode($row_rsRings['category']) ?>"><?php echo $row_rsRings['product name']; ?></a></td>
</tr>
<tr>
<td><?php echo $row_rsRings['price']; ?></td>
</tr>
<tr>
<td><?php echo $row_rsRings['itemcode']; ?></td>
</tr>
</table></td>
<?php $rsRings_endRow++;
if($rsRings_endRow >= $rsRings_columns) {
?>
</tr>
<?php
$rsRings_endRow = 0;
}
} while ($row_rsRings = mysql_fetch_assoc($rsRings));
if($rsRings_endRow != 0) {
while ($rsRings_endRow < $rsRings_columns) {
echo("<td>&nbsp;</td>");
$rsRings_endRow++;
}
echo("</tr>");
}?>
</table><p align="center">&nbsp;

Records <?php echo ($startRow_rsRings + 1) ?> to <?php echo min($startRow_rsRings + $maxRows_rsRings, $totalRows_rsRings) ?> of <?php echo $totalRows_rsRings ?>
<table width="244" border="0" align="center">
<tr>
<td width="30"><?php if ($pageNum_rsRings > 0) { // Show if not first page ?>
<a href="<?php printf("%s?pageNum_rsRings=%d%s", $currentPage, 0, $queryString_rsRings); ?>"><img src="First.gif" border="0" /></a>
<?php } // Show if not first page ?>
</td>
<td width="28"><?php if ($pageNum_rsRings > 0) { // Show if not first page ?>
<a href="<?php printf("%s?pageNum_rsRings=%d%s", $currentPage, max(0, $pageNum_rsRings - 1), $queryString_rsRings); ?>"><img src="Previous.gif" border="0" /></a>
<?php } // Show if not first page ?>
</td>
<td width="47"><?php if ($pageNum_rsRings < $totalPages_rsRings) { // Show if not last page ?>
<a href="<?php printf("%s?pageNum_rsRings=%d%s", $currentPage, min($totalPages_rsRings, $pageNum_rsRings + 1), $queryString_rsRings); ?>"><img src="Next.gif" border="0" /></a>
<?php } // Show if not last page ?>
</td>
<td width="121"><?php if ($pageNum_rsRings < $totalPages_rsRings) { // Show if not last page ?>
<a href="<?php printf("%s?pageNum_rsRings=%d%s", $currentPage, $totalPages_rsRings, $queryString_rsRings); ?>"><img src="Last.gif" border="0" /></a>
<?php } // Show if not last page ?>
</td>
</tr>
</table>
</p>
<p>&nbsp;</p>
<p><br />
</p>
<!-- end #mainContent -->
</div>

<!-- This clearing element should immediately follow the #mainContent div in order to force the #container div to contain all child floats --><br class="clearfloat" />
<div class="productlinepagefooter" id="footer">
<p>copyright 2008 Burapa Asian Perspective&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;contact&nbsp;&nbsp;<img src="root folder/images/headerandfooter/Mail1.gif" alt="contact" width="38" height="23" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;phone: 613-274-0963</p>
<!-- end #footer --></div>
<!-- end #container --></div>
<script type="text/javascript">
<!--
var MenuBar1 = new Spry.Widget.MenuBar("MenuBar1", {imgDown:"../SpryAssets/SpryMenuBarDownHover.gif", imgRight:"../SpryAssets/SpryMenuBarRightHover.gif"});
var MenuBar2 = new Spry.Widget.MenuBar("MenuBar2", {imgRight:"../SpryAssets/SpryMenuBarRightHover.gif"});
//-->
</script>
</body>
</html>
<?php
mysql_free_result($rsRings);
?>

davidj
09-02-2008, 08:27 PM
edit that post and wrap the code in PHP ( code ) tags

[[[[foot tapping !]]]]]

amanda
09-15-2008, 11:35 AM
Thank you for a problem report. Please, try to repeat with a newer version, 5.0.27
and inform about the results.