Select Order

[Doyley]

Hi all,

 

I am trying to structure a query where it selects all rows in a table but it selects a certain row first and then selects the rest of the rows after that.  Does that make sense?

 

Many thanks.

[cmgmyr]

SELECT * FROM your_table ORDER BY id ASC

 

like that?

[Doyley]

Thanks for your reply.

 

No, i'll try to provide an example.

 

uid    item

1      Shoes

2      Socks

3      Gloves

4      Coat

5      Scarf

 

I would like it so it would select item 3 first and then the rest afterwards.

[igor berger]

So run 5 MySql commands select this

select that

etc.

Or slect * than print them from the array depending on the order you want!

 

Or redesign your database

5 fields item 1 item 2 etc

use

ORDER BY `item1` ASC , `item2` ASC "

 

Okay.

[Doyley]

Ok, this is the table

 

  id  rulesdesc 

      1 House Rules (Ask Licensee)

      2 Old EPA

      3 BAPTO

      4 World Rules

      5 Blackball

      6 Official Pub Pool Rules

      7 Other

 

Earlier on in the PHP script I have pulled out the ID from another table that relates to the id from this table.  I want to make a drop down field with the ID I have pulled out selected and the rest of the results.

 

I can do this pretty easily with 2 sql queries but I was wondering if there was a more efficient way to do this.

[igor berger]

I am still not clear what you want to do?

 

This is your MySql table, right?

 

And how do you want the results to come out?

[fenway]

You can use ORDER BY FIELD( yourField, yourID ) DESC, yourField

 

or add an IF() to your select column list, and then order by that... same idea.

[bubblegum.anarchy]

If the desire is to have rulesdesc `Other` at the bottom of the list:

 

SELECT * FROM table 
ORDER BY rulesdesc = 'Other', rulesdesc

[Doyley]

Hmm I don't think so.

 

This is the code I am currently using.  It works but I think there is a better way to do it.  $rules is a number which relates to the id column from the table I posted above.

 

$selectsql="select rulesdesc from rules where id='$rules'";
$selectnew=mysql_query($selectsql) or die(mysql_error());
$sel=mysql_fetch_array($selectnew);

$select2sql="select id,rulesdesc from rules where id!='$rules'";
$select2new=mysql_query($select2sql) or die(mysql_error());

echo "<tr>\n";
echo "<td><b>Rules</b></td>\n";
echo "<td>\n";
echo "<select name=\"rules\">\n";
echo "<option value=\"$rules\">$sel[0]</option>\n";

while($sel2=mysql_fetch_array($select2new)){

echo "<option value=\"$sel2[0]\">$sel2[1]</option>\n";
}

echo "</select>\n";
echo "</td>\n";
echo "</tr>\n";