如何使用PHP在MySQL表上使用内连接？

问题描述：

I have two tables like this:

Employee

EmployeeID  EmployeeName
1234        Jessica
1235        Tiffany
1236        Kayla
1237        Jackson
1238        Junior
1239        Ray
1240        Raymond

And...

Form

IDForm Form_EmployeeID Content AgreementBy  Verificationby  Validateby  receiver

1      1234            abcde   1235         1236            1237        1240

2      1238            dcbe    1235         1239            1237        1240

The problem is, I want to display a data like this:

I have tried use this code:

$id=$_GET['id'];

$sql=mysql_query("SELECT Employee.*, Form.*
                        FROM Form
                        INNER JOIN Employee ON Form.Form_EmployeeID= Employee.EmployeeID
                        where Form.FormID = '$id'") or die(mysql_error()); $data=mysql_fetch_array($sql);

But the data appears just like this:

Form ID        : 1
Employee Name  : Jessica
Content        : abcde
Agreement By   : 
Verification By: 
Validate By    : 
Received By    :

or like this:

 Form ID        : 1
Employee Name  : Jessica
Content        : abcde
Agreement By   : 1235
Verification By: 1236
Validate By    : 1237
Received By    : 1240

May I know where is the mistake?

我有两个这样的表： p>

员工 strong> p>

  EmployeeID EmployeeName 
1234 Jessica 
1235 Tiffany 
1236 Kayla 
1237 Jackson 
1238 Junior 
1239 Ray 
1240 Raymond 
  code>  pre> \  n 
 并且......  p> 
 
 
 表单 strong>  p> 
 
 
  IDForm Form_EmployeeID Content AgreementBy Verificationby Validateby receiver  
 
1 1234 abcde 1235 1236 1237 1240 
 
 1238 dcbe 1235 1239 1237 1240 
  code>  pre> 
 
 问题是，我想显示如下数据：   p> 
 
 
我尝试过使用此代码： p> 
 
 
  $ id = $ _ GET ['id']; 
 
 $ sql =  mysql_query（“SELECT Employee。*，Form。* 
 FROM Form 
 INNER JOIN Employee ON Form.Form_EmployeeID = Employee.EmployeeID 
，其中Form.FormID ='$ id'”）或死（mysq  l_error（））;  $ data = mysql_fetch_array（$ sql）; 
  code>  pre> 
 
 但数据显示如下： p> 
 
 
 表单ID  ：1 
员工姓名：Jessica 
Content：abcde 
Agreement By：
Verification By：
Validate By：
Received By：
  code>  pre> 
 
 或者像这样：  p> 
 
 
 表单ID：1 
员工姓名：Jessica 
Content：abcde 
Agreement By：1235 
验证者：1236 
Validate作者：1237 
接收者：1240 
  code  >  pre> 
 
 我可以知道错误在哪里吗？ p> 
  div>

答

Your Query something like this...

SELECT Form.*, 
    a.EmployeeName as employee_name,
    b.EmployeeName as agreement_by,
    c.EmployeeName as verification_by,      
    d.EmployeeName as validate_by,
    e.EmployeeName as receiver_by
                    FROM Form
                    LEFT JOIN Employee a ON Form.Form_EmployeeID= a.EmployeeID
                    LEFT JOIN Employee b ON Form.AgreementBy= b.EmployeeID
                    LEFT JOIN Employee c ON Form.Verificationby= c.EmployeeID
                    LEFT JOIN Employee d ON Form.Validateby= d.EmployeeID
                    LEFT JOIN Employee e ON Form.receiver= e.EmployeeID
                    where Form.FormID = '$id'

答

Try this query and don't use '*' in select query...

 SELECT f.IDForm , f.Content ,
 emp1.EmployeeName as employee_name,
 emp2.EmployeeName as agreement_by,
 emp3.EmployeeName as verification_by ,      
 emp4.EmployeeName as validate_by,
 emp5.EmployeeName as receiver_by
 FROM Form f
    INNER JOIN Employee emp1 ON emp1.Form_EmployeeID= f.EmployeeID
    INNER JOIN Employee emp2 ON emp2.AgreementBy= f.EmployeeID
    INNER JOIN Employee emp3 ON emp3.Verificationby= f.EmployeeID
    INNER JOIN Employee emp4 ON emp4.Validateby= f.EmployeeID
    INNER JOIN Employee emp5 ON emp5.receiver= f.EmployeeID
 where Form.FormID = '$id'

如何使用PHP在MySQL表上使用内连接？

相关推荐