[SOLVED] getting all data from three mysql tables

[rockindano30]

hello

 

i have a problem here. 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

 

so here is part of my code:

this web app is to search for employees by names or for keys by key number

 

///bla bla bla connection to db here 
///
///
///
<?php 

//this is where i'm trying to get all info from all tables
//problem is this will print the same employee six time because there are six departments
//so doesn't know when to stop
//maybe i'm join the tables wrong.......
$query = "select Fname,Lname, email, dept, key_num, accss FROM departments, employees, emp_keys where Fname like '%$search%' and Lname like '$search2'";

			$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 {
			?>
          <table width="95%" border="0" cellspacing="2" cellpadding="2">
            <tr>
              <table width="100%" border="0" cellpadding="1" cellspacing="0" style="border-bottom:1px solid #000033;">
                <tr style="color:#ffffff; font-size:12px" align="left" bgcolor="#fc8200">
                  <td width="14%" colspan="1"><font face="Arial, Helvetica, sans-serif">Dept</font></td>
                  <td width="17%" colspan="1"><font face="Arial, Helvetica, sans-serif">Employee</font></td>
                  <td width="28%" colspan="1"><font face="Arial, Helvetica, sans-serif">Email</font></td>
                  <td width="10%"><font face="Arial, Helvetica, sans-serif">Key #</font></td>
                  <td width="50%"><font face="Arial, Helvetica, sans-serif">Department Access</font></td>
                  <td><font face="Arial, Helvetica, sans-serif">Remove</font></td>
                </tr>
              </table>
            </tr>
          <?php
		//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>
            </tr>
          <?php
		}//end while loop
		?>
				  <?php
		}
	}
		?>

 

PHPFreaks any Help???

 

Thanks in advance.

[moselkady]

 

Myabe you can modify your query as

 

$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";

 

 

[rockindano30]

Thank you man!!!! ;D ;D

it worked. i was using or trying to get it by using the emp_id and thats wrong and didn't noticed it.

 

thanks