Welcome to the MetaMod and Chameleon Support Forums.

Before posting, please check out the FAQs.

helpme

 

Need extra help with your Joomla site? Consider paid Joomla support by the developer of Chameleon and MetaMod.

 

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

Edited By: technetgr
18-Mar-11 22:06:02

technetgr
Beginner Modder
ranks
useravatar
Offline
2 Posts
Administrator has disabled public posting

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

Stephen Brandon
MetaMod / Chameleon developer
If you use MetaMod or Chameleon, please post a rating and a review at the Joomla! Extensions Directory: Chameleon | MetaMod

metamodguy
useravatar
Offline
3314 Posts
User info in posts
Administrator has disabled public posting

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
ranks
useravatar
Offline
2 Posts
Administrator has disabled public posting

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

Stephen Brandon
MetaMod / Chameleon developer
If you use MetaMod or Chameleon, please post a rating and a review at the Joomla! Extensions Directory: Chameleon | MetaMod

metamodguy
useravatar
Offline
3314 Posts
User info in posts
Administrator has disabled public posting

Board Info

Board Stats:
 
Total Topics:
1685
Total Polls:
6
Total Posts:
5912
Total Posts Today:
4
User Info:
 
Total Users:
11509
Newest User:
michael2154
Members Online:
0
Guests Online:
138

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