Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sleepingpeace
Starting Member
1 Post |
 Posted - 2011-02-15 : 14:11:34
|
| I know this is a SQL forum but I'm going to lay all my PHP code along with it. I'm having issues with the PHP script displaying the right results with the multiple prepared statements so I'm looking for an alternative to have a better query because I'm not that great with obtaining the data I want here. The $sql query indicated brings back all I need, but I want to bring back also the accounts.firstName and accounts.lastName of the wall.userId. It only brings back the firstName and lastName of the wall.posterId, how would I get it to do both?$sql = "select wall.userId, wall.posterId, wall.post, wall.date, accounts.id, accounts.firstName, accounts.lastName, accounts.profilePic from wall join accounts on accounts.id = wall.posterId where posterId in (select friendId from friends where userId = ?) and userId in (select friendId from friends where userId = ?) or userId = ? and posterId = ? or posterId = ? and userId in (select friendId from friends where userId = ?) or posterId in (select friendId from friends where userId = ?) and userId = ? order by date desc"; if ($stmt = $link->prepare($sql)) { $stmt->bind_param('iiiiiiii', $_SESSION['id'], $_SESSION['id'], $_SESSION['id'], $_SESSION['id'], $_SESSION['id'], $_SESSION['id'], $_SESSION['id'], $_SESSION['id']); $stmt->bind_result($userId, $posterId, $post, $date, $id, $firstName, $lastName, $profilePic); $stmt->execute(); $stmt->store_result(); while ($stmt->fetch()) { if ($posterId != $userId) { $sql2 = "select firstName from acccounts where id = ?"; if ($stmt2 = $link->prepare($sql2)) { $stmt2->bind_param('i', $userId); $stmt2->bind_result($postedName); $stmt2->execute(); if ($stmt2->fetch()) { echo "{$userId}<hr style='clear:both;' />"; echo "<a href='./profile.php?id={$posterId}'><img style='float:left;' class='profilePic' src='{$profilePic}' alt='Profile Pic' /></a>"; echo "<div style='float:left;width:510px;'><a href='./profile.php?id={$posterId}'>{$firstName} {$lastName}</a> >> {$postedName}<span style='float:right;'>{$date}</span><br />{$post}</div><hr style='clear:both;' />"; } $stmt2->close(); } } else { echo "<a href='./profile.php?id={$posterId}'><img style='float:left;' class='profilePic' src='{$profilePic}' alt='Profile Pic' /></a>"; echo "<div style='float:left;width:510px;'><a href='./profile.php?id={$posterId}'>{$firstName} {$lastName}</a><span style='float:right;'>{$date}</span><br />{$post}</div><hr style='clear:both;' />"; } } $stmt->close(); } |
|
|
RobertKaucher
Posting Yak Master
169 Posts |
Posted - 2011-02-15 : 21:33:45
|
| I think more importantly is this qctually SQL Server or MySQL?Could you post the DDL for the tables in question?===http://www.ElementalSQL.com/ |
 |
|
|
|
|
|
|
|
Query/Join Help (May be hard to understand)