selecting multiple rows from a table and printing it in the same line

[nlal]

i m trying to extract multiple rows and print it in a single line in a web page.
My code

while ($line = mysql_fetch_array($app, MYSQL_ASSOC))
 {     
   echo "<tr><td>";    
   echo "<a href=AssignedUpdateForm.php?app=".$line['App'].             
   ">".$line['App']."</a></td>";     
   echo "<td>" . $line['firstName'] ." ". $line['surname'] . "</td>"; 
   echo "<td>" . $line['ServerName'] . "</td>";     echo "</tr>";

}
outputs this:

Application    employee    Server
   z             a          c
   z             b          c
   z             a          d
   z             b          d

however i want it to output

Application    employee    Server
   z             a,b        c,d
   z             a,b        c,d

can someone help please

[kovik]

What is the logic here? There are 4 different types of records, but you are compacting them into 2. What is the logic for this compacting? Why is it that you can compact unrelated a's, b's, c's, and d's, but not z's? You need to be more specific.

[nlal]

wel actually the relationship hea is that an application can be handled by more than one employee and an application can be located on more than one server.Thus the one to many relatinship.
For eg.
relationship between Application and Employee

Employee    Application  
john        sales
mary        sales

relationship between application and server

Server      Application
Server A    sales     
Server B    sales     
 

when i do a select statement from the two tables to print in a single table, i get

Application     Employee    Server
sales           john        Server A
sales           mary        Server A
sales           john        Server B
sales           mary        Server B
 

However i want to print

Application     Employee      Server
sales           john,mary     Server A,Server B

Is that possible?

[kovik]

Probably, yes. Tell us what your table structure looks like and the query that you are currently using.

[nlal]

My table structure is as follows:

DROP TABLE IF EXISTS employee;
CREATE TABLE employee(
emp_id VARCHAR(5) NOT NULL,
firstName VARCHAR(30) NOT NULL,
surname VARCHAR(30) NOT NULL,
TelephoneNumber VARCHAR(7),
Email VARCHAR(30),
username VARCHAR(30),
password VARCHAR(256),
CONSTRAINT emp_pk_EDPnum PRIMARY KEY(emp_id));
 
DROP TABLE IF EXISTS Application;
CREATE TABLE Application(
App VARCHAR(30) NOT NULL,
AppName VARCHAR(60) NOT NULL,
AppDescription VARCHAR(180),
YearDeveloped YEAR(4),
Development_Language VARCHAR(30),
Comments VARCHAR(120),
SectionID INT NOT NULL,
DBID INT NOT NULL,
CONSTRAINT Application_pk_App PRIMARY KEY(App),
CONSTRAINT Application_fk_SectionID FOREIGN KEY(SectionID) REFERENCES Section(SectionID),
CONSTRAINT Application_fk_DBID FOREIGN KEY(DBID) REFERENCES DB(DBID));
 
DROP TABLE IF EXISTS App_emp;
CREATE TABLE App_emp(
App_empID INT NOT NULL AUTO_INCREMENT,
emp_id VARCHAR(5) NOT NULL,
App VARCHAR(30) NOT NULL,
CONSTRAINT AppSupp_pk_AppSuppID PRIMARY KEY(App_empID),
CONSTRAINT AppSupp_fk_EDPnum FOREIGN KEY(emp_id) REFERENCES SupportPersonnel(emp_id),
CONSTRAINT AppSupp_fk_App FOREIGN KEY(App) REFERENCES Application(App));
 
DROP TABLE IF EXISTS Server;
CREATE TABLE Server(
ServerName VARCHAR(60) NOT NULL,
RackNum INT NOT NULL,
NetworkStatus INT,
ProcessorSpecification VARCHAR(90),
Memory INT,
AvailableMemory INT NOT NULL,
OperatingSystem VARCHAR(60),
OS_version VARCHAR(60),
LogonServer VARCHAR(30),
BIOSversion VARCHAR(30),
Remarks VARCHAR(60),
App VARCHAR(30),
CONSTRAINT Server_pk_ServerName PRIMARY KEY(ServerName),
CONSTRAINT Server_fk_App FOREIGN KEY(App) REFERENCES Application(App));
 

<?php    
   // build query
   $qry = "Select firstName, surname, App_emp.App, ServerName from  employee,App_emp,".
   "Server where App_emp.emp_id=employee.emp_id and   App_emp.App=Server.App";
 
   //execute query
  $app = execute_query($qry) or die('Query failed: ' . mysql_error());
 
   // write a loop to print out the results.
   while ($line = mysql_fetch_array($app, MYSQL_ASSOC)) 
     { 
        echo "<tr><td>";
        echo "<a href=AssignedUpdateForm.php?app=".$line['App'].
        ">".$line['App']."</a></td>";
        echo "<td>" . $line['firstName'] ." ". $line['surname'] . "</td>";
        echo "<td>" . $line['ServerName'] . "</td>";
        echo "</tr>";        
     }    
?>

[kovik]

Would you mind showing the database structure in a tabular format? All of us who help you here do so on our own time, and sorting through your CREATE TABLE query isn't exactly fun. :P

[nlal]

I have attached the databse structure.

[kovik]

Not what I meant, but at least you've shown the relationships.

Servers have an application, applications can have multiple employees, and employees and can have multiple applications. And you want to merge all employees and applications by server?

I can't think of a way to make that into an atomic query. You'll have to use logic to merge the data together in the loops prior to output. Here's some semi-usable code, using implode():

$currentServer = null;
$applications = array();
$employees = array();
 
while ($data = next MySQL row) {
  if ($currentServer && $currentServer != $data->server) {
    // output table row here using implode() on the arrays
 
    $applications = array();
    $employees = array();
  } else {
    $applications[] = $data->application;
    $employees[] = $data->employee;
  }
 
  $currentServer = $data->server;
}