Sort Drop Down List populated with MYSQL table + Add Links from same

Hello Guys
I am using Pligg Beta 9 and got this code to create a drop-down list from the list of stories in that table.
What I want to do now is to

Add a header to the drop down like - "Select Story"
Sort stories alphabetically
Get html links that are stored in those tables for the appropriate story and when the user selects that story, the page goes to that link

I have also included the format of the table.

Any help will be appreciated.

Thank you.

Table Structure:

CREATE TABLE `pliggb9_links` (
  `link_id` int(20) NOT NULL auto_increment,
  `link_author` int(20) NOT NULL default '0',
  `link_blog` int(20) default '0',
  `link_status` enum('discard','queued','published','abuse','duplicated') NOT NULL default 'discard',
  `link_randkey` int(20) NOT NULL default '0',
  `link_votes` int(20) NOT NULL default '0',
  `link_karma` decimal(10,2) NOT NULL default '0.00',
  `link_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `link_date` timestamp NOT NULL default '0000-00-00 00:00:00',
  `link_published_date` timestamp NOT NULL default '0000-00-00 00:00:00',
  `link_category` int(11) NOT NULL default '0',
  `link_lang` int(11) NOT NULL default '1',
  `link_url` varchar(200) NOT NULL default '',
  `link_url_title` text,
  `link_title` text NOT NULL,
  `link_title_url` varchar(255) default NULL,
  `link_content` text NOT NULL,
  `link_summary` text,
  `link_tags` text,
  `link_field1` varchar(255) NOT NULL default '',
  `link_field2` varchar(255) NOT NULL default '',
  `link_field3` varchar(255) NOT NULL default '',
  `link_field4` varchar(255) NOT NULL default '',
  `link_field5` varchar(255) NOT NULL default '',
  `link_field6` varchar(255) NOT NULL default '',
  `link_field7` varchar(255) NOT NULL default '',
  `link_field8` varchar(255) NOT NULL default '',
  `link_field9` varchar(255) NOT NULL default '',
  `link_field10` text NOT NULL,
  `link_field11` text NOT NULL,
  `link_field12` text NOT NULL,
  `link_field13` text NOT NULL,
  `link_field14` text NOT NULL,
  `link_field15` text NOT NULL,
  PRIMARY KEY  (`link_id`),
  KEY `link_author` (`link_author`),
  KEY `link_url` (`link_url`),
  KEY `link_date` (`link_date`),
  KEY `link_published_date` (`link_published_date`),
  FULLTEXT KEY `link_url_2` (`link_url`,`link_url_title`,`link_title`,`link_content`,`link_tags`),
  FULLTEXT KEY `link_tags` (`link_tags`)
) ENGINE=MyISAM AUTO_INCREMENT=61 DEFAULT CHARSET=latin1;

Code (markup):

System Table Load Code (to alphabetize and add links here?)

//this gets all the stories
    $storySql = "SELECT link_id, link_title from " . table_links . " where link_status = 'published' or link_status = 'queued';";
    $stories = $db->get_results($storySql);
    //store the id and title in an array
    $storylist = array();
    foreach ( $stories as $story )
        $storylist[] = array($story->link_id,$story->link_title);
    //pass the array to the template
    $var_smarty->assign('dropdownStories',$storylist);  

Code (markup):

Drop Down List Code (need ajax?)

<li>
    <div class="box" id="dropdownstory">
        <select>
            {foreach from=$dropdownStories value=dropdownstory}
            <option value="{$dropdownstory[0]}">{$dropdownstory[1]}</option>
            {/foreach}
        </select>
    </div>
</li>  

Code (markup):

 

Hi,

First off the easy one, to add a header just change it to this:

<li>
    <div class="box" id="dropdownstory">
        <select>
            <option value="">Select Story</option>
            {foreach from=$dropdownStories value=dropdownstory}
            <option value="{$dropdownstory[0]}">{$dropdownstory[1]}</option>
            {/foreach}
        </select>
    </div>
</li>

Code (markup):

To implement the ordering you would edit the query like so:

$storySql = "SELECT link_id, link_title from " . table_links . " where link_status = 'published' or link_status = 'queued' ORDER BY link_title ASC;";

Code (markup):

That will order the results by the 'link_title' column in Ascending order, you can use any column you want here and you can also change it to Descending (DESC) if you prefer.

To add new columns you will need to edit the query to begin with, by adding the column to the select like so (Note that I have removed 'link_id' as it will no longer be used):

$storySql = "SELECT link_url, link_title from " . table_links . " where link_status = 'published' or link_status = 'queued' ORDER BY link_title ASC;";

Code (markup):

You will then need to add it to the array so that it is available in your template (Again, I have removed 'link_id' as it won't be used):

$storylist[] = array($story->link_url,$story->link_title);

Code (markup):

All of these changes will then give you the select list with a header, populated with url and title in alphabetical order. The last thing to do would be to carry out the redirection, you could edit the select to use an onchange event which would perhaps reload the page and at the top you could have a little PHP script which redirects the user if the select is not empty.

Or you could use Javascript to do something similar.

HTH