Page 1 of 1
Count for only users records
Posted: 2019-07-07 02:26
by bruceholt
Hi all,
I have a modified home page which shows a user how many tasks he/she has uncompleted but it is showing all open tasks from all users.
The code I am using is:
Code: Select all
<?php echo sqlValue("select count(1) from tasks WHERE completed IS NULL"); ?>
Can I limit it to only the users records?
Regards, Bruce
Re: Count for only users records
Posted: 2019-07-07 09:35
by pbottcher
Hi,
have a look at the memberinfo and link it to the membership owner table.
Re: Count for only users records
Posted: 2019-07-08 09:26
by bruceholt
Hi pböttcher ,
Sorry I don't understand.
Re: Count for only users records
Posted: 2019-07-09 07:50
by pbottcher
Hi,
in order to get more specific it would be necessary that you explain from whom you would need to show the records.
Do you want to show the user only the amount of his records, or the amount of records of the group the user belongs to?
Re: Count for only users records
Posted: 2019-07-09 08:06
by bruceholt
Hi pböttcher,
I have modified the homepage with and have some text that says "Good evening "user", you have 2 task(s) to complete. You have 1 equipment that the repair or maintenance is not completed and 1 equipment that need attention."
The tasks for example has a check box to indicate if the task is completed or not. If it is not completed then it is null. The count shows how many of those "completed" fields are null. ie - empty. It is showing the count OK but the problem is that it is showing the total in the table instead of just that users records. I hope I have explained what I am trying to do. I am not sure how to user the member records lookup.
Regards, Bruce
Re: Count for only users records
Posted: 2019-07-09 13:39
by pbottcher
Hi,
try
Code: Select all
<?php echo sqlvalue("select count(1) from tasks left join membership_userrecords on recid=table1.id WHERE tasks.completed IS NULL and memberid='".getLoggedMemberID()."'"); ?>
Re: Count for only users records
Posted: 2019-07-10 09:57
by bruceholt
Hi,
Oddly, it is showing all of the uncompleted tasks belonging to other members when logged in as admin but 0 when a user is logged in even though the user has tasks which are not completed.
What I have is:
Code: Select all
<?php echo sqlvalue("select count(1) from tasks left join membership_userrecords on recid=tasks.id WHERE tasks.completed IS NULL and memberID='".getLoggedMemberID()."'"); ?>
Re: Count for only users records
Posted: 2019-07-10 10:22
by pbottcher
Hi, are the task (=records) owned by the user?
It is hard to understand how your setup is.
How do you identify which task belongs to a user?
Re: Count for only users records
Posted: 2019-07-11 08:24
by bruceholt
Hi,
Yes, the records are owned by the user. They are not viewed by anyone else other than the user. The tasks are for the user only to be used as reminders to themselves. The table is tasks. I hope that helps. Thanks, Bruce
Re: Count for only users records
Posted: 2019-07-11 12:17
by pbottcher
Hi,
can you share the table definition of your tasks (especially the primary key).
Re: Count for only users records
Posted: 2019-07-12 08:06
by bruceholt
Hi pböttcher,
Is this what you mean?
Re: Count for only users records
Posted: 2019-07-12 09:29
by pbottcher
Hi,
thanks yes.
Sorry, I had an error in the provided SQL.
Can you try
Code: Select all
<?php echo sqlvalue("select count(1) from tasks left join membership_userrecords on pkValue=tasks.id WHERE tasks.completed IS NULL and memberID='".getLoggedMemberID()."'"); ?>
Re: Count for only users records
Posted: 2019-07-12 11:18
by bruceholt
Thanks very much pböttcher.
That worked perfectly.
Regards, Bruce