我有一个带有表"profile_values"的sql数据库(是的,它是一个Drupal db),我想通过一个查询提取用户的姓名,头衔和公司ID。(我过去曾使用三个查询来执行此操作,但我想在一个查询中完成。
| fid | uid | value
| 4 | 100 | john
| 8 | 100 | doe
| 9 | 100 | web developer
| 22 | 100 | D3239GE77
我使用了不同的字段 id (fid) 来表明我要拉取的行之间可以有字段 ID,但字段 ID 不会更改,因此 4 将始终是名字,8 将是姓氏,9 将是标题,22 将是公司 ID。
我的第一次尝试:
$result = mysql_query("SELECT value FROM profile_values WHERE uid = '$uid' ORDER BY fid ASC");
while($row = mysql_fetch_array($result)) {
$userinfo[] = $row['value'];
$firstname = $userinfo[0];
$lastname = $userinfo[1];
$title = $userinfo[10];
$companyid = $userinfo[12];
}
但是此方法不起作用,因为根据用户将填写的可选字段,两者之间可能或多或少有字段。
我需要一种方法来表示,"在这个数组中,如果 fid = 4,$firstname = 值
$result = mysql_query("SELECT fid, uid, value FROM profile_values WHERE uid = '$uid' ORDER BY fid ASC");
$user = array();
while ($row = mysql_fetch_array($result)) {
switch ($row['fid']) {
case 4:
$key = 'firstname';
break;
case 8:
$key = 'lastname';
break;
case 9:
$key = 'title';
break;
case 22:
$key = 'company_id';
break;
default:
continue 2; // 2 required when using switch inside loop
}
$user[$key] = $row['value'];
}
我会尝试以下方法:
$result = mysql_query("SELECT fid, value FROM profile_values WHERE uid = '$uid' ORDER BY fid ASC");
while($row = mysql_fetch_assoc($result)) {
switch ((int)$row['fid']) {
case 4:
$firstname = $row['value'];
break;
case 8:
$lastname = $row['value'];
break;
case 9:
$title = $row['value'];
break;
case 22:
$companyid = $row['value'];
break;
}
}
然后,我将使用 isset()
来检查变量是否存在,例如 isset($lastname)
希望这有帮助
这是一种不需要switch
语句或if
的方法。它基于您之前将表的fid
映射到目标数组上的键的数组:
$fieldMap = array(
4 => 'firstname',
8 => 'lastname',
9 => 'title',
22 => 'companyid'
);
$userinfo = array();
$result = mysql_query("
SELECT fid, value
FROM profile_values
WHERE uid = $uid
ORDER BY fid ASC
");
while($row = mysql_fetch_array($result)) {
$fieldKey = $fieldMap[$row['fid']];
$userinfo[$fieldKey] = $row['value'];
}
下面是我最初的建议,我们将结果透视以获得"常规"表格式,而不是属性表格式:
SELECT
p1.value AS first_name,
p2.value AS last_name,
p3.value AS title,
p4.value AS company_id
FROM profile_values p1
LEFT OUTER JOIN profile_values p2
ON p2.uid = p1.uid
AND p2.fid = 8
LEFT OUTER JOIN profile_values p3
ON p3.uid = p1.uid
AND p3.fid = 9
LEFT OUTER JOIN profile_values p4
ON p4.uid = p1.uid
AND p4.fid = 22
WHERE p1.uid = $uid AND p1.fid = 4
这对于SQL来说比您当前的做法更昂贵,但它将允许您更轻松地从多个用户获取数据(只需将p1.uid = $uid
更改为例如p1.uid IN (2, 6, 29)
。