Mysql - PHP 显示 3 列中一列的数据

Question

我安装了moodle，但正在构建独立的报告。

fieldid是与人员信息相关的整数。

fieldid    information
   1        Job Title
   2        Payrol
   3        Dept 

我想要包含信息的三个单独的列。它在moodle中使用别名运行良好，然后引用列信息的别名。但我无法让它在 php 中工作。

From Moodle

JOIN prefix_user_info_data AS uid ON uid.userid = u.id
JOIN prefix_user_info_data AS uid2 ON uid2.userid = u.id
JOIN prefix_user_info_data AS uid3 ON uid3.userid = u.id
JOIN prefix_user_info_data AS uid4 ON uid4.userid = u.id

WHERE uid.fieldid = '13'
AND uid2.fieldid = '1'
AND uid3.fieldid = '3'
AND uid4.fieldid = '8'

共享 php 和完整代码不会有太大帮助，我只展示了相关部分。我已经包含了使用 info.php 的部分。['data'] 列是信息所在的列。当我包含 AND uid2.fieldid = '1' 时，它只显示部门。我想要另外两列职位名称和工资单号码。

$course = $_POST["course"];
$date= $_POST["date"];
$sql = "SELECT firstname, lastname, data, name, statuscode, DATE_FORMAT       (FROM_UNIXTIME(timestart),'%d/%m/%y') AS timestart,
DATE_FORMAT(FROM_UNIXTIME(timefinish),'%d/%m/%y') AS timefinish


 FROM mdl_facetoface 
INNER JOIN mdl_facetoface_sessions ON       mdl_facetoface_sessions.facetoface=mdl_facetoface.id
INNER JOIN mdl_facetoface_sessions_dates  ON      mdl_facetoface_sessions.id=mdl_facetoface_sessions_dates.sessionid
 INNER JOIN mdl_facetoface_signups  ON     mdl_facetoface_sessions.id=mdl_facetoface_signups.sessionid
 INNER JOIN mdl_user ON mdl_facetoface_signups.userid=mdl_user.id
 INNER JOIN mdl_facetoface_signups_status ON   mdl_facetoface_signups.id=mdl_facetoface_signups_status.signupid
 INNER JOIN mdl_user_info_data  ON mdl_user_info_data.userid = mdl_user.id




 WHERE name LIKE '%".$course."%'
 AND mdl_user_info_data.fieldid ='1'
 AND DATE_FORMAT(FROM_UNIXTIME(timestart),'%d/%m/%y') LIKE '%".$date."%'
  AND firstname <> 'test'
 AND lastname <> 'test'
 AND statuscode ='70'
 AND statuscode<>'10'
 AND statuscode <>'20'
 AND statuscode <>'30'
 AND statuscode <>'40'
 AND statuscode <>'50'
 AND statuscode <>'60'
 AND statuscode <>'80'
 AND statuscode <>'90'
 AND statuscode <>'100'";

echo '<table width="90%">';

  echo '<td width="25%">'.'<h2>'.$row["firstname"].' '.$row["lastname"].'</h2>'.'</td><td width="25%">'.'<h2>'.$row["data"].'</h2>'.'</td><td width="20%"></td><td width="30%"></td>';

        }
      echo "</table>";

Answer 1

我终于有时间来解决这个问题了。像往常一样，当我弄清楚时，事情很简单。这些是我需要更改或添加的部分。

 SELECT  p.data AS pay, t.data AS title, d.data AS dept,

 FROM 
 INNER JOIN mdl_user_info_data AS p ON p.userid = mdl_user.id
 INNER JOIN mdl_user_info_data AS t ON t.userid = mdl_user.id
 INNER JOIN mdl_user_info_data AS d ON d.userid = mdl_user.id

 WHERE  p.fieldid='3'
 AND t.fieldid='9'
 AND d.fieldid='1'

 <td width="20%"><h2>'.$row["title"].'</h2></td>
 <td width="20%"><h2>'.$row["dept"].'</h2></td>
 <td width="15%"><h2>'.$row["pay"].'</h2></td>

为寻找干杯