Grouping results of php query equivalent to cfoutput group attribute

Hello,

I need to rewrite an existing Coldfusion-based dynamic navigation bar into a php one. I have the query and results working, but I am unable to figure out how to group the results properly. For example, as done via the cfoutput group attribute, the results correctly display as:

Category 1
     Subcategory 1
     Subcategory 2

Category 2
    Subcategory 1
     Subcategory 2

...etc.

All that I am able to accomplish at this point, using php, is:

Category 1
     Subcategory 1

Category 1
     Subcategory 2

Category 2
     Subcategory 1

Category 2
     Subcategory 2

I am familiar with ColdFusion, but am a newbie with php, and I just cannot determine an elegant way (or any way, for that matter!) to achieve the same results with the php version, as the cf version.

My cf script looks like:

<CFQUERY NAME="getnavdropdowns" DATASOURCE="mydatabase">
      SELECT DISTINCT ComplexArea, UnitName, ComplexID, Complex_ID
      FROM masterComplex, masterunit
      WHERE ComplexArea IS NOT NULL AND masterComplex.ComplexID = masterunit.Complex_ID
      ORDER BY ComplexArea, UnitName
</CFQUERY>

<cfoutput query="getnavdropdowns" group="ComplexArea">

        <li><a href="/vacation-rentals/#LCase(REReplaceNoCase(ComplexArea, " ", "-", "ALL"))#" title="#ComplexArea#">#ComplexArea#</a>

            <ul>
                  <cfoutput><li><a href="/vacation-rentals/#LCase(REReplaceNoCase(ComplexArea, " ", "-", "ALL"))#/#LCase(REReplaceNoCase(UnitNAME, " ", "-",

"ALL"))#" title="#UnitName#">#UnitName#</a></li></cfoutput>
            </ul>
      </li>

</cfoutput>

My php script looks like:

<?php

      $DSN="mydatabase";
      $DSN_User="";
      $DSN_Passwd="";
      $sqlString = "SELECT DISTINCT ComplexArea, UnitName, ComplexID, Complex_ID
      FROM masterComplex, masterunit
      WHERE ComplexArea IS NOT NULL AND masterComplex.ComplexID = masterunit.Complex_ID
      ORDER BY ComplexArea, UnitName";
      $Conn      = odbc_connect( $DSN, $DSN_User, $DSN_Passwd);
      $rsResult = odbc_exec($Conn, $sqlString);

  while($row = odbc_fetch_row( $rsResult ) )
  {
        $ComplexArea  = odbc_result($rsResult,"ComplexArea");
      $ComplexAreaLower = strtolower($ComplexArea);
        $UnitName  = odbc_result($rsResult,"UnitName");
      $UnitNameLower = strtolower($UnitName);
        $ComplexID  = odbc_result($rsResult,"ComplexID");
        $Complex_ID  = odbc_result($rsResult,"Complex_ID");

        echo "<li><a href=\"/vacation-rentals/" . str_replace(" ", "-", $ComplexAreaLower) . "\" title=\"$ComplexArea\">$ComplexArea</a>";
        echo "<ul>";

          echo "<li><a href=\"/vacation-rentals/" . str_replace(" ", "-", $ComplexAreaLower) . "/" . str_replace(" ", "-", $UnitNameLower) . "\" title=\"$UnitName\">

$UnitName</a>";
          echo "</li>
            </ul>
            </li>";

  }

  echo "</ul>";

echo "</li>";


odbc_free_result( $rsResult );
  odbc_close( $Conn );

?>

If someone might be able to assist me with how to modify my php script to accomplish equivalent display results as the cf script, I would so appreciate it. I've been trying so many things, based on Google searches, but everything I try just seems to result in a blank page, so my syntax, or something, is off. Thank you in advance!
tam423Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
Google searches are a bad way to chase PHP knowledge.  Try this article instead.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html

Next, I'll take a look at your code.  Going forward, when you post code here at EE, please use the code snippet feature (click "code" in the gray bar).  It will give us a unispace font and line numbers -- makes it easier to read, copy and discuss).

Best regards, ~Ray
0
Ray PaseurCommented:
Let's try it this way for starters.  I want to be sure the connection and query works (and I cannot test because I do not have your data set).  If this gives us output, please use "view source" then copy and paste the output into the code snippet here.  Once we can see the query results set in the output display on line 69, we can begin organizing and formatting.

<?php // RAY_temp_tam423.php


// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28233289.html

/*
My cf script looks like:

<CFQUERY NAME="getnavdropdowns" DATASOURCE="mydatabase">
      SELECT DISTINCT ComplexArea, UnitName, ComplexID, Complex_ID
      FROM masterComplex, masterunit
      WHERE ComplexArea IS NOT NULL AND masterComplex.ComplexID = masterunit.Complex_ID
      ORDER BY ComplexArea, UnitName
</CFQUERY>

<cfoutput query="getnavdropdowns" group="ComplexArea">

        <li><a href="/vacation-rentals/#LCase(REReplaceNoCase(ComplexArea, " ", "-", "ALL"))#" title="#ComplexArea#">#ComplexArea#</a>

            <ul>
                  <cfoutput><li><a href="/vacation-rentals/#LCase(REReplaceNoCase(ComplexArea, " ", "-", "ALL"))#/#LCase(REReplaceNoCase(UnitNAME, " ", "-",

"ALL"))#" title="#UnitName#">#UnitName#</a></li></cfoutput>
            </ul>
      </li>

</cfoutput>
*/

error_reporting(E_ALL);

// ESTABLISH DATABASE CONNECTION
$DSN        = "mydatabase";
$DSN_User   = "";
$DSN_Passwd = "";
$Conn       = odbc_connect($DSN, $DSN_User, $DSN_Passwd) or die('ODBC CONNECT FAILED');

// CREATE THE QUERY STRING
$sql
=
"
SELECT DISTINCT ComplexArea, UnitName, ComplexID, Complex_ID
FROM masterComplex, masterunit
WHERE ComplexArea IS NOT NULL
AND masterComplex.ComplexID = masterunit.Complex_ID
ORDER BY ComplexArea, UnitName
"
;

// RUN THE QUERY AND VISUALIZE ERRORS, IF ANY
$res = odbc_exec($Conn, $sqlString);
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . odbc_error($Conn)
    . ' ERROR: '
    . odbc_errormsg($Conn)
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// USE THE WHILE ITERATOR TO ACCESS THE ROWS OF THE RESULTS SET
while($row = odbc_fetch_row($res))
{
    print_r($row);
}


/* STORED CODE
    $ComplexArea      = odbc_result($res,"ComplexArea");
    $ComplexAreaLower = strtolower($ComplexArea);
    $UnitName         = odbc_result($res,"UnitName");
    $UnitNameLower    = strtolower($UnitName);
    $ComplexID        = odbc_result($res,"ComplexID");
    $Complex_ID       = odbc_result($res,"Complex_ID");

// THIS WILL NEED TO CHANGE
  echo "<li><a href=\"/vacation-rentals/" . str_replace(" ", "-", $ComplexAreaLower) . "\" title=\"$ComplexArea\">$ComplexArea</a>";
  echo "<ul>";

    echo "<li><a href=\"/vacation-rentals/" . str_replace(" ", "-", $ComplexAreaLower) . "/" . str_replace(" ", "-", $UnitNameLower) . "\" title=\"$UnitName\">

$UnitName</a>";
    echo "</li>
      </ul>
      </li>";

}

echo "</ul>";

echo "</li>";


odbc_free_result($res);
odbc_close( $Conn );
*/

Open in new window

0
tam423Author Commented:
Ray,

Thank you very much for your response. And my apologies for not posting the code properly. :) I have cut and pasted your code, but viewing it gives me a blank page. I am sure it's me doing something wrong, as I am pretty clueless with php, but you may view the results here:

http://www.mammothsierraonline.com/includes/navbar-test2.php

And I have posted the complete code below. So at this point I am not seeing any output.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
<head>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

<link href="/css/reset.css" rel="stylesheet" type="text/css">
<link href="/css/styles-main.css" rel="stylesheet" type="text/css">
<link href="/css/nav-new.css" rel="stylesheet" type="text/css">
<link href="/css/layout.css" rel="stylesheet" type="text/css">
<link href="/css/calendar.css" rel="stylesheet" type="text/css">

</head>
<body>

<div id="wrapper">

<?php // RAY_temp_tam423.php


// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28233289.html

/*
My cf script looks like:

<CFQUERY NAME="getnavdropdowns" DATASOURCE="mamonline">
      SELECT DISTINCT ComplexArea, UnitName, ComplexID, Complex_ID
      FROM masterComplex, masterunit
      WHERE ComplexArea IS NOT NULL AND masterComplex.ComplexID = masterunit.Complex_ID
      ORDER BY ComplexArea, UnitName
</CFQUERY>

<cfoutput query="getnavdropdowns" group="ComplexArea">

        <li><a href="/vacation-rentals/#LCase(REReplaceNoCase(ComplexArea, " ", "-", "ALL"))#" title="#ComplexArea#">#ComplexArea#</a>

            <ul>
                  <cfoutput><li><a href="/vacation-rentals/#LCase(REReplaceNoCase(ComplexArea, " ", "-", "ALL"))#/#LCase(REReplaceNoCase(UnitNAME, " ", "-",

"ALL"))#" title="#UnitName#">#UnitName#</a></li></cfoutput>
            </ul>
      </li>

</cfoutput>
*/

error_reporting(E_ALL);

// ESTABLISH DATABASE CONNECTION
$DSN        = "mamonline";
$DSN_User   = "";
$DSN_Passwd = "";
$Conn       = odbc_connect($DSN, $DSN_User, $DSN_Passwd) or die('ODBC CONNECT FAILED');

// CREATE THE QUERY STRING
$sql
=
"
SELECT DISTINCT ComplexArea, UnitName, ComplexID, Complex_ID
FROM masterComplex, masterunit
WHERE ComplexArea IS NOT NULL
AND masterComplex.ComplexID = masterunit.Complex_ID
ORDER BY ComplexArea, UnitName
"
;

// RUN THE QUERY AND VISUALIZE ERRORS, IF ANY
$res = odbc_exec($Conn, $sqlString);
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . odbc_error($Conn)
    . ' ERROR: '
    . odbc_errormsg($Conn)
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// USE THE WHILE ITERATOR TO ACCESS THE ROWS OF THE RESULTS SET
while($row = odbc_fetch_row($res))
{
    print_r($row);
}


/* STORED CODE
    $ComplexArea      = odbc_result($res,"ComplexArea");
    $ComplexAreaLower = strtolower($ComplexArea);
    $UnitName         = odbc_result($res,"UnitName");
    $UnitNameLower    = strtolower($UnitName);
    $ComplexID        = odbc_result($res,"ComplexID");
    $Complex_ID       = odbc_result($res,"Complex_ID");

// THIS WILL NEED TO CHANGE
  echo "<li><a href=\"/vacation-rentals/" . str_replace(" ", "-", $ComplexAreaLower) . "\" title=\"$ComplexArea\">$ComplexArea</a>";
  echo "<ul>";

    echo "<li><a href=\"/vacation-rentals/" . str_replace(" ", "-", $ComplexAreaLower) . "/" . str_replace(" ", "-", $UnitNameLower) . "\" title=\"$UnitName\">

$UnitName</a>";
    echo "</li>
      </ul>
      </li>";

}

echo "</ul>";

echo "</li>";


odbc_free_result($res);
odbc_close( $Conn );
*/

?>

</div>

</body>
</html>

Open in new window

0
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Ray PaseurCommented:
Please just run this script all alone, not a modified version of this script.  I cannot test this because I do not have your data base, so I need you to perform the tests for me exactly as written.  The only changes should be the authentication credentials on lines 34-37.  

If this gives us output, please use "view source" then copy and paste the output into the code snippet here.  

Thanks.

<?php // RAY_temp_tam423.php


// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28233289.html

/*
My cf script looks like:

<CFQUERY NAME="getnavdropdowns" DATASOURCE="mydatabase">
      SELECT DISTINCT ComplexArea, UnitName, ComplexID, Complex_ID
      FROM masterComplex, masterunit
      WHERE ComplexArea IS NOT NULL AND masterComplex.ComplexID = masterunit.Complex_ID
      ORDER BY ComplexArea, UnitName
</CFQUERY>

<cfoutput query="getnavdropdowns" group="ComplexArea">

        <li><a href="/vacation-rentals/#LCase(REReplaceNoCase(ComplexArea, " ", "-", "ALL"))#" title="#ComplexArea#">#ComplexArea#</a>

            <ul>
                  <cfoutput><li><a href="/vacation-rentals/#LCase(REReplaceNoCase(ComplexArea, " ", "-", "ALL"))#/#LCase(REReplaceNoCase(UnitNAME, " ", "-",

"ALL"))#" title="#UnitName#">#UnitName#</a></li></cfoutput>
            </ul>
      </li>

</cfoutput>
*/

ini_set('display_errors', TRUE);
error_reporting(E_ALL);

// ESTABLISH DATABASE CONNECTION
$DSN        = "mydatabase";
$DSN_User   = "";
$DSN_Passwd = "";
$Conn       = odbc_connect($DSN, $DSN_User, $DSN_Passwd) or die('ODBC CONNECT FAILED');

// CREATE THE QUERY STRING
$sql
=
"
SELECT DISTINCT ComplexArea, UnitName, ComplexID, Complex_ID
FROM masterComplex, masterunit
WHERE ComplexArea IS NOT NULL
AND masterComplex.ComplexID = masterunit.Complex_ID
ORDER BY ComplexArea, UnitName
"
;

// RUN THE QUERY AND VISUALIZE ERRORS, IF ANY
$res = odbc_exec($Conn, $sqlString);
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . odbc_error($Conn)
    . ' ERROR: '
    . odbc_errormsg($Conn)
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// USE THE WHILE ITERATOR TO ACCESS THE ROWS OF THE RESULTS SET
while($row = odbc_fetch_row($res))
{
    print_r($row);
}


/* STORED CODE
    $ComplexArea      = odbc_result($res,"ComplexArea");
    $ComplexAreaLower = strtolower($ComplexArea);
    $UnitName         = odbc_result($res,"UnitName");
    $UnitNameLower    = strtolower($UnitName);
    $ComplexID        = odbc_result($res,"ComplexID");
    $Complex_ID       = odbc_result($res,"Complex_ID");

// THIS WILL NEED TO CHANGE
  echo "<li><a href=\"/vacation-rentals/" . str_replace(" ", "-", $ComplexAreaLower) . "\" title=\"$ComplexArea\">$ComplexArea</a>";
  echo "<ul>";

    echo "<li><a href=\"/vacation-rentals/" . str_replace(" ", "-", $ComplexAreaLower) . "/" . str_replace(" ", "-", $UnitNameLower) . "\" title=\"$UnitName\">

$UnitName</a>";
    echo "</li>
      </ul>
      </li>";

}

echo "</ul>";

echo "</li>";


odbc_free_result($res);
odbc_close( $Conn );
*/

Open in new window

0
tam423Author Commented:
Ray,

I did receive output this time and the "view source" code is below:
<br />
<b>Notice</b>:  Undefined variable: sqlString in <b>D:\Inetpub\mammothsierraonline\includes\navbar-test2.php</b> on line <b>52</b><br />
<br />
<b>Warning</b>:  odbc_exec() [<a href='function.odbc-exec'>function.odbc-exec</a>]: SQL error: [Microsoft][ODBC Microsoft Access Driver]Invalid use of null pointer , SQL state S1009 in SQLExecDirect in <b>D:\Inetpub\mammothsierraonline\includes\navbar-test2.php</b> on line <b>52</b><br />
<br />
<b>Fatal error</b>:  QUERY FAILURE: ERRNO: S1009 ERROR: [Microsoft][ODBC Microsoft Access Driver]Invalid use of null pointer  QUERY: 
SELECT DISTINCT ComplexArea, UnitName, ComplexID, Complex_ID
FROM masterComplex, masterunit
WHERE ComplexArea IS NOT NULL
AND masterComplex.ComplexID = masterunit.Complex_ID
ORDER BY ComplexArea, UnitName
 in <b>D:\Inetpub\mammothsierraonline\includes\navbar-test2.php</b> on line <b>64</b><br />

Open in new window

0
tam423Author Commented:
Hello again, Ray,

I did notice, from this error message, that the variable "sqlString" was not defined as this in line 40, but rather as "sql", so I went ahead and changed it to "sqlString". Now, an error is not thrown, but rather the following output:
11111111111111111111111111111

Open in new window

0
Ray PaseurCommented:
OK, looks like odbc_fetch_row() is not giving us what we want.  I'm kind of feeling my way along here.  Let's try this next and look at the "view source" output/

<?php // RAY_temp_tam423.php


// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28233289.html

/*
My cf script looks like:

<CFQUERY NAME="getnavdropdowns" DATASOURCE="mydatabase">
      SELECT DISTINCT ComplexArea, UnitName, ComplexID, Complex_ID
      FROM masterComplex, masterunit
      WHERE ComplexArea IS NOT NULL AND masterComplex.ComplexID = masterunit.Complex_ID
      ORDER BY ComplexArea, UnitName
</CFQUERY>

<cfoutput query="getnavdropdowns" group="ComplexArea">

        <li><a href="/vacation-rentals/#LCase(REReplaceNoCase(ComplexArea, " ", "-", "ALL"))#" title="#ComplexArea#">#ComplexArea#</a>

            <ul>
                  <cfoutput><li><a href="/vacation-rentals/#LCase(REReplaceNoCase(ComplexArea, " ", "-", "ALL"))#/#LCase(REReplaceNoCase(UnitNAME, " ", "-",

"ALL"))#" title="#UnitName#">#UnitName#</a></li></cfoutput>
            </ul>
      </li>

</cfoutput>
*/

ini_set('display_errors', TRUE);
error_reporting(E_ALL);

// ESTABLISH DATABASE CONNECTION
$DSN        = "mydatabase";
$DSN_User   = "";
$DSN_Passwd = "";
$Conn       = odbc_connect($DSN, $DSN_User, $DSN_Passwd) or die('ODBC CONNECT FAILED');

// CREATE THE QUERY STRING
$sql
=
"
SELECT DISTINCT ComplexArea, UnitName, ComplexID, Complex_ID
FROM masterComplex, masterunit
WHERE ComplexArea IS NOT NULL
AND masterComplex.ComplexID = masterunit.Complex_ID
ORDER BY ComplexArea, UnitName
"
;

// RUN THE QUERY AND VISUALIZE ERRORS, IF ANY
$res = odbc_exec($Conn, $sql);
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . odbc_error($Conn)
    . ' ERROR: '
    . odbc_errormsg($Conn)
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// USE THE WHILE ITERATOR TO ACCESS THE ROWS OF THE RESULTS SET
while($row = odbc_fetch_row($res))
{
    $complexarea = odbc_result($res, 'ComplexArea');
    $unitname    = odbc_result($res, 'UnitName');
    $complexid   = odbc_result($res, 'ComplexID');
    $complex_id  = odbc_result($res, 'Complex_id');
    echo PHP_EOL . "$complexarea $unitname $complexid $complex_id";
}


/* STORED CODE
    $ComplexArea      = odbc_result($res,"ComplexArea");
    $ComplexAreaLower = strtolower($ComplexArea);
    $UnitName         = odbc_result($res,"UnitName");
    $UnitNameLower    = strtolower($UnitName);
    $ComplexID        = odbc_result($res,"ComplexID");
    $Complex_ID       = odbc_result($res,"Complex_ID");

// THIS WILL NEED TO CHANGE
  echo "<li><a href=\"/vacation-rentals/" . str_replace(" ", "-", $ComplexAreaLower) . "\" title=\"$ComplexArea\">$ComplexArea</a>";
  echo "<ul>";

    echo "<li><a href=\"/vacation-rentals/" . str_replace(" ", "-", $ComplexAreaLower) . "/" . str_replace(" ", "-", $UnitNameLower) . "\" title=\"$UnitName\">

$UnitName</a>";
    echo "</li>
      </ul>
      </li>";

}

echo "</ul>";

echo "</li>";


odbc_free_result($res);
odbc_close( $Conn );
*/

Open in new window

0
tam423Author Commented:
Hi Ray,

Well, I think we're making some progress. Here is the "view source" output:
Canyon Lodge Chamonix CHAM CHAM
Canyon Lodge Conestoga CO CO
Canyon Lodge Discovery Four DC DC
Canyon Lodge Mammoth Point MP MP
Eagle Lodge Bigwood BW BW
Eagle Lodge Bridges BR BR
Eagle Lodge Summit SU SU
Eagle Lodge Sunstone Lodge SUN SUN
Old Mammoth La Vista Blanc LV LV
Old Mammoth Lodges L L
Old Mammoth Mammoth Creek MC MC
Old Mammoth Snowcreek SC SC
Old Mammoth Snowflower SF SF
The Village San Sierra SS SS
The Village Val D'Isere VA VA
Town Chateau Blanc CB CB
Town Chateau de Montagne CDM CDM
Town Chateau Sierra CS CS
Town Forest Creek FC FC
Town Horizons 4 H4 H4
Town La Residence LA LA
Town Mammoth Sierra Townhomes MT MT
Town Mountain Shadows MS MS
Town Sierra Manors SM SM
Town St Moritz ST ST
Town Timberline TL TL
Town Villa de Los Pinos VD VD
Town Wildflower WF WF
Town Winterset WS WS

Open in new window

0
Ray PaseurCommented:
Yes, this is looking much more promising!  Let's try this next (please feel free to correct any obvious errors).

<?php // RAY_temp_tam423.php


// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28233289.html

/*
My cf script looks like:

<CFQUERY NAME="getnavdropdowns" DATASOURCE="mydatabase">
      SELECT DISTINCT ComplexArea, UnitName, ComplexID, Complex_ID
      FROM masterComplex, masterunit
      WHERE ComplexArea IS NOT NULL AND masterComplex.ComplexID = masterunit.Complex_ID
      ORDER BY ComplexArea, UnitName
</CFQUERY>

<cfoutput query="getnavdropdowns" group="ComplexArea">

        <li><a href="/vacation-rentals/#LCase(REReplaceNoCase(ComplexArea, " ", "-", "ALL"))#" title="#ComplexArea#">#ComplexArea#</a>

            <ul>
                  <cfoutput><li><a href="/vacation-rentals/#LCase(REReplaceNoCase(ComplexArea, " ", "-", "ALL"))#/#LCase(REReplaceNoCase(UnitNAME, " ", "-",

"ALL"))#" title="#UnitName#">#UnitName#</a></li></cfoutput>
            </ul>
      </li>

</cfoutput>
*/

ini_set('display_errors', TRUE);
error_reporting(E_ALL);

// ESTABLISH DATABASE CONNECTION
$DSN        = "mydatabase";
$DSN_User   = "";
$DSN_Passwd = "";
$Conn       = odbc_connect($DSN, $DSN_User, $DSN_Passwd) or die('ODBC CONNECT FAILED');

// CREATE THE QUERY STRING
$sql
=
"
SELECT DISTINCT ComplexArea, UnitName, ComplexID, Complex_ID
FROM masterComplex, masterunit
WHERE ComplexArea IS NOT NULL
AND masterComplex.ComplexID = masterunit.Complex_ID
ORDER BY ComplexArea, UnitName
"
;

// RUN THE QUERY AND VISUALIZE ERRORS, IF ANY
$res = odbc_exec($Conn, $sql);
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . odbc_error($Conn)
    . ' ERROR: '
    . odbc_errormsg($Conn)
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACCUMULATE THE HTML DOCUMENT HERE
$htm = NULL;

// USE THE WHILE ITERATOR TO ACCESS THE ROWS OF THE RESULTS SET
while($row = odbc_fetch_row($res))
{
    // ACQUIRE THE VARIABLES FROM THE QUERY ROW
    $complexarea = odbc_result($res, 'ComplexArea');
    $unitname    = odbc_result($res, 'UnitName');
    $complexid   = odbc_result($res, 'ComplexID');
    $complex_id  = odbc_result($res, 'Complex_id');

    // MAKE THE LOWERCASE VERSIONS
    $complexarea_l = strtolower($complexarea);
    $unitname_l    = strtolower($unitname);

    // REPLACE BLANKS WITH HYPHENS
    $complexarea_l = str_replace(' ', '-', $complexarea_l);
    $unitname_l    = str_replace(' ', '-', $unitname_l);

    // CREATE THE LIST ITEM TAGS AND LINKS
    $htm .= '<li>'
    . '<a href="vacation-rentals'
    . DIRECTORY_SEPARATOR
    . $complexarea_l
    . '" title="'
    . $complexarea
    . '">'
    . $complexarea
    . '</a>'
    . PHP_EOL
    ;

    $htm .= '<ul>'
    . '<li>'
    . '<a href="/vacation-rentals'
    . DIRECTORY_SEPARATOR
    . $complexarea_l
    . DIRECTORY_SEPARATOR
    . $unitname_l
    . '" title="'
    . $unitname
    . '">'
    . $unitname
    . '</a>'
    . '</li>'
    . PHP_EOL
    ;
    $htm .= '</ul>';
    $htm .= '</li>';
    $htm .= PHP_EOL;
}

echo $htm;

/* STORED CODE
    $ComplexArea      = odbc_result($res,"ComplexArea");
    $ComplexAreaLower = strtolower($ComplexArea);
    $UnitName         = odbc_result($res,"UnitName");
    $UnitNameLower    = strtolower($UnitName);
    $ComplexID        = odbc_result($res,"ComplexID");
    $Complex_ID       = odbc_result($res,"Complex_ID");

// THIS WILL NEED TO CHANGE
  echo "<li><a href=\"/vacation-rentals/" . str_replace(" ", "-", $ComplexAreaLower) . "\" title=\"$ComplexArea\">$ComplexArea</a>";
  echo "<ul>";

    echo "<li><a href=\"/vacation-rentals/" . str_replace(" ", "-", $ComplexAreaLower) . "/" . str_replace(" ", "-", $UnitNameLower) . "\" title=\"$UnitName\">

$UnitName</a>";
    echo "</li>
      </ul>
      </li>";

}

echo "</ul>";

echo "</li>";


odbc_free_result($res);
odbc_close( $Conn );
*/

Open in new window

0
tam423Author Commented:
Hi Ray,

Looking better! We have the ordered list displaying; it just needs to have the unitnames grouped within their respective complexarea. I'm getting excited!:
<li><a href="vacation-rentals\canyon-lodge" title="Canyon Lodge">Canyon Lodge</a>
<ul><li><a href="/vacation-rentals\canyon-lodge\chamonix" title="Chamonix">Chamonix</a></li>
</ul></li>
<li><a href="vacation-rentals\canyon-lodge" title="Canyon Lodge">Canyon Lodge</a>
<ul><li><a href="/vacation-rentals\canyon-lodge\conestoga" title="Conestoga">Conestoga</a></li>
</ul></li>
<li><a href="vacation-rentals\canyon-lodge" title="Canyon Lodge">Canyon Lodge</a>
<ul><li><a href="/vacation-rentals\canyon-lodge\discovery-four" title="Discovery Four">Discovery Four</a></li>
</ul></li>
<li><a href="vacation-rentals\canyon-lodge" title="Canyon Lodge">Canyon Lodge</a>
<ul><li><a href="/vacation-rentals\canyon-lodge\mammoth-point" title="Mammoth Point">Mammoth Point</a></li>
</ul></li>
<li><a href="vacation-rentals\eagle-lodge" title="Eagle Lodge">Eagle Lodge</a>
<ul><li><a href="/vacation-rentals\eagle-lodge\bigwood" title="Bigwood">Bigwood</a></li>
</ul></li>
<li><a href="vacation-rentals\eagle-lodge" title="Eagle Lodge">Eagle Lodge</a>
<ul><li><a href="/vacation-rentals\eagle-lodge\bridges" title="Bridges">Bridges</a></li>
</ul></li>
<li><a href="vacation-rentals\eagle-lodge" title="Eagle Lodge">Eagle Lodge</a>
<ul><li><a href="/vacation-rentals\eagle-lodge\summit" title="Summit">Summit</a></li>
</ul></li>
<li><a href="vacation-rentals\eagle-lodge" title="Eagle Lodge">Eagle Lodge</a>
<ul><li><a href="/vacation-rentals\eagle-lodge\sunstone-lodge" title="Sunstone Lodge">Sunstone Lodge</a></li>
</ul></li>
<li><a href="vacation-rentals\old-mammoth" title="Old Mammoth">Old Mammoth</a>
<ul><li><a href="/vacation-rentals\old-mammoth\la-vista-blanc" title="La Vista Blanc">La Vista Blanc</a></li>
</ul></li>
<li><a href="vacation-rentals\old-mammoth" title="Old Mammoth">Old Mammoth</a>
<ul><li><a href="/vacation-rentals\old-mammoth\lodges" title="Lodges">Lodges</a></li>
</ul></li>
<li><a href="vacation-rentals\old-mammoth" title="Old Mammoth">Old Mammoth</a>
<ul><li><a href="/vacation-rentals\old-mammoth\mammoth-creek" title="Mammoth Creek">Mammoth Creek</a></li>
</ul></li>
<li><a href="vacation-rentals\old-mammoth" title="Old Mammoth">Old Mammoth</a>
<ul><li><a href="/vacation-rentals\old-mammoth\snowcreek" title="Snowcreek">Snowcreek</a></li>
</ul></li>
<li><a href="vacation-rentals\old-mammoth" title="Old Mammoth">Old Mammoth</a>
<ul><li><a href="/vacation-rentals\old-mammoth\snowflower" title="Snowflower">Snowflower</a></li>
</ul></li>
<li><a href="vacation-rentals\the-village" title="The Village">The Village</a>
<ul><li><a href="/vacation-rentals\the-village\san-sierra" title="San Sierra">San Sierra</a></li>
</ul></li>
<li><a href="vacation-rentals\the-village" title="The Village">The Village</a>
<ul><li><a href="/vacation-rentals\the-village\val-d'isere" title="Val D'Isere">Val D'Isere</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\chateau-blanc" title="Chateau Blanc">Chateau Blanc</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\chateau-de-montagne" title="Chateau de Montagne">Chateau de Montagne</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\chateau-sierra" title="Chateau Sierra">Chateau Sierra</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\forest-creek" title="Forest Creek">Forest Creek</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\horizons-4" title="Horizons 4">Horizons 4</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\la-residence" title="La Residence">La Residence</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\mammoth-sierra-townhomes" title="Mammoth Sierra Townhomes">Mammoth Sierra Townhomes</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\mountain-shadows" title="Mountain Shadows">Mountain Shadows</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\sierra-manors" title="Sierra Manors">Sierra Manors</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\st-moritz" title="St Moritz">St Moritz</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\timberline" title="Timberline">Timberline</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\villa-de-los-pinos" title="Villa de Los Pinos">Villa de Los Pinos</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\wildflower" title="Wildflower">Wildflower</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\winterset" title="Winterset">Winterset</a></li>
</ul></li>

Open in new window

0
Ray PaseurCommented:
Yep, making good progress!  See if this helps group the items.  I know the HTML is not going to be exactly right, but hopefully it illustrates the concept.

<?php // RAY_temp_tam423.php


// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28233289.html

/*
My cf script looks like:

<CFQUERY NAME="getnavdropdowns" DATASOURCE="mydatabase">
      SELECT DISTINCT ComplexArea, UnitName, ComplexID, Complex_ID
      FROM masterComplex, masterunit
      WHERE ComplexArea IS NOT NULL AND masterComplex.ComplexID = masterunit.Complex_ID
      ORDER BY ComplexArea, UnitName
</CFQUERY>

<cfoutput query="getnavdropdowns" group="ComplexArea">

        <li><a href="/vacation-rentals/#LCase(REReplaceNoCase(ComplexArea, " ", "-", "ALL"))#" title="#ComplexArea#">#ComplexArea#</a>

            <ul>
                  <cfoutput><li><a href="/vacation-rentals/#LCase(REReplaceNoCase(ComplexArea, " ", "-", "ALL"))#/#LCase(REReplaceNoCase(UnitNAME, " ", "-",

"ALL"))#" title="#UnitName#">#UnitName#</a></li></cfoutput>
            </ul>
      </li>

</cfoutput>
*/

ini_set('display_errors', TRUE);
error_reporting(E_ALL);

// ESTABLISH DATABASE CONNECTION
$DSN        = "mydatabase";
$DSN_User   = "";
$DSN_Passwd = "";
$Conn       = odbc_connect($DSN, $DSN_User, $DSN_Passwd) or die('ODBC CONNECT FAILED');

// CREATE THE QUERY STRING
$sql
=
"
SELECT DISTINCT ComplexArea, UnitName, ComplexID, Complex_ID
FROM masterComplex, masterunit
WHERE ComplexArea IS NOT NULL
AND masterComplex.ComplexID = masterunit.Complex_ID
ORDER BY ComplexArea, UnitName
"
;

// RUN THE QUERY AND VISUALIZE ERRORS, IF ANY
$res = odbc_exec($Conn, $sql);
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . odbc_error($Conn)
    . ' ERROR: '
    . odbc_errormsg($Conn)
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACCUMULATE THE HTML DOCUMENT HERE
$htm = NULL;

// USE THIS TO DETECT THE COMPLEX AREA
$cxa = NULL;

// USE THE WHILE ITERATOR TO ACCESS THE ROWS OF THE RESULTS SET
while($row = odbc_fetch_row($res))
{
    // ACQUIRE THE VARIABLES FROM THE QUERY ROW
    $complexarea = odbc_result($res, 'ComplexArea');
    $unitname    = odbc_result($res, 'UnitName');
    $complexid   = odbc_result($res, 'ComplexID');
    $complex_id  = odbc_result($res, 'Complex_id');

    // MAKE THE LOWERCASE VERSIONS
    $complexarea_l = strtolower($complexarea);
    $unitname_l    = strtolower($unitname);

    // REPLACE BLANKS WITH HYPHENS
    $complexarea_l = str_replace(' ', '-', $complexarea_l);
    $unitname_l    = str_replace(' ', '-', $unitname_l);

    // IF THERE IS A NEW COMPLEX AREA
    if ($cxa != $complexarea)
    {
        $cxa = $complexarea;
        $htm .= '<h2>$complexarea</h2>' . PHP_EOL;
    }

    // CREATE THE LIST ITEM TAGS AND LINKS
    $htm .= '<li>'
    . '<a href="vacation-rentals'
    . DIRECTORY_SEPARATOR
    . $complexarea_l
    . '" title="'
    . $complexarea
    . '">'
    . $complexarea
    . '</a>'
    . PHP_EOL
    ;

    $htm .= '<ul>'
    . '<li>'
    . '<a href="/vacation-rentals'
    . DIRECTORY_SEPARATOR
    . $complexarea_l
    . DIRECTORY_SEPARATOR
    . $unitname_l
    . '" title="'
    . $unitname
    . '">'
    . $unitname
    . '</a>'
    . '</li>'
    . PHP_EOL
    ;
    $htm .= '</ul>';
    $htm .= '</li>';
    $htm .= PHP_EOL;
}

echo $htm;

/* STORED CODE
    $ComplexArea      = odbc_result($res,"ComplexArea");
    $ComplexAreaLower = strtolower($ComplexArea);
    $UnitName         = odbc_result($res,"UnitName");
    $UnitNameLower    = strtolower($UnitName);
    $ComplexID        = odbc_result($res,"ComplexID");
    $Complex_ID       = odbc_result($res,"Complex_ID");

// THIS WILL NEED TO CHANGE
  echo "<li><a href=\"/vacation-rentals/" . str_replace(" ", "-", $ComplexAreaLower) . "\" title=\"$ComplexArea\">$ComplexArea</a>";
  echo "<ul>";

    echo "<li><a href=\"/vacation-rentals/" . str_replace(" ", "-", $ComplexAreaLower) . "/" . str_replace(" ", "-", $UnitNameLower) . "\" title=\"$UnitName\">

$UnitName</a>";
    echo "</li>
      </ul>
      </li>";

}

echo "</ul>";

echo "</li>";


odbc_free_result($res);
odbc_close( $Conn );
*/

Open in new window

If that gets you close enough, you can discard the "stored code" in the comment area at the end.  Best regards, ~Ray
0
tam423Author Commented:
Hi Ray,

Thank you so much. We're getting even closer. It looks like a grouping is occurring between $complexarea (though not displaying as actual complexarea); we just need the unitnames grouped within their complexarea. So, so close! :)
<h2>$complexarea</h2>
<li><a href="vacation-rentals\canyon-lodge" title="Canyon Lodge">Canyon Lodge</a>
<ul><li><a href="/vacation-rentals\canyon-lodge\chamonix" title="Chamonix">Chamonix</a></li>
</ul></li>
<li><a href="vacation-rentals\canyon-lodge" title="Canyon Lodge">Canyon Lodge</a>
<ul><li><a href="/vacation-rentals\canyon-lodge\conestoga" title="Conestoga">Conestoga</a></li>
</ul></li>
<li><a href="vacation-rentals\canyon-lodge" title="Canyon Lodge">Canyon Lodge</a>
<ul><li><a href="/vacation-rentals\canyon-lodge\discovery-four" title="Discovery Four">Discovery Four</a></li>
</ul></li>
<li><a href="vacation-rentals\canyon-lodge" title="Canyon Lodge">Canyon Lodge</a>
<ul><li><a href="/vacation-rentals\canyon-lodge\mammoth-point" title="Mammoth Point">Mammoth Point</a></li>
</ul></li>
<h2>$complexarea</h2>
<li><a href="vacation-rentals\eagle-lodge" title="Eagle Lodge">Eagle Lodge</a>
<ul><li><a href="/vacation-rentals\eagle-lodge\bigwood" title="Bigwood">Bigwood</a></li>
</ul></li>
<li><a href="vacation-rentals\eagle-lodge" title="Eagle Lodge">Eagle Lodge</a>
<ul><li><a href="/vacation-rentals\eagle-lodge\bridges" title="Bridges">Bridges</a></li>
</ul></li>
<li><a href="vacation-rentals\eagle-lodge" title="Eagle Lodge">Eagle Lodge</a>
<ul><li><a href="/vacation-rentals\eagle-lodge\summit" title="Summit">Summit</a></li>
</ul></li>
<li><a href="vacation-rentals\eagle-lodge" title="Eagle Lodge">Eagle Lodge</a>
<ul><li><a href="/vacation-rentals\eagle-lodge\sunstone-lodge" title="Sunstone Lodge">Sunstone Lodge</a></li>
</ul></li>
<h2>$complexarea</h2>
<li><a href="vacation-rentals\old-mammoth" title="Old Mammoth">Old Mammoth</a>
<ul><li><a href="/vacation-rentals\old-mammoth\la-vista-blanc" title="La Vista Blanc">La Vista Blanc</a></li>
</ul></li>
<li><a href="vacation-rentals\old-mammoth" title="Old Mammoth">Old Mammoth</a>
<ul><li><a href="/vacation-rentals\old-mammoth\lodges" title="Lodges">Lodges</a></li>
</ul></li>
<li><a href="vacation-rentals\old-mammoth" title="Old Mammoth">Old Mammoth</a>
<ul><li><a href="/vacation-rentals\old-mammoth\mammoth-creek" title="Mammoth Creek">Mammoth Creek</a></li>
</ul></li>
<li><a href="vacation-rentals\old-mammoth" title="Old Mammoth">Old Mammoth</a>
<ul><li><a href="/vacation-rentals\old-mammoth\snowcreek" title="Snowcreek">Snowcreek</a></li>
</ul></li>
<li><a href="vacation-rentals\old-mammoth" title="Old Mammoth">Old Mammoth</a>
<ul><li><a href="/vacation-rentals\old-mammoth\snowflower" title="Snowflower">Snowflower</a></li>
</ul></li>
<h2>$complexarea</h2>
<li><a href="vacation-rentals\the-village" title="The Village">The Village</a>
<ul><li><a href="/vacation-rentals\the-village\san-sierra" title="San Sierra">San Sierra</a></li>
</ul></li>
<li><a href="vacation-rentals\the-village" title="The Village">The Village</a>
<ul><li><a href="/vacation-rentals\the-village\val-d'isere" title="Val D'Isere">Val D'Isere</a></li>
</ul></li>
<h2>$complexarea</h2>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\chateau-blanc" title="Chateau Blanc">Chateau Blanc</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\chateau-de-montagne" title="Chateau de Montagne">Chateau de Montagne</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\chateau-sierra" title="Chateau Sierra">Chateau Sierra</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\forest-creek" title="Forest Creek">Forest Creek</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\horizons-4" title="Horizons 4">Horizons 4</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\la-residence" title="La Residence">La Residence</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\mammoth-sierra-townhomes" title="Mammoth Sierra Townhomes">Mammoth Sierra Townhomes</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\mountain-shadows" title="Mountain Shadows">Mountain Shadows</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\sierra-manors" title="Sierra Manors">Sierra Manors</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\st-moritz" title="St Moritz">St Moritz</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\timberline" title="Timberline">Timberline</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\villa-de-los-pinos" title="Villa de Los Pinos">Villa de Los Pinos</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\wildflower" title="Wildflower">Wildflower</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\winterset" title="Winterset">Winterset</a></li>
</ul></li>

Open in new window

0
Ray PaseurCommented:
Try this again.  I know it's only an incremental change, but it will help me to see what's going on.  Are you on a Windows platform?

<?php // RAY_temp_tam423.php


// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28233289.html

/*
My cf script looks like:

<CFQUERY NAME="getnavdropdowns" DATASOURCE="mydatabase">
      SELECT DISTINCT ComplexArea, UnitName, ComplexID, Complex_ID
      FROM masterComplex, masterunit
      WHERE ComplexArea IS NOT NULL AND masterComplex.ComplexID = masterunit.Complex_ID
      ORDER BY ComplexArea, UnitName
</CFQUERY>

<cfoutput query="getnavdropdowns" group="ComplexArea">

        <li><a href="/vacation-rentals/#LCase(REReplaceNoCase(ComplexArea, " ", "-", "ALL"))#" title="#ComplexArea#">#ComplexArea#</a>

            <ul>
                  <cfoutput><li><a href="/vacation-rentals/#LCase(REReplaceNoCase(ComplexArea, " ", "-", "ALL"))#/#LCase(REReplaceNoCase(UnitNAME, " ", "-",

"ALL"))#" title="#UnitName#">#UnitName#</a></li></cfoutput>
            </ul>
      </li>

</cfoutput>
*/

ini_set('display_errors', TRUE);
error_reporting(E_ALL);

// ESTABLISH DATABASE CONNECTION
$DSN        = "mydatabase";
$DSN_User   = "";
$DSN_Passwd = "";
$Conn       = odbc_connect($DSN, $DSN_User, $DSN_Passwd) or die('ODBC CONNECT FAILED');

// CREATE THE QUERY STRING
$sql
=
"
SELECT DISTINCT ComplexArea, UnitName, ComplexID, Complex_ID
FROM masterComplex, masterunit
WHERE ComplexArea IS NOT NULL
AND masterComplex.ComplexID = masterunit.Complex_ID
ORDER BY ComplexArea, UnitName
"
;

// RUN THE QUERY AND VISUALIZE ERRORS, IF ANY
$res = odbc_exec($Conn, $sql);
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . odbc_error($Conn)
    . ' ERROR: '
    . odbc_errormsg($Conn)
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACCUMULATE THE HTML DOCUMENT HERE
$htm = NULL;

// USE THIS TO DETECT THE COMPLEX AREA
$cxa = NULL;

// USE THE WHILE ITERATOR TO ACCESS THE ROWS OF THE RESULTS SET
while($row = odbc_fetch_row($res))
{
    // ACQUIRE THE VARIABLES FROM THE QUERY ROW
    $complexarea = odbc_result($res, 'ComplexArea');
    $unitname    = odbc_result($res, 'UnitName');
    $complexid   = odbc_result($res, 'ComplexID');
    $complex_id  = odbc_result($res, 'Complex_id');

    // MAKE THE LOWERCASE VERSIONS
    $complexarea_l = strtolower($complexarea);
    $unitname_l    = strtolower($unitname);

    // REPLACE BLANKS WITH HYPHENS
    $complexarea_l = str_replace(' ', '-', $complexarea_l);
    $unitname_l    = str_replace(' ', '-', $unitname_l);

    // IF THERE IS A NEW COMPLEX AREA
    if ($cxa != $complexarea)
    {
        $cxa = $complexarea;
        $htm .= "<h2>$complexarea</h2>" . PHP_EOL;
    }

    // CREATE THE LIST ITEM TAGS AND LINKS
    $htm .= '<li>'
    . '<a href="vacation-rentals'
    . DIRECTORY_SEPARATOR
    . $complexarea_l
    . '" title="'
    . $complexarea
    . '">'
    . $complexarea
    . '</a>'
    . PHP_EOL
    ;

    $htm .= '<ul>'
    . '<li>'
    . '<a href="/vacation-rentals'
    . DIRECTORY_SEPARATOR
    . $complexarea_l
    . DIRECTORY_SEPARATOR
    . $unitname_l
    . '" title="'
    . $unitname
    . '">'
    . $unitname
    . '</a>'
    . '</li>'
    . PHP_EOL
    ;
    $htm .= '</ul>';
    $htm .= '</li>';
    $htm .= PHP_EOL;
}

echo $htm;

/* STORED CODE
    $ComplexArea      = odbc_result($res,"ComplexArea");
    $ComplexAreaLower = strtolower($ComplexArea);
    $UnitName         = odbc_result($res,"UnitName");
    $UnitNameLower    = strtolower($UnitName);
    $ComplexID        = odbc_result($res,"ComplexID");
    $Complex_ID       = odbc_result($res,"Complex_ID");

// THIS WILL NEED TO CHANGE
  echo "<li><a href=\"/vacation-rentals/" . str_replace(" ", "-", $ComplexAreaLower) . "\" title=\"$ComplexArea\">$ComplexArea</a>";
  echo "<ul>";

    echo "<li><a href=\"/vacation-rentals/" . str_replace(" ", "-", $ComplexAreaLower) . "/" . str_replace(" ", "-", $UnitNameLower) . "\" title=\"$UnitName\">

$UnitName</a>";
    echo "</li>
      </ul>
      </li>";

}

echo "</ul>";

echo "</li>";


odbc_free_result($res);
odbc_close( $Conn );
*/

Open in new window

Thanks, ~Ray
0
tam423Author Commented:
Ray,

OK, this now displays the actual complexarea name, rather than $complexarea. It looks like all we need to do now is somehow remove the complexarea names repeated within each complexarea group and leave the unitnames. Woo hoo! Yes, I am on a Windows platform.
<h2>Canyon Lodge</h2>
<li><a href="vacation-rentals\canyon-lodge" title="Canyon Lodge">Canyon Lodge</a>
<ul><li><a href="/vacation-rentals\canyon-lodge\chamonix" title="Chamonix">Chamonix</a></li>
</ul></li>
<li><a href="vacation-rentals\canyon-lodge" title="Canyon Lodge">Canyon Lodge</a>
<ul><li><a href="/vacation-rentals\canyon-lodge\conestoga" title="Conestoga">Conestoga</a></li>
</ul></li>
<li><a href="vacation-rentals\canyon-lodge" title="Canyon Lodge">Canyon Lodge</a>
<ul><li><a href="/vacation-rentals\canyon-lodge\discovery-four" title="Discovery Four">Discovery Four</a></li>
</ul></li>
<li><a href="vacation-rentals\canyon-lodge" title="Canyon Lodge">Canyon Lodge</a>
<ul><li><a href="/vacation-rentals\canyon-lodge\mammoth-point" title="Mammoth Point">Mammoth Point</a></li>
</ul></li>
<h2>Eagle Lodge</h2>
<li><a href="vacation-rentals\eagle-lodge" title="Eagle Lodge">Eagle Lodge</a>
<ul><li><a href="/vacation-rentals\eagle-lodge\bigwood" title="Bigwood">Bigwood</a></li>
</ul></li>
<li><a href="vacation-rentals\eagle-lodge" title="Eagle Lodge">Eagle Lodge</a>
<ul><li><a href="/vacation-rentals\eagle-lodge\bridges" title="Bridges">Bridges</a></li>
</ul></li>
<li><a href="vacation-rentals\eagle-lodge" title="Eagle Lodge">Eagle Lodge</a>
<ul><li><a href="/vacation-rentals\eagle-lodge\summit" title="Summit">Summit</a></li>
</ul></li>
<li><a href="vacation-rentals\eagle-lodge" title="Eagle Lodge">Eagle Lodge</a>
<ul><li><a href="/vacation-rentals\eagle-lodge\sunstone-lodge" title="Sunstone Lodge">Sunstone Lodge</a></li>
</ul></li>
<h2>Old Mammoth</h2>
<li><a href="vacation-rentals\old-mammoth" title="Old Mammoth">Old Mammoth</a>
<ul><li><a href="/vacation-rentals\old-mammoth\la-vista-blanc" title="La Vista Blanc">La Vista Blanc</a></li>
</ul></li>
<li><a href="vacation-rentals\old-mammoth" title="Old Mammoth">Old Mammoth</a>
<ul><li><a href="/vacation-rentals\old-mammoth\lodges" title="Lodges">Lodges</a></li>
</ul></li>
<li><a href="vacation-rentals\old-mammoth" title="Old Mammoth">Old Mammoth</a>
<ul><li><a href="/vacation-rentals\old-mammoth\mammoth-creek" title="Mammoth Creek">Mammoth Creek</a></li>
</ul></li>
<li><a href="vacation-rentals\old-mammoth" title="Old Mammoth">Old Mammoth</a>
<ul><li><a href="/vacation-rentals\old-mammoth\snowcreek" title="Snowcreek">Snowcreek</a></li>
</ul></li>
<li><a href="vacation-rentals\old-mammoth" title="Old Mammoth">Old Mammoth</a>
<ul><li><a href="/vacation-rentals\old-mammoth\snowflower" title="Snowflower">Snowflower</a></li>
</ul></li>
<h2>The Village</h2>
<li><a href="vacation-rentals\the-village" title="The Village">The Village</a>
<ul><li><a href="/vacation-rentals\the-village\san-sierra" title="San Sierra">San Sierra</a></li>
</ul></li>
<li><a href="vacation-rentals\the-village" title="The Village">The Village</a>
<ul><li><a href="/vacation-rentals\the-village\val-d'isere" title="Val D'Isere">Val D'Isere</a></li>
</ul></li>
<h2>Town</h2>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\chateau-blanc" title="Chateau Blanc">Chateau Blanc</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\chateau-de-montagne" title="Chateau de Montagne">Chateau de Montagne</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\chateau-sierra" title="Chateau Sierra">Chateau Sierra</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\forest-creek" title="Forest Creek">Forest Creek</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\horizons-4" title="Horizons 4">Horizons 4</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\la-residence" title="La Residence">La Residence</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\mammoth-sierra-townhomes" title="Mammoth Sierra Townhomes">Mammoth Sierra Townhomes</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\mountain-shadows" title="Mountain Shadows">Mountain Shadows</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\sierra-manors" title="Sierra Manors">Sierra Manors</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\st-moritz" title="St Moritz">St Moritz</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\timberline" title="Timberline">Timberline</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\villa-de-los-pinos" title="Villa de Los Pinos">Villa de Los Pinos</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\wildflower" title="Wildflower">Wildflower</a></li>
</ul></li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="/vacation-rentals\town\winterset" title="Winterset">Winterset</a></li>
</ul></li>

Open in new window

0
Ray PaseurCommented:
See if this gets (ever) closer...

<?php // RAY_temp_tam423.php


// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28233289.html

/*
My cf script looks like:

<CFQUERY NAME="getnavdropdowns" DATASOURCE="mydatabase">
      SELECT DISTINCT ComplexArea, UnitName, ComplexID, Complex_ID
      FROM masterComplex, masterunit
      WHERE ComplexArea IS NOT NULL AND masterComplex.ComplexID = masterunit.Complex_ID
      ORDER BY ComplexArea, UnitName
</CFQUERY>

<cfoutput query="getnavdropdowns" group="ComplexArea">

        <li><a href="/vacation-rentals/#LCase(REReplaceNoCase(ComplexArea, " ", "-", "ALL"))#" title="#ComplexArea#">#ComplexArea#</a>

            <ul>
                  <cfoutput><li><a href="/vacation-rentals/#LCase(REReplaceNoCase(ComplexArea, " ", "-", "ALL"))#/#LCase(REReplaceNoCase(UnitNAME, " ", "-",

"ALL"))#" title="#UnitName#">#UnitName#</a></li></cfoutput>
            </ul>
      </li>

</cfoutput>
*/

ini_set('display_errors', TRUE);
error_reporting(E_ALL);

// ESTABLISH DATABASE CONNECTION
$DSN        = "mydatabase";
$DSN_User   = "";
$DSN_Passwd = "";
$Conn       = odbc_connect($DSN, $DSN_User, $DSN_Passwd) or die('ODBC CONNECT FAILED');

// CREATE THE QUERY STRING
$sql
=
"
SELECT DISTINCT ComplexArea, UnitName, ComplexID, Complex_ID
FROM masterComplex, masterunit
WHERE ComplexArea IS NOT NULL
AND masterComplex.ComplexID = masterunit.Complex_ID
ORDER BY ComplexArea, UnitName
"
;

// RUN THE QUERY AND VISUALIZE ERRORS, IF ANY
$res = odbc_exec($Conn, $sql);
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . odbc_error($Conn)
    . ' ERROR: '
    . odbc_errormsg($Conn)
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACCUMULATE THE HTML DOCUMENT HERE
$htm = NULL;

// USE THIS TO DETECT THE COMPLEX AREA
$cxa = NULL;

// USE THE WHILE ITERATOR TO ACCESS THE ROWS OF THE RESULTS SET
while($row = odbc_fetch_row($res))
{
    // ACQUIRE THE VARIABLES FROM THE QUERY ROW
    $complexarea = odbc_result($res, 'ComplexArea');
    $unitname    = odbc_result($res, 'UnitName');
    $complexid   = odbc_result($res, 'ComplexID');
    $complex_id  = odbc_result($res, 'Complex_id');

    // MAKE THE LOWERCASE VERSIONS
    $complexarea_l = strtolower($complexarea);
    $unitname_l    = strtolower($unitname);

    // REPLACE BLANKS WITH HYPHENS
    $complexarea_l = str_replace(' ', '-', $complexarea_l);
    $unitname_l    = str_replace(' ', '-', $unitname_l);

    // IF THERE IS A NON-FIRST COMPLEX AREA, CLOSE THE OLD LIST ITEM
    if ($cxa !== NULL) $htm .= '</li>' . PHP_EOL;

    // IF THERE IS A NEW COMPLEX AREA
    if ($cxa != $complexarea)
    {
        $cxa = $complexarea;

        // START A NEW LIST ITEM
	    $htm .= '<li>'
	    . '<a href="vacation-rentals'
	    . DIRECTORY_SEPARATOR
	    . $complexarea_l
	    . '" title="'
	    . $complexarea
	    . '">'
	    . $complexarea
	    . '</a>'
	    . PHP_EOL
	    ;

    }

    // ADD THIS UNIT NAME
    $htm .= '<ul>'
    . '<li>'
    . '<a href="vacation-rentals'
    . DIRECTORY_SEPARATOR
    . $complexarea_l
    . DIRECTORY_SEPARATOR
    . $unitname_l
    . '" title="'
    . $unitname
    . '">'
    . $unitname
    . '</a>'
    . '</li>'
    . PHP_EOL
    ;
    $htm .= '</ul>';
    $htm .= PHP_EOL;
}

// CLOSE THE LAST LIST ITEM
$htm .= '</li>' . PHP_EOL;

// WRITE THE HTM DOCUMENT
echo $htm;

Open in new window

0
tam423Author Commented:
Ray,

Wow -- this is it! Things are now grouped as they should:
<li><a href="vacation-rentals\canyon-lodge" title="Canyon Lodge">Canyon Lodge</a>
<ul><li><a href="vacation-rentals\canyon-lodge\chamonix" title="Chamonix">Chamonix</a></li>
</ul>
</li>
<ul><li><a href="vacation-rentals\canyon-lodge\conestoga" title="Conestoga">Conestoga</a></li>
</ul>
</li>
<ul><li><a href="vacation-rentals\canyon-lodge\discovery-four" title="Discovery Four">Discovery Four</a></li>
</ul>
</li>
<ul><li><a href="vacation-rentals\canyon-lodge\mammoth-point" title="Mammoth Point">Mammoth Point</a></li>
</ul>
</li>
<li><a href="vacation-rentals\eagle-lodge" title="Eagle Lodge">Eagle Lodge</a>
<ul><li><a href="vacation-rentals\eagle-lodge\bigwood" title="Bigwood">Bigwood</a></li>
</ul>
</li>
<ul><li><a href="vacation-rentals\eagle-lodge\bridges" title="Bridges">Bridges</a></li>
</ul>
</li>
<ul><li><a href="vacation-rentals\eagle-lodge\summit" title="Summit">Summit</a></li>
</ul>
</li>
<ul><li><a href="vacation-rentals\eagle-lodge\sunstone-lodge" title="Sunstone Lodge">Sunstone Lodge</a></li>
</ul>
</li>
<li><a href="vacation-rentals\old-mammoth" title="Old Mammoth">Old Mammoth</a>
<ul><li><a href="vacation-rentals\old-mammoth\la-vista-blanc" title="La Vista Blanc">La Vista Blanc</a></li>
</ul>
</li>
<ul><li><a href="vacation-rentals\old-mammoth\lodges" title="Lodges">Lodges</a></li>
</ul>
</li>
<ul><li><a href="vacation-rentals\old-mammoth\mammoth-creek" title="Mammoth Creek">Mammoth Creek</a></li>
</ul>
</li>
<ul><li><a href="vacation-rentals\old-mammoth\snowcreek" title="Snowcreek">Snowcreek</a></li>
</ul>
</li>
<ul><li><a href="vacation-rentals\old-mammoth\snowflower" title="Snowflower">Snowflower</a></li>
</ul>
</li>
<li><a href="vacation-rentals\the-village" title="The Village">The Village</a>
<ul><li><a href="vacation-rentals\the-village\san-sierra" title="San Sierra">San Sierra</a></li>
</ul>
</li>
<ul><li><a href="vacation-rentals\the-village\val-d'isere" title="Val D'Isere">Val D'Isere</a></li>
</ul>
</li>
<li><a href="vacation-rentals\town" title="Town">Town</a>
<ul><li><a href="vacation-rentals\town\chateau-blanc" title="Chateau Blanc">Chateau Blanc</a></li>
</ul>
</li>
<ul><li><a href="vacation-rentals\town\chateau-de-montagne" title="Chateau de Montagne">Chateau de Montagne</a></li>
</ul>
</li>
<ul><li><a href="vacation-rentals\town\chateau-sierra" title="Chateau Sierra">Chateau Sierra</a></li>
</ul>
</li>
<ul><li><a href="vacation-rentals\town\forest-creek" title="Forest Creek">Forest Creek</a></li>
</ul>
</li>
<ul><li><a href="vacation-rentals\town\horizons-4" title="Horizons 4">Horizons 4</a></li>
</ul>
</li>
<ul><li><a href="vacation-rentals\town\la-residence" title="La Residence">La Residence</a></li>
</ul>
</li>
<ul><li><a href="vacation-rentals\town\mammoth-sierra-townhomes" title="Mammoth Sierra Townhomes">Mammoth Sierra Townhomes</a></li>
</ul>
</li>
<ul><li><a href="vacation-rentals\town\mountain-shadows" title="Mountain Shadows">Mountain Shadows</a></li>
</ul>
</li>
<ul><li><a href="vacation-rentals\town\sierra-manors" title="Sierra Manors">Sierra Manors</a></li>
</ul>
</li>
<ul><li><a href="vacation-rentals\town\st-moritz" title="St Moritz">St Moritz</a></li>
</ul>
</li>
<ul><li><a href="vacation-rentals\town\timberline" title="Timberline">Timberline</a></li>
</ul>
</li>
<ul><li><a href="vacation-rentals\town\villa-de-los-pinos" title="Villa de Los Pinos">Villa de Los Pinos</a></li>
</ul>
</li>
<ul><li><a href="vacation-rentals\town\wildflower" title="Wildflower">Wildflower</a></li>
</ul>
</li>
<ul><li><a href="vacation-rentals\town\winterset" title="Winterset">Winterset</a></li>
</ul>
</li>

Open in new window

I've been trying to now finalize the structure and wondered if I could impose just a bit more. All I need now is to have "</ul></li>" come immediately after each list of unitnames within a complexarea; i.e. following the last unitname listed within a complexarea group. I am thinking it is probably a simple thing, but everything I try puts the code in the wrong place (my lack of php understanding shining through again). Here is a static version of what I need. Please note the <ul></li> immediately following Mammoth Point, Sunstone Lodge, etc.:
<ul>

<li><a href="vacation-rentals\canyon-lodge" title="Canyon Lodge">Canyon Lodge</a>

<ul>
<li><a href="vacation-rentals\canyon-lodge\chamonix" title="Chamonix">Chamonix</a></li>

<li><a href="vacation-rentals\canyon-lodge\conestoga" title="Conestoga">Conestoga</a></li>

<li><a href="vacation-rentals\canyon-lodge\discovery-four" title="Discovery Four">Discovery Four</a></li>

<li><a href="vacation-rentals\canyon-lodge\mammoth-point" title="Mammoth Point">Mammoth Point</a></li>
</ul>
</li>

<li><a href="vacation-rentals\eagle-lodge" title="Eagle Lodge">Eagle Lodge</a>

<ul>
<li><a href="vacation-rentals\eagle-lodge\bigwood" title="Bigwood">Bigwood</a></li>

<li><a href="vacation-rentals\eagle-lodge\bridges" title="Bridges">Bridges</a></li>

<li><a href="vacation-rentals\eagle-lodge\summit" title="Summit">Summit</a></li>

<li><a href="vacation-rentals\eagle-lodge\sunstone-lodge" title="Sunstone Lodge">Sunstone Lodge</a></li>
</ul>
</li>

<li><a href="vacation-rentals\old-mammoth" title="Old Mammoth">Old Mammoth</a>

<ul>
<li><a href="vacation-rentals\old-mammoth\la-vista-blanc" title="La Vista Blanc">La Vista Blanc</a></li>

<li><a href="vacation-rentals\old-mammoth\lodges" title="Lodges">Lodges</a></li>

<li><a href="vacation-rentals\old-mammoth\mammoth-creek" title="Mammoth Creek">Mammoth Creek</a></li>

<li><a href="vacation-rentals\old-mammoth\snowcreek" title="Snowcreek">Snowcreek</a></li>

<li><a href="vacation-rentals\old-mammoth\snowflower" title="Snowflower">Snowflower</a></li>
</ul>
</li>

<li><a href="vacation-rentals\the-village" title="The Village">The Village</a>

<ul>
<li><a href="vacation-rentals\the-village\san-sierra" title="San Sierra">San Sierra</a></li>

<li><a href="vacation-rentals\the-village\val-d'isere" title="Val D'Isere">Val D'Isere</a></li>
</ul>
</li>

<li><a href="vacation-rentals\town" title="Town">Town</a>

<ul>
<li><a href="vacation-rentals\town\chateau-blanc" title="Chateau Blanc">Chateau Blanc</a></li>

<li><a href="vacation-rentals\town\chateau-de-montagne" title="Chateau de Montagne">Chateau de Montagne</a></li>

<li><a href="vacation-rentals\town\chateau-sierra" title="Chateau Sierra">Chateau Sierra</a></li>

<li><a href="vacation-rentals\town\forest-creek" title="Forest Creek">Forest Creek</a></li>

<li><a href="vacation-rentals\town\horizons-4" title="Horizons 4">Horizons 4</a></li>

<li><a href="vacation-rentals\town\la-residence" title="La Residence">La Residence</a></li>

<li><a href="vacation-rentals\town\mammoth-sierra-townhomes" title="Mammoth Sierra Townhomes">Mammoth Sierra Townhomes</a></li>

<li><a href="vacation-rentals\town\mountain-shadows" title="Mountain Shadows">Mountain Shadows</a></li>

<li><a href="vacation-rentals\town\sierra-manors" title="Sierra Manors">Sierra Manors</a></li>

<li><a href="vacation-rentals\town\st-moritz" title="St Moritz">St Moritz</a></li>

<li><a href="vacation-rentals\town\timberline" title="Timberline">Timberline</a></li>

<li><a href="vacation-rentals\town\villa-de-los-pinos" title="Villa de Los Pinos">Villa de Los Pinos</a></li>

<li><a href="vacation-rentals\town\wildflower" title="Wildflower">Wildflower</a></li>

<li><a href="vacation-rentals\town\winterset" title="Winterset">Winterset</a></li>
</ul>
</li>

</ul>

Open in new window

0
Ray PaseurCommented:
Here is my latest effort.

This seems to have evolved from a question with an answer into a programming assignment, so I'm going to recommend that you hire a professional programmer if you still need help finishing the project.  It's just too complicated and too much guess-work for me since I do not have any test data.  Best of luck with it, ~Ray

<?php // RAY_temp_tam423.php


// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28233289.html

/*
My cf script looks like:

<CFQUERY NAME="getnavdropdowns" DATASOURCE="mydatabase">
      SELECT DISTINCT ComplexArea, UnitName, ComplexID, Complex_ID
      FROM masterComplex, masterunit
      WHERE ComplexArea IS NOT NULL AND masterComplex.ComplexID = masterunit.Complex_ID
      ORDER BY ComplexArea, UnitName
</CFQUERY>

<cfoutput query="getnavdropdowns" group="ComplexArea">

        <li><a href="/vacation-rentals/#LCase(REReplaceNoCase(ComplexArea, " ", "-", "ALL"))#" title="#ComplexArea#">#ComplexArea#</a>

            <ul>
                  <cfoutput><li><a href="/vacation-rentals/#LCase(REReplaceNoCase(ComplexArea, " ", "-", "ALL"))#/#LCase(REReplaceNoCase(UnitNAME, " ", "-",

"ALL"))#" title="#UnitName#">#UnitName#</a></li></cfoutput>
            </ul>
      </li>

</cfoutput>
*/

ini_set('display_errors', TRUE);
error_reporting(E_ALL);

// ESTABLISH DATABASE CONNECTION
$DSN        = "mydatabase";
$DSN_User   = "";
$DSN_Passwd = "";
$Conn       = odbc_connect($DSN, $DSN_User, $DSN_Passwd) or die('ODBC CONNECT FAILED');

// CREATE THE QUERY STRING
$sql
=
"
SELECT DISTINCT ComplexArea, UnitName, ComplexID, Complex_ID
FROM masterComplex, masterunit
WHERE ComplexArea IS NOT NULL
AND masterComplex.ComplexID = masterunit.Complex_ID
ORDER BY ComplexArea, UnitName
"
;

// RUN THE QUERY AND VISUALIZE ERRORS, IF ANY
$res = odbc_exec($Conn, $sql);
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . odbc_error($Conn)
    . ' ERROR: '
    . odbc_errormsg($Conn)
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACCUMULATE THE HTML DOCUMENT HERE
$htm = NULL;
$htm .= '<ul>' . PHP_EOL;

// USE THIS TO DETECT THE COMPLEX AREA
$cxa = NULL;

// USE THE WHILE ITERATOR TO ACCESS THE ROWS OF THE RESULTS SET
while($row = odbc_fetch_row($res))
{
    // ACQUIRE THE VARIABLES FROM THE QUERY ROW
    $complexarea = odbc_result($res, 'ComplexArea');
    $unitname    = odbc_result($res, 'UnitName');
    $complexid   = odbc_result($res, 'ComplexID');
    $complex_id  = odbc_result($res, 'Complex_id');

    // MAKE THE LOWERCASE VERSIONS
    $complexarea_l = strtolower($complexarea);
    $unitname_l    = strtolower($unitname);

    // REPLACE BLANKS WITH HYPHENS
    $complexarea_l = str_replace(' ', '-', $complexarea_l);
    $unitname_l    = str_replace(' ', '-', $unitname_l);

    // IF THERE IS A NEW COMPLEX AREA
    if ($cxa != $complexarea)
    {
        // IF THERE IS A NON-FIRST COMPLEX AREA, CLOSE THE OLD UL AND LIST ITEM
	    if ($cxa !== NULL)
	    {
	        $htm .= '    </ul>' . PHP_EOL;
	        $htm .= '  </li>'   . PHP_EOL;
	        $htm .= PHP_EOL;
	    }

        // ASSIGN THIS COMPLEX AREA TO OUR SIGNAL STRING
        $cxa = $complexarea;

        // START A NEW LIST ITEM
	    $htm .= '  <li>'
	    . '<a href="vacation-rentals'
	    . DIRECTORY_SEPARATOR
	    . $complexarea_l
	    . '" title="'
	    . $complexarea
	    . '">'
	    . $complexarea
	    . '</a>'
	    . PHP_EOL
	    ;

    }

    // ADD THIS UNIT NAME
    $htm .= '    <ul>' . PHP_EOL
    . '      <li>'
    . '<a href="vacation-rentals'
    . DIRECTORY_SEPARATOR
    . $complexarea_l
    . DIRECTORY_SEPARATOR
    . $unitname_l
    . '" title="'
    . $unitname
    . '">'
    . $unitname
    . '</a>'
    . '</li>'
    . PHP_EOL
    ;
}

// CLOSE THE LAST LIST ITEM
$htm .= '  </li>' . PHP_EOL;
$htm .= '</ul>' . PHP_EOL;

// WRITE THE HTM DOCUMENT
echo $htm;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tam423Author Commented:
Ray,

Thank you very much for all of your assistance with this. With just a few slight modifications, I was able to get it to display as needed. I can't express enough my appreciation; if I could give triple points, I would. I am sorry if my last request was inappropriate; I was just trying to get things to display as in my original post, and as I am just starting with php, it has been a bit rough. I will try to do better next time. I will definitely be reviewing the article that you provided a link to, above, as well.

All the best,

tam
0
Ray PaseurCommented:
Thanks for the points, Tam.  Here is a link to my "wisdom for early learners of PHP"
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html

Best regards, ~Ray
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.