Ordering a group and subgroup of data

[Anon]

Any help with getting the following to work is greatly appreciated!

I have an application where I’m letting people enter a statement in answer to a particular policy question that pertains to a national (U.S.) concern. The statement gets saved to a MySQL database.

Anyone can post a statement however we want to give priority to statements posted by members of our organization.

In the end, people will be able to choose to read all available statements, or just statements from a particular state. The statements entered by members should appear at the top of the state-only page with non-member statements showing underneath.

When viewing all statements, we want the statements to print to the screen ordered by state with the member from that state showing first and all other statements from that state following the leader. So:

VIEW ALL PAGE

Alabama
Member statement
Non-member statement 1
Non-member statement 2
Non-member statement 3, etc.

Alaska
Member statement
Non-member statement 1
Non-member statement 2
Non-member statement 3, etc.

The database includes a member field where we can indicate whether or not someone is a member with a “yes” or “no” value.

I’ve got all of this working except for the VIEW ALL PAGE. The member statement shows up within the correct state, but not at the top of the state section.

I have written the PHP for this like so (abbreviated here…):

// connect to dbase
$result = mysql_query("SELECT * FROM table ORDER BY state", $db);
while ($row = mysql_fetch_array($result)) {
if ($member == 'yes') {
print ("member statement");
} else {
print ("non-member statements");
}
}

I’ve also tried this without the else clause and with a different $result variable but the results are the same:

// connect to dbase
$result = mysql_query("SELECT * FROM table ORDER BY state AND leader=’yes’", $db);
while ($row = mysql_fetch_array($result)) {
if ($member == 'yes') {
print ("member statement");
}
if ($member == 'no’) {
print ("non-member statements");
}
}

I’m stumped! Any ideas? Thanks for any help you can offer!

[sarahk]

try this
SELECT * FROM table ORDER BY state, leader desc

The problem is that the leader/member flag is yes/no and you don't want the no's at the top so you have to sort the leader flag in Descending order. State is by default Ascending although you can add ASC for your own readability.

Best trick I have for new-to-SQL people is to get M'soft Access (most people have it on their office 2000 discs) and dummy up your tables and data. It has a pretty good query tool which will help you build your query. You can then view the SQL and adapt it back to MySQL.

this should be easy, so perservere.

[Anon]

Bingo! That works perfectly. Thanks so much - what a relief! One note for those that come after.... I had to use DESC rather than desc.

THANKS SARAH!