Need extra help with your Joomla site? Consider paid Joomla support by the developer of Chameleon and MetaMod.
- Index
- » MetaMod
- » MetaMod General Support
- » Count rows of two tables and subtract
Count rows of two tables and subtract
Count rows of two tables and subtract
Hello and congratulations on this LIFESAVER of a module...
I would like to ask you about the way we can implement the following rule :
I have a series of forms which I want shown (as links in a module) based on specific usertypes. The two forms I am having difficulty in making them "play nice" are : A."Register a project" B."Submit a course". Basically a user (of type "Author") has to first "Register a project" and THEN be able to "Submit a course".
The way I figured this would work, is by counting the rows of the "Register a project" table of that user and subtracting that from the row count of the "Submit a course" table. If the result is greater than zero, then return "Submit a course" module.
Does this sound right? If yes, what might the syntax be?
Thanx in advance!
Alex
P.S. I am using Joomla 1.5.22 and Metamod 2.6
18-Mar-11 22:06:02
- technetgr
- Beginner Modder
-
-
-
- 2 Posts
Re: Count rows of two tables and subtract
Hi technetgr,
yes, you should be able to do achieve this result.
Is this what you want:
- if user is not logged in, perhaps display a module saying "log in" and end there.
- if user is logged in and "Registered" then check Project table for any projects with the user id of that user
- display a module with link to "Projects" form, and continue...
- if there are no projects for that user, end there.
- count number of projects for that user
- count number of courses for that user
- if number of projects is more than number of courses, display module with link to courses.
To sum up, logged in users always get the option to create a new project.
If they have any projects without a course, then they also get a link to create a new course.
Is that what you're after?
Without knowing the structure of your database and the component that you're using, I can't create any code for it at this time. With some more info we can make some more progress.
Cheers,
Stephen
- metamodguy
-
-
- 3329 Posts
-
Re: Count rows of two tables and subtract
My apologies for not being very clear,
We are building an e-learning academy using three usertypes in our instance,
Guest, Registered, & Author.
Besides the client perspective (shopping cart etc), there is a contributors community (Jomsocial) where users (Usertype:Registered) can automatically come in and submit their media (pics,video) and gain points so they can have free access to the courses of their choice. We also accept registrations for Authors who wish to write course for us. Authors registered with us can do (but are not restricted to) the following :
1.Register a project : This is a separate form (Let's call it "Register Project") where they give us details about their project and we A. manually enter them in our Project Management System (projectfork), and B. manually enter the same details in our internal authors-only Learning Management System (so they can develop and test their courses etc).
2.Submit a course : Once they finish the development of their course, authors can package it and send it to us for review. This is again done with another form (lets call it "Submit Course").
However an author should not be able to see "Submit a course" unless he has actually completed a "Register Project" form.
Off to the components:
Usertype "Registered" is an automated registration using JomSocial & the standard Joomla registration. In order to elevate priviledges to author we do so manually after the user has submitted a specific author registration form (let's call it "Register author" - it is a moderated registration so this process is manual on purpose).
The form component for all three forms (Register Author, Register Project, Submit Course) is jforms (http://jforms.mosmar.com/). Their respective database tables are (jforms_7124c, jforms_645ea, jforms_7124c) Every form has a different table created in the joomla database, where one of the fields is "uid" (which is the joomla user id).
So here is what we (ideally) like to do :
- count number of times the logged in Author has his "uid" mentioned in "jforms_645ea"
- count number of times the logged in Author has his "uid" mentioned in "jforms_7124c"
- if [# of uid in jforms_645ea]-[# of uid in jforms_7124c] > 0 then return 1 (where 1 is module id which contains option "Submit Course").
- if [# of uid in jforms_645ea]-[# of uid in jforms_7124c] =< 0 then return 2 (where 2 is module id which does not contain option "Submit Course").
I hope I haven't bored you with my mumblings...
Cheers (and once again, congratulations on a fantastic module),
Alex
- technetgr
- Beginner Modder
-
-
-
- 2 Posts
Re: Count rows of two tables and subtract
Ok most of that makes sense (apart from jforms_7124c, jforms_645ea, jforms_7124c where one of the tables, 7124c, is repeated?).
It comes down to a simple database query. You may need to tweak this a little but it's something like this:
if (MM_USER_AUTHOR) {
$userid = (int)$user->id;
$query = "select ( (select count(*) from jforms_645ea where uid = $userid) - (select count(*) from jforms_7124c where uid = $userid ) )";
$db->setQuery($query);
$result = $db->loadResult();
if ($result > 0) return 1;
else return 2;
}
I think that should work. You might want to double check the table names and the column that holds the user id (is it uid?).
Cheers,
Stephen
- metamodguy
-
-
- 3329 Posts
-
- Index
- » MetaMod
- » MetaMod General Support
- » Count rows of two tables and subtract
Board Info
- Board Stats:
- Total Topics:
- 1694
- Total Polls:
- 6
- Total Posts:
- 5940
- Posts this week:
- 3
- User Info:
- Total Users:
- 7297
- Newest User:
- aliciataylor9
- Members Online:
- 0
- Guests Online:
- 119
- Online:
- There are no members online
Forum Legend:
Topic
- New
- Locked
- Sticky
- Active
- New/Active
- New/Locked
- New Sticky
- Locked/Active
- Active/Sticky
- Sticky/Locked
- Sticky/Active/Locked