Question regarding Database/User/Joomla

Do you have an idea for the Joomla community that you can help implement? Discuss in here.
Forum rules
Global Rules
Posting guidelines for this board <-- please read before posting.

Joomla Idea Pool <-- Add your suggestions for future versions of Joomla and vote on current suggestions.
Locked
Shadowalker125
Joomla! Fledgling
Joomla! Fledgling
Posts: 3
Joined: Wed Jan 21, 2015 10:24 pm

Question regarding Database/User/Joomla

Post by Shadowalker125 » Wed Jan 21, 2015 10:45 pm

This is a concept question to help me out here a little bit. (Note: this is mostly concept due to me being completely new to Joomla, but I still plan on going through with this.)

Is it possible, with native Joomla (or with something like fabrik or component creator) and mysql, to:
  • Have a new table be created in the Joomla database when a user is created
  • Have the user front end access to only that table
  • Have the user be able to edit said table
  • Have a module or component to query and manipulate said data(mostly just numbers)
  • Control access to site(subscription based)
Sort of like a bug tracking system, bug not quite.

A starting point would be very helpful. I'm trying to learn everything I can but without an entry point its pretty scary. :eek:

I have programming skills, am willing to program. Would rather not have to build something from the ground up, but I do have Visual studio/dreamweaver.

User avatar
sitesrus
Joomla! Ace
Joomla! Ace
Posts: 1431
Joined: Mon Nov 12, 2012 10:48 pm

Re: Question regarding Database/User/Joomla

Post by sitesrus » Thu Jan 22, 2015 6:16 pm

I'm a bit confused, you didn't really explain why a new table needs to exist per user and why they need to edit it?

Joomla has an extension directory, it has all kinds of things. It probably has an extension that fits your needs or that may be close enough you could augment with custom plugins (a lot of stuff is pluggable to alter behavior, add features, etc.).

If you did pure custom the simplest would be,

1)Write a plugin (copy existing) that uses the on user register event to create a new database table on successful registration (store user id and other data you need)
2)Use a custom HTML modules to save yourself hassle/headache (create new instances of them)
3)Get a content plugin (free and exists in JED) that lets you write PHP/Javascript/CSS inside modules and articles
4)Use something like jquery data tables plugin to load/modify that specific database table and load it into your custom HTML module, use can edit it and etc. the jquery plugin is really good
5)Store the user id in that db table on creation, that way you can easily get the right database table for that user to change
6)Write a custom HTML module this time using PHP to query this database however you like and display whatever you like

By being able to write PHP in custom HTML modules you can easily poll the Joomla system for a user object, check if they're logged in, and etc. To use datatables and load data dynamically and write to it you'll need a PHP back end.

This is the simplest, least overwhelming approach. And you can use all the necessary logic to restrict access.

I think you should better explain what you're trying to accomplish, what this database table would hold, how users would edit it and why, etc.
I like working with Joomla :)

Shadowalker125
Joomla! Fledgling
Joomla! Fledgling
Posts: 3
Joined: Wed Jan 21, 2015 10:24 pm

Re: Question regarding Database/User/Joomla

Post by Shadowalker125 » Fri Jan 23, 2015 3:50 am

Okay, Explanation time.

I want each user to have their own table because I'm thinking of future scalability. The actual data isn't that much, about 25ish columns, 23 of which are floats 3 digits or less, the others are consist of small text ( < 30 characters). Maybe each user will have a few hundred rows, with a few in the thousands, but with more users I didn't want to have a few million rows in one table (I doubt it will get that high but I like thinking of the future).

If it would be better (or easier) to just have one table the so be it. Just depends on how mysql can handle that much data.

This data is user specific, I don't want them to have access to other tables, not even to look at.

To be honest, I wish the interface could look something like this. Except not a bug tracker, unless it can be modified to fit my purpose.

The purpose is actually pretty specific, and to be simple, its a personal logbook. Other applications already exist, but for Mac and IOS only, and I'm trying to move it to PC and android(eventually).

If you have any specific questions, I'll do my best to answer them. :)

User avatar
sitesrus
Joomla! Ace
Joomla! Ace
Posts: 1431
Joined: Mon Nov 12, 2012 10:48 pm

Re: Question regarding Database/User/Joomla

Post by sitesrus » Fri Jan 23, 2015 4:31 pm

I think having separate tables per user isn't realistic, it also introduces several more unreasonable maintenance and scalability issues. Why would you have 1 million tables just because you have one million users?

Look at joomla's user table, it has X users and it's one table. You too would use one table and a user identifier to identify the user and their data, or use some other kind of ACL to dictate what data access they have or what they can and can't do to other users. Also managing it would be simpler, as an admin you want one table not many....

What you do is create a single table and code a component accordingly. Provide the UI's to interact with the data how you see fit, to restrict access to only a users data, build in ACL and management type actions for admins, and so on.

I strongly recommend you re-think your implementation. With a single table and code you can accomplish whatever it is you need to and it's also easier to manage, maintain, and improve upon in the future.
I like working with Joomla :)

Shadowalker125
Joomla! Fledgling
Joomla! Fledgling
Posts: 3
Joined: Wed Jan 21, 2015 10:24 pm

Re: Question regarding Database/User/Joomla

Post by Shadowalker125 » Fri Jan 23, 2015 9:16 pm

OK, that does make things easier.

So now I just have:
  • Content plugin to write code in modules
  • jquery data tables to display/edit database table(1)
  • use php/css/html to display overview of information for user in a module
I like that alot better. 8)

User avatar
sitesrus
Joomla! Ace
Joomla! Ace
Posts: 1431
Joined: Mon Nov 12, 2012 10:48 pm

Re: Question regarding Database/User/Joomla

Post by sitesrus » Sat Jan 24, 2015 6:25 pm

Ya, you could code a component but for what you're after you can likely get away with doing the adhoc method I outlined via modules and content plugins.

You'll need to PHP saavy though, look up some Joomla docs for whatever access to the joomla framework you need. In your stand alone PHP script for interacting with data tables (look at their ajax example) you'll probably need to load the Joomla framework, connect, and write code accordingly to interface with the db in this one off script.
I like working with Joomla :)


Locked

Return to “Joomla! Ideas Forum”