Please help me group the data from the table. The data should look like this:
Department1
SubDept1
Name1
Name2
Name3
SubDept2
Name4
Name5
Department2
Name6
Name7
There is always Department and members under it. But sometimes there are no subdepartments. but it is not grouping properly. The members go to different Department or Subdepartment.
Here's my code
And here's the other code that process the array:
Department1
SubDept1
Name1
Name2
Name3
SubDept2
Name4
Name5
Department2
Name6
Name7
There is always Department and members under it. But sometimes there are no subdepartments. but it is not grouping properly. The members go to different Department or Subdepartment.
Here's my code
Code:
<%
@Language=JavaScript
%>
<!-- #include file="../global/basePath.asp" -->
<%
/*
* Variables that are used by this file
*/
var filename = basePath() + "/intranet/data/phone_list.mdb";
var sql = "SELECT * FROM PhoneList_by_Department";
var subfields = "department,subdepartment";
// Variables to hold database objects
var rs, conn;
// Create a database connection object
conn = Server.CreateObject("ADODB.Connection");
// Create a recordset object
rs = Server.CreateObject("ADODB.Recordset");
// Attempt to open the database we want to use
try
{
conn.open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filename);
}
catch(err)
{
Response.write("<font size='4'><b>Fatal Script Error:</font><br><br>" +
err.description);
Response.End();
}
// Get names of subfields into an array
var subfield_names = subfields.split(",");
// Declare the subfield arrays
eval( "var " + subfields);
// Create an array of each subfield
for (var i = 0; i < subfield_names.length; i++)
{
// Open the table given by subfield_names[i]
rs = conn.Execute("SELECT * FROM " + subfield_names[i] + " ORDER BY " +
subfield_names[i] + "_id");
// Loop through records, getting values from the table and storing them in a
comma delimited list
var value_list = "";
while (!rs.EOF)
{
if (value_list.length > 0)
value_list += ",";
value_list += "\"" + unescape( rs.Fields(1) ) + "\"";
rs.MoveNext();
}
// Assign to an array
eval ( subfield_names[i] + " = new Array(" + value_list + ")" );
// Close the recordset
rs.Close();
}
// Get a recordset from a table
rs = conn.Execute (sql);
%>
<body bgcolor="#FFFFFF">
<!-- #include file="script.asp" -->
<!-- #include file="../global/header.asp" -->
<!-- the following is a bar with a message and the date -->
<table border="0" cellspacing="0" cellpadding="0" width="80%">
<tr>
<td height="10"></td>
</tr>
<tr><td colspan="3" height="1" bgcolor="#EEE5C9"></td></tr>
<tr><td colspan="3" height="1" bgcolor="#CC9900"></td></tr>
<tr><td colspan="3" height="1" bgcolor="#D0A832"></td></tr>
<tr>
<td class="h_title" height="25"> Phone Directory - Departmental Listings</td>
</tr>
<tr><td colspan="3" height="1" bgcolor="#EEE5C9"></td></tr>
<tr><td colspan="3" height="1" bgcolor="#CC9900"></td></tr>
<tr><td colspan="3" height="1" bgcolor="#D0A832"></td></tr>
</table>
<%
// Display records by department using the write_records() function
write_records("SELECT * FROM PhoneList_by_Department", "department", "FALSE", conn);
// Close database connections
rs.Close();
conn.Close();
%>And here's the other code that process the array:
Code:
/*
* FUNCTION write_records(sql, group_by, is_search)
*
* sql - the SQL statement indicating the records to be printed
* group_by - the grouping to group the records by
* is_search - is this a search? used to print message if no search results are found
*
* This function is used to write the records that are returned from the database
* using the sql statement to the page, in the grouping that is specified.
*/
function write_records(sql, group_by, is_search, db)
{
// Open recordset according to SQL statement given
rs = conn.Execute(sql);
// Check if there are any search results by testing if the recordset is initially at
// the end of file, and thus empty
var no_results = false;
if (is_search == "TRUE" && rs.EOF)
{
// Write a did not find any results message
Response.write( "<p class='h_text'>Sorry, the search <b>\"" + query + "\"</b> didn't return any results.
</p>" );
// Indicate for later that no records were returned by the search
no_results = true;
}
// Write a link to the previous page; the link text is different depending on if
// it is a search or not
if ( is_search == "TRUE" )
{
Response.write("<p><a href='/intranet/phone_list/index.asp'>");
Response.write("Search again");
Response.write("</a></p>");
}
else
{
Response.Write("<p></p>");
}
// Cycle through all records in the recordset and print them
var lastname = "";
var dept_current = -1;
var dept_heading = -1;
var subdept_current = -1;
var subdept_heading = -1;
var letter = "0";
while (!rs.EOF)
{
// Get the last name and the dept_current into a format we can use
lastname = unescape(rs.Fields("last_name").value);
dept_current = rs.Fields("department").value;
subdept_current = rs.Fields("subdepartment").value;
// Check if there is a different (first letter of) last_name or department heading to write
if ( group_by == "last_name" && lastname.substring(0,1).toUpperCase() != letter )
{
if ( letter != "0" )
Response.Write( "<br>" );
// Set new (first letter of) last_name, print the heading in 'section' style
letter = lastname.substring(0,1).toUpperCase();
// Print the lastname in uppercase in a table
printHeading( letter, 100);
}
if ( group_by == "department")
{
// Check for new department heading
if ( dept_current != dept_heading )
{
// Set new department heading, print the heading in 'section' style
dept_heading = dept_current;
//If the department is blank, say '(no department specific)
//if ( department[dept_heading] == "null")
//{
//printHeading( "(no department specified)", 600);
//}
//else
printHeading( department[dept_heading], 600) ;
// Check for subdepartment headings
}
// Check for new subdepartment heading
if ( subdept_current != subdept_heading )
{
// Set new department heading, print the heading in 'section' style
subdept_heading = subdept_current;
// If the department is blank, say '(no department specific)
//if ( subdepartment[dept_heading] == "null")
//{
//Response.write( "<p class='h_section'>(no department specified)</p>");
//}
//else
printHeading( subdepartment[dept_heading], 600 ) ;
}
}
// Print the records in 'text' style
Response.write( "<span class='h_text'>");
// Determine if any formatting of last name and first name is needed
// bold if record is a department head
// italic if record is an admin team leader
var department_head = unescape(rs.Fields("department_head").value) == "true";
var admin_team_leader = unescape(rs.Fields("admin_team_leader").value) == "true";
if ( department_head )
Response.write("<b>");
if ( admin_team_leader )
Response.write("<i>");
Response.write( lastname.toUpperCase() );
// Print first name and end any special formatting
if ( rs.Fields("first_name").ActualSize > 0)
{
Response.write( ", " + rs.Fields("first_name").value );
// End special formatting if it exists
if ( admin_team_leader )
Response.write("</i>");
if ( department_head )
Response.write("</b>");
}
// Print the job_title, dept_current and both phone numbers if they exist
if ( rs.Fields("job_title").ActualSize > 0)
Response.write( " – " + rs.Fields("job_title").value );
if ( rs.Fields("phone_no_1").ActualSize > 0)
Response.write( ", " + rs.Fields("phone_no_1").value );
if ( rs.Fields("phone_no_2").ActualSize > 0)
Response.write( ", " + rs.Fields("phone_no_2").value );
// Print images of heart for CPR or Blackberry if record has these designations
if ( unescape(rs.Fields("has_CPR_training").value) == "true" )
Response.write(" <img border=\"0\" src=\"../images/cpr.gif\" WIDTH=\"11\" HEIGHT=\"10\">");
if ( unescape(rs.Fields("has_blackberry").value) == "true" )
Response.write(" <img border=\"0\" src=\"../images/berry.gif\" WIDTH=\"14\" HEIGHT=\"10\">");
// Print to the next line next time
Response.write( "<br>");
// End the style tag
Response.write( "</span>");
// Go to next record
rs.MoveNext();
}
// Write legend at bottom of document
if (! no_results) // prevent extra space from being printed when there are no search results
Response.write( "<br>");
Response.write( "<br>");
Response.write( "<table border='0' cellspacing='0' style='border-collapse: collapse' bordercolor='#111111'
width='261' cellpadding='0' id='AutoNumber1'>");
Response.write( "<tr><td width='253' colspan='3' bgcolor='#CC9900' height='1'></td></tr><tr>");
Response.write( "<td width='1' bgcolor='#CC9900'></td><td width='222'>");
Response.write( "<span class='h_text'><p><br>");
// LEGEND TEXT BEGINS HERE
Response.write( " <b><u>LEGEND<br>");
Response.write( "</u> </b>");
// --- display image of a heart
Response.write( "<img src='../images/cpr.gif' border='0' width='11' height='10'> ");
Response.write( "Employee has CPR training<br>");
Response.write( " ");
// --- display image of a blackberry
Response.write( "<img src='../images/berry.gif' border='0' width='14' height='10'> ");
Response.write( "Employee is a Blackberry user<br>");
Response.write( "<b> Department Head</b><br>");
Response.write( " ");
Response.write( "<i>Admin Team Leader</i>");
// LEGEND TEXT ENDS HERE
Response.write( "</span><br><br></p></td><td width='2' bgcolor='#CC9900'></td></tr><tr>");
Response.write( "<td width='253' colspan='3' bgcolor='#CC9900' height='2'></td></tr></table><br><br>");
}