creating columns

[eluser]Bigil Michael[/eluser]

I would like to add columns to a particular table dynamically ????

any help/suggestions please ......

[eluser]TheFuzzy0ne[/eluser]
Before you start anything, learn how to finish it.

http://lmgtfy.com/?q=dynamically+add+col...javascript

[eluser]Bigil Michael[/eluser]
Thanks for your reply
I want to add columns in mysql table using php not javascript. I will explain my requirement.

Now I am creating an employee management system. Here each member has login and access to certain functions. In order to do that me set privileges.
me use 2 tables

privileges

id privilege
1 create_shift
2 delete_shift
3 .
4 .

member

id name create_shift delete_shift ...........

when one entry is added in privileges table, then that entry is added as a column in member table. I think now the problem is clear...

any suggestions/answer please....

[eluser]TheFuzzy0ne[/eluser]
I still don't quite understand how your system works.

First of all, you should never add anything to two places in your database (unless it's an ID). If you do, you are likely to run into problems with your data integrity.

Also, adding columns with PHP should be avoided also. If you need to dynamically add columns, it's probably an indication that your database structure is wrong. I would instead suggest you look into a way of achieving the same thing, but using rows in a table, instead of columns.

I'm not entirely sure why you need to add the columns dynamically. Can you not just add them anyway, and just keep them empty until you need to store something in them?

In the privileges table and the member table, please could you explain what type of data you plan to store in your create_shit and delete_shift fields? Also, are your IDs just there for the sake of being there, or are they used elsewhere in the app?

If you can explain a bit more about what your app is supposed to do (rather than how it does it), I might be able to offer you a better solution.

[eluser]Bigil Michael[/eluser]
Thanks for Your reply.
I really plan to develop a role model system with 3 levels.
first one is group
second one is team
third one employees

Here employees has login. Admin can manage employees privileges, in order to do that i designed 2 tables admin and privileges. Initially i don't know what are the privileges. So i planned to do like this, when one item is entered in privileges table then one column must add in admin table. then admin can insert 1 or 0 for each privileges in admin table.

any other idea or suggestions please..........

[eluser]TheFuzzy0ne[/eluser]
So why not just add those columns anyway, and set them to 0 by default?

[eluser]Bigil Michael[/eluser]
initially I don't know what are the privileges. then how I create columns initially ??

[eluser]TheFuzzy0ne[/eluser]
Aaah, I think I understand what you mean. Again, it comes down to table structure.

Code:

users

+----+----------+---------------+----
| id | username | password_hash | ...
+----+----------+---------------+----
| .. |   ...    |     ...       | ...

privileges

+----+------+
| id | name |
+----+------+
| .. | ...  |

privileges_users

+---------+--------------+
| user_id | privilege_id |
+---------+--------------+
|   ...   |     ...      |

With the above structure, it's easy to add new privileges and to asign privileges to a user (without adding any extra columns). You could do a separate query to get the permissions, or you could get all of the permissions for a given user in a single query, in which case you'd need to have a little extra logic to counteract the cross join effect.

[eluser]Bigil Michael[/eluser]
thanks for your reply. I will try this idea.