我有3个表。其中一个表保存具有路径和object_id(不是唯一的(的某些活动的报告,另一个表,通过它我可以通过object_id获取用户id,第三个表,我可以从中获取适当条目的电子邮件。
我需要的是形式的阵列
email1 {
objectid1 {
path1
path2
...
}
objectid2 {
path3
...
}
}
email2{
...
}
等等。
我想先用SELECT DISTINCT检索电子邮件,然后foreach获取object_ids,foreach获取路径,但这会导致数据库查询量呈指数级增长,有更好的方法吗?
我最终做得有点不同,但从某种意义上说,我得到了所需的结果,我可以根据需要进一步解析数据。解决方案:
private function getrejects(){
// Here I just got joined data with duplicate emails and object_ids
global $DBC;
$qRecipients = pg_query($DBC,'
SELECT
users.email, objects.object_id, rejects.path, rejects.report_id
FROM
rejects
LEFT JOIN
objects
ON
rejects.object_id = objects.object_id
LEFT JOIN
users
ON
objects.user_id = users.user_id
WHERE
date_sent IS NULL
');
$result = array();
while($row = pg_fetch_assoc($qRecipients)){
array_push($result,$row);
}
return $result;
}
在这里,我将其放入一个数组中,并将电子邮件和object_id作为后续关键字。
private function formatemaildata($in){
foreach ($in as $row){
if (!isset($result[$row['email']][$row['object_id']])){
$result[$row['email']][$row['object_id']]=array();
}
array_push($result[$row['email']][$row['object_id']], $row['path']);
}
return $result;
}
转储时的结果与一致
'test@email.com' =>
array(3) {
[8573] =>
array(4) {
[0] =>
string(30) "http://lorempixel.com/400/200/"
[1] =>
string(30) "http://lorempixel.com/400/200/"
[2] =>
string(30) "http://lorempixel.com/400/200/"
[3] =>
string(30) "http://lorempixel.com/400/200/"
}
[8574] =>
array(4) {
[0] =>
string(30) "http://lorempixel.com/400/200/"
[1] =>
string(30) "http://lorempixel.com/400/200/"
[2] =>
string(30) "http://lorempixel.com/400/200/"
[3] =>
string(30) "http://lorempixel.com/400/200/"
}