Insert into specific table if the user session group

Question

I am trying to insert data into specific table by user session and group.

The tables are users, workers, help.

Structure of table "users":

id | username | password | group
---+----------+----------+---------
1  | john     | 123      | workers
2  | david    | 123      | help

Structure of table "workers" and "help":

id | username | message

I finished login with session pages and include session onto every page. So when the user login it check and display from "user" table session: Hello "user"

The problem is with "insert into" statement, when john or david logs in and then they post something into database.

Which is the best way to create insert into statement so if it's john all insert into will go into "workers" and if it's david all insert into will go into "help"?

Answer 1

This look s like a job for Dynamic SQL

For this example, let's use david

STEP 01 : Look up the id of the username

SELECT id INTO @userid FROM users WHERE username = 'david';

STEP 02 : Create the SQL string to insert based on the id of username

SET @m = 'Whatever my message is supposed to be';
SELECT
CONCAT('INSERT INTO ',B.group,' (username,message) VALUES (''',B.username,''',''',@m,''')')
INTO @dynamic_sql
FROM information_schema.tables A INNER JOIN users B
ON A.table_name = B.group AND A.table_schema = DATABASE()
WHERE B.id = @userid;

STEP 03 : Compile and Execute the SQL

PREPARE s FROM @dynamic_sql;
EXECUTE s;
DEALLOCATE PREPARE s;

or in your case, just use the formatted SQL in PHP

Answer 2

I don't think they're is a good idea to divide all group message in different tables. You should make a table and storage your user_id and group fields, or even better, just the user because users table have the group field, and you can easily join and filter it with this field.

Proposition:

Make a table named message:

CREATE TABLE `message` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(45) DEFAULT NULL,
  `group` varchar(45) DEFAULT NULL, -- remember that you could avoid this field because you have it in `users` table.
  `mdate` datetime DEFAULT NULL,
  `message` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And you just have to make and INSERT INTO this table.

INSERT INTO test.message
    (id,user_id,`group`,mdate,message)
VALUES
    (@id,@user_id,@group,@date,@message);

@ = Your user and message information variables.

And if you have only your user_id and you want to bring all the workers group messages:

SELECT
    u.id,
    u.username,
    u.`group`,
    m.mdate,
    m.message
FROM test.users AS u
JOIN test.message AS m ON (m.user_id=u.id)
WHERE u.`group`='workers'
ORDER BY m.mdate ASC;

Note: Adding indexes on u.group and m.mdate would be helpful.

But, if you want to still do it with your actually design, you can make a compare or switch in Php level for the group, so you can easily know if belongs to workers or help table. Or you can make a Stored Procedure and make the code inside it.