Creating a module to search the database and display the results

Question

I have got a table in the database called #__magazines, this holds data for magazines (date published, issue number etc..). I have created a Component, Plugin and Module which is for Joomla 3.x, I want to be able to have the module display variables (let's use Published Dates as an example) which if they are chosen and then the "Search" button is clicked, the module will go to the database and then bring back all of the results which match the chosen date and display the results in a new page.

I've followed the Joomla tutorials, but I can't workout the best way to query the #__magazines table and then display the results.

UPDATE

I have now got the search plugin working correctly, so if I search for a magazine title (which I know is in the database) it appears in the search results.

What I want to know however, is how I can display all of the different variables from the database table of #__magazines and show them as dropdowns in a search module but not have duplicates. For example, if there is a title called June 2014 and this is in the database twice, I only want it to show in the Select Option dropdown once, if it is then chosen and the "Search" button clicked the user is taken to the results page, where the multiple version of the "June 2014" details are shown.

Answer 1

I've fixed this by using the following in the default.php file within the module:

// Get a db connection.
$db = JFactory::getDbo();

// Published dates
$query = $db->getQuery(true);
$query->select($db->quoteName(array('publishedDate')));
$query->from($db->quoteName('#__magazines'));
$query->order('publishedDateASC');
$query->group('publishedDate');
$db->setQuery($query);

// Load dates
$dates = $db->loadObjectList();

// Input
$input = JFactory::getApplication()->input;

<label for="publisheddate">Departure Date:</label>
            <select id="publisheddate" name="publisheddate">
                <option selected="selected" value="A">Choose Date</option>
                <?php $dateInput = $input->get('publisheddate', null, 'string'); ?>
                <?php foreach ($dates as $result): ?>
                <option value="<?php echo $result->publishedDate; ?>"<?php echo $dateInput == $result->publishedDate ? ' selected="selected"' : ''; ?>>
                    <?php echo $result->publishedDate; ?>
                </option>
                <?php endforeach; ?>
            </select>

This went to the #__magazines table, and retrieved one of each publishDate and populated the table with it!

Answer 2

Update: To modify my snippet for dynamic use for an assortment of possible incoming variable values, I suppose you could just declare a "whitelist" of column names and check that the incoming $column to be queried on is valid.

For example, $column = "publishedDate"

You just need to relace publishedDate with $column in my script and call $db->qn() upon it each time just to be safe.

... and use strtolower() to ready the variable for dom attribute declarations.

---

Tested Code:

$db = JFactory::getDBO();
try {
    $query = $db->getQuery(true)
                ->select("DISTINCT publishedDate")
                ->from("#__magazines")
                ->order("publishedDate");
    // echo $query->dump();  // uncomment if you want to see what is generated
    $db->setQuery($query);
    if ($dates = $db->loadColumn()) {
        $user_date = JFactory::getApplication()->input->get('publisheddate', null, 'string');
    }
} catch (Exception $e) {
    $dates = array();
    // echo "Syntax Error" , $e->getMessage();
}

echo "<label for=\"publisheddate\">Departure Date:</label>";
echo "<select id=\"publisheddate\" name=\"publisheddate\">";
    echo "<option value=\"A\">Choose Date</option>";
    foreach ($dates as $date) {
        echo "<option" , ($user_date == $date ? " selected" : "") , ">{$date}</option>";
    }
echo "</select>";

Explanations:

• A try {} catch {} block is a clean way to check for errors and control how your code performs after a potential query syntax error.
• I am "method chaining" from getQuery() so that $query is only written once while building the query.
• In most cases, your query is better written as a SELECT DISTINCT rather than GROUP BY. Here is a supporting post.
• Your select() method doesn't need to receive an array - a string will do just fine. Because publishedDate doesn't NEED to be backtick-wrapped, spare the qn() method call and leave it as an unquoted string with no security implications.
• ORDER BY will use ASC as the default direction, so there is no need to state this explicitly. And you needed a spaced before your ASC, but that may have merely been a posting typo.
• loadColumn() is the most appropriate call when you want to generate a one-dimensional array from a single column resultset.
• I am only seeking $user_date if the $dates array is not empty. The conditional is doing two things: 1. declaring $dates and 2. checking for a non-falsey value (an empty array is falsey). If there are no elements in $dates then whether or not the user has submitted $_GET['publisheddate'] is moot.
• If there is a syntax error in your query, I am declaring $dates as an empty array so that the foreach() loop cannot iterate.
• Never show $e->getMessage() to the public.
• My personal preference is to use an echo to display each line of code rather than bounce in and out of php or write multi-line strings.
• I am not sure why the first/default option has a value of A. I'll assume this means "All" and you will display all records in the search results which seems like a reasonable feature.
• Writing selected="selected" in the first <option> is not needed. If no other option is selected, it will be displayed as the selected option.
• The resultset values are declared as $date inside of the foreach() loop and no object syntax (->) is required.
• When writing <option> values that are identical to the visible text, there is no need to declare the value attribute. Spare the code bloat; the text will be submitted as the value.
• The curly brackets around $date is my preferred coding style -- not necessary in this case.