How to use GROUP BY or other solution to MySQL list

May 4, 2008

I have this page:

http://www.donjo.net/test/

And I want to list stores by State, but only show the states once per.

Similar to this site

http://www.innerasiarugs.com/storelocator/print.php

Here is the page code:

<?php require_once('config.php'); ?>
<?php
$currentPage = $_SERVER["PHP_SELF"];

$maxRows_StoresDb = 200;
$pageNum_StoresDb = 0;
if (isset($_GET['pageNum_StoresDb'])) {
  $pageNum_StoresDb = $_GET['pageNum_StoresDb'];
}
$startRow_StoresDb = $pageNum_StoresDb * $maxRows_StoresDb;

mysql_select_db($database_DonjoDesigns, $DonjoDesigns);
$query_StoresDb = "SELECT * FROM storeRetailers ORDER BY state ASC";
$query_limit_StoresDb = sprintf("%s LIMIT %d, %d", $query_StoresDb, $startRow_StoresDb, $maxRows_StoresDb);
$StoresDb = mysql_query($query_limit_StoresDb, $DonjoDesigns) or die(mysql_error());
$row_StoresDb = mysql_fetch_assoc($StoresDb);

if (isset($_GET['totalRows_StoresDb'])) {
  $totalRows_StoresDb = $_GET['totalRows_StoresDb'];
} else {
  $all_StoresDb = mysql_query($query_StoresDb);
  $totalRows_StoresDb = mysql_num_rows($all_StoresDb);
}
$totalPages_StoresDb = ceil($totalRows_StoresDb/$maxRows_StoresDb)-1;

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






<!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=iso-8859-1"><title>Welcome to Donjo Designs : Where to Buy</title>

<style type="text/css">
<!--
body {
margin-left: 0px;
margin-top: 0px;
margin-right: 0px;
margin-bottom: 0px;
background-repeat: repeat;
}
-->
</style>
<link href="http://www.donjo.net/css/donjo.css" rel="stylesheet" type="text/css">
<script type="text/javascript">
<!--
function MM_swapImgRestore() { //v3.0
  var i,x,a=document.MM_sr; for(i=0;a&&i<a.length&&(x=a[i])&&x.oSrc;i++) x.src=x.oSrc;
}
//-->
</script>
</head><body text="#000000">
<table border="0" align="center" cellpadding="0" cellspacing="0">
  <tr>
    <td width="790" height="140" colspan="10" valign="top" bgcolor="#57ABD0"><div align="center"><img src="images/header.gif" usemap="#Map" border="0" height="140" width="790" /></div></td>
  </tr>
  <tr>
    
    
    <td width="71" height="19" valign="top"><a href="http://www.donjo.net/index.html" class="menulink1">Home</a></td>
    <td width="150" height="19" valign="top"><a href="http://www.donjo.net/where_to_buy.html" class="menulink2">Where
      to Buy </a></td>
    <td width="181" height="19" colspan="4" valign="top"><a href="http://www.donjo.net/about_the_artist.html" class="menulink1"> About
      the Artist </a></td>
    <td width="134" height="19" valign="top"><div align="center"><a href="http://www.donjo.net/photo_gallery.html" class="menulink1">Collection </a></div></td>
    <td width="127" height="19" valign="top"><a href="http://www.donjo.net/wholesale.php" class="menulink1">Wholesale </a></td>
    <td width="127" height="19" colspan="2" valign="top"><a href="http://www.donjo.net/contact.html" class="menulink1">Contact
      Us </a></td>
  </tr>
  
  <tr>  </tr>
  <tr>  </tr>
</table>
<map name="Map" id="Map"><area shape="rect" coords="24,87,150,124" href="http://www.donjo.net/events.html" />
<area shape="rect" coords="8,2,176,83" href="http://www.donjo.net/index.html" />
</map><p> </p>
<center>
  <table frame=VOID cellspacing=0 cols=3 rules=NONE border=0>
<colgroup><col width=236><col width=403><col width=80></colgroup>
<tbody>
	<tr>
	  <td height=21 align=CENTER valign=TOP><h3><b><font face="Times New Roman" size=3>Retail Location </font></b></h3></td>
	  <td align=CENTER valign=TOP><h3><b><font face="Times New Roman" size=3>Address</font></b></h3></td>

	  <td align=CENTER><h3><b><font face="Times New Roman" size=3>Phone</font></b></h3></td>
  </tr>
	<tr>
		<td colspan=3 height=17 align=CENTER><font face="Times New Roman"> </font></td>
  </tr>
                      <?php do { ?>

	<tr>
		<td height=19 align=CENTER valign=TOP><b><font face="Times New Roman"><?php echo $row_StoresDb['state']; ?></font></b></td>

		<td align=CENTER valign=TOP><font face="Times New Roman"> </font></td>
		<td align=CENTER><font face="Times New Roman"> </font></td>
	</tr>
	<tr>
		<td height=17 align=CENTER valign=TOP><font face="Times New Roman"><?php echo $row_StoresDb['retailer']; ?></font></td>
		<td align=CENTER valign=TOP><font face="Times New Roman"><a href="<?php echo $row_StoresDb['url']; ?>"> <?php echo $row_StoresDb['address']; ?> </a></font></td>
		<td align=CENTER><font face="Times New Roman" color="#000000"><?php echo $row_StoresDb['phone']; ?></font></td>
	</tr>
	<tr>
	  <td height=17 align=CENTER valign=TOP> </td>
	  <td align=CENTER valign=TOP> </td>
	  <td align=CENTER> </td>
  </tr>                <?php } while ($row_StoresDb = @mysql_fetch_assoc($StoresDb)); ?>

	<tr>



    <td height="95" colspan="10" valign="top"><div align="center"><span class="font">Donjo Designs<br />
      205 E. Franklin Avenue <br />
      El Segundo, CA    90245 <br />
      Tel: 310-441-2580   Fax: 310-640-2580</span><br />      
      <a href="mailto:info@donjo.net" class="hlink">info@donjo.net</a></div></td>
  </tr>
  <tr>
    
    <td height="4" colspan="2" valign="top"><img src="images/spacer.gif" height="1" width="1" /><br />      <img src="images/spacer.gif" height="1" width="1" /><img src="images/spacer.gif" height="1" width="1" /></td>
  </tr>
    <td height="38" colspan="12" valign="top"><div align="center"><span class="copyright">Copyright © 2008. Donjo Designs.<br />
      Website designed and maintained by <a href="http://www.boldnetwork.com/" target="_blank" class="link">Bold
        Network </a></span></div></td>

	</tr>
</TBODY>
</table>
</center>
<!-- ************************************************************************** -->



</body>
</html>

hitman6003 · May 4, 2008

Please provide only relevant code.

You are probably looking for a DISTINCT query...

http://www.mysql.com/distinct

But I am too lazy / don't care enough to dig through all the code to find where your query is.

May 4, 2008

Here is the code to display the table data:

                      <?php do { ?>

	<tr>
		<td height=19 align=CENTER valign=TOP><b><font face="Times New Roman"><?php echo $row_StoresDb['state']; ?></font></b></td>

		<td align=CENTER valign=TOP><font face="Times New Roman"> </font></td>
		<td align=CENTER><font face="Times New Roman"> </font></td>
	</tr>
	<tr>
		<td height=17 align=CENTER valign=TOP><font face="Times New Roman"><?php echo $row_StoresDb['retailer']; ?></font></td>
		<td align=CENTER valign=TOP><font face="Times New Roman"><a href="<?php echo $row_StoresDb['url']; ?>"> <?php echo $row_StoresDb['address']; ?> </a></font></td>
		<td align=CENTER><font face="Times New Roman" color="#000000"><?php echo $row_StoresDb['phone']; ?></font></td>
	</tr>
	<tr>
	  <td height=17 align=CENTER valign=TOP> </td>
	  <td align=CENTER valign=TOP> </td>
	  <td align=CENTER> </td>
  </tr>                <?php } while ($row_StoresDb = @mysql_fetch_assoc($StoresDb)); ?>

hitman6003 · May 4, 2008

Ahh, then I misunderstood your question...store the "current" state and check to see if it changed, then echo out the change the first time...

<?php 
$current_state = "";
$display_state = "";

do { 
if ($row_StoresDb['state'] != $current_state) {
	$current_state = $row_StoresDb['state'];
	$display_state = $current_state;
} else {
	$display_state = " ";
}
?>

	<tr>
		<td height=19 align=CENTER valign=TOP><b><font face="Times New Roman"><?php echo $display_state; ?></font></b></td>

		<td align=CENTER valign=TOP><font face="Times New Roman"> </font></td>
		<td align=CENTER><font face="Times New Roman"> </font></td>
	</tr>
	<tr>
		<td height=17 align=CENTER valign=TOP><font face="Times New Roman"><?php echo $row_StoresDb['retailer']; ?></font></td>
		<td align=CENTER valign=TOP><font face="Times New Roman"><a href="<?php echo $row_StoresDb['url']; ?>"> <?php echo $row_StoresDb['address']; ?> </a></font></td>
		<td align=CENTER><font face="Times New Roman" color="#000000"><?php echo $row_StoresDb['phone']; ?></font></td>
	</tr>
	<tr>
	  <td height=17 align=CENTER valign=TOP> </td>
	  <td align=CENTER valign=TOP> </td>
	  <td align=CENTER> </td>
  </tr>                
<?php 

} while ($row_StoresDb = @mysql_fetch_assoc($StoresDb)); ?>

sasa · May 4, 2008

try

                      <?php $curent_state =''; do {
                      	if ($row_StoresDb['state'] != $curent_state) {
                      		$curent_state = $row_StoresDb['state']; ?>


	<tr>
		<td height=19 align=CENTER valign=TOP><b><font face="Times New Roman"><?php echo $row_StoresDb['state']; ?></font></b></td>

		<td align=CENTER valign=TOP><font face="Times New Roman"> </font></td>
		<td align=CENTER><font face="Times New Roman"> </font></td>
	</tr>
				<?php } ?>
	<tr>
		<td height=17 align=CENTER valign=TOP><font face="Times New Roman"><?php echo $row_StoresDb['retailer']; ?></font></td>
		<td align=CENTER valign=TOP><font face="Times New Roman"><a href="<?php echo $row_StoresDb['url']; ?>"> <?php echo $row_StoresDb['address']; ?> </a></font></td>
		<td align=CENTER><font face="Times New Roman" color="#000000"><?php echo $row_StoresDb['phone']; ?></font></td>
	</tr>
	<tr>
	  <td height=17 align=CENTER valign=TOP> </td>
	  <td align=CENTER valign=TOP> </td>
	  <td align=CENTER> </td>
  </tr>                <?php } while ($row_StoresDb = @mysql_fetch_assoc($StoresDb)); ?>

May 4, 2008

Perfect! Thank you so much.

Sign In

How to use GROUP BY or other solution to MySQL list

Recommended Posts

1219

Link to comment

Share on other sites

hitman6003

Link to comment

Share on other sites

1219

Link to comment

Share on other sites

hitman6003

Link to comment

Share on other sites

sasa

Link to comment

Share on other sites

1219

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information