[SOLVED] diplaying data from mysql tables;

[rockindano30]

"hello me, its me again..."

 

i have this web app i'm working on and it should display an employee' firstname, lastname, email, their department, keynum, and access to what door.

 

so i have three tables:

employees(emp_id,Fname,Lname,email,dept_id)  /////dept_id foreign key and emp_id is primary

departments(dept_id,dept_name,Phone)

emp_keys(kid,emp_id,accss)                          /////kid is primary and emp_id is foreign

 

 

now i have it working where it displays the needed info, but now lets say for example and employee has three keys, it will display the same employee three times, where i just want it to display the employee's name email department once and key_nums N times the number of keys.

 

here's my code "ch... ch... ch... check it out..."

mysql_select_db("bldg_keys"); 

$search=$_POST["search"];
$search2=$_POST["search2"];

if(!isset($search) || empty($search) || !isset($search2) || empty($search2))
	{
		print"<p>Your search was not sucessfull due for one of the followings:<br />";
		print "Make sure that you have the first and last name filled.<br />";
		print "Make sure that you spelled the employee's name currectly.</p>";

	}
	else{
	$emp_id=$_GET["emp_id"];

			//get the mysql and store them in $result
			//change whatevertable to the mysql table you're using
			//change whatevercolumn to the column in the table you want to search
			////departments.emp_id='$emp_id',
//				$query = "select Fname,Lname, email, dept, key_num, accss FROM departments, employees, emp_keys where Fname like '%$search%' and Lname like '$search2'";

			$query = "select Fname,Lname, email, dept, key_num, accss FROM departments, employees, emp_keys where Fname like '%$search%' and Lname like '$search2' and departments.dept_id= employees.dept_id and emp_keys.emp_id=employees.emp_id";

			$result = mysql_query($query); 
			$num_result = mysql_num_rows($result);


			if ($num_result < 1)
			 print "<p>No employee found by that name.<br />Please make sure that you typed in the employee's name correctly.</p>";
			else {
		//grab all the content
		while($r=mysql_fetch_array($result))
		{	
		   //the format is $variable = $r["nameofmysqlcolumn"];
		   //modify these to match your mysql table columns
		   $dept=$r["dept"];			   
		   $Fname=$r["Fname"];
		   $Lname=$r["Lname"];
		   $email=$r["email"];
		   $key_num=$r["key_num"];
		   $accss=$r["accss"];

		   //display the row
		 //display the row
		?>
          <tr>
              <table width="100%" border="0" cellpadding="1" cellspacing="0" class="table1">
                <tr>
                  <td width="14%"><font face="Arial, Helvetica, sans-serif"><?php print $dept; ?></font></td>
                  <td width="17%"><font face="Arial, Helvetica, sans-serif"  style="color:#CC6600"><?php print $Fname." ".$Lname; ?></font></td>
                  <td width="28%"><font face="Arial, Helvetica, sans-serif"><?php print $email; ?></font></td>
                  <td width="10%"><font face="Arial, Helvetica, sans-serif"><?php print $key_num; ?></font></td>
                  <td width="50%"><font face="Arial, Helvetica, sans-serif"><?php print $accss ?></font></td>
                  <td width="50%">
                  <?php print"<a href='delete.php?emp_id={$emp_id['emp_id']}'>Remove</a><br />";?>
                  </td>
                </tr>
              </table>
          <?php
		}//end while loop
		?>
				  <?php
		}
	}
		?>

[rockindano30]

one more thing,

 

for every key it has to display what doors does it open.

[moselkady]

 

Try this modified query:

 

$query = "select Fname,Lname, email, dept, key_num, group_concat(accss separator ',') FROM departments, employees, emp_keys where Fname like '%$search%' and Lname like '$search2' and departments.dept_id= employees.dept_id and emp_keys.emp_id=employees.emp_id group by employees.emp_id";

 

[rockindano30]

i tried it but only displays one key number instead of three and no key access info.

[rockindano30]

i'm thinking the problem is in the while loop

[moselkady]

 

Actually it is my mistake. I should have renamed the concatenated field. Try this one.

 

$query = "select Fname,Lname, email, dept, group_concat(key_num separator ',') as key_num, group_concat(accss separator ',') as access FROM departments, employees, emp_keys where Fname like '%$search%' and Lname like '$search2' and departments.dept_id= employees.dept_id and emp_keys.emp_id=employees.emp_id group by employees.emp_id";

 

[rockindano30]

Thank you moselkady that solved it.