2

I need to compose a list for a RSFormPro form field which contains options that display article titles and their respective date from a custom field.

In front something like this :

Select your courses :
Title of the article - 25/12/2019 (date from the custom field)

I see this article, but I don't know how combine it with the custom field www.rsjoomla.com/support/documentation/r...st-from-a-table.html

With the following code, I am able to retrieve article title from specific categories

// Prepare the empty array
$items = array();
// Prepare the database connection
$db = JFactory::getDbo();
// Keep this if you'd like a "Please select" option, otherwise comment or 
remove it
$items[] = "|Please Select[c]";

// Run the SQL query and store it in $results
$db->setQuery("SELECT title, title FROM jos0x_content WHERE catid = 13 or 
catid = 14");
$results = $db->loadObjectList();

// Now, we need to convert the results into a readable RSForm! Pro format.
// The Items field will accept values in this format:
// value-to-be-stored|value-to-be-shown
// Eg. m|M-sized T-shirt
foreach ($results as $result) {
    $value = $result->title;
    $label = $result->title;
    $items[] = $value.'|'.$label;
}

// Multiple values are separated by new lines, so we need to do this now
$items = implode("\n", $items);

// Now we need to return the value to the field
return $items;

In database :

Table jos0x_content : title
Table jos0x_fields_values : field_id=2(calendar field), value, and 
  in item_id the related id of the article 
  • There are several refinements that I would like to suggest, but before that we need to get some more clarity regarding where the associated date values are coming from. If this is about how to compose the raw query, please provide sufficient/relevant database table information (precise table names and columns of what you means to extract). – mickmackusa Nov 27 '19 at 22:33
  • Hi, add the infos, sorry it is difficult for me to explain en english. Thanks. – Laurent Nov 28 '19 at 5:35
1

It is basically a LEFT JOIN to relate the tables and WS_CONCAT() calls to prepare the output strings within the SQL (no extra php handling of the result set). If EVERY article has a date value in the fields_values table, then you can use innerJoin(); if you use innerJoin() and there is no related row in the joined table for a specific article, then that article will not be represented in the result set.

Because I am using a "glue" to concatenate with Joomla's concatenate() method, the output is not prone to breaking on NULL fv.value values.

I am using nested concatenation because I need two different "glues". The inner concatenation provides pipe-separated text like The Title|The Title. The outer concatenation appends space-hyphen-space then the formatted date string like The Title|The Title - 28-11-2019.

In case you want to apply some sorting to the query, I've arbitrarily decided to sort by article date in descending order -- feel free to play with that per your needs.

For the record, ALL of the $db->qn() calls can be safely removed from my script. Joomla endorses the use of its quoting methods to err on the side of keeping their websites secure and stable. However, all of your tables, columns, and their aliases will run just as smoothly without being backtick wrapped. (...as I have explained here, here, here, here, and here based on the concrete criteria discussed here.) You will notice a profound increase in script readability upon removing these unnecessary quoting methods.

This is not tested and it is entirely possible that I've made mistakes. If so, please don't be shy about commenting under my answer; I require all of my answers to work as desired.

$db = JFactory::getDBO();
try {
    $query = $db->getQuery(true);
    $query
        ->select(
              $query->concatenate([
                  $query->concatenate([
                      $db->qn("c.title"),
                      $db->qn("c.title")
                  ], "|"),
                  "DATE_FORMAT(" . $db->qn("fv.value") . ", '%d-%m-%Y')"
              ], " - ")
          )
        ->from($db->qn("#__content", "c"))
        ->leftJoin($db->qn("#__fields_values", "fv")
              . " ON " . $db->qn("c.id") . " = " . $db->qn("fv.item_id"))
        ->where([
              $db->qn("fv.field_id") . " = 2",
              $db->qn("c.catid") . " IN (13,14)",
              $db->qn("c.state") . " = 1")
          ])
        ->order($db->qn("fv.value") . " DESC");
    $db->setQuery($query);
    return "|Please Select[c]\n" . implode("\n", $db->loadColumn());
} catch (Exception $e) {
    return "|Syntax Error";
}

Additions based on commented requirements:

  • use MySQL's DATE_FORMAT() to customise your datetime stamp.
  • to include only published articles in the result set, add $db->qn("c.state") . " = 1" as another element in where()'s array.
  • Yes, this work, i have the date display like this 2019-12-22 23:00:00 do you know a way to display in french format 22-12-2019 without the time after. – Laurent Nov 28 '19 at 10:15
  • Sorry, is it possible to select only article published. Thanks. – Laurent Nov 28 '19 at 10:25
  • Yes i add this line to select only published $db->qn("c.state") . "=1", – Laurent Nov 28 '19 at 10:30
  • Thanks and sorry i have that error 0 syntax error, unexpected ')', expecting ']' – Laurent Nov 28 '19 at 13:13
  • 1
    Yes thanks you very much Mick, very helpful for me. – Laurent Nov 29 '19 at 7:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.