Grouping states into territories

[11InchH4mm3r]

I could use some help with this problem. I've been searching for a couple of days and not had any luck finding something similar:

 

I'd like to create sales territories based on the states that the leads are in.

for example:

 

$southwest = CA, NV, UT, CO, AZ and NM

 

so that I can place a simple drop down with the territories for pulling specific reports.

 

If someone could give me a good starting point or even a well thought out hint, that would be very appreciated.

 

Thanks

 

[gwolgamott]

You could put them in arrays according to their territorial area. Then you could foreach loop through the array when building the drop down to display.

 

$southwest = array("CA", "NV", "UT", "CO", "AZ", "NM");

echo '<select name="southwest"> ';
foreach ($southwest as $value)
{
echo '<option value="'.$value.'">'.$value.'</option>';
}
echo '</select> ';

[11InchH4mm3r]

gwolgamott,

 

thanks for the reply; one follow-up question:

 

Can I sort them inside the code like so?-->(below) ...and then if I try to use the territories (instead) of states as a selectable variable for my query string, would '".$row['territories']."' be th equivalent of listing all of those particular states?

 

 

$states = array(
   'AL'=>'Alabama',
   'AK'=>'Alaska',
   'AZ'=>'Arizona',
   'AR'=>'Arkansas',
   'CA'=>'California',
   'CO'=>'Colorado',
   'CT'=>'Connecticut',
   'DE'=>'Delaware',
   'DC'=>'District of Columbia',
   'FL'=>'Florida',
   'GA'=>'Georgia',
   'HI'=>'Hawaii',
   'ID'=>'Idaho',
   'IL'=>'Illinois',
   'IN'=>'Indiana',
   'IA'=>'Iowa',
   'KS'=>'Kansas',
   'KY'=>'Kentucky',
   'LA'=>'Louisiana',
   'ME'=>'Maine',
   'MD'=>'Maryland',
   'MA'=>'Massachusetts',
   'MI'=>'Michigan',
   'MN'=>'Minnesota',
   'MS'=>'Mississippi',
   'MO'=>'Missouri',
   'MT'=>'Montana',
   'NE'=>'Nebraska',
   'NV'=>'Nevada',
   'NH'=>'New Hampshire',
   'NJ'=>'New Jersey',
   'NM'=>'New Mexico',
   'NY'=>'New York',
   'NC'=>'North Carolina',
   'ND'=>'North Dakota',
   'OH'=>'Ohio',
   'OK'=>'Oklahoma',
   'OR'=>'Oregon',
   'PA'=>'Pennsylvania',
   'RI'=>'Rhode Island',
   'SC'=>'South Carolina',
   'SD'=>'South Dakota',
   'TN'=>'Tennessee',
   'TX'=>'Texas',
   'UT'=>'Utah',
   'VT'=>'Vermont',
   'VA'=>'Virginia',
   'WA'=>'Washington',
   'WV'=>'West Virginia',
   'WI'=>'Wisconsin',
   'WY'=>'Wyoming'
);

$territories = array (
'CA','NV','UT','CO','AZ','NM','AK'=>'southwest',		
'WA','OR','ID','MT','WY','ND','SD','NE','KS','MO','IA','WI','IL','IN','OH','MI'=>'northern',
'TX','OK','AR','LA','MS','TN','GA','FL','WV','VA','NC','SC','AL','HI'=>'southeast',
'PA','MD','DE','NJ','NY','CT','ME','VT','NH','RI','MA'=>'northeast',

 

[gwolgamott]

I think so, not that off top of my head do I know how that would work though.

[Maq]

Why don't you use a database?  That way, it's easier to maintain and is more flexible for adding additional information, instead of hard-coding this information.

[11InchH4mm3r]

MAQ,

 

Here's what I have and what I'm trying to accomplish:

 

I have a database called leads that contains contact info including states (as CA, OH, FL, etc)

 

I am creating a report management page where the user will be able to select a group of leads by states; for example-> ($southwest = array("CA", "NV", "UT", "CO", "AZ", "NM");

 

The first report allows the user to choose which tradeshows have been attended, I want report 2 and three to be able to choose the existing query (+) chose by territory.

 

So I'm going to have a simple dropdown menu that will have southwest, southeast,northwest, northeast and their choice will insert that list of states into my query, so they are in effect selecting a bunch of states at once.

 

THIS is where I'm stuck, I don't know how to move those multiple state choices from the dropdown back up into the query string

 

the states ARE part of lead, but territories are not, so I am trying to establish those territories, then insert them.

 

here's the page code at this point

<?php

require('../includes/init.php');

$sql = "SELECT DISTINCT source FROM leads ORDER BY source ASC";

$results = mysql_query($sql);

while($row = mysql_fetch_array($results))
{
  $sources[]=$row['source'];
}

// Find leads from 3 or more tradeshows that have not become customers
	$query = "SELECT *, COUNT(source) AS NumOccurences FROM leads WHERE email!='' AND source !='Sales Calls' AND source !='Inquiry' AND source !='Leashables Tour' AND email NOT LIKE '%noemail%' GROUP BY email, first_name HAVING (COUNT(source) > 2 ) ORDER BY NumOccurences DESC";
	$data = $db->execute($query);
	foreach($data->results as $row) { 
		$query = "SELECT * FROM customers_insynch WHERE customers_email_address = '".$row['email']."' LIMIT 1";
		$data_leads = $db->execute($query);
		if($data_leads->recordCount == 0) {
			$goodleads3[] = $row;
		}
	}

//REPORT 2 RESULTS
//Start the query with a date range of 1 year
$query = "SELECT leads.*, lead_comments.comment, MAX(lead_comments.date) AS date, FROM_UNIXTIME(MAX(inserted_timestamp), '%m-%d-%Y') AS `inserted_date`, COUNT(source) AS NumOccurrences 
	  FROM leads
	  JOIN lead_comments ON leads.id = lead_comments.lid
	  WHERE source != 'Sales Call' AND source != 'Leashables.com' AND source != 'Tradeshows:'
	  AND email!=''
	  AND inserted_timestamp >= (UNIX_TIMESTAMP()-31536000) 
	  AND LOCATE('Received the `',comment)=0 AND ('Order #')=0
	  GROUP BY email HAVING ( COUNT(source) > 1 ) 
	  ORDER BY inserted_timestamp DESC";
$result = mysql_query( $query );

while ($row = mysql_fetch_assoc($result)) {
//Qualify the lead
$timediff = mktime() - $row['date'];
$days = ceil($timediff/86400);
if($days>182) {
	$query = "SELECT CustomerNo FROM customers_insynch WHERE customers_email_address = '".$row['email']."'";
	$cust_data = mysql_query( $query );
	$data = mysql_fetch_assoc( $cust_data );
	// store customer number in $row array
	$row['CustomerNo'] = $data['CustomerNo'];
	$row['lastleadactivity'] = $days;
	$goodleads2[] = $row;
}
}

//Check results against order history
$db->select_db('oralabs_insynch');

//Select lead saleshistory
foreach($goodleads2 as $key=>$row) {
$query = "SELECT OrderDate FROM FromMAS_SO_SalesOrderHistoryHeader WHERE CustomerNo='".$row['CustomerNo']."' ORDER BY OrderDate DESC LIMIT 1";
$salesheader = $db->execute($query);
$year = substr($salesheader->results[0]['OrderDate'], 0, 4);  // returns "year value"
$month = substr($salesheader->results[0]['OrderDate'], 4, 2);  // returns "month value"
$day = substr($salesheader->results[0]['OrderDate'], 6, 2);  // returns "day value"	
$date = strtotime($year ."-" . $month ."-" . $day);
$row['order_date'] = $date;
if ($date < (mktime() - 15768000)) {
	$goodleads2[] = $row;
}	
}

$db->select_db('oralabs_dev');
//REPORT 1 RESULTS
// Select leashables customers from selected tradeshow
$source = $_GET["source"];

//Only run this query if a source has been selected
if (isset($source)) {
$query = "SELECT ci.customerNo, ci.contactCode, l.first_name, l.last_name, l.company, l.source, ci.customers_telephone, ci.customers_email_address FROM leads l, customers_insynch ci WHERE l.email = ci.customers_email_address AND ci.customers_email_address!='' AND source='".$source."' ";
$data1 = $db->execute($query);
// Select lead history
foreach($data1->results as $row) {
// Qualify if lead is good
$goodlead = false;
$query = "SELECT * FROM leads l, lead_comments lc WHERE l.id=lc.lid AND email='".$row['customers_email_address']."' AND LOCATE('Received the `',comment)=0 LIMIT 1";
$data_leads = $db->execute($query);
if($data_leads->recordCount > 0) {
	$timediff = mktime() - $data_leads->results['date'];
	$days = timediff/86400;
	if($days>60) {
		$goodlead = true;
	}
} else {
	$goodlead = true;
}
// Save good leads
if($goodlead) {
	$goodleads[] = $row;	
}
}
//Check results against order history
$db->select_db('oralabs_insynch');

//Select lead saleshistory
foreach($goodleads as $key=>$row) {
$query = "SELECT OrderDate FROM FromMAS_SO_SalesOrderHistoryHeader WHERE CustomerNo='".$row['customerNo']."' LIMIT 1";
$salesheader = $db->execute($query);
$year = substr($salesheader->results[0]['OrderDate'], 0, 4);  // returns "year value"
$month = substr($salesheader->results[0]['OrderDate'], 4, 2);  // returns "month value"
$day = substr($salesheader->results[0]['OrderDate'], 6, 2);  // returns "day value"	
$date = strtotime($year ."-" . $month ."-" . $day);
$row['order_date'] = $date;
if ($date < (mktime() - 31536000)) {
	$goodleads_final[] = $row;
}	
}
}
?>
<!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>Reports...Woot Woot!</title>
<script type="text/javascript" src="http://use.typekit.com/mwk6ikn.js"></script>
<script type="text/javascript">try{Typekit.load();}catch(e){}</script>
<link rel="stylesheet" href="../reports/reports.css" type="text/css" media="screen" />
<link href=' http://fonts.googleapis.com/css?family=Tangerine:regular,bold' rel='stylesheet' type='text/css'>
</head>
<body>
<div class="highlight-middle">
  <div class="shadow-middle"></div>
</div>
<div id="title">
  <div class="wrapper">
    <hgroup id="introduction">
      <h2><img src="http://leashables.com/images/headerlogo.png" /></h2>
      <h1>reports</h1>
    </hgroup>
  </div>
</div>
<!-- Content -->
<div class="content">
  <div class="container">
    <div class="highlight-top">
      <div class="shadow-top"></div>
    </div>
    <div class="wrapper clearfix">
      <!-- top box 1 -->
      <div class="service-box">
        <h2>Report #1</h2>
        <h3>Customers who visited <?php echo $source; ?>, but have not made a purchase in the last year</h3>
        <P>Select one or more shows to search:
          <select class="widthed" name="source" onchange="window.location='reports-layout.php?source='+this.value";">
            <option selected value=''>Select a Trade Show</option>
            <?php 
			foreach ($sources as $source){	
		?>
            <option><?php echo $source; ?></option>
            <?php } 
		?>
          </select>
        </P>
      </div>
      <!-- top box 2 -->
      <div class="service-box">
        <h2>Report #2</h2>
        <h3>Leads that have been to 2 or more shows but have not placed any orders within the last year</h3>
      </div>
      <!--top box 3-->
      <div class="service-box last">
        <h2>Report #3</h2>
        <h3>Leads from 3 or more tradeshows that have not become customers</h3>
      </div>
      <!--bottom box 1-->
      <div class="bottom-service-box">
        <?php
					if (isset($_GET['source'])) {
				?>
        <p>This report has <?php echo count($goodleads_final); ?> leads available</p>
        <?php    
					} else {
				?>
        <p>Choose a show above</p>
        <?php
					}
				?>
        <strong>
        <h3># of leads for this report
          <input type="text" size="3" value="">
          <br />
          Add This Report
          <input type="checkbox" name="report1" value="report1"  />
        </h3>
        </strong> </div>
      <!--bottom box 2-->
      <div class="bottom-service-box">
        <P>This report has <?php echo count($goodleads2); ?> leads available </P>
        <strong>
        <h3># of leads for this report
          <input type="text" size="3" value="">
          <br />
          Add This Report
          <input type="checkbox" name="report2" value="report2"  />
        </h3>
        </strong> </div>
      <!--bottom box 3-->
      <div class="bottom-service-box last">
        <p>This report has <?php echo count($goodleads3); ?> leads available </p>
        <strong>
        <h3># of leads for this report
          <input type="text" size="3" value="">
          <br />
          Add This Report
          <input type="checkbox" name="report3" value="report3"  />
        </h3>
        </strong> </div>

<!-- Big 'Ol Button -->
    <div class="contact-box">CREATE REPORT</div>
    </div>
    <div class="highlight-bottom">
      <div class="shadow-bottom"></div>
    </div>
  </div>
</body>
</html>

 

[Maq]

I'm suggesting having 2 tables:

 

state_info
abbrev  name  territory

 

territory_info
abbrev  territory

 

so in your drop down list you will query the territory_info table and have a list of the 'abbrev's.  Then when you want to find the states you join the tables and grab the states with that territory's 'abbrev'.