dongwen5351
dongwen5351
2015-06-29 07:10

如何使用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?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • dontoften8899 dontoften8899 6年前

    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'
    
    点赞 评论 复制链接分享
  • douang1243 douang1243 6年前

    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'
    
    点赞 评论 复制链接分享

相关推荐