Please read and follow the COMMON ISSUES first!
Always add your version information in every new post! Thank you!

Form2Content Forum

Questions and answers for Form2Content, a Joomla CCK.
  1. avit
  2. Form2Content Lite & Pro
  3. Wednesday, September 27 2017, 03:37 PM
  4.  Subscribe via email
Hi there,

i am building a website with multiple users. Every user has the possibility to select articles from a specific category. I used the code for "Database lookup (multi-select) field" from the examples and altered it to my needs.


SELECT id, title
FROM jos_content
WHERE state = 1 AND
catid IN
(
SELECT id FROM
(
SELECT a.id
FROM jos_categories AS a
WHERE a.parent_id > 0 AND
extension = 'com_content' AND
a.published = 1 AND
a.lft >= (SELECT b.lft FROM jos_categories b WHERE b.id = 40) AND
a.rgt = (SELECT c.rgt FROM jos_categories c WHERE c.id = 40)
) tmp
)
ORDER BY title


the code works, but it shows all articles in the category. It would be nice, if the select box would only show articles, the current user has written. is there a way to realize this? Something like WHERE USERID = CURRENTUSER or such. I suck in database stuff btw. :-)

best
AVIT
Responses (5)
Julien Accepted Answer Pending Moderation
Hi Avit,

We have three special parameters that you can use in queries. They are:

{$CURRENT_USER_ID}
{$CURRENT_USER_GROUPS}
{$LANGUAGE}

In this case it's the current user id you are looking for.

best,
Julien
avit Accepted Answer Pending Moderation
Thank youvery much! It works
best, Avit
avit Accepted Answer Pending Moderation
hi, i asked myself if it would be better for my project to use groups instead of users.

This doesnt seem to work (i get 'JHTML Select' not found Error)


SELECT id, title
FROM jos_content
WHERE state = 1 AND created_by
in ( SELECT id
FROM jos_users
WHERE id = '{$CURRENT_USER_GROUPS}'
) AND
catid IN
(
SELECT id FROM
(
SELECT a.id
FROM jos_categories AS a
WHERE a.parent_id > 0 AND
extension = 'com_content' AND
a.published = 1 AND
a.lft >= (SELECT b.lft FROM jos_categories b WHERE b.id = 40) AND
a.rgt = (SELECT c.rgt FROM jos_categories c WHERE c.id = 40)
) tmp
)
ORDER BY title


this works fine, but it would be better to see all articles of the group instead of the user


SELECT id, title
FROM jos_content
WHERE state = 1 AND created_by
in ( SELECT id
FROM jos_users
WHERE id = '{$CURRENT_USER_ID}'
) AND
catid IN
(
SELECT id FROM
(
SELECT a.id
FROM jos_categories AS a
WHERE a.parent_id > 0 AND
extension = 'com_content' AND
a.published = 1 AND
a.lft >= (SELECT b.lft FROM jos_categories b WHERE b.id = 40) AND
a.rgt = (SELECT c.rgt FROM jos_categories c WHERE c.id = 40)
) tmp
)
ORDER BY title


can you tell mel, what i did wrong?
Julien Accepted Answer Pending Moderation
Hi,

{$CURRENT_USER_GROUPS} is a list of id's, it will resolve to something like 1,2,3

You can't use the = operator, you need to use the IN operator instead. Plus you have to enclose the list in parenthesis like this:


WHERE id IN ({$CURRENT_USER_GROUPS})


best,
Julien
Julien Accepted Answer Pending Moderation
Oh and one more thing: The {$CURRENT_USER_GROUPS} gives a list of the id's of the groups the user is a member of, this is not the same as the user Id!
I think you have to join the table jos_user_group_map to go from the group to the user.

best,
Julien
  • Page :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.