Add field to Users table of joomla

Question

Can we add extra field directly to users table? We have one concept like user can get notification or not. Here if we add data to profiles field we can not compare that field directly in SQL query as it stored data in attribs column of user profile table. we are planning to add extra field in users table directly into database. It is valid and it will create any issue in Joomla upgrade.

Our new field will not shown on Joomla admin at where we are adding Users information for User management.

It would be great if anyone can guide over here like what we have to do in this type of functionality.

Answer 1

It's almost always a mistake to change any of core Joomla, as patches and database fixes could override your work. Also, any third party extensions will expect the database structure to be as standard, and so it could cause conflicts there.

Ultimately user_profiles is the right place to put the information.

The only official info I could find on how they expect it to be used is this mention on an adding fields to articles tutorial:

The contents of the extra custom fields need to be stored in the database. Ideally would be if they have their own dedicated database table. This however, requires considerable extension overhead. With Joomla 2.5 is is possible to extend the user profile. The extra fields are stored in the table #__user_profiles. That table is constructed and used in such a manner that it can also hold the extra article fields. (I would like to spark the discussion to evolve __user_profiles into a generic table for expanding core tables.)

My feeling is that the issue here is more likely your mySQL - a question here shows how to join the two tables in your query.

Answer 2

The easiest way is to store this data in #__user_profiles. My advise is to create you own user profile plugin, just copy paste existing Joomla! User plugin and extend XML by your new fields.

The biggest advantage of this solutions is that you can easily get validation and enable/disable this fields in user registration process.

Answer 3

I absolutely agree with Richard B's first two paragraphs, but the parts after that are both outdated and too complicated except for quite skilled administrators (who wouldn't look up this question). So instead of heavily editing that answer (as I started doing, but then felt I was putting words in his mouth), I'll begin by repeating the two starting paragraphs, and go from there:

It's almost always a mistake to change any of core Joomla, as patches and database fixes will delete your work. Also, any third party extensions will expect the database structure to be as standard, and so it could cause conflicts there.

The pre-made jos_user_profiles database table is where your information must go.

1. Traditionally, for a simple purpose as yours you could (mis-/creatively )use the "notes" field that each user had, and use that as a marker/flag.
2. The obvious/classical start is the User - Profile plugin that's delivered with core Joomla, exactly for adding custom user fields. Just activate it and see what it can do; it has a backend where you can make each of those required/optional/disabled, separately while the user first registers, and later while they edit their profile data. If you're a terrible person, you then edit that plugin directly for your purpose, make a backup of it; and when an update inevitably obliterates your work (but NOT your data, safely in jos_user_profiles), you re-install your backup. But if you're a reasonable person, you follow the Creating a Profile Plugin tutorial (replacing each profile5 with a reasonably unique name; e.g., Alfred E. Neumann creates plugin aenprofile from his initials; this naming convention avoids clashes except with say Albert E. Northwood's work). That tutorial shows how to add a variety of fields, from text via dropdowns and a calendar to a 'Accept Terms of Service yes/no' EULA. This is the classic approach; this is the way to go if you have a lot of fields to add and want to control their presentation and so forth. But this is too much work for your purpose I think (you'd have to still add an override via [yourTemplateName] > overrides > com_users to hide it from the users, or less thoroughly via CSS with display:none on the form field ID.
3. However, if your website is updated to Joomla 3.7 and beyond (and it absolutely should be; no excuses, except if you're using dangerously outdated third-party plugins or expensively-developed platform where the contractors disappeared), then you can use custom fields'. They're right there, in the back-end: Click onUsers' and in that dropdown you see Fields and Fieldgroups. The official documentation still lags (only discusses adding them to articles, contacts and contact forms, today 28-Sept-2017) but details how to add and the 15 available types. At first search, Joomlashack has a tutorial explicitly mentioning custom user fields; but there will be more, and it's quite straightforward so the article only boosts confidence, not really knowledge. I've personally not used these (note "custom fields" is WordPress terminology, you may want to search both with and without "custom" in your question).