Using Sourcerer to get text lines in a textarea field.

Nifa

Dear support,

I really hope you can help me, as this issue is freaking me out here 😉.

One of my extensions allow me to create a custom field type list dropdown select box for their form.
In the configuration component, I get a Values textfield where I can put in the values for that dropdown box.

So manually, you add something like below in that field:
---Select user---
Peter
Randy
Shelly
Junior

However, I want to fill this textfield dynamically based on a POST variable in the URL. Putting the Sourcerer code below in that textfield gets all the right information/names out of the database. However, I cannot get them on separate lines. I get just one dropdown option with the value "Peter Randy Shelly Junior" instead of 4 separate dropdown option lines.

My Sourcerer code looks like:

{source}
<?php
$db =& JFactory::getDBO();
$query= 'SELECT firstname FROM #__my_addressbook where `company`='.$_GET['company'];
$db->setQuery($query);
$result = $db->query();
while ($row = mysqli_fetch_assoc( $result ))
{
echo printf ("%s \r\n", $row["firstname"]);
// My second try to get the next value on a new line
echo nl2br(" \r\n ");
// My third brute force try to get the next value of the while loop on a new line
echo "<br></br><br />";
}
?>
{/source}

I tried a lot with things like \n, \rn, echo "<br>", nl2br() etc. But on all those tries I get a dropdown with all the right SQL information but in only one selection "Peter Randy Shelly Junior" instead of four.

Hope you can help me to get the names vertical, instead of horizontal, in the dropdown selection Values textfield.

With thanks in advance, Nico

Peter van Westen ADMIN

1 year 7 months ago

Peter van Westen ADMIN

1 year 7 months ago

See here for how to work with the Joomla database object:
docs4.regularlabs.com/sourcerer/the-basi...se-internal-database
docs.joomla.org/Selecting_data_using_JDatabase

You shouldn't use the mysqli_fetch_assoc() function.
You can simply ask the DB object to return the data in the way you want. So as an array or object in this case.

In your example it would be something like this:

{source}
   $query = $db->getQuery(true)
      ->select($db->quoteName('firstname'))
      ->from('#__my_addressbook')
      ->where($db->quoteName('company').' = '.$db->quote($_GET['company']));

   $db->setQuery($query);
   $firstnames = $database->loadColumn();

   ... output the array data ...
{/source}

For outputting the data in your array, you could loop through it.

foreach($firstnames as $firstname) {
   echo $firstname . '<br>';
}

Or you could simply do this (which also prevents the <br> tag after the last item):

echo implode('<br>', $firstnames);

Please post a rating at the Joomla! Extensions Directory

Nifa

1 year 7 months ago

Nifa

1 year 7 months ago

Hi Peter,

I find the material you describe quite difficult to use for more complex SQL-queries, but I'm going to study and play with the new information you provided me.

Because I gave you a simple SQL query to clarify my problem as I did not expect that you would make the SQL part completely different and bring new players like QuoteName, loadColumn(), implode() in the game. The SQL query I am dealing with is (certainly for me) a bit more complex. With multiple values. So probably I cannot use loadColumn() for that one.

I'm enclosing the SQL-query in a confidential section below. If you want to push me even further towards the solution based on that query, you will become my hero of the week. If not, it will just take me a few days longer to solve my problem with the new information obtained from you 😉

Confidential information:

(hidden)

Thank you so much for your great extensions and support!
Met vriendelijke groet, Nico

Peter van Westen ADMIN

1 year 7 months ago

Peter van Westen ADMIN

1 year 7 months ago

You can find what you need in the Joomla documentation:
docs.joomla.org/Selecting_data_using_JDatabase
Especially: docs.joomla.org/Selecting_data_using_JDa...se#Multi-Row_Results

Please post a rating at the Joomla! Extensions Directory

Sourcerer

S Sourcerer