Run a query within Custom Code on a form

I have the follwing custom code running in the On Load event for a form:

<table style="width:98%; margin-bottom:30px;">
<tr valign="bottom" style="border-bottom:2px solid #444444;">
<td style="width:20%;" align="left">Company Name</td>
<td style="width:15%;" align="left">Registrant Name</td>
<td style="width:10%;" align="left">Purchases<br />To Date</td>
<td style="width:10%;" align="left">% Goal</td>
<td style="width:10%;" align="left">Points<br />To Date</td>
<td style="width:10%;" align="left">% Goal</td>
<td>Logins</td>
<tr>
<?php
foreach($form->data['VIPList'] as $detail):
?>
<td><?php echo $detail['acct_name']; ?></td>
<td><?php echo $detail['acct_reg_fname']; ?> <?php echo $detail['acct_reg_lname']; ?></td>
<td><?php 
$showpurchase = number_format($detail['acct_purchase_todate'],2);
echo $showpurchase; ?></td>
<td><?php
if ($detail['acct_purchase_quota'] != 0): $purchaseratio = ($detail['acct_purchase_todate']/$detail['acct_purchase_quota'])*100; else: $purchaseratio = 0;
endif;
$showpurchaseratio = number_format($purchaseratio,0);
echo $showpurchaseratio." %";
?></td>
<td><?php 
$showpoints = number_format($detail['acct_points_todate'],0);
echo $showpoints; ?></td>
<td><?php
if ($detail['acct_points_quota'] != 0): $pointsratio = ($detail['acct_points_todate']/$detail['acct_points_quota'])*100; else: $pointsratio = 0;
endif;
$showpointsratio = number_format($pointsratio,0);
echo $showpointsratio." %";
?></td>
<td>
<?php $db =& JFactory::getDBO();
$query="SELECT COUNT(created_by), FROM #__activities_activities WHERE type = 'com' AND package = 'users' AND action = 'login' AND status = 'logged in' and created_by = acct_user_id";
$db->setQuery($query);
$count = $db->loadResult();
echo $count;
?>
</td>
</tr>
<?php
endforeach;
?>
</table>

I want the last column in the table to pull from the query defined by:

<?php $db =& JFactory::getDBO();
$query="SELECT COUNT(*), FROM #__activities_activities WHERE type = 'com' AND package = 'users' AND action = 'login' AND status = 'logged in' and created_by = acct_user_id";
$db->setQuery($query);
$count = $db->loadResult();
echo $count;
?>

I know I am blowing the syntax somewhere, but I can't figure it out. 😶

Hi Rick,

I think that it's the comma here

SELECT COUNT(created_by),

It would be more elegant to get these all before the loop rather than adding multiple MySQL queries - but if it works OK . . .

Bob

Thanks, the comma was the problem. It works if I insert a specific userID in place of "acct_user_id. However, if I use the actual field "acct_user_id", nothing is populated. Debug action doesn't show anything.

Also, I would agree that this query would be better to execute before the loop, but sadly I am not sure how to do it. Well, I can run the query and get something like:

acct_user_id #logins
63 6
64 2
65 0
67 8

But how would I pass off these values to the appropriate rows in the resultant table, during the loop?

Bob,

I solved the issue I was having. I had to change the code from this:

<?php $db =& JFactory::getDBO();
$query="SELECT COUNT(created_by), FROM #__activities_activities WHERE type = 'com' AND package = 'users' AND action = 'login' AND status = 'logged in' and created_by = acct_user_id";
$db->setQuery($query);
$count = $db->loadResult();
echo $count;
?>

to this:

<?php $db =& JFactory::getDBO();
$query="SELECT COUNT(created_by) FROM #__activities_activities WHERE type = 'com' AND package = 'users' AND action = 'login' AND status = 'logged in' and created_by = ".$detail['acct_user_id'];
$db->setQuery($query);
$count = $db->loadResult();
echo $count;
?>

Now, I would still like to know how to run this outside the loop and them pull the values in. Likewise, I am trying to pull the 'lastvisitDate' from #__users for each user, and I am sure I can use this same method to do that. Thanks!!!

Bob,

Just as a follow up - how would I move queries like this outside the loop, while using the appropriate values for each record within the loop?

Rick

Hi Rick,

I think tha if you change this query

$query="SELECT COUNT(created_by) FROM #__activities_activities WHERE type = 'com' AND package = 'users' AND action = 'login' AND status = 'logged in' and created_by = ".$detail['acct_user_id'];

to use GROUP BY

$query="
  SELECT `created_by`, COUNT(`created_by`) 
    FROM `#__activities_activities` 
    WHERE `type` = 'com' AND `package` = 'users' AND `action` = 'login' AND `status` = 'logged in' 
    GROUP BY `created_by` ;
";

that will give you an array of values that you can use in the loop steps. NB Written from memory and will probably need debugging!!

Bob

Thanks for that, Bob! Now, I have the query executing at the beginning of the custom code like this:

<?php
$db =& JFactory::getDBO();
$query="SELECT `created_by`, COUNT(`created_by`) AS logins
FROM `#__activities_activities`
WHERE `type` = 'com' AND `package` = 'users' AND `action` = 'login' AND `status` = 'logged in'
GROUP BY `created_by`";
$db->setQuery($query);
$login_count = $db->loadResult();
?>

How do I match up the count in this with each row in the resulting table? The tables are related on the 'created_by' column. It's like I have to create a join, but I don't know how to do it within this code.

Anyone have an idea on this? I am totally in the dark! 😶

Hi Rick,

In this code

$query="SELECT $db->loadResult(, COUNT(`created_by`) AS logins
FROM `#__activities_activities`
. . .
$login_count = $db->loadResult();

you need to know that $db->loadResult() only returns a single value. I think it will be the first result in this case.

You need an array of results - one for each value of `created_by`

If you check the Joomla! docs for the $db methods here you'll see that $db->loadResultArray() returns the values from a single column, but this still doesn't let us identify the results.

Instead use $db->loadObjectList('key'); this returns the results in an array indexed on the key. In your case the key will be 'created_by' so you can reference the object by this value later.

The query becomes

<?php
$db =& JFactory::getDBO();
$query="SELECT `created_by`, COUNT(`created_by`) AS logins
FROM `#__activities_activities`
WHERE `type` = 'com' AND `package` = 'users' AND `action` = 'login' AND `status` = 'logged in'
GROUP BY `created_by`";
$db->setQuery($query);
$login_count = $db->loadObjectList('created_by');
?>

Then in the body section you will have something like $detail['created_by'] so you can reference the correct value:

$logins = $login_count[$detail['created_by']];
$logins = $logins->logins;

Bob

Oh man!!! Thanks! I finally get it.

The form was using a column named 'acct_user_id', which correlated with the 'created_by' that was called in the code you sent. I realized that all I had to do was substitute 'acct_user_id' for 'created_by' here:

$logins = $login_count[$detail['acct_user_id']];

and it worked perfectly. Thanks SO, SO much Bob! 😀 😀 😀 😀 😀

One other question on this...

I have the following code in a Custom Code action:

<?php
$db =& JFactory::getDBO();
$query="SELECT 25teac_user_usergroup_map.user_id, 25teac_users.name, 25teac_users.id AS userid
FROM 25teac_user_usergroup_map INNER JOIN 25teac_users ON 25teac_user_usergroup_map.user_id = 25teac_users.id
WHERE 25teac_user_usergroup_map.group_id = 9 AND 25teac_users.block = 0 GROUP BY 25teac_users.id";
$db->setQuery($query);
$staff_list = $db->loadObjectList('userid');
?>

I want to step through the resulting array using FOREACH. However, I cannot seem to get the syntax for it correct, and I keep getting errors like:

Warning: Invalid argument supplied for foreach()...

I have tried the following:

foreach($staff_list['userid'] as $detail):

foreach($staff_list->data['userid'] as $detail):

foreach($userid as $detail):

foreach($staff_list.userid as $detail):

Nothing works. Can you please let me know how to reference this?

Then, how do I reference the fields I want in each row of the resulting table?

Thanks!!!!

Well, again - nevermind! I got it!

The FOREACH is:

foreach($staff_list as $detail):

and each line is referenced like:

echo $detail->name;

and

echo $detail->userid;

Sorry to flood the forums with my lack of ability!!!!

Hi Rick,

Well found ;-)

Bob