Populate a PHP Dropdown List from MySQL Database in Joomla

Question

I am a very inexpert php programmer, I ve done some asp.net programming but never php.

I need the requirement to add a dropdownlist with values from a table in the mysql database. I manually created a table training: id training date hour openseats

And I need to display this dates and hour in the dropdownlist, so once the user clikc submits this get stored into a table called jos_jquarks_users_acknowledge

Can you help me how to pupulate the dropdown?

Code:

{source}
<!-- You can place html anywhere within the source tags -->
<?php 
// If the constant _JEXEC is not defined, quit now.
// This stops this script from running outside of the system.
defined( '_JEXEC' ) or die( 'Restricted access' );
?>

<?php

$user = JFactory::getUser(); 
$id = $user->get('id'); 
$name = $user->get('name');
$username = $user->get('username'); 
$department = $user->get('department');

$vardate = date("Y-m-d H:i:s");

$acknowledge = 1;
$courseTitle = $mainframe->getPageTitle();

$courseDate = ;
$courseHour =;


/***************************************/

$db = &JFactory::getDBO();

$query = "
INSERT INTO
`jos_jquarks_users_acknowledge`
(
course_name,
user_id,
employeeNumber,
department,
name,
acknowledge,
timeStamp,courseDate,
courseHour
)
VALUES
(
'{$courseTitle}',
'{$id}',
'{$username}',
'{$department}',
'{$name}',
'{$acknowledge}',
'{$vardate}',
'{$courseDate}',
'{courseHour}'

 )";

$db->setQuery($query);

$db->query();


if($db->getErrorNum()) { 
JError::raiseError( 500, $db->stderr()); 
}

?>

<form name="quiz_info" method="post" action="<?php echo $_SERVER['REQUEST_URI']; ?>"> 

<?php echo JText::_('Do you want to enroll into the course?') ; ?>

<? $queryCourses="SELECT training_id,training,trainingDate FROM training"; ?>

$result = mysql_query ($queryCourses); 
echo "<select name=courseDates value=''>Date</option>"; 
// printing the list box select command 

while($nt=mysql_fetch_array($result)){//Array or records stored in $nt 
echo "<option value=$nt[id]>$nt[training]</option>"; 
/* Option values are added by looping through the array */ 
} 
echo "</select>";//Closing of list box

<input id="proceedButton" name="proceedButton" value="Acknowledge" type="submit" />

<input type="hidden" name="layout" value="default" /> <?php echo JHTML::_( 'form.token' ); ?>

</form>

{/source}

There's many many questions/answers on this site on how to populate a dropdown via PHP/MySQL. here's one: http://stackoverflow.com/questions/6725263/php-mysql-drop-down-box-populate-selected-value — Marc B, Nov 16 '11 at 20:36

score 0 · Answer 1 · edited Oct 23 '14 at 11:15

COMPONENT CONTROLLER/TASK FUNCTION:

public  function ShowData()        {
    $course_id = JRequest::getVar('id');

    $db = JFactory::getDBO();
                $query = $db->getQuery(true);
                $query->select('*');
                $query->from('#__tablename');
                $query->where('courseid =\'' . $course_id.'\'');
                $db->setQuery((string)$query);

$data=$db->loadObjectList();

    $option = '<option value="0">choose...</option>';
     foreach ( $data  as $row)  {

     $option .= '<option value="' . $row->candidateid . '">' . $row->firstname .' '. $row->lastname. '</option>';

    }
    echo json_encode(array('options' =>$option));
jexit(); 

 }

ajax:

Searching from some combo (option selected value) and listing on some other combobox

$("select#searchcombo").change(function(){

        $("select#listingcombo").html("<option>wait...</option>");

        var id = $("select#searchcombo option:selected").attr('value');           
     var url='index.php?option=com_example&task=methodName&format=json';
    var dat = {'id':id};
            $.ajax({ 
                type: "POST", 
                cache: false, 
                url: url, 
                 data: dat,  
                dataType: "json", 
                success: function(data) {           
            //alert(data['options']);
        $("select#listingcombo").html(data['options']);
    }, error:function(xhr, status, data) {
            //alert("Status "+status + xhr.responseText );
    }
            }); 

      });

score 0 · Accepted Answer · answered Nov 16 '11 at 23:23

Difficult to know where to start in trying to answer. How much of the above code is actual, and how much is for demo purposes in asking the question? I assume you wont be inserting data into the database on each page load. I assume you won't be hard coding your database table name with the 'jos_' prefix. When doing this for real you should use '#__' without the quotes.

Does a database query returning an error need to kill the script and raise a server 500 error? I think detecting and intercepting the error and handling gracefully would be better.

After your tag you have the word date and then a - ie you close an option tag you never opened. I assume you intend to somehow label the select - best to do this with an actual tag rather than populating a dummy option within the select.

If you don't wrap your array keys in quotes you'll probably generate warnings - so do this $nt['training'] rather than $nt[training]

You probably need to retrieve as associative array rather than a standard ordinal array.

You then run a database query 'outside' of Joomla. You have retrieved the database object - you should use it rather than running mysql_query directly.

Instead of this:

$queryCourses="SELECT training_id,training,trainingDate FROM training"; ?>
$result = mysql_query ($queryCourses);

You probably need to do something more like this

// http://docs.joomla.org/How_to_use_the_database_classes_in_your_script#loadAssoc.28.29

$queryCourses="SELECT training_id,training,trainingDate FROM training"; ?>
$db->setQuery($queryCourses);
$db->query();

$num_rows = $db->getNumRows();
if(! $num_rows){ 
    // return or die or something - there ar no results
}
while($nt = $db->loadAssoc()){

On this line

echo "<option value=$nt['id']>$nt['training']</option>";

you should probably do this:

echo "<option value=\"{$nt['id']}\">{$nt['training']}</option>";

clearly delineating the variables with curly braces as the whole line is wrapped in quotes and remembering to put quotes around the value parameter in case you decide to add other variables into there and include spaces etc.

From your description you want to display the date to the user - you probably want to add an extra variable - maybe two within the ... part, perhaps something like:

echo "<option value=\"{$nt['id']}\">{$nt['training']} {$nt['trainingDate']}</option>";

I'm sure there are other things that I'm missing - and I've assumed your db query is broadly correct and returns results, but there should be enough pointers there to get you on the right track.

Finally - when building / populating a select list you can build data structures and get Joomla's jHTML class to do the heavy lifting for you. I wonder sometimes if too much abstraction creates more work than rolling your own - but hey, the option is there.

Populate a PHP Dropdown List from MySQL Database in Joomla

2 Answers2