user5641678 user5641678 - 2 months ago 16
MySQL Question

insert records from one table to another

Hi I am trying to add records from one table to another, once i have added a 'user' record, the table that is being selected contains rows of available security options, and the table that is being inserted to is the child table for the user, detailing security options.

I cam across this code in an earlier post, which i am sure works nicely, however i am trying to modify it so that the values from statement, includes two parts, one from the select query and one which is the key from the master record.#

This is the original code I found from this site:

INSERT INTO def (catid, title, page, publish)
SELECT catid, title, 'page','yes' from `abc`


And this is what I am trying to do with it:

$sql = "INSERT INTO Link_UserSecurity (UserFk, ModuleFk) values ('".$keys["UserPk"]."', SELECT ModulePk from Global_Modules)";
CustomQuery($sql);


And this is the error I am getting:


INSERT INTO Link_UserSecurity (UserFk, ModuleFk) values ('4', SELECT
ModulePk from Global_Modules)


See screenshot for further detail

Obviously I am not concating the from statement properly, but would appreciate any help?

Answer

You can insert the $keys["UserPk"] variable as if it were a constant in the SQL:

$sql = "INSERT INTO Link_UserSecurity (UserFk, ModuleFk) SELECT '{$keys["UserPk"]}' ModulePk from Global_Modules";

Do note that $keys["UserPk"] must be escaped before adding it into the query. In PDO, it would look like this:

$keys["UserPk"] = $pdo->quote($keys["UserPk"]);

$sql = "INSERT INTO Link_UserSecurity (UserFk, ModuleFk) SELECT '{$keys["UserPk"]}' ModulePk from Global_Modules";