Databases
StackOverflow2013
Postsdbo
POMSSQL and PHP echos repeating rows for same information
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMSSQL and PHP echos repeating rows for same information
    text
    copied!<p>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. </p> <p>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. </p> <p>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. </p> <p>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. </p> <p>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) </p> <p>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?</p> <p>here is the code</p> <pre><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 "&lt;h3&gt;Please select a department:&lt;/h3&gt;\n"; echo "&lt;ul&gt;\n"; for ($i=0; $i&lt;mssql_num_rows($result); ) { $info = mssql_fetch_assoc($result); echo "&lt;li&gt;&lt;a href=\"dept.php?dept=$info[id]\"&gt;$info[name]&lt;/a&gt;&lt;/li&gt;\n"; } echo "&lt;/ul&gt;\n\n"; } </code></pre> <p>Other query</p> <pre><code>function displayResults($query) { $result = mssql_query($query); if (mssql_num_rows($result) &gt; 0) { for ($i=0; $i&lt;mssql_num_rows($result); $i++) { $info = mssql_fetch_assoc($result); if ($info[dept_name] != $last_dept) { if ($i &gt; 0) { echo "&lt;/table&gt;\n\n"; } echo "&lt;h3&gt;&lt;a href=\"$info[dept_url]\"&gt;$info[dept_name]&lt;/a&gt;&lt;/h3&gt;\n\n"; echo "&lt;table id=\"directory_table\"&gt;\n"; echo "&lt;tr&gt;\n"; echo "&lt;th&gt;Name&lt;/th&gt;\n"; echo "&lt;th&gt;E-mail&lt;/th&gt;\n"; echo "&lt;th&gt;Phone&lt;/th&gt;\n"; echo "&lt;th&gt;Office&lt;/th&gt;\n"; echo "&lt;th&gt;Title&lt;/th&gt;\n"; echo "&lt;/tr&gt;\n"; } if (!$info[dept_name] &amp;&amp; $i==0) { echo "&lt;table id=\"directory_table\"&gt;\n"; echo "&lt;tr&gt;\n"; echo "&lt;th&gt;Name&lt;/th&gt;\n"; echo "&lt;th&gt;E-mail&lt;/th&gt;\n"; echo "&lt;th&gt;Phone&lt;/th&gt;\n"; echo "&lt;th&gt;Office&lt;/th&gt;\n"; echo "&lt;th&gt;Title&lt;/th&gt;\n"; echo "&lt;/tr&gt;\n"; } if ($i % 2 == 0) { echo "&lt;tr class=\"even\"&gt;\n"; } else { echo "&lt;tr class=\"odd\"&gt;\n"; } echo "&lt;td&gt;"; echo ($info[Firstname]) ? "$info[Firstname]" . " " . "$info[Lastname]" : "&amp;nbsp;"; echo "&lt;/td&gt;\n"; echo "&lt;td&gt;"; echo ($info[email]) ? "&lt;a href=\"mailto:$info[email]\"&gt;$info[email]&lt;/a&gt;" : "&amp;nbsp;"; echo "&lt;/td&gt;\n"; echo "&lt;td&gt;"; echo ($info[phone]) ? "$info[phone]" : "&amp;nbsp;"; echo "&lt;/td&gt;\n"; echo "&lt;td&gt;"; echo ($info[office]) ? "$info[office]" : "&amp;nbsp;"; echo "&lt;/td&gt;\n"; echo "&lt;td&gt;"; echo ($info[title]) ? "$info[title]" : "&amp;nbsp;"; echo "&lt;/td&gt;\n"; $last_dept = $info[dept_name]; } echo "&lt;/table&gt;\n\n"; } else { echo "&lt;p&gt;No results found.&lt;/p&gt;\n\n"; } } </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload