Mysql – Insert into specific table if the user session group

MySQLPHP

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"?

Best Answer

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

Related Question