Is it possible to inbed a select statement inside an insert using PDO [duplicate]

Question

I am trying to accomplish an INSERT statement and one of my fields requires me to pull data from the database and use it in my INSERT statement. For Example.

$stmt = $dbh->prepare("INSERT INTO city(id, cityname, stateID) ". "VALUES ('NULL', :city, :stID)");

At this point I would bindValue's and execute. However I want to use a select statement to find the value for stID.

SELECT id FROM state WHERE statename=NY

Do I need to save the query in a variable then execute the INSERT statement with the variable or is there a way to add the select statement inside my insert statement using PDO?

I feel this question is not a duplicate because the questions that are referenced as duplicates did not talk about how to accomplish this task using PDO. I was unaware that select statements can be placed directly inside the VALUES parameter.

@PeeHaa edited question with explanation as requested.

seanr
– seanr

2014-09-18 17:26:08 +00:00
Commented Sep 18, 2014 at 17:26 — seanr
– seanr, Commented Sep 18, 2014 at 17:26
inbed ? is this how you spell embed

meda
– meda

2014-09-18 18:36:37 +00:00
Commented Sep 18, 2014 at 18:36 — meda
– meda, Commented Sep 18, 2014 at 18:36
@meda lol yeah you dont have to pass 5th grade to code.

seanr
– seanr

2014-09-18 18:46:41 +00:00
Commented Sep 18, 2014 at 18:46 — seanr
– seanr, Commented Sep 18, 2014 at 18:46

mostlydev · Accepted Answer · 2014-09-18 17:08:10Z

1

Just nest the SELECT right into place:

INSERT INTO city(id, cityname, stateID) VALUES ('NULL', :city, (SELECT stID from state WHERE stname = :stname LIMIT 1))

Then bind the parameters that are left, i.e., :city and :stname. I'm guessing the remaining table and field names.

answered Sep 18, 2014 at 17:08

mostlydev

7236 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mostlydev Over a year ago

Incidentally, this is just bad form. I can think of a number of reasons why you might want to actually find the state first. The foremost being that it might not (yet) exist in the table. So, you can fetch, create, and then insert your city. This is much more readable as separate statements.

seanr Over a year ago

Thank you very much @gamut I assumed this would make my code easier to comprehend when looking back.

Collectives™ on Stack Overflow

Is it possible to inbed a select statement inside an insert using PDO [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related