SQL ORDER BY (Starting with)

I have a database with the following table:

CATEGORY
id
name

Another entity in the database can be assigned any of these categories through a foreign key on that entity. In my PHP application, I want a dropdown option select box to show the current category by default, but also allow any of the others to be selected in order to change the category that the entity belongs to.

In trying to formulate a SQL query to populate the dropdown select options that shows all the categories starting with the currently sele开发者_如何学Ccted one, I was trying different things and came up with this:

SELECT name FROM category ORDER BY id=[ID] DESC

Which seems to work properly given my small set of sample data, but seeing as I was just guessing around, I'm not 100% confident this is the proper query and will continue to work with the full dataset. I'm hoping someone can confirm that this SQL is appropriate, or suggest if there is a better way to accomplish this.

Also, the form action links to a page that contains the UPDATE query, and currently I am getting the category ID back by first running a SELECT query for the category name, but is there a way I can just assign the category ID for each category to it's respective select option?

Thanks!

it is ok.
But you can easily use it without sorting(or with other order i.e by name) and add selected="selected" to one with id=[ID]

I'm not sure your best option is re-ordering the select instead of simply making the particular option selected (example below).

Your query should work consistently because id=[id] is a boolean which will then be converted to a 0 or a 1 depending on whether it is true or false. I do wonder about the index in such cases -- MySQL will not be likely to compensate for that. You may wish to swap that with a UNION DISTINCT, but you'll have to benchmark yourself:

-- possible UNION DISTINCT
SELECT * FROM CATEGORY WHERE ID = [id]
UNION DISTINCT
SELECT * FROM CATEGORY;

As to getting the ID from the <select> directly, you can always use the value property of the options:

<select name="mySelect">
    <option value="1">Green</option>
    <option value="2">Blue</option>
</select>

In the above, if green is selected, PHP will receive $_REQUEST['mySelecte'] == '1'.

FINAL PHP EXAMPLE

$selectedID = $_REQUEST['mySelect'];
// if you absolutely *have* to make the selected item first, replace the 
// following with one of the two queries above
$q = mysql_query( 'SELECT id, name FROM CATEGORIES' ) or die( mysql_error() );
echo '<select name="mySelect">';
while( $option = mysql_fetch_assoc( $q ) )
{
    echo '<option value="' . $selectedID . '"';
    if( $option[ 'id' ] == $selectedID ) echo ' selected="selected"';
    echo '>' . $option['name'] . '</option>' . PHP_EOL;
}
echo '</select>';

You suggested the query:

SELECT name FROM category ORDER BY id=[ID] DESC

This query will work, as id=[ID] will give a boolean result (either 1 or 0), and you use DESC to put 1 first. However, the other categories are not guaranteed to show up in any particular order. However, if you have some field that you want to sort by once you've taken care of putting the category first, you can add that on as another field in the ORDER BY clause.

SELECT name FROM category ORDER BY id=[ID] DESC, name ASC

If you want to sort the others by their id, that's allowed too:

SELECT name FROM category ORDER BY id=[ID] DESC, id ASC

To answer your second question, the way that you would populate the select statement is

puts "<select>"
databasehandle.select_all "select name, id from category ORDER BY..." do |name,id|
   printf '<option value="%d">%s</option>', id, name
end
puts "</select>"

(forgive my use of Ruby instead of PHP -- I assume you can translate.)

The contents of the value= attribute are sent back the value selected in the form.