How do i insert a row with select statement in PHP?

Question

I'm having trouble inserting with select in PHP.

the statement runs fine in MYSQL
The main problem is on the Select statement in PHP.
I check on database rolename is inserted with the value object
The statement adds new row into "login" with value $login and "rolename" with value from another table which store default rolename hence the select statement.

$db->insert(Common::prefixTable("roles_user"), array("login" =>
$userLogin, "rolename" => $db->query('SELECT REVERSE(SUBSTR(REVERSE(SUBSTR(REVERSE(SUBSTRING_INDEX(REVERSE(option_value),":",1)),2)),4)) FROM '. Common::prefixTable("option").' WHERE option_name = "RolesManager"')));

Please help me check how can I use select statement with insert statement in php.

Thanks

Why even assign it to a variable. How about just an `Insert tblA (col1, ...) select colA, ... from [the rest including join or whatever]` — Drew, Aug 10 '16 at 04:58

score 2 · Answer 1 · edited May 23 '17 at 12:01

2

How do I insert a row with a select statement

You cannot. INSERT is to add a row. SELECT is to return rows. These are two mutually exclusive operations.

Your question is analogous to: How do I write a new line by reading from a page?

Edit: Based on your comments below I think I understand: You are trying to insert a record with information from a select operation. The solution is to put it all in one query, and use the INSERT...SELECT syntax. You would do something such as:

INSERT INTO roles_user (login, rolename)
SELECT '$userLogin' as login, option_name as rolename
FROM option
WHERE ...; /*the condition to meet in option table to select option_name*/
LIMIT 1

You may need to adjust this to fit your application. Take a look at this post.

edited May 23 '17 at 12:01

Community

1
1

answered Aug 10 '16 at 04:48

BeetleJuice

39,516
19
105
165

Beetle it is an orm. You can insert a row using a select stmt you know that. – Drew Aug 10 '16 at 04:49
But i'm able to run the statement in MySQL, i'm sorry my knowledge with php is very limited, is it that you mean, I can't use select with insert statement in PHP? – nicker Aug 10 '16 at 04:50
@Drew don't know what `orm` is. Does it have to do with trolling? @nicker yes that's what I mean. Are you trying to add new data to your table (`insert`) or retrieve data from your table (`select`) ? – BeetleJuice Aug 10 '16 at 04:51
Does what have to do with trolling? – Drew Aug 10 '16 at 04:53
@Drew talking about `orm` – BeetleJuice Aug 10 '16 at 04:54
@Beetlejuice, i'm inserting a new row, where I need to get value from another table, hence the select statement. – nicker Aug 10 '16 at 04:54
@nicker Oh I see. You have an answer from Rakesh Kumar – BeetleJuice Aug 10 '16 at 04:56
@Beetlejuice, Thanks – nicker Aug 10 '16 at 05:04
@nicker I've added a bit to my answer. Good luck. – BeetleJuice Aug 10 '16 at 05:30

score 1 · Accepted Answer · answered Aug 10 '16 at 04:54

You can't use select and insert this way:-

$db->insert("test_user", array("login" => $userLogin, "rolename" => $db->query('SELECT role_name FROM "test" WHERE role_name = "manager"')));

Your select query provide a object, from this object take value of role_name.

$roleData = $db->query('SELECT role_name FROM "test" WHERE role_name = "manager"')

For example if you assign role name to a variable called $role. Use this $role to insert.

$role = $roleData[0]['role_name'];

$db->insert("test_user", array("login" => $userLogin, "rolename" =>$role));

And why you are selecting role_name from query as you already know role name "manager", Use direct query as like:-

$db->insert("test_user", array("login" => $userLogin, "rolename" =>"manager"));

Hi, Rakesh, your logic solve my problem, however, the code wouldn't run, this part `$roleData = $db->query('SELECT REVERSE(SUBSTR(REVERSE(SUBSTR(REVERSE(SUBSTRING_INDEX(REVERSE(option_value),":",1)),2)),4)) AS Val FROM '. Common::prefixTable("option").' WHERE option_name = "RolesManager"'); $role = $roleData[0]['Val'];` — nicker, Aug 10 '16 at 05:02
Solved using `$db->fetchAll()` instead of `$db->query()`. Thanks — nicker, Aug 10 '16 at 05:34

How do i insert a row with select statement in PHP?

2 Answers2