Populating a drop down list wih sql select statement

Hi

I do apologize if this topic is in the wrong forum. I am trying to populate a drop down list with an sql select statement. I know the correct sql statements to create the appropriate results but I don't know how to populate the results in a drop down list.

Below is an example of the sql statement.

SELECT title
FROM jos_eventlist_events
WHERE dates > CURDATE( )

Thanks

Mark

I'd stick something like this into the form code.
I took the liberty of also retrieving the event id, and using this as the value for each drop down item. If you'd rather have the event-title as the value aswell, just change $event->id to $event->title

<select name="somefield">
<?php
$db =& JFactory::getDBO();
$db->setQuery("SELECT id, title FROM jos_eventlist_events WHERE dates > CURDATE()");
$events = $db->loadObjectList();
foreach ($events as $event) {
  echo ('<option value="' . $event->id . '">' . $event->title . '</option>');
}
?>
</select>

Edit:
Fixed minor typo.

thanks for you quick reply although at the moment i get this error

Parse error: syntax error, unexpected T_OBJECT_OPERATOR

hope you can help

Made a minor typo, previous post is updated to fix this.

Change

$db = JFactory->getDBO();

Into

$db =& JFactory::getDBO();

[[>> edited to add the & to get a reference to the db object, you don't want a whole new copy of the object : greyhead <<]]

Thanks for the fix, Bob.
All those tiny issues you don't see in your own code...

Hi,

I have been able to get all of this working. The only problem I have now is that after a selection is made the id# is selected and not the name of the event that was selected. If anyone could help me I would really appreciate it.

The reason I need this is that when the email is sent right now it says "You have registered for id#" adn what I want it to say is "You have registered for event"

Below is the php from my form for the dropdown list.

<?php
$db =& JFactory::getDBO();
$db->setQuery("SELECT id, title FROM jos_eventlist_events WHERE dates >= CURDATE()");
$events = $db->loadObjectList();
foreach ($events as $event) {
  echo ('<option value=' . $event->id . '>' . $event->title . '</option>');
}
?>

Thanks in advance for all the help.

Hi, in the onsubmit before email you will need to query the same table and use the posted data of this select box to load the event title! OR just load your select box options values with the title directly if its fine for you ?

Cheers
Max

I believe that would be fine, but how would I do that. When I try I just end up with blank options, but I am not very good at PHP.

Hi Ayn,

if you are going to use my 2nd option then you need to replace $event->id with $event->title in the loop!

Cheers
Max