First I have a loop that displays a given users friend list.
$stmt=$db->prepare('SELECT friend1, friend2
FROM list_friends
WHERE (friend1 = :user AND friend2 <> :user)
OR (friend2 = :user AND friend1 <> :user)');
$stmt->bindParam(':user', $username);
$stmt->execute();
$row = $stmt->fetchAll()
foreach ($row AS $row) {
if ($row[friend1] !== $username) {
$friend = $row[friend1];
} else {
$friend = $row[friend2];
}
echo $friend;
}
Second I have a series of arrays being compared and sorted by time and date, then echo'd
$stmt=$db->prepare('SELECT * FROM banners WHERE username = :user');
$stmt->bindParam(':user', $friend);
$stmt->execute();
$row = $stmt->fetchAll();
$stmt=$db->prepare('SELECT * FROM favorites WHERE username = :user');
$stmt->bindParam(':user', $friend);
$stmt->execute();
$row1 = $stmt->fetchAll();
$stmt=$db->prepare('SELECT * FROM sites WHERE username = :user');
$stmt->bindParam(':user', $friend);
$stmt->execute();
$row2 = $stmt->fetchAll();
$stmt=$db->prepare('SELECT * FROM social_posts WHERE username = :user');
$stmt->bindParam(':user', $friend);
$stmt->execute();
$row3 = $stmt->fetchAll();
if ($db2->query("SHOW TABLES LIKE 'elfinder_file_".strtolower($friend)."'"
)->rowCount() > 0
){
$stmt=$db2->prepare("SELECT * FROM elfinder_file_".strtolower($friend)." WHERE mime <> 'directory' GROUP BY time");
$stmt->execute();
$row4 = $stmt->fetchAll();
}
foreach( $row AS $banner_table ) {
$data[] = array('type' => 'banner', 'time' => $banner_table["time"]);
}
foreach( $row1 AS $favorites_table ) {
$data[] = array('type' => 'favorite', 'time' => $favorites_table["time"]);
}
foreach( $row2 AS $sites_table ) {
$data[] = array('type' => 'sites', 'time' => $sites_table["time"], 'site' => $sites_table["url"], 'title' => $sites_table["title"]);
}
foreach( $row3 AS $social_table ) {
$data[] = array('type' => 'social', 'time' => $social_table["time"], 'thetype' => $social_table["type"]);
}
if ($db2->query("SHOW TABLES LIKE 'elfinder_file_".strtolower($_GET[user])."'")->rowCount() > 0 )
{
foreach( $row4 AS $photos_table ) {
$data[] = array('type' => 'photo', 'time' => $photos_table["time"]);
}
}
function cmp($a, $b) {
$ad = new DateTime($a['time']);
$bd = new DateTime($b['time']);
if ($ad == $bd) {
return 0;
}
return $ad < $bd ? -1 : 1;
}
if ($data !== NULL) {
usort($data, "cmp");
for($i=(count($data)-1);$i>=0;$i--){
$tttime = $data[$i]['time'];
$ttime = new DateTime($tttime);
$stmt=$db->prepare('SELECT timezone FROM member_credits WHERE username = :user');
$stmt->bindParam(':user', $username);
$stmt->execute();
$row = $stmt->fetch();
if ($row[timezone] === NULL) { $row[timezone] = 'America/Denver'; }
$usersTimezone = (new DateTimeZone($row[timezone]));
$ttime->setTimeZone($usersTimezone);
$ttimee = $ttime->format('D M j, Y g:i A');
if($data[$i]['type']=='favorite'){
echo '<li style="padding: 8px 5px 8px 95px;"><span class="date"><b>'.$ttimee.'</b></span>'.$friend.' added a website to their favorites while surfing the exchange.</li>';
} elseif($data[$i]['type']=='banner'){
echo '<li style="padding: 8px 5px 8px 95px;"><span class="date"><b>'.$ttimee.'</b></span>'.$friend.' added a banner to the banner exchange.</li>';
} elseif($data[$i]['type']=='sites'){
echo '<li style="padding: 8px 5px 8px 95px;"><span class="date"><b>'.$ttimee.'</b></span>'.$friend.' added the website <a href="'.$data[$i]['site'].'" target="_blank" style="font-weight:bolder;">'.$data[$i]['title'].'</a> to the traffic exchange.</li>';
} elseif($data[$i]['type']=='social'){
echo '<li style="padding: 8px 5px 8px 95px;"><span class="date"><b>'.$ttimee.'</b></span>'.$friend.' has requested a social exchange on '.$data[$i]['thetype'].'.</li>';
} elseif($data[$i]['type']=='photo'){
echo '<li style="padding: 8px 5px 8px 95px;"><span class="date"><b>'.$ttimee.'</b></span>'.$friend.' uploaded a photo to their profile.</li>';
}
}
}
The problem I am having is that if I do the first foreach, then the second, it only echo's results from the last friend in the loop (logically), and if I contain the second foreach inside the first, it generates all results for each user one by one, so all of user1's results will be returned and sorted by time, then all of user2's, and so on.
The result I am looking for, is a list of results all joined together, and then each returned based on time.
SAMPLE RESULTS:
user1 uploaded a photo at 12:00pm
user2 added a banner at 11:00 am
user2 uploaded a photo at 10:00 am
user1 added a favorite 9:00 am
etc.
etc.
etc.