[SOLVED] Calc element to return multiple results

[Mustafa_s]

Hi guys,

I'm new to databases and queries, I'm trying to learn how to display some results depending on a WHERE clause. My database structure is as follows. Basically put I'm telling it to get the comment that matches a variable:

When I run the following code I get the first result of comment "XXXXXXXXX", which works good but how can I get the last result instead? I've been hitting my head against the wall for the past 3 hours trying different combinations with no luck.

$id = '{rowid}';
$db = JFactory::getDbo();
$query = $db->getQuery( true );
$query->select('comment')->from('oyhvg_fabrik_comments')->where('row_id = ' . $db->quote($id));
$db->setQuery($query);
$comment = $db->loadresult();
return $comment;

Any help or guidance is appreciated.

 

[Mustafa_s]

OK I solved it by finding this link: http://fabrikar.com/forums/index.php?wiki/common-php-tasks/

This is the code that worked for me, hope it helps others too:

$id = '{rowid}';
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query
    ->select(array('comment', 'name'))
    ->from('oyhvg_fabrik_comments')
    ->where('row_id = ' . $db->quote($id));
$db->setQuery($query);
$rows = $db->loadObjectList();
 
$list = array();
foreach ($rows as $row)
{
    $list[] = "<li>" . $row->comment . " " . $row->name . "</li>";
}
return "<ol>" . implode($list) . "</ol>";

 

[Bauer]

Just so you know - rather than just stumbling upon some code you could copy and paste that worked -
The problem with your original code was the function used to load the query data -
You were using
$comment = $db->loadResult();
When it should have been
$comment = $db->loadObjectList();
or simpler yet,
$comment = $db->loadColumn();
because you only needed results from one column of the table.
loadResult() returns only the 1st column of the 1st row of the results (as a string - never an array or object). It is intended to be used when you only expect to return a single value from 1 specific column from 1 specific row.

The first "Selecting..." link below is where you'd find the correct loadXxxx() function to use in Joomla 3.x - depending on the circumstance of what you are trying to retrieve.

I still keep these 3 links from docs.joomla.org bookmarked...
Selecting data using JDatabase
Inserting, Updating and Removing data using JDatabase
Joomla API17Platform

As my aging mind keeps playing with me, they are still a frequent reference, and essential to understanding how to use and work with Joomla Databases - complete with simple examples.

Once you have an better understanding of how to interact with Joomla database tables and other aspects of the Joomla API , any customizing done with fabrik will become a breeze.

 

[Mustafa_s]

Bauer thanks for the heads up, I actually used https://docs.joomla.org/Selecting_data_using_JDatabase last night in addition to the Common PHP tasks link, combining the two got me to my result

In any case, you're correct in that those links are invaluable. Practice makes perfect, I'm having fun learning the Joomla API and customization with Fabrik.

This cheat sheet helps too

$db->loadObject() ? returns first row as an object. e.g. $obj->field.
$db->loadObjectList() ? returns resultset as an object. For multiple records.
$db->loadResult() ? returns first field of first row as a value. A single value
$db->loadRow() ? returns the first row as an indexed array.
$db->loadAssoc() - returns first row as an associated array.
$db->loadAssocList() - returns resultset as an associated array. For multiple records.
$db->Execute($sql) - Execute sql that doesn't return anything. Pass sql as parameter.

 

[Bauer]

That's it in a nutshell, thanks. (would make a good mousepad ) ...And glad to help.