The WHERE statement in CF5

In CF4 you were able to use the WHERE statement in a DB Multi Record Loader. But how to do so in CF5?

In SQL the statement is as follows:

SELECT * FROM `web_users` WHERE id IN ( SELECT user_id FROM web_user_usergroup_map WHERE group_id = 11 ) AND block = 0

This works perfectly.

But how to convert this to PHP code? I tried this:

<?php $sql = "SELECT * FROM `web_users` WHERE id IN ( SELECT user_id FROM web_user_usergroup_map WHERE group_id = 11 ) AND block = 0"; ?>

But this only results in all the records.

What am I doing wrong?

Hi geertmartens,

Please see this FAQ

Bob

Thanks for the answer Bob.
Unfortunately I'm not that great in php, so if you have to 'translate' this in my example what would it be then?

Hi geertmartens,

Personally I'd just use a Custom Code action and put the query in there.

Bob

So in 'On submit' I have:

Custom code:

<?php $sql = "SELECT * FROM `web_users` WHERE id IN ( SELECT user_id FROM web_user_usergroup_map WHERE group_id = 11 ) AND block = 0"; ?>

DB Read:

Action code: <empty>
Table name: web_users
Multi read: yes
Fields: email
Conditions: <empty>
Enable relations: no

CSV Export

Table name: web_users

Correct?

Hi geertmartens,

No, use just a Custom code action - but you need to include all the code to execute the query

<?php
$db = JFactory::getDBO();
$query = "
    SELECT ``
        FROM `#__`
        WHERE `` = '' ;
";
$db->setQuery($query);
$data = $db->loadAssocList();
. . .
// possibly more code to tidy up and re-format the data
?>

Bob

Hi Bob

I tried this:

<?php
$db = JFactory::getDBO();
$query = "SELECT * FROM web_users WHERE id IN (SELECT user_id FROM web_user_usergroup_map WHERE group_id = 11);";
$db->setQuery($query);
$data = $db->loadAssocList();
?>

Still every record is shown and not only the records in web_users who have "11" as group_id in the table web_usergroup_map.

What am I doing wrong?

Hi Bob

This did the trick:

$form->data['users'] = $data; // added this to save the data

Thanks!