How to Organize a PHP/MySQL List by State

May 3, 2008

I have a list here in static HTML.

http://www.donjo.net/where_to_buy.html

I have worked on being able to insert store, address, state, URL, phone into a database, however, I am having problems organizing it so that it seperates the stores in the database by State and listing them as in the static version.

Thanks

Adam

lokie538 · May 3, 2008

http://www.w3schools.com/php/php_mysql_order_by.asp

May 3, 2008

This is the page for : http://www.donjo.net/test/

If someone could tell me how to list the states only once per state that would help greatly.

<?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:[email protected]" class="hlink">[email protected]</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>

trq · May 3, 2008

We have

tags for posting code, please use them.

To answer your question, you'll want to use a GROUP BY clause.

SELECT * FROM storeRetailers GROUP BY state ORDER BY state ASC

May 3, 2008

Sorry about that, never posted code before.

Adam

May 3, 2008

Is there something that I have to change in the body of the html. When I add

SELECT * FROM storeRetailers GROUP BY state ORDER BY state ASC

it only shows on store per state.

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

May 3, 2008

Similar to how this site lists its stores:

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

Sign In

How to Organize a PHP/MySQL List by State

Recommended Posts

1219

Link to comment

Share on other sites

lokie538

Link to comment

Share on other sites

1219

Link to comment

Share on other sites

trq

Link to comment

Share on other sites

1219

Link to comment

Share on other sites

1219

Link to comment

Share on other sites

1219

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information