I am sorry about the title as I am not sure whether my problem is in the PHP or the MSSQL query. I am pretty sure it is php.
I am repairing a PHP page that accesses two different MSSQL tables. the page worked fine until we had a system-wide change that created new department ID's and hence some new department names.
I have this almost completely fixed now except one minor thing. When you click List all departments, it Lists all the departments and each link has the correct individuals under them.
The problem is it lists a link for EACH individual in that department name for example if there are 10 people in the Business department we have ten links to Business and all have the same information under them.
I want to make it so there is only 1 link for each department. the two tables are directory (columns involved are Displayname and Department) and departments (columns involved are name and id)
Can someone please tell me where I need to change this so it only prints one link to each department? Is the problem with my SQL query or the PHP?
here is the code
function listDepts() {
$query = "SELECT directory.Lastname, directory.Firstname, directory.email,
directory.phone, directory.Office, directory.Department, departments.id,
departments.name FROM directory FULL JOIN departments ON
directory.Department=departments.id ORDER BY name";
$result = mssql_query($query);
echo "<h3>Please select a department:</h3>
";
echo "<ul>
";
for ($i=0; $i<mssql_num_rows($result); ) {
$info = mssql_fetch_assoc($result);
echo "<li><a href=\"dept.php?dept=$info[id]\">$info[name]</a></li>
";
}
echo "</ul>
";
}
Other query
function displayResults($query) {
$result = mssql_query($query);
if (mssql_num_rows($result) > 0) {
for ($i=0; $i<mssql_num_rows($result); $i++) {
$info = mssql_fetch_assoc($result);
if ($info[dept_name] != $last_dept) {
if ($i > 0) {
echo "</table>
";
}
echo "<h3><a href=\"$info[dept_url]\">$info[dept_name]</a></h3>
";
echo "<table id=\"directory_table\">
";
echo "<tr>
";
echo "<th>Name</th>
";
echo "<th>E-mail</th>
";
echo "<th>Phone</th>
";
echo "<th>Office</th>
";
echo "<th>Title</th>
";
echo "</tr>
";
}
if (!$info[dept_name] && $i==0) {
echo "<table id=\"directory_table\">
";
echo "<tr>
";
echo "<th>Name</th>
";
echo "<th>E-mail</th>
";
echo "<th>Phone</th>
";
echo "<th>Office</th>
";
echo "<th>Title</th>
";
echo "</tr>
";
}
if ($i % 2 == 0) {
echo "<tr class=\"even\">
";
} else {
echo "<tr class=\"odd\">
";
}
echo "<td>";
echo ($info[Firstname]) ? "$info[Firstname]" . " " . "$info[Lastname]" : " ";
echo "</td>
";
echo "<td>";
echo ($info[email]) ? "<a href=\"mailto:$info[email]\">$info[email]</a>" : " ";
echo "</td>
";
echo "<td>";
echo ($info[phone]) ? "$info[phone]" : " ";
echo "</td>
";
echo "<td>";
echo ($info[office]) ? "$info[office]" : " ";
echo "</td>
";
echo "<td>";
echo ($info[title]) ? "$info[title]" : " ";
echo "</td>
";
$last_dept = $info[dept_name];
}
echo "</table>
";
} else {
echo "<p>No results found.</p>
";
}
}