current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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']));
share|improve this question
    
What is the correct order? Could you show the query? –  Juhana Nov 18 '11 at 8:11
    
Ok will go edit my question now... –  NovacTownCode Nov 18 '11 at 8:12
    
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 Nov 18 '11 at 9:55

3 Answers 3

up vote 1 down vote accepted

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.

share|improve this answer
    
Ok will try this now, I haven't come across this before, thanks. –  NovacTownCode Nov 18 '11 at 8:30
    
Thanks this was so simple and easy, I didn't know that queries could be ordered based on results thanks again bud! –  NovacTownCode Nov 18 '11 at 8:32
up vote 0 down vote

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.

share|improve this answer
    
Will look into this thanks. –  NovacTownCode Nov 18 '11 at 8:15
up vote 0 down vote 
$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

share|improve this answer
    
It should be ORDER BY, not ORDERY BY :) –  brianreavis Nov 18 '11 at 8:22
    
thank you for warning fixed –  Utku Yıldırım Nov 18 '11 at 8:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.