0

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.

seanr
  • 195
  • 1
  • 4
  • 10

1 Answers1

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.

mostlydev
  • 715
  • 6
  • 16
  • 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. – mostlydev Sep 18 '14 at 17:10
  • Thank you very much @gamut I assumed this would make my code easier to comprehend when looking back. – seanr Sep 18 '14 at 17:27