ListTab "Table Mod"

I am wondering if it would be possible to turn this plugin into a table administrator which would allow users to create/edit/delete records. Of course that such a solution would imply fields to be grouped, i guess.

Does anyone know of a CB plugin and tab to perform simple CRUD operations?

Nuno.

[EDIT]

Doh! It does exactly what i was just suggesting.
Still could not make it work properly, but the main feature i was interested in is already tested

Too bad it cannot be used in listings.

I am also confused about the ending note on changing core tables.

The reason why this is included is to enable one to implement search functionality

Does this make custom SQL queries in CB Lists possible, in spite of the data loss risks involved?

Post edited by: nzimas, at: 2007/01/28 03:22

nzimas wrote:

I am also confused about the ending note on changing core tables.

The reason why this is included is to enable one to implement search functionality

Does this make custom SQL queries in CB Lists possible, in spite of the data loss risks involved?

No, but it eases development of search functionality. To search the tables which contain listtab records, one needs to join it with comprofiler and users tables. If this option is not checked this join will result in a table which contain several columns with same name. This creates a need for explicit naming in the search query and makes constructing queries with php a bit more complex process.

mikko

If you want to be able to edit the jos_comprofiler table, and every other table in Joomla, you need to download and study DBQ. Just search the Joomla Extensions site for DBQ.

Raj

I am very much aware of DBQ, though not familiar with its usage.
My main aim is to let registered users add/edit/delete records to a few tables from the frontend. ListTab already does a superb job towards what i need. However, i am not enough fluent in PHP and SQL to make these lists serchable. Cannot also implemente a pagination system (assuming that ListTab does not provide this feature.

Nuno.

Post edited by: nzimas, at: 2007/01/28 23:04

Making lists searchable can currently be only done by modifying CB core files. I did this modification to advanced search component and currently the component can search and display data from listtab. I included the modified function here.

[code:1]
function usersList($row,$lfields,$lists,$listid, $search_sql, $where, $whattitle){

global $database,$limitstart,$search,$mosConfig_absolute_path, $mosConfig_sitename,$ueConfig,$_POST,$acl,$_REQUEST, $Itemid, $my;
//$search_sql = cbEscapeSQLsearch(cbGetEscaped(trim( strtolower( $search_sql))));
//$option_itemid = "&Itemid=".cbGetEscaped($_REQUEST);
//$ue_base_url = "index.php?option=com_comprofiler&task=usersList&listid=".$listid.$option_itemid; // Base URL string
$ue_base_url = "index.php?option=com_cb_search&task=usersList&listid=".$listid; // Base URL string
$adminimagesdir = "components/com_comprofiler/images/";

require ($mosConfig_absolute_path."/administrator/components/com_cb_search/cb_search.config.php"«»);

$allusergids=array();
$usergids=explode(",",$row->usergroupids);
foreach($usergids AS $usergid) {
$allusergids[]=$usergid;
if ($usergid==29 || $usergid==30) {
$groupchildren = array();
$groupchildren = $acl->get_group_children( $usergid, 'ARO','RECURSE' );
$allusergids = array_merge($allusergids,$groupchildren);
}
}
$usergids=implode(",",$allusergids);
/*
For listtab to work with search, the queery need to join #__users,
#__comprofiler and tables by the listtabs #__comprofiler_listtab_<id></id>
The variable $listtabJoin is used for this purpose

The keywords DISTINCT and GROUP BY are also added to queries, since joining tables
can result in multiple tuples for one user

added by Mikko Rönkkö mikko.ronkko@iki.fi
*/

$database->setQuery("SHOW TABLES LIKE '%comprofiler_listtab%'"«»);
$listtabs = $database->loadResultArray();

$listtabJoin ="";

foreach($listtabs as $listtab){
$listtabJoin .=" LEFT JOIN ".$listtab." on (ue.id=".$listtab.".userid)";

}

/*End of listtab hack*/

// Total
$database->setQuery("SELECT count(u.id) FROM #__users u, #__comprofiler ue WHERE u.id=ue.id AND u.block !=1 AND ue.approved=1 AND ue.banned=0 AND ue.confirmed=1 AND u.gid IN (".$usergids."«»)"«»);
$total_results = $database->loadResult();

// Select query
if($row->sortfields!='') $orderby = " ORDER BY ".$row->sortfields;
$filterby="";
if($row->filterfields!='') $filterby = " AND ".utf8RawUrlDecode(substr($row->filterfields,1));

// Search total
$query = "SELECT count(u.id) FROM #__users u, #__comprofiler ue WHERE u.id=ue.id AND u.block !=1 AND ue.approved=1 AND ue.banned=0 AND ue.confirmed=1 AND u.gid IN (".$usergids."«»)";

$query .= $where;
$query .= " ".$filterby;

if(!$database->setQuery($query)) print $database->getErrorMsg();
$total = $database->loadResult();

if (empty($limitstart)) $limitstart = 0;
$limit = $ueConfig;
if ($limit > $total) {
$limitstart = 0;
}

$query = "SELECT *, '' AS 'NA' FROM #__users u, #__comprofiler ue WHERE u.id=ue.id AND u.block!=1 and ue.approved=1 AND ue.banned=0 AND ue.confirmed=1 AND u.gid IN (".$usergids."«»)";

//$query .= $query2;
$query .= $where;
$query .= " ".$filterby;
$query .= " GROUP BY u.id";
$query .= " ".$orderby;
$query .= " LIMIT $limitstart, $limit";

//echo $query;

$cb_sa_base_url = "index.php?option=com_cb_search&amp;task=advancedsearch&amp;listid=".$listid;

$database->setQuery($query);

//We check the query worked
if ($cbsa_show_sql_error) {
if (!$database->query()) {
echo "<script>alert('".$database->getErrorMsg()."'); window.history.go(-1);</script>\n";
exit;
}
}

$users=$database->loadObjectList();

//Calculate the Total

//This query has been modified to include data from listtab
$query_total = "SELECT *, '' AS 'NA' FROM #__users u, #__comprofiler ue".$listtabJoin." WHERE u.id=ue.id AND u.block!=1 and ue.approved=1 AND ue.banned=0 AND ue.confirmed=1 AND u.gid IN (".$usergids."«»)";
$query_total .= $where;
$query_total .= " ".$filterby;
$query_total .= " GROUP BY u.id";
$query_total .= " ".$orderby;
$database->setQuery( $query_total );
$total_count =$database->loadObjectList();
$results_found = count( $total_count );
?>

<table width="100%" cellpadding="4" cellspacing="0" border="0" align="center" class="contentpane">
<tr>
<td colspan="2"><span class="contentheading"><?php
if ($whattitle == 0){
echo _CB_AS_SEARCH_RESULTS;
} else {
echo getLangDefinition($row->title);;
} ?></span></td>
</tr>
<tr>
<td valign="top" class="contentdescription" colspan="2">
<?php echo $mosConfig_sitename . " " . _UE_HAS . ": <b>" . $total_results . "</b> " . _UE_USERS; ?>
</td>
</tr>
<?php if ($whattitle == 0 ){ ?>
<tr>
<td valign="top" class="contentdescription">
<?php echo _CB_AS_RESULTS_FOUND . ": <b>" . $results_found . "</b> "; ?>
</td>
<form name="adminForm" method="post" action="<?php echo sefRelToAbs($ue_base_url);?>" >
<td valign = "right"><?php echo $lists; ?>
</td>
</form>
</tr>
<?php } else { ?>
<tr>
<form name="adminForm" method="post" action="<?php echo sefRelToAbs($ue_base_url);?>" >
<td style="width:100%;text-align:right;"><?php echo $lists; ?>
</td>
</form>
</tr>
<?php } ?>
<tr>
<td colspan="2">
<a href="<?php echo sefRelToAbs("index.php?option=com_cb_search&amp;task=simple&amp;Itemid=$Itemid"«»); ?>"><?php echo _CB_AS_TITLE; ?></a>
</td>
</tr>
<tr>
<td colspan="2">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td><a href="<?php echo sefRelToAbs($ue_base_url); ?>" onclick="javascript:adminForm.search.value=''"><?php echo _UE_LIST_ALL; ?></a></td>

<td align="right"></td>
</tr>
</table>
<?php
if ($cbsa_page_nav == 0 || $cbsa_page_nav == 2){
if ($results_found > $limit){ ?>
<div style="width:100%;text-align:center;"><?php echo writePagesLinks($limitstart, $limit, $total, $cb_sa_base_url,$where); ?></div>
<hr noshade="noshade" size="1" />
<?php }
}
//Actions
$database->setQuery("select * from #__comprofiler_actions where published=1 and onlist=1 and access<=".$my->gid);
$actions=$database->loadObjectList();
?>
<script type="text/javascript">
function stop(e)
{
if (!e) var e = window.event;
e.cancelBubble = true;
if (e.stopPropagation) e.stopPropagation();
}
</script>


<form method="post" name="adminForm" id="adf">

<?php
if($actions!=null && count($actions)>0){
?>
<table border=1>
<tr>
<?php
foreach ($actions as $action){
?>
<td><input value="<?php echo $action->title;?>" type="submit" onclick="document.getElementById('adf').action='<?php echo $action->url;?>'"/></td>
<?php
}
?>
</tr>
</table>
<?php
}
?>

<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<?php
if($actions!=null && count($actions)>0)
echo '<td><input type="checkbox" name="toggle" value="" onclick="checkAll('.count($users).');" /></td>';
echo "<td><b>".getLangDefinition($row->col1title)."</b></td>\n";
if ($row->col2enabled) echo "<td><b>".getLangDefinition($row->col2title)."</b></td>\n";
if ($row->col3enabled) echo "<td><b>".getLangDefinition($row->col3title)."</b></td>\n";
if ($row->col4enabled) echo "<td><b>".getLangDefinition($row->col4title)."</b></td>\n";
?>
</tr>
<?php
$i = 1;

foreach($users as $user) {
$evenodd = $i % 2;
if ($evenodd == 0) {
$class = "sectiontableentry1";
} else {
$class = "sectiontableentry2";
}
//print $user->name;
if($ueConfig==1) {
$onclick = "onclick=\"javascript:window.location='".sefRelToAbs("index.php?option=com_comprofiler&amp;task=userProfile&amp;user=".$user->id)."'\"";
$style="style=\"cursor:hand;cursor:«»pointer;\"";
}
print "\t<tr class=\"$class\" ".$style." ".$onclick.">\n";
//print $lfields;
$str = null;
if($actions!=null && count($actions)>0)
echo '<td onclick="stop(event)"><input type="checkbox" id="cb'.($i-1).'" name="uid[]" value="'.$user->id.'"/></td>';
eval("\$str = \"$lfields\";"«»);
echo $str. "\n";
print "\t</tr>\n";
$i++;
}
?>
</table>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td>&nbsp;</td>
<td align="right"></td>
</tr>
</table>
<?php
if ($cbsa_page_nav == 1 || $cbsa_page_nav == 2){
if ($results_found > $limit){ ?>
<hr noshade="noshade" size="1" />
<div style="width:100%;text-align:center;"><?php echo writePagesLinks($limitstart, $limit, $total, $cb_sa_base_url, $where ); ?></div>
<?php }
} ?>
</td>
</tr>
</table>
</form>
<?php }
[/code:1]

The reason why you need a hack is because CB plugin api document says the following:
1.9.5 User lists support
This is not supported yet. But will in the future.
1.9.6 User search support
This is not supported yet. But will in the future.

Hope this helps you even a bit.

-Mikko

Not sure if this is the proper thread still. I have joomla 1.0.11 with community builder 1.0.2. I have installed the latest (I think) listtab plugin and I am having 2 issues.

First, While the list tabs I have configured are working well. My other standard (non-listtab) fields are not getting updated when I try to save changes. Don't see any errors.

Second, When I go to edit profile I get errors for each of my listtab fields like:

Notice: Undefined property: stdClass::$cb_listfield in C:\Program Files\Apache Group\Apache2\htdocs\patient\administrator\components\com_comprofiler\comprofiler.class.php(3196) : eval()'d code on line 1

I saw this mentioned in an earlier post but no solution was given that I could see.

Thanks for the plugin.