Substition in a list for Connections

Hi

I would like to obtain a list of users who have connections.
It seems that the table of the connections is 'xxxx_comprofiler_members' and the column is 'accepted'

I try something like this :

((select count(*) from `xxxx_comprofiler_members` AS m where m.`accepted` = '1' ) > 0 ) but the result return all the users.

The same with :

(select count(*) from `xxxx_comprofiler_members`)


Any help ?

Thank you

((select count(*) from `xxxx_comprofiler_members` AS m where m.`accepted` = '1' ) > 0 ) but the result return all the users.

Your filter doesn't include matching their CB row to the connections table row. You need to add the following.


So your query should look like the below for example.


For userlist query the "u" alias is for Joomlas users table and the "c" alias is for CBs _comprofiler table. This allows access to CB and Joomla data.

Thank you for the good answer.
I forget to ask the same request with members who have connections WITH ME (the login member)

So I try your answer with :

SELECT COUNT( * ) FROM `#__comprofiler_members` AS m WHERE m.`referenceid` = '[id]' AND m.`accepted` = 1

but didn't work.

PS : in the table #__comprofiler_members what is the column of the 'login member' : referenceid or memberid

Help.

Best regards.

[id] isn't a substitution. To get the viewing users user id you need to use [user_id]. referenceid is the user who created the connection request. memberid is the recipient of the connection request. There will be 2 rows for a connection reversing those as needed if you're using cross connections.

Very strange :
when i use an user_id who have 'connections' with this select :

SELECT COUNT( * ) FROM `#__comprofiler_members` AS m WHERE m.`referenceid` = 554 AND m.`accepted` = 1

the answer return ALL THE MEMBERS and not only members who have connections with user_id=554.

PS : if the user_id have no connections, the answer return 0 registered. (that's normal)

Enable debug mode and maximum error reporting in Joomla global configuration and debug the query at the bottom of the page in the query log. You can test the query from the log in phpmyadmin directly to help refine your query. referenceid is the user making the connection so you're querying for connections that 554 has connected to not necessarily users who have connected to 554, which if that's what you're wanting you need to use memberid.