Better select - where code?

[ziah75]

I think this code can be optimized? (although it works, it seems a bit ugly ;-) )

Let me explain what I'm trying to do. I have a form where you can select different audio formats. These possible formats are specified in a mySQL table called def_audio.
Def audio holds the fields def_audio_id and def_audio_text.

def_audio_text are the options available in a form selectbox. Options are Generated like this:

<select size=1 name="audio">
<?php
//audio select
$result = mysql_query("select * from def_audio order by def_audio_text asc");

            while ( $audio_result = mysql_fetch_array($result) ) {
            ?>
            <option><?php echo $audio_result['def_audio_text']; ?>
            <?php
            }
            ?>

</select>

That's pretty ok, I think (and hope).
But, then I wan't to pull out the index (def_audio_id) for the selected item.
I've solved it like below, but since I will always get only one number as result, could it be done more compact?

$result = mysql_query("SELECT def_audio_id FROM def_audio WHERE def_audio_text='$audio'");

$audio_result = mysql_fetch_array($result);

echo $audio_result['def_audio_id'];

BR
Anders

[toma42]

If you used a database abstraction library like PEAR you could do it:

$value = $db->getOne("select onecolumn from atable");

[dannys]

If you're really wanting a oneliner you could do something like:

$defAudioId=mysql_result(mysql_query("SELECT def_audio_id FROM def_audio WHERE def_audio_text='$audio'"),0);