PHP/MySQL - Ordering rows

Question

I'm using a row to return all of the users for a particular project, which isn't a problem. However, how would I order it so that the first result is always the logged in user.

I see two solutions, both of which I've tried with no success:

Printing the user's name then using an if statement to only print the rest of the row if they are different from the user's name.
```
print user's name;
if (username !== user's name) {
    print this name;
}
```
Printing them all in a row but ordering it somehow so that the current user is at the top.

Any ideas?

EDIT

I'm trying it now with the following code:

$query7 = mysql_query("SELECT users_ID FROM projects_users WHERE projects_id = '$id' AND WHERE users_id <>'$q6[0]'") or die(mysql_error());

But I get the error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE users_id <>'1'' at line 1

I've tried != and NOT instead of <> but I can't see what's going wrong!

Never mind, fixed it. For future reference, don't sure WHERE twice ;)

Thanks for your help everyone.

Please show your query code and your output code.

Fosco
– Fosco

2011-06-04 17:16:27 +00:00
Commented Jun 4, 2011 at 17:16 — Fosco
– Fosco, Commented Jun 4, 2011 at 17:16
added some info in my answerù

dynamic
– dynamic

2011-06-04 17:18:58 +00:00
Commented Jun 4, 2011 at 17:18 — dynamic
– dynamic, Commented Jun 4, 2011 at 17:18

dynamic · Accepted Answer · 2011-06-04 17:17:38Z

2

You first solution is perfect.

If you want to use your second solution you should build your SQL query like this:

(SELECT * FROM user WHERE id = :currentUserID) UNION (SELECT * FROM user)

And you are good

answered Jun 4, 2011 at 17:17

dynamic

48.2k56 gold badges163 silver badges236 bronze badges

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

8 Comments

Sujit Agarwal Over a year ago

and this could be a comment @yes123 instead of an answer.

rid Over a year ago

+1 for speed :) Don't forget to exclude from the last query the user found in the first query, so you don't get it twice.

dynamic Over a year ago

@coding: he asked how to do the second solution. Isn't that an answer?

dynamic Over a year ago

@radu: I know it's pretty bad when you waste time when already one posted an answer. Stackoverflow should implement some more advanced LIVE notification when you are writing an answer

dynamic Over a year ago

@radu: regarding the exclude. You don't need that beacuse UNION isn't UNION ALL :)

|

rolling stone · Accepted Answer · 2011-06-04 17:22:28Z

1

I think you could do this with a simple mysql query:

SELECT * FROM table_name ORDER BY FIELD(username, 'username');

To learn more: http://dev.mysql.com/doc/refman/5.0/en/sorting-rows.html

answered Jun 4, 2011 at 17:22

rolling stone

13.1k9 gold badges49 silver badges63 bronze badges

1 Comment

rolling stone Over a year ago

@Sebastian I saw that you found a solution to your question, but just wanted to make sure you saw this. It should solve your problem with just a single query without having to do any more work. Here's a tutorial on ordering by specific field values with MySQL: electrictoolbox.com/mysql-order-specific-field-values

Mark Baker · Accepted Answer · 2011-06-04 17:25:32Z

1

SELECT *
  FROM USERS
 ORDER BY  (CASE user_id WHEN :currentUserID THEN 1 ELSE 2 END);

answered Jun 4, 2011 at 17:25

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

Comments

Sebastian Paaske Tørholm · Accepted Answer · 2011-06-04 17:33:16Z

1

if you want to use php than, You can try something like this

$currentuser = "";

$otherusers = "";

if(username == user's name)
{

$currentuser = username;

}
else
{
 $otherusers .= username;

}

echo $currentuser;
echo $otherusers;

edited Jun 4, 2011 at 17:33

Sebastian Paaske Tørholm

51.3k11 gold badges103 silver badges123 bronze badges

answered Jun 4, 2011 at 17:23

tejash

1675 bronze badges

Collectives™ on Stack Overflow

PHP/MySQL - Ordering rows

4 Answers 4

8 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

8 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related