While loop in order based on associative array php?

Question

I have the following code for displaying invites sent, received and accepted for a simple friends system.

while ($friend = $q -> fetch(PDO::FETCH_ASSOC)) {
      if ($friend['id'] == $user['id'] && $friend['friendAccept'] == 0) echo '<p>You have sent a request to be <a href="http://www.n.com/viewaccount?id=' . $friend['withID'] . '">' . $friend['username'] . '\'s</a> friend.</p>';
      if ($friend['withID'] == $user['id'] && $friend['friendAccept'] == 0) echo '<p>You have recieved a request from <a href="http://www.n.com/viewaccount?id=' . $friend['withID'] . '">' . $friend['username'] . '</a>.</p>';
      if ($friend['id'] == $user['id'] && $friend['friendAccept'] == 1) echo '<p>You are friends with <a href="http://www.n.com/viewaccount?id=' . $friend['withID'] . '">' . $friend['username'] . '</a>.</p>';
}

It will display what friends you have got, what friend requests you have sent, and what friend request have been received. I get all of this from one mysql query. But the only problem being they are not in order as the while loop iterates itself down the list and does whatever it is supposed to do.

Is there a way of getting them in order while still only using one query? By order I mean all the requests received in one list, requests sent in one list, and friends in one list. As it is at the minute they are in the order they are pulled from the table.

The query...

$q = $dbc -> prepare("SELECT social.*, accounts.username FROM social INNER JOIN accounts WHERE social.withID = accounts.ID AND (social.id = ? OR social.withID = ?) AND type = 'friend'");
$q -> execute(array($user['id'], $user['id']));

Note that PDOStatement implements the Traversable interface, so you can loop over results with foreach rather than while. It doesn't affect the order of the results, but is more readable ("for each element of a sequence" rather than "while some condition holds" better describes what the loop does). — outis
– outis, Commented Nov 18, 2011 at 9:55

Gustav Bertram · Accepted Answer · 2011-11-18 08:28:20Z

1

It's absolutely possible:

SELECT social.*, accounts.username 
FROM social 
INNER JOIN accounts 
WHERE social.withID = accounts.ID 
AND (social.id = ? OR social.withID = ?) 
AND type = 'friend'
ORDER BY 
(social.id = $user_id AND social.friendAccept = 0), 
(social.withID = $user_id AND social.friendAccept = 0),
(social.id = $user_id AND social.friendAccept = 1)

Just put the ORDER BY clause in the correct order. The way this works, it orders it using boolean values that the expressions evaluate to.

Of course, you should also convert $user_id to ? in the prepared statement.

answered Nov 18, 2011 at 8:28

Gustav Bertram

15k3 gold badges46 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

NovacTownCode Over a year ago

Ok will try this now, I haven't come across this before, thanks.

NovacTownCode Over a year ago

Thanks this was so simple and easy, I didn't know that queries could be ordered based on results thanks again bud!

Willem Mulder · Accepted Answer · 2011-11-18 08:13:36Z

0

You could change the SQL query to do an

ORDER BY x ASC/DESC

Or you could create an array of results in PHP, and then use any of the PHP sort functions to sort the array based on whatever criteria.

See http://php.net/manual/en/array.sorting.php for more info.

Edit: I think

uasort()

might be of help. You can define your own compare function, and PHP will then sort based upon that. See http://www.php.net/manual/en/function.uasort.php for mor info.

answered Nov 18, 2011 at 8:13

Willem Mulder

14.1k4 gold badges42 silver badges64 bronze badges

Comments

Utku Yıldırım · Accepted Answer · 2011-11-18 08:28:27Z

0

$q = $dbc -> prepare("SELECT social.*, accounts.username FROM social INNER JOIN accounts WHERE social.withID = accounts.ID AND (social.id = ? OR social.withID = ?) AND type = 'friend' ORDER BY social.friendAccept ASC");
$q -> execute(array($user['id'], $user['id']));

ORDER BY social.friendAccept

it will get not accepted users first.

if you give some data example i can be more help

edited Nov 18, 2011 at 8:28

answered Nov 18, 2011 at 8:20

Utku Yıldırım

2,27716 silver badges20 bronze badges

1 Comment

brianreavis Over a year ago

It should be ORDER BY, not ORDERY BY :)

Collectives™ on Stack Overflow

While loop in order based on associative array php?

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related